Budgeting - Allocating sales at customer and product level

[Flemming]

I have a budget at customer level, and a similar budget at product level. Now I need to combine these into one budget, this may sound easy but it is certainly not. The end result must be a matrix with approx. 15 customers in column 2, 3, 4 etc. and approx. 50 rows with different product lines.

There are 3 constraints:
1) There is a budget figure for each product line. The sum of all customers purchase for each product line is therefore fixed.
2) There is a budget figure for each customer. The total purchase for each customer is therefore fixed.
3) The customers had different preferences and marketshare of each product line in 2005. That is, customer 1 may had 20% market share of product line 5 and only 10% market share of product line 20, because their preferences are different. This pattern resembled by the following matrix must be kept more or less intact (it has to change a little to satisfy constraint 1 and 2).


Market share 2005
Customer 1 Customer 2...........Customer 15
Product 1 20% 10% 30%
Product 2 5% 15% 35%
.......
.......
Product 50 10% 12% 31%


Basically, all I need is to change the percentages from last year into sales figures that satisfy my budget for each customer and for each product line, with as little change in the overall buying pattern as possible.

This sounds like a job for Excel solver, but it reports the number of "adjustable cells" is to big.......

I am running out of ideas here and hope someone can help

Best regards.

Flemming

 

[BrianB]

The easiest way is to make a table of actual sales for the previous year and apply a percentage to make a new table.

However you do it will not be 100% accurate as time goes on - but this would seem better than introducing another variable rate based on a mythical product manufacture/sale .... unless your product is so good you have to ration (?)

In this case you can manually adjust the percentage rates to get the totals you need. Solver might work in this secenario depending on whether you apply the percentage to past sales or new production. In my case this would take longer to do than manually .