How is projected inventory calculated?

Closing inventory = opening inventory + production plan - demand forecast, and each month opens with the previous month's closing stock. Mowers open the plan with 1,500 units; making 1,200 against demand of 800 in November closes the month at 1,900. A negative closing inventory means demand is not covered that month.

What does days of supply mean here?

Closing inventory divided by the month's demand per working day. 1,900 mowers against 800 units of demand in a 20-day month is 1,900 / 40 = 47.5 days, above a 40-day maximum, so the row says Above target. Set the minimum and maximum on Setup to match your inventory policy.

How does the template check line capacity?

Each row's load is production divided by the good rate (run rate x yield). Available hours are working days x hours per day x uptime. In April the example's Line A needs about 313 hours against 299 available, 105 percent, so the heat map turns red and the peak tile says Over capacity.

How is the Capacity column related to the Effective Capacity calculator?

Both compute good capacity = output per hour x scheduled hours x uptime x yield. Mowers at 15 units per hour on Line A in a 20-day month of 16-hour days with 85 percent uptime and 98 percent yield give 15 x 320 x 0.85 x 0.98 = 3,998 good units, the most the line could make if it built only mowers.

Is the revenue line a financial plan?

No. Revenue is demand forecast x average price per unit, in the currency label you set, so finance can see the volume plan in money. It does not deduct shortages, discounts or costs; the Method sheet lists these limits.

Can I add families, lines or months?

The Setup lists and the Plan table are Excel tables that grow as you add rows. The dashboard blocks cover two lines, four families and twelve months as shipped; the Method sheet explains how to extend them, and a warning appears if your lists are longer.

Planning and Supply Chain

S&OP Monthly Plan Excel Template

Balance twelve months of demand, production, inventory and line capacity by product family in one workbook, and see where the plan runs short, builds stock above your policy or overloads a line.

Overview

This S&OP template is a working Excel model for S&OP leads, planners and plant managers who need one agreed plan per product family each month. It ships with a fictional plan for a garden-equipment maker with four families on two assembly lines from November 2026 to October 2027: mowers pre-built through the winter above the days-of-supply policy, Line A over capacity in April, and leaf blowers running short in October while Line B still has hours to spare.

Each family-month projects inventory from the opening stock: plus production, minus demand. Days of supply divide the closing stock by the month's demand per working day and are compared with your minimum and maximum, so the plan shows both stock-outs and cash tied up in inventory. Production is converted into line hours with each family's run rate and the line's yield and compared with the hours the line can deliver after uptime.

The dashboard shows a utilisation heat map by line and month, the monthly balance of demand, production and inventory, revenue at the forecast, and a family summary with months below and above policy and months short. A family's capacity on its line is the same calculation as the Effective Capacity calculator on this site: rate times scheduled hours times uptime times yield.

What this template includes

Setup sheet for the currency, first plan month, minimum and maximum days of supply, utilisation thresholds, production lines (hours per day, uptime, yield) and product families (line, run rate, price, opening inventory)
A 12-month plan calendar with working days per month
Plan table with one row per family per month: demand forecast and production plan in; opening and closing inventory, days of supply, inventory status, line hours, capacity and revenue out
A Check column that flags families missing from Setup, months outside the calendar, duplicates and production above the line's capacity
Dashboard KPI tiles for the revenue plan, production over demand, peak line utilisation and family-months short
Line utilisation heat map by month, a monthly balance of demand, production and inventory, and a family summary with months below and above policy
Charts of demand, production and closing inventory by month and of each line's utilisation against your thresholds
Method sheet with every formula, and a Verify sheet that re-checks them against an independent reference and the Effective Capacity calculator
CSV with live formulas of the Plan table for any spreadsheet app; the dashboard, charts, dropdowns and self-check are in the .xlsx

Suggested use case

Use it to prepare the monthly S&OP meeting: test a level or chase production plan for seasonal demand, see which months need overtime or pre-build, check inventory against a days-of-supply policy and agree one plan that sales, operations and finance can sign off.

How to use it

On Setup, set the currency, first plan month and your days-of-supply policy, then list your lines and product families with their run rates, prices and opening inventory.
On Plan, type the working days of each month, then give every family one row per month with the consensus demand forecast and the production plan.
Read each row's closing inventory, days of supply and status, and fix anything the Check column reports.
On the Dashboard, look at the utilisation heat map and the family summary; move production between months until no line is over capacity and inventory stays inside policy.

Frequently Asked Questions

How is projected inventory calculated?: Closing inventory = opening inventory + production plan - demand forecast, and each month opens with the previous month's closing stock. Mowers open the plan with 1,500 units; making 1,200 against demand of 800 in November closes the month at 1,900. A negative closing inventory means demand is not covered that month.
What does days of supply mean here?: Closing inventory divided by the month's demand per working day. 1,900 mowers against 800 units of demand in a 20-day month is 1,900 / 40 = 47.5 days, above a 40-day maximum, so the row says Above target. Set the minimum and maximum on Setup to match your inventory policy.
How does the template check line capacity?: Each row's load is production divided by the good rate (run rate x yield). Available hours are working days x hours per day x uptime. In April the example's Line A needs about 313 hours against 299 available, 105 percent, so the heat map turns red and the peak tile says Over capacity.
How is the Capacity column related to the Effective Capacity calculator?: Both compute good capacity = output per hour x scheduled hours x uptime x yield. Mowers at 15 units per hour on Line A in a 20-day month of 16-hour days with 85 percent uptime and 98 percent yield give 15 x 320 x 0.85 x 0.98 = 3,998 good units, the most the line could make if it built only mowers.
Is the revenue line a financial plan?: No. Revenue is demand forecast x average price per unit, in the currency label you set, so finance can see the volume plan in money. It does not deduct shortages, discounts or costs; the Method sheet lists these limits.
Can I add families, lines or months?: The Setup lists and the Plan table are Excel tables that grow as you add rows. The dashboard blocks cover two lines, four families and twelve months as shipped; the Method sheet explains how to extend them, and a warning appears if your lists are longer.