Bad forecasts.
Cocoa price spikes.
Packaging shortages.
Co-man downtime.
So many other bad things can happen.
Scenario planning allows companies to be more prepared. From “we didn’t see it coming” to “we had a playbook.”
In this article, I cover the following:
- Why scenario planning matters (what the big names say)
- Practical 7-step guide
- Make it real in 48 hours
Why scenario planning matters (what the big names say)
- Volatility is not done with us. McKinsey notes CPG input costs remain elevated and food commodities face rising drought exposure, keeping pressure on margins and supply. For example, this means that corn, sugar, cocoa, palm oil, and packaging volatility is the new normal.
- Leaders must balance resilience and efficiency. Deloitte sees continuing instability and longer transit times. Planning needs options as opposed to a single plan.
- Scenario planning is a core discipline—not a one-off. Gartner and HBR frame scenarios as an ongoing capability that reveals critical uncertainties and drives better decisions (and warn that scenarios fail when they’re disconnected from choices).
- Mature, tech-enabled planning pays. Accenture reports companies with more advanced, simulation-ready supply chains are materially more profitable.
Practical 7-Step Guide
Let’s see how we follow this guide with an example at a snacks company.
Step 1 — Create a baseline scenario
Snack example: Your tortilla chip line targets +6% growth vs. AOP (annual operating plan) with historic upticks around sports weekends.
Actions to take:
- Pull the last 24–36 months of weekly shipments & orders by SKU/channel into Excel or Power BI.
- Compute a clean baseline: remove promo uplifts, outlier service cuts, and supply-bound weeks.
- Document assumptions on one sheet: demand growth %, mix %, scrap %, OEE, line speed, lead times, key commodity costs (corn, oil, film), FX, and freight.
Step 2 — Model best, worst, most-likely cases
Snack example:
- Best: retailer promo acceptance +10%, oil costs -8%, co-man uptime 99%.
- Most likely: AOP demand, current cost curve.
- Worst: sunflower oil +20%, metallized OPP film short, fryer capacity -10% from downtime.
Actions to take: - Use Data → What-If Analysis → Scenario Manager.
- Define changing cells: demand growth, promo uplift, line speed/OEE, yield, scrap, material costs, lead times.
- Name each scenario and show summary to a results sheet (units, revenue, COGS, gross margin, OTIF).
Step 3 — Add sensitivity analysis (find the levers)
Snack example: Which hurts more: cocoa +15% (for dipped pretzels) or a 2-pt OEE loss on the bake line?
Actions to take:
- Build a tornado: one variable at a time, measure impact on Gross Margin % or Case Fill. Use a one-variable Data Table to sweep each input across a range (e.g., cocoa +0 to +40%, OEE −3 to +3 pts).
- Rank by impact. Focus meetings on the top three levers.
Step 4 — Build flexible input sections
Snack example: Drop-downs to switch oil source (sunflower ↔ palm), packaging spec (25µ ↔ 20µ film), or co-man site.
Actions to take:
- Create an Inputs tab with named ranges and Data Validation drop-downs (Yes/No, Source A/B, Lead-time bucket).
- Keep units explicit (USD/MT, cases/hour, % yield). Color code input cells; lock the rest.
Step 5 — Run what-if analysis across scenarios
Snack example: Two-way tradeoff of price change vs promo depth on a flagship chip SKU: What combination protects volume and margin if corn spikes?
Actions to Take:
- Set up a two-variable Data Table (rows = price change %, columns = promo depth %) feeding a demand model (include simple elasticity).
- Summarize outcome by volume, GM%, and retailer OTIF risk in a Pivot Table.
- Add a capacity guardrail: if required fryer hours > available hours, flag red.
Step 6 — Integrate financial impacts
Snack example: Cocoa shock for chocolate-covered pretzels: margin dips and cash tied in WIP rises. Cocoa has recently seen extreme swings.
Companies like Hershey publicly discussed material impact and the value of hedging. Use scenarios to quantify gross margin exposure and the benefit of price/package actions.
Actions to Take:
- Build a mini P&L (profit and loss statement) linked to scenarios: Revenue → Trade → COGS (materials, conversion, freight) → GM (gross margin) → OTIF penalties → EBITDA proxy.
- Add working capital: average inventory (by Raw material/ Packaging/ Finished Goods) and cash impact.
- Show variance to AOP for each scenario; that’s the language finance and leadership need.
Step 7 — Visualize outcomes for decisions
Snack example: One Power BI page: slicers for scenario, channel, SKU family; visuals for case fill, capacity utilization, GM%, inventory $, and a variance waterfall vs. AOP.
Actions to Take:
- In Excel: create a dashboard with Sparklines, a GM% vs. OTIF scatter, and a variance waterfall.
- In Power BI: build a model with Scenario and Assumption tables; relate to Fact tables (Orders, Shipments, Standard Costs). Use bookmarks to flip between Best/Worst/Most-Likely.
Make it real in 48 hours (sprint checklist)
Day 1
- Extract 2–3 families (e.g., tortilla chips, sandwich cookies, chocolate snacks).
- Build baseline and three scenarios in Excel Scenario Manager.
- Run a tornado to identify the top three value drivers.
Day 2
- Add the mini P&L and inventory cash model.
- Build a one-page dashboard (Excel or Power BI).
- Decide triggers & actions:
- Trigger: OPP film lead time > 8 weeks → Action: switch to 20µ, cut low-velocity SKUs, raise safety stocks for top sellers.
- Trigger: Cocoa > $8,000/MT for 4 weeks → Action: immediate price/package review, accelerate hedges, pause minor promos on chocolate snacks.
- Trigger: Fryer utilization > 92% for 3 weeks → Action: reduce changeovers, prioritize hero SKUs, shift promos to baked lines.
Summary
Scenario planning in Excel and Power BI gives you options to be more prepared and make better decisions. It’s important to define clear triggers, financial impacts, and pre-agreed actions so your team shifts from reacting to deciding.
