Move volume if machine is overloaded to other machines based on their free capacity

This took some setup.

I altered the formulas for total to add the normal production run for machine A and subtract those units diverted to the other machines. For the other machines, these units are added to the workload. Units diverted are shown as positive numbers.

I set up some formulas in columns P:S. Cell Q10 is the total demand. Cells Q13:Q16 are the capacities bringing the machines on line with overtime in B, A, C order (most "bang for the buck" based on increase in capacity by adding overtime).

These numbers populate a "Truth Table" as to whether a particular machine is scheduled for overtime. The results are also reflected in row 16. Row 15 uses the True/False to determine if it should add the OT capacity for the machine.

The shortfall is calculated by comparing the revised machine capacity with the required capacity (accounting for diversions in/out). This is "bottomed out" at zero - a machine can't have a negative shortfall.

Cell Q17 is the sum of the shortfalls.

Next I engaged solver. Solver is an add-in that is a standard part of Excel. Go to File->Options->Add-ins and use the drop-down for Excel Adins. Click on Go and then check the box for solver. Dover shows up on the data tab,.

The setup for solver is shown in the attached picture.

What it says is Make cell P17 (total shortfalls) zero by changing cells G6, G8 and G9. With the following constraints: only whole units can be diverted.

Solver will give you an answer. A lot depends on the initial conditions. With the original problem I started with all zeros in cells. I accepted this answer and then upped the required production of product A to 150 and re-solved.

Note: if you have more product than the combined capacity of all three machines at OT, Solver will not find and answer and tell you so.