Optimising / Simulation / Auto charting

[gloopy]

I have a query about the functionality of excel. I have written as sheet which enables me to calculate costs for certain manufacturing scenarios but need help with identifying the optimal approach.

Imagine the following scenario. I am making widgets via a 5 stage process. Each stage is discreet and each operated in a batch wise approach.

Stage 1: Max throughput = 100 per batch

Stage 2: Max throughput = 400 per batch

Stage 3: Max throughput = 700 per batch

Stage 4: Max throughput = 50 per batch

Stage 5: Max throughput = 600 per batch

For any given number of widgets that are ordered, the number of batches of each stage will vary. For the example above, 25 widgets would mean a single batch at each stage. 50 widgets would also mean a single batch at each stage and hence the total price doesn't really change. On a price per widget basis, the customer get a better deal by ordering 50 as he was previously paying for capacity (when ordering 25) that he wasn't using.

My question is this. Can I use the features of excel to help me select the size of the production run to minimise the price per widget and if so, how? Ideally I would like to be able to plot the price per widget (within a certain window) such that it is easy to see where the optimum is.

For information, the current sheet tells me the number of batches that are required via a userform. I enter the widgets required, number of stages and yield and it tells me (reported to a cell) the total number of batches required. Once the user form is complete, the only variable that I need to plot/vary is the number of widgets required.

Any advise that can be given would be much appreciated. I am quite knowledgable of excel but this one has eluded me for a long time now.

Thanks
Steve

[Kaper]

To see how real price per piece changes I'd start from simple approach (see attachment).
B2:F2 cost of 1 batch at each stage, and next row max batch size
in column A (starting A5) ordered number
then B5 (and copy right and down) formula for batches required at given stage:

=ROUNDUP($A5/B$3,0)

and G5 and copy down unit price:

=SUMPRODUCT(B5:F5,$B$2:$F$2)/A5

now changes in unit price could be observed
may be also additional indicator of price change H6 and copy down:

=G6/MIN(G$5:G5)-1

(negative value and received by conditional formatting green indicates that the price is lower than lowest so far)


From the point of view of primary school mathematics - you can treat this part of question

Can I use the features of excel to help me select the size of the production run to minimise the price per widget

as http://en.wikipedia.org/wiki/Least_common_multiple so for your batch sizes 100 400 700 50 and 600 it is 8400.