Need to calculate minimum lot quantity with a changing variable cost

Hi all,

New to posting here, mainly because I can usually solve excel problems on my own but this one has me stumped. My IT department, nor my purchasing department, has it figured out yet either.

I work at a production plant near Toronto, Canada and we produce parts for automobiles. Some of these parts are now service-only parts, meaning our customers will buy the parts in small quantities. Because of the costs of setting up machines (presses, welding stations), I have been tasked to find a minimum lot charge and/or quantity to break even. Essentially, I need to find for internal purposes, the number of parts that we break even at; and I need to find a sum of money we charge our customer for failing to meet the minimum number of parts in an order.

Open up the attached spreadsheet, and I will explain (it's somewhat complex) RunQtyExample.xlsx

Column B: The number of parts that will be made in a run (from the time the machine is set up for that part, until the machine is changed over to a different part)
Column C: The price we charge per part. This could change but likely will not.
Column D: Variable cost per part based on columns J through Y.
Column G: Fixed total cost of setting up machine, based on formula
Column H: Break-even quantity. The number of parts that will cause the cell in column E >= zero.
Column I: A minimum charge we should charge our customers for not meeting the break-even quantity.
Columns J through Y: Production factors. Some parts are made successively on more than one machine. If you would like an explanation of the factors:

• SU Time: Length of time (in hours) it takes to setup machine
• SU Crew: Number of people it takes to setup machine
• SU Rate: Labour rate for setup people
• PPH: Parts per hour, production rate
• Crew: Number of people operating machine
• Labour: Labour rate for machine operators
• Burden: Fixed cost per hour regardless of whether machine is running or not

Now I realize that I can just use Solver to change Column B one at a time, but we have hundreds of service parts with this customer so I am hoping to find a more mobile solution in a formula. So the task is finding a way to calculate a value, output into column H, that when entered into column B, causes Profit (column E) to equal or be greater than zero.

Please note that column D already includes the setup cost. This can be removed however if it makes it easier to calculate (the variables are in column G). Column G was mainly being used as reference but if it can be used in the formula I think you would need to remove the variables from column D.

If this was not clear enough, let me know what parts aren't clear and I will re-explain.

I thank you with cookies and internets in advance :-)

- Brendon