My math skills are not nearly what you guys possess, but I am willing to work at this if you can help me. Here's my problem; it is optimizing the supply against known demand:
Supply:
machine 1: 10 products per month (we have 5 of these machines) = 50 per month capacity
machine 2: 20 products per month (we have 20 of these machines) = 400 per month capacity
machine 3: 4 products per month (we have 10 of these machines) = 40 per month capacity
Demand:
Product batch a: size=1,000 due-date= month 7
Product batch b: size=2,000 due-date= month 4
Product batch c: size=1,200 due-date= month 5
Assume we start at month 1, so something due month 7 has 7 full months of process time.
Assume no penalty for completing a batch before the due date.
Assume machines are fungible and batch can be run on multiple machines types.
Need to back-solve for optimal start month for each product batch, and show where we are short capacity and need more machines. I can do it manually by making start/end month the same, then "pulling" each project start month backward and try to smooth the demand to fit within capacity, but once I get over a few batches in the pipeline, it becomes untenable.
Would like to figure out an algorithm or equation that I can put into MS Excel to do this.
You just need to compare the cumulative amount needed at each month with the amount you can produce. At month $4$ you need $2000$, but can only produce $490$ per month for a total $1960$. You are short $40$. At month $5$ you need $3200$ and can only produce $2450$ so you are short $750$ At month $7$, you need $4200$ and can only produce $3430$ so are short $770$ Your biggest shortfall per month is the month $5$ one, so you need an additional $150$ per month production capacity.