Optimising / Simulation / Auto charting

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