Sales & Operations Planning: Demand Plans by Product Family, Capacity Gaps, Inventory Projections, Level vs Chase & the Monthly S&OP Pack

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Roll ten SKU forecasts up into three product families with SUMIFS and COUNTIF, total each family over six months in one SUMPRODUCT and measure its share of the volume: the level at which every S&OP meeting talks.
• Turn the statistical forecast into the consensus demand plan, measure how much it moved since last month's cycle and flag every month whose change is big enough to be discussed in the meeting.
• Convert the family demand into assembly hours with SUMPRODUCT, compare them with the available hours month by month, measure utilisation and the hours that are missing, and calculate the utilisation of the whole half-year correctly.
• Price three ways of covering May's missing 605 hours – overtime, Saturday shifts and building loungers ahead – in whole units, apply each option's limit with IF and pick the cheapest option that is actually possible.
• Project the closing stock of the Chairs family month by month, convert it into days of cover of the coming month's demand and check every month against a minimum and maximum target band.
• Set a level production rate that ends June on the target stock, project the stock it builds up, cost the level plan and compare it with a chase plan that follows demand month by month.
• Apply upside and downside demand factors to the required hours, add up only the monthly shortfalls with a SUMPRODUCT condition, count the months over capacity and price what the upside would cost in overtime.
• Turn the volume plan into net revenue, cost of goods and margin per month, give every month a red/amber/green flag against the margin targets and chart the volume by family for the executive meeting.