Integrated S&OP Planning Workbook — Demand, Capacity, Inventory and Margin in One Excel Model

Description
Built for manufacturers running an ERP with no advanced planning module — where the monthly cycle still means exporting to Excel and rebuilding the same analysis by hand. The workbook connects the whole planning chain in one file.
A demand forecast and twelve months of history drive statistical safety stock, calculated from demand variability and lead-time variability at each item's target service level. Net requirements become a production plan that respects minimum order quantity and lot size. That plan converts into machine hours and is tested against standard and maximum capacity, reporting overload hours and the additional shifts needed to close the gap. Projected stock, forward coverage and reorder points follow, with shortfalls and thin coverage flagged automatically.
Forecast quality is measured alongside it — weighted MAPE and bias across the history window — so you can see whether the plan rests on a forecast worth trusting.

A financial reconciliation restates the same volume plan in money: revenue and standard-cost COGS from the demand plan, gross margin against target, budget variance by month, working capital tied up in projected inventory, and what it would cost to buy the machine hours the plan needs. Revenue follows demand rather than production, so building stock never flatters the P&L. A product-family view splits the horizon by family.

A dashboard summarises the cycle on one page: load against capacity, the resources that constrain it, items at service risk, margin against target, and supporting charts.

Everything recalculates from five input sheets. Input cells are colour-coded, worked example data is included, and the methods, formulas and assumptions are documented inside the workbook.

Pure Excel — no macros, no add-ins, no system connection. Sized for 60 planning items, 20 resources and an 18-month horizon.

This is rough-cut capacity planning: it sizes the capacity gap, it does not re-sequence production.