Scenario Analysis For SME Retail And FMCG Operators

Introduction

SME retailers and FMCG operators face quick shifts in demand, input prices, and cash flow timing. Scenario analysis helps you stress test your numbers before the pressure hits your bank account. This guide shows how to build a simple yet effective scenario model in Excel.

Why scenario analysis matters for SME retail and FMCG

You deal with seasonality, stock constraints, and supplier changes. Small changes in volume or price hit margins fast. A scenario model shows you the range of outcomes and prepares you for shocks before they arrive.

Core components of a scenario analysis model

1. Revenue drivers: units sold, price per unit, seasonal swings.
2. Cost structure: supplier prices, shipping, wages, rent.
3. Working capital: stock levels, debtor days, creditor terms.
4. Cash flow timing: payment cycles, inventory build, promotions.
5. Scenarios: base case, downside case, upside case.

Step by step walkthrough

Business scenario

Consider a small FMCG retailer selling a three-item bundle for $29. The bundle includes snacks and drinks sourced from two suppliers. The retailer sells 2,000 bundles per month on average.

Note: This scenario is illustrative. Actual figures depend on product mix, supplier terms, and market conditions.

Step 1. Build the base case

Supplier A provides snacks at $9 per bundle. Supplier B provides drinks at $8 per bundle. Lead times average 14 days.

Step 2. Define the key risks

• Seasonality: December volume jumps 25%. March volume drops 15%.
• Supplier price change: Supplier A plans a 6% increase next quarter.
• Demand volatility: Foot traffic shifts when weather changes or a competitor launches a promo.
• Cash flow risk: Inventory build before peak months strains cash.

Step 3. Build the downside case

Assumptions:

• Volume drops 20% to 1,600 bundles.
• Supplier A increases price by 10% instead of 6%.
• Delivery delays force an extra week of stock on hand.

Results:

• New COGS per bundle: $9.90 + $8.00 = $17.90
• Gross margin per bundle: $29.00 - $17.90 = $11.10
• Monthly gross margin: 1,600 x $11.10 = $17,760
• Stock build: extra 400 bundles of inventory funded upfront

Impact: Gross margin falls 26%. Cash outflow rises due to higher inventory.

Step 4. Build the upside case

Assumptions:

• Volume increases 15% to 2,300 bundles.
• Stable supplier prices.
• Faster turnover cuts stock on hand by 20%.

Results:

• COGS stays at $17.00.
• Monthly gross margin: 2,300 x $12.00 = $27,600.
• Lower working capital frees cash.

Impact: Cash position improves. Margin grows due to higher volume.

Building the Scenario Model in Excel

Create a simple three-tab model:

1. Inputs: Price, volume, COGS, stock levels
2. Scenarios: Use Excel's Scenario Manager (Data > What-If Analysis > Scenario Manager) to switch between base, downside, and upside
3. Summary: A dashboard showing key metrics under each scenario, with variance columns

The most important output is the cash flow impact - specifically, how many months of peak cash the business needs to hold to survive the trough.

How to interpret results

Focus on the sensitivity. A 20% volume drop cuts monthly gross margin by more than $6,000. A 10% supplier increase removes another $1 per bundle. Compare these shifts to your buffer and check if your cash reserves or credit lines cover the downside.

Actions operators can take based on the scenarios

• Adjust price: a $1 price rise recovers most of the supplier increase.
• Negotiate supplier terms: push for a two-week extension.
• Tighten stock: reduce slow sellers to cut tied-up cash.
• Plan seasonal orders: pre-order early only for fast movers.
• Run small promotions to stabilise volume in soft months.
• Track weekly margin and update the model when conditions shift.

Frequently Asked Questions

What data do I need to start a scenario model?

You need your selling price, unit costs, volumes, and stock levels.

How many scenarios should I run?

Run a base case, a downside case, and an upside case. Three is usually sufficient for SME operators.

How often should I update the model?

Update it monthly or when supplier or demand conditions change.

What metric should I watch first?

Watch monthly gross margin and cash tied up in stock.

Does scenario analysis help with pricing decisions?

Yes. It shows how small price moves offset supplier increases or volume shifts.

Conclusion

Scenario analysis helps you see the likely range of your cash flow and margin. You prepare for swings before they hit your store or warehouse.