How to Build a CPG Demand Planning Template in Excel
If you are a growing CPG brand, demand planning is the difference between having cash in the bank and having a warehouse full of expired product. While purpose-built operations software is the ultimate goal, most brands start by building a demand planning template in Excel. Here is how to build one that actually works for grocery retail.
The Anatomy of a CPG Demand Plan
A generic e-commerce forecast just draws a straight line based on historical Shopify sales. A CPG demand plan must account for the "lumpy" nature of wholesale grocery. Your template needs three core components:
1. The Baseline Forecast
This is your "everyday" velocity. How many units do you sell per store, per week (UPSPW), when there are no promotions running? You calculate this by taking your historical depletion data from distributor portals (like UNFI Clearview) or retailer portals (like Whole Foods VIP) and smoothing out the spikes.
2. The Promotional Lift (Events)
This is where CPG forecasting gets complicated. If you run a TPR (Temporary Price Reduction) at Sprouts in October, your sales might spike by 300% for those four weeks. Your Excel template must have a separate row to layer these promotional events on top of the baseline forecast.
3. New Distribution (Pipeline)
If your sales team just landed a 500-store authorization at Target launching in March, you need to forecast the massive pipeline fill required to stock those shelves, plus the ongoing baseline velocity for those new stores.
Connecting Demand to Supply (MRP)
A forecast is useless if it doesn't tell you what to buy. The second tab of your Excel template must be a Material Requirements Planning (MRP) calculator.
You must translate the finished goods forecast into raw material requirements. If you forecast selling 10,000 cases of granola in November, your MRP tab must explode that BOM (Bill of Materials) and calculate that you need 5,000 lbs of oats, 1,000 lbs of honey, and 10,000 empty pouches.
Crucially, the MRP tab must factor in supplier lead times. If honey takes 6 weeks to arrive, and your co-packer needs 2 weeks to produce, you must order the honey 8 weeks before the November forecast hits.
When Excel Breaks
This template works beautifully for 5 SKUs and 2 distributors. It breaks catastrophically when you hit 20 SKUs, 5 distributors, and multiple co-packers. The manual data entry required to update the baseline forecast from five different retailer portals becomes a full-time job.
When you reach this breaking point, it is time to move to an operations platform like Guidance, which automatically connects your sales orders, promotional calendar, and BOMs to generate dynamic purchase recommendations.
Frequently Asked Questions
What is UPSPW?
Units Per Store Per Week. It is the standard metric used in grocery retail to measure the velocity (sales speed) of a product on the shelf.
How do I forecast a new product launch?
Without historical data, you must use 'like-item' forecasting. Look at the historical velocity of a similar product in your portfolio (or syndicated data for a competitor's product) and apply that curve to the new SKU.
What is a pipeline fill?
The initial massive order required to stock the shelves and distribution centers when you launch in a new retailer. Pipeline fills are often 3x to 5x larger than the ongoing weekly replenishment orders.
Help shape the commercial release.
A working prototype validated Guidance's core operating model. The commercial rebuild is translating that proof into production-ready workflows with CPG design partners.
Apply as Design Partner →Safety Stock, Reorder Points, and Simple Inventory Math
You need a rule to turn demand into order quantities. Use a basic safety stock formula: Safety Stock = z * sigma_daily * sqrt(Lead Time in days). Pick z from your target service level: 90% z=1.28, 95% z=1.645. Then calculate Reorder Point = Average Daily Demand * Lead Time + Safety Stock. Example: average daily demand 50 units, lead time 14 days, sigma_daily 12, target 95%: SS = 1.645 * 12 * sqrt(14) ≈ 74; ROP = 50*14 + 74 = 774 units.
For periodic review (weekly or biweekly replenishment) use Order-Up-To level: S = Average Demand during review + Lead Time + Safety Stock. Convert weeks to days consistently. Track Days of Cover = On-hand / Average Daily Demand; flag SKUs under your minimum coverage threshold (for example 21 days). Put these formulas into Excel as named ranges so planners can change z or lead time centrally and update all SKUs in one recalculation.
Rolling Forecast Cadence and Version Control in Excel
Run a 52-week rolling forecast updated every week and a 12-month rolling forecast updated monthly. Lock the last 4 weeks as actuals, then have a baseline column for weeks +1 to +52. Each update: paste new actuals, then do your re-forecast only for weeks beyond your freeze window (commonly 4 weeks). Keep a "Version" column with a timestamp and initials so you can trace changes. In Excel use tables and structured references so your formulas follow new rows and columns automatically.
For collaboration avoid multiple files named Final_v2_FINAL.xlsx. Instead keep a single master workbook on a shared drive, use one tab per scenario (Baseline, Best, Worst), and a hidden tab called Change Log. Log entries with DATE(), USER(), SKU, old value, new value, and reason. That audit trail makes it practical to explain why a forecast shifted ahead of an internal review or a conversation with your contract manufacturer.
Scenario Modeling and Sensitivity Analysis
Build scenario sheets that run the same SKU-level model with modifier multipliers. Create three scenarios: Base (1.0), Promotion High (1.2–1.5), and Supply Shock (0.6–0.8). Use Excel formulas to calculate scenario demand: ScenarioDemand = BaselineDemand * Multiplier. For promotional planning, layer event weeks with a temporary lift and a decay factor for cannibalization. Example: event lift 35% in week of promo, -10% in following 2 weeks. Put probability weights on scenarios to compute Expected Demand = Σ(probability_i * scenario_i_demand).
Run simple sensitivity tables for lead time and service level. Use Data Table or manual runs: vary lead time ±25% and z ±0.5 to see inventory and working capital impact. Capture results as two KPIs per scenario: Weeks of Cover and Inventory Value (Unit Cost * On-Hand). That tells you whether a tighter service level or longer lead time will blow your cash. Keep scenario assumptions visible on each tab so buyers and founders don’t argue over hidden numbers.
Demand Signals, Data Hygiene, and Practical Smoothing
Decide which signals feed your baseline: POS sell-through, distributor sell-in, or your shipped orders. For retailers use sell-through where available. Clean the data: remove returns and one-off spikes from trade shows, or Winsorize top and bottom 1% of observations. Use a 3-month moving average for stable SKUs and exponential smoothing for volatile SKUs. EWMA formula: Forecast_t = alpha * Actual_{t-1} + (1-alpha) * Forecast_{t-1}. Typical alpha values: 0.2 for stable, 0.4–0.6 for trending SKUs.
Track forecast performance by SKU monthly: MAPE = AVERAGE(ABS((Actual - Forecast)/Actual)). Measure bias as SUM(Forecast - Actual) / SUM(Actual). Flag SKUs with MAPE > 30% or bias beyond plus/minus 10% for investigation. In Excel, color-code these KPIs and roll up to category level. Good data hygiene and clear signal rules reduce the noise you try to forecast and cut the time you spend firefighting stockouts or overstocks.