• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Distributing Demand as per Selling Price

G

gauravchawla

New Member
Hi All,

I am desperately looking a way to solve this in excel:


There are 3 companies in the market. C1,C2,C3. Each company has multiple products. Some products are unique and some are sold by other companies also. Sample portfolio: C1 - P1,P2,P3 ; C2- P1,P2,P5 ; C3- P1, P6,P4. The companies are free to keep any selling price for their products but sales would happen only to the one having lowest price. And once its stock is finished, the balance demand would be fulfilled by the company selling at next higher price. The data set as made in excel is below:


(Company,Product,SP,Demand, Stock on hand)- in each column

C1,P1,3,50,10

C1,P2,9,39,40

C1,P3,8,12,8

C2,P1,5,50,45

C2,P2,7,39,40

C2,P5,11,61,75

C3,P1,4,50,42

C3,P6,15,35,10

C3,P4,12,14,15


So as per above- for P1: C1 has the lowest price (3) so 1st sales will happen to C1 (10 units) followed by C3 (price=4) for balance 40 units.


It will be really helpful if anyone can suggest me how to do it in excel using a formula. As I change the SP,Demand,Stocks on hand: the sales should distribute accordingly.


Thanks in advance.


Regards,

Gaurav
 
Hi gauravchawla,


Firstly welcome to the Chandoo's forum.


I would like to workout this problem. Kindly tell me the lay out of the output you would like to see. I worked out a similar problem earlier that might be of any interest to you it was related to stock distribution:


http://chandoo.org/forums/topic/using-excel-to-analyze-and-find-largest-surplus-in-inventory-with-multiple-locat


However i am interested more in the layout of the output you want from this data.


Regards,

Faseeh
 
Hi Faseeh!


Thanks for the welcome and going through the problem .

You can download the excel layout from below:-


https://docs.google.com/file/d/0B-9U8UiqGA_GNFdlS1kybTVoT1U/edit?pli=1


I need the output that simply shows how much sales would happen for each product in each company. I have added remarks about the logic that should drive the formula in column 'F'.

Went through the link you shared for a similar problem but that doesn't seem to have solution to what I am looking for.


The approach that I am working on is as below-

1. Find the lowest sales price for each product. ( I can assume there cannot be similar selling price offered by 2 companies for same product (to make the problem simpler) but if excel permits, I would love to have a logic for : If the 2 companies offer same Sellling price, sales should be halved between the two)

2. Then sales should happen against the company offering lowest sales price to an extent of stock on hand.

3. Check for unfulfilled demand. If unfulfilled demand is >0, look for next higher Selling Price.

4. Sales should happen to company offering next higher selling price to an extent of stocks on hand...and similarly check for unfulfilled demand that should go to next higher SP.


Regards,

Gaurav
 
Hi gauravchawla,


I have just download file so i will revert i came across anything i am unable to understand, Stay in touch.


Regards,

Faseeh
 
Hi Faseeh !


Surely. If possible, you can also drop me an email at mech.gaurav@gmail.com for a quickest reply.


Thanks.


Gaurav
 
Hi gauravchawla,


I have worked out a file but i am doubtful regarding the desired distribution you have shown in your sample file. You have said that:


1. go for the lowest price.

2. move to next higher price if stock out for the last lowest.

3. move till all stock out.


But when i see your file:


Total demand for p1 is 150, stock is 97, you sold only 50 (why not all 97)?

Total demand for p2 is 78, stock is 80, you sold only 39 (why not all 80)?


..or there is something i am unable to understand.


see this workout: http://dl.dropbox.com/u/60644346/Distribution%20Problem.xlsx


Its 12:15 AM now, see you tomorrow! :)


Regards,
 
Hi Faseeh!


Quick one- Actually demand is as follows:


P1- 50

P2- 39

P3- 12

P4- 14

P5- 61

P6- 35


As I had mapped against each pair of (company,product); it got interpreted as individual demand. So please just refer to the demand as above and do not take the aggregate.

I hope that clarifies your doubt. I missed out to mention it explicitly at first place.


I am going through your solution and would get back to you.

Thanks for taking out time!


Regards,

Gaurav
 
Hi Faseeh,


Wondefull! I went through the solution and it is exactly what I was looking for.

Moreover, I could edit the excel you shared as per the demand mentioned in my previous post and the solution seems to work perfect!!


https://docs.google.com/open?id=0B-9U8UiqGA_GQm5nY3BzZnh0eXM


But the next challenge I am facing is to how to fetch the values from the solution matrix (yellow highlighted - in worksheet 'final') and put them in column F in sheet one to make the front end simple (although we have complex calculations in the backend :))


To take it to closer to reality, just wanted to check, what will happen when the Selling Price offerred by 2 companies for a same product is same? Which company will have the sales in that case. Ideally demand should be halved between the two.


Regards,

Gaurav
 
Hi gauravchawla,


Its great to know that it worked for you!!


Obviously we can add such a scenario-based solution to our sheet, but what will you do if you encounter such a situation that is the question?? I thought you will be adding some sought of "Reputation Points" to prefer one company over the other if they have same price for a product.But as you said that "Demand must be halved" so let me try it with the sheet and fetch those values back to the original sheet! :)


Regards,

Faseeh
 
Hi gauravchawla,


I think its done! The problem of Equal price is settled (but with a trick) and the values are brought back to the original sheet as well, see this one:


http://dl.dropbox.com/u/60644346/Distribution%20Problem%20V%202.0.xlsx


Regards,

Faseeh
 
Thank you so much Faseeh!


I am going through the solution. Apparently it seems you have still considered the aggregate demand. As I corrected in my last to last post - The demand is at product level (and not company-product level). So the demand for P1 is actually 50. That means all the companies together cannot sell more than 50 units in the market.


I will get back to you once I fully go through the solution.


BTW, the formulae you have used in the sheet, I couldn't have even dreamt off. ;-p


Regards,

Gaurav
 
Hi gauravchawla,


I think you should place somewhere else these demands not in the table in main sheet because once prices are examined then you will put in these demands against each company, am i right??


I am waiting for feedback and hopefully it will suffice the need. and stay in touch with the forum, i can remember but can't recall the exact thread(s) that contains giganitc formula even i never dream t of! :D


Is this your email ID:
Code: 
gewbruldvu@hotmail.com
??


Regards,

Faseeh
 
@Faseeh


Hi!


About long formulas I remember two:


a) an oldchippy's one, that clearly outperforms any other I've seen before: 1881 characters

http://chandoo.org/forums/topic/match-2-column-with-diffrent-name-format#post-21160

=IF(IF(OR(LEFT(A3,FIND(" ",A3)-1)=LEFT(B3,FIND(" ",B3)-1),LEFT(A3,FIND(" ",A3)-1)=IF(IFERROR(FIND(" ",B3,FIND(" ",B3)+1),0)=0,RIGHT(B3,LEN(B3)-FIND(" ",B3)),MID(B3,FIND(" ",B3)+1,IFERROR(FIND(" ",B3,FIND(" ",B3)+1),0)-FIND(" ",B3)-1)),LEFT(A3,FIND(" ",A3)-1)=IF(IFERROR(FIND(" ",B3,FIND(" ",B3)+1),"")="","",RIGHT(B3,LEN(B3)-IFERROR(FIND(" ",B3,FIND(" ",B3)+1),"")))),1,0)+IF(OR(IF(IFERROR(FIND(" ",A3,FIND(" ",A3)+1),0)=0,RIGHT(A3,LEN(A3)-FIND(" ",A3)),MID(A3,FIND(" ",A3)+1,IFERROR(FIND(" ",A3,FIND(" ",A3)+1),0)-FIND(" ",A3)-1))=LEFT(B3,FIND(" ",B3)-1),IF(IFERROR(FIND(" ",A3,FIND(" ",A3)+1),0)=0,RIGHT(A3,LEN(A3)-FIND(" ",A3)),MID(A3,FIND(" ",A3)+1,IFERROR(FIND(" ",A3,FIND(" ",A3)+1),0)-FIND(" ",A3)-1))=IF(IFERROR(FIND(" ",B3,FIND(" ",B3)+1),0)=0,RIGHT(B3,LEN(B3)-FIND(" ",B3)),MID(B3,FIND(" ",B3)+1,IFERROR(FIND(" ",B3,FIND(" ",B3)+1),0)-FIND(" ",B3)-1)),IF(IFERROR(FIND(" ",A3,FIND(" ",A3)+1),0)=0,RIGHT(A3,LEN(A3)-FIND(" ",A3)),MID(A3,FIND(" ",A3)+1,IFERROR(FIND(" ",A3,FIND(" ",A3)+1),0)-FIND(" ",A3)-1))=IF(IFERROR(FIND(" ",B3,FIND(" ",B3)+1),"")="","",RIGHT(B3,LEN(B3)-IFERROR(FIND(" ",B3,FIND(" ",B3)+1),"")))),1,0)+IF(IF(IFERROR(FIND(" ",A3,FIND(" ",A3)+1),"")="","",RIGHT(A3,LEN(A3)-IFERROR(FIND(" ",A3,FIND(" ",A3)+1),"")))="",0,IF(OR(IF(IFERROR(FIND(" ",A3,FIND(" ",A3)+1),"")="","",RIGHT(A3,LEN(A3)-IFERROR(FIND(" ",A3,FIND(" ",A3)+1),"")))=LEFT(B3,FIND(" ",B3)-1),IF(IFERROR(FIND(" ",A3,FIND(" ",A3)+1),"")="","",RIGHT(A3,LEN(A3)-IFERROR(FIND(" ",A3,FIND(" ",A3)+1),"")))=IF(IFERROR(FIND(" ",B3,FIND(" ",B3)+1),0)=0,RIGHT(B3,LEN(B3)-FIND(" ",B3)),MID(B3,FIND(" ",B3)+1,IFERROR(FIND(" ",B3,FIND(" ",B3)+1),0)-FIND(" ",B3)-1)),IF(IFERROR(FIND(" ",A3,FIND(" ",A3)+1),"")="","",RIGHT(A3,LEN(A3)-IFERROR(FIND(" ",A3,FIND(" ",A3)+1),"")))=IF(IFERROR(FIND(" ",B3,FIND(" ",B3)+1),"")="","",RIGHT(B3,LEN(B3)-IFERROR(FIND(" ",B3,FIND(" ",B3)+1),"")))),1,0))>=2,TRUE,FALSE)


b) mine with a modest 659 count

http://chandoo.org/forums/topic/help-with-tat-calculation#post-15579

=IF(WEEKDAY(A2,2)=7,0,IF(O(A2-INTEGER(A2)<TIMEVALUE("07:00"),A2-INTEGER(A2)>TIMEVALUE("22:00")),0,TIMEVALUE("22:00")-(A2-INTEGER(A2))))+(INTEGER(B2)-1-INTEGER(A2)+1-1-IF(IF(7-WEEKDAY(A2+1,2)<=INTEGER(B2)-1-INTEGER(A2)+1-1,7-WEEKDAY(A2+1,2),0)>0,INTEGER((INTEGER(B2)-1-INTEGER(A2)+1-1-IF(7-WEEKDAY(A2+1,2)<=INTEGER(B2)-1-INTEGER(A2)+1-1,7-WEEKDAY(A2+1,2),0))/7)+IF(MOD(INTEGER(B2)-1-INTEGER(A2)+1-1-IF(7-WEEKDAY(A2+1,2)<=INTEGER(B2)-1-INTEGER(A2)+1-1,7-WEEKDAY(A2+1,2),0),7)=0,0,1),0))*(TIMEVALUE("22:00")-TIMEVALUE("07:00"))+IF(WEEKDAY(B2,2)=7,0,IF(O(B2-INTEGER(B2)<TIMEVALUE("07:00"),B2-INTEGER(B2)>TIMEVALUE("22:00")),0,(B2-INTEGER(B2))-TIMEVALUE("07:00")))


Regards!
 
Hi SirJB7,


:p I think gauravchawla will need some sleeping pills after reading your last post! :D Lolz


I remembered oldchippy's but was unable find it! how did you do that? :)


Regards,
 
Hi SirJB7, That was one of a formula...For a while, I thought my screen has got corrupt.. :)


Hi Faseeh,


gewbruldvu@hotmail.com is not my id. What happened?

I went through the solution. It is working perfectly fine but only when the Selling prices are different. May be I am not able to understand the logic you have put when prices would be equal. And the idea behind demand getting halved between 2 companies when they are offering same price for a product is to give them equal opportunity to sell. But never mind, we can ignore that fact, if you think it will make the logic too complex as this would suffice my need at elementary level..


Please let me know if you happen to work on it further and find a solution to this same price issue. To keep it open, any logical solution would do. Be it "reputation points" you mentioned, or demand getting distributed equally between the two companies.. as long as we can incorporate it in the formula, any logical solution to this case would do.


Once again, thank you for taking out time for this. Much appreciated!!


Regards,

Gaurav
 
@Faseeh


Hi!


Does this paragraphs sound familiar to you?:


-----

"Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords "oldchippy longest formula" and press Search button. You'd retrieve many links from this website, like the following one(s), maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


http://chandoo.org/forums/topic/match-2-column-with-diffrent-name-format"

-----


Well, then just follow the instructions, my friend! Ha ha ha...


Regards!


PS: Just advise if any issue :=)
 
Hi gauravchawla,


The logic that i followed in my last posted workbook was that if you differentiate two prices by as little as 0.0001 then excel will consider them two different prices!! Check the last file again. (Since you are not concerned with revenue generated or something similar this minute diff. should not bother you i assume)


And yes i will be working over this again to and will revert with (hopefully) a final answer stay in touch with this thread. :)


@SirJB7,


:D


Regards,

Faseeh
 
Hi Faseeh,


We can be black and white in this case.. Even if the price difference is as little as 0.0001, we will assume, sales will go to the one offerring lower price. I agree with you that practically this will not influence any customer to buy a lower price product, but for the model I am making, we need not to put any tolerance limit for the price change.


Eagerly waiting for your final worksheet!


Regards,

Gaurav
 
@gauravchawla

Hi!

As Cat Stevens once said (if unknown, google it!)... "just relax, take it easy, you're still young... just sit down, take it slowly...".

Regards!


@Faseeh

Hi!

Are you going to take much longer? What are you doing right now, buddy?

:)

Regards!
 
Hi gauravchawla,


Sorry for the day-long wait please see this file and am waiting for feedback:


http://dl.dropbox.com/u/60644346/Distribution%20Problem%20%20v3.xlsx


@SirJB7,


I think its finally done. :)


Regards,

Faseeh
 
Hi Faseeh,


No worries!! Rather, thank you for taking it that far... I am testing it using my actual data that has got like 5-6 companies and 50 products.. Will get back to you shortly..


Actually, my actual data is in the form of the table. So entering the data manually in the matrix would be tedious. I will use the formula you suggested in your previous excel(s) to make a matrix out of the table.


Regards,

Gaurav
 
Hi Faseeh,


You rock man!!

The solution you gave is perfect!!! I could incorporate it to my actual datasheet successfully! :)


Thanks a ton for helping me out!


Regards,

Gaurav
 
You must log in or register to reply here.
Back
Top