> ## Content Index
> Fetch the complete content index at: https://www.theplanningnerd.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# How to Build a Supply Plan in Excel
- URL: https://www.theplanningnerd.com/how-to-build-a-supply-plan-in-excel/
- Published: 2026-09-10T12:00:40.000Z
- Updated: 2026-09-14T13:21:20.000Z
- Description: A supply plan doesn't need to be complicated to be useful. At its core, it's a simple balance: opening inventory, plus what's coming in, minus what's going out, rolled forward week by week. Here's how to build one that actually holds up.
- Author: Shane Fernando
- Tags: Supply

A supply plan doesn't need to be complicated to be useful. At its core, it's a simple balance: opening inventory, plus what's coming in, minus what's going out, rolled forward week by week. Here's how to build one that actually holds up.

## The basic structure

One row per SKU, one column per week, with these calculated rows underneath each SKU:

- **Opening inventory** — closing inventory from the previous week
- **Demand** — pulled from your demand plan
- **Supply / production** — what's due to arrive or be produced that week
- **Closing inventory** — `Opening + Supply - Demand`
- **Weeks of cover** — closing inventory divided by average forward demand

That closing inventory row is the engine of the whole model — every week's closing balance becomes next week's opening balance, so build it as a proper rolling formula, not a manually typed number.

## Bring in your real constraints

This is where a supply plan earns its keep. Add:

**Lead time** — how far in advance supply needs to be triggered before it actually lands as inventory. If your lead time is four weeks, a shortage you spot in week 3 is already too late to fix by ordering more.

**Capacity limits** — a maximum achievable production or purchase quantity per week, per line or per supplier. Compare planned supply against this limit and flag anything that exceeds it.

**Minimum order quantities** — if a supplier won't ship less than a set amount, your model needs to round up to that, not assume you can order exactly what's needed.

## Build in exception flags, not just numbers

A 52-week by 200-SKU grid is unreadable if you have to scan every cell manually. Add conditional formatting or a simple formula-driven flag column that highlights:

- Any week where closing inventory goes negative (a shortage)
- Any week where weeks of cover exceeds a threshold you set (excess stock risk)
- Any week where planned supply exceeds known capacity

This turns the model from "here's a wall of numbers" into "here's what actually needs your attention this week" — which is the difference between a spreadsheet and a planning tool.

## Add scenarios once the base model works

Once your core balance is solid, a simple way to add scenario flexibility is a single input cell — a dropdown for Base / Upside / Downside — that adjusts the demand pull-through by a percentage. This lets you stress-test the plan against a demand swing without rebuilding the whole model each time.

## Common mistakes to avoid

**Treating the plan as static.** A supply plan is only useful if it's a living document, refreshed regularly as actuals come in and lead times move. A supply plan you built once and haven't touched in six weeks is closer to a historical record than a planning tool.

**Ignoring the frozen horizon.** Not every week in your plan should be equally open to change. The weeks within your lead time are largely locked in — changing them is expensive and disruptive. Build your model to visually distinguish the frozen near-term from the flexible further-out weeks, so you don't casually "adjust" something that's actually already committed.

**No visibility into why a number changed.** When a supply figure moves between versions of the plan, it's worth being able to trace why — a capacity change, a new demand number, a supplier delay. Without that, a supply plan becomes something people stop trusting.

---

*Next in this series:* *How to build a 13-week supply plan* *— or see this structure built out fully on real data in the* *Nerd Foods dataset* *, which includes production plans, capacity and supplier lead times.*