Master Production Schedule (MPS) Projected Available Formula
Master production schedule formula in Excel: projected available balance and MPS quantity by week, with safety stock and lot sizing. Free master production schedule template.
=LET( prevPAB, [@PriorWeekPAB], demand, MAX([@Forecast], [@CustomerOrders]), mps, IF(prevPAB - demand < [@SafetyStock], CEILING.MATH(demand - prevPAB + [@SafetyStock], [@LotSize]), 0), prevPAB + mps - demand )
Projected available balance carried over from the previous week.
Master Production Schedule (MPS) & Material Requirements Planner
Skip writing this formula by hand — download the pre-built Master Production Schedule (MPS) & Material Requirements Planner workbook with live structured tables, KPI dashboard, and sample data.
The Problem with Standard Excel Approaches
Planning production from a sales forecast alone causes stockouts in some weeks and excess inventory in others. The MPS projected available balance shows exactly when a production run is needed.
How Modern Dynamic Arrays Execute This Formula
Lays out weekly buckets with forecast and confirmed customer orders.
Carries the projected available balance forward week by week.
Schedules an MPS run whenever the balance would drop below safety stock.
Rounds each run up to the production lot size.
Common Excel Errors & How to Fix Them
Solution: Point prevPAB at the previous week's balance only.
Solution: Default lot size to 1 when no batch minimum applies.
Skip building this from scratch.
This formula is already fully wired, error-checked, and formatted with Big-4 print-ready A4 reports inside Master Production Schedule (MPS) & Material Requirements Planner.