SKU & Margin Forecasting
This use case explains how to forecast SKU level revenue and margins for wholesale, distribution and B2B trade businesses in Model Reef.
You will:
Represent product hierarchies and channels in the branch and category structure.
Build drivers for volumes, prices, discounts and unit costs per SKU.
Model gross margin by product, customer or channel.
Connect SKU forecasts to P&L, Cashflow and valuation outputs.
Model Reef is not an inventory planning system. It uses high level SKU and margin assumptions to produce financial statements, cash forecasts and scenarios.
When to use this pattern
Use this pattern when:
You sell many SKUs through multiple channels or customer segments.
Gross margin and mix are critical to performance.
You need to understand the impact of price, discount and cost changes.
You want to link SKU economics to customer, warehouse or portfolio views.
It combines well with:
Inventory Replenishment Cycles
Warehouse Capacity and Logistics Model
Customer Level Revenue Modelling
Build a Unit Economics Model
Architecture overview
SKU and margin forecasting uses:
Structure
Branches for regions, business units or channels.
Categories for product families and SKU groupings.
Volume and price drivers
Units sold per SKU or SKU group.
List prices, discounts and net prices.
Channel or customer specific pricing where relevant.
Cost and margin drivers
Unit cost of goods sold per SKU.
Freight, handling and other variable costs.
Gross margin targets and checks.
Financial outputs
Revenue and COGS by SKU family.
Gross margin by product, customer or channel.
Cashflow and working capital impacts when combined with inventory logic.
Step 1: Decide on SKU aggregation level
Modelling every SKU individually may be unnecessary and hard to maintain. Decide whether to model at the level of:
Individual high value or high volume SKUs.
Product families, lines or brands.
Price bands or margin tiers.
Customer or channel specific bundles.
As a rule of thumb:
Model key SKUs that materially move the P&L individually.
Group long tail SKUs into aggregated buckets with average price and margin.
Define a simple product hierarchy that can be reflected in categories and naming.
Step 2: Create product and SKU structures
Use categories and variable names to represent products, for example:
Revenue - Product Family A - SKU Key 1.
Revenue - Product Family A - SKU Group Tail.
Revenue - Product Family B - SKU Key 2.
COGS - Product Family A - SKU Key 1.
COGS - Product Family B - SKU Key 2.
If you have channel specific pricing or mix, you can set up branches such as:
Region - UK
Channel - Direct
Channel - Distributor
Region - EU
Channel - Direct
Channel - Distributor
Then place SKU revenue variables in the appropriate branches.
Step 3: Build volume and price drivers
In the Data Library, create drivers for each SKU or SKU group, such as:
Units Sold per Period.
List Price per Unit.
Average Discount Percentage.
Net Price per Unit = List Price × (1 minus Discount Percentage).
Volume drivers can be:
Imported from historical sales.
Built from customer level models.
Driven by growth, seasonality or campaign assumptions.
For each SKU revenue variable, define a formula like:
Revenue - SKU Key 1 = Units Sold - SKU Key 1 × Net Price per Unit - SKU Key 1.
This keeps price and volume assumptions separate and easy to adjust in scenarios.
Step 4: Model unit costs and gross margin
Create COGS variables per SKU or SKU group, for example:
COGS - SKU Key 1.
COGS - SKU Group Tail.
COGS - Product Family B.
Link them to drivers such as:
Unit Cost per SKU (landed cost including freight and duties).
Additional variable costs per order or per shipment.
Supplier rebate or discount schemes expressed as effective cost reductions.
Formula examples:
COGS - SKU Key 1 = Units Sold - SKU Key 1 × Unit Cost per Unit - SKU Key 1.
Where you have supplier rebates based on volume or spend, approximate their effect as an adjustment to effective unit cost in the forecast.
Gross margin can then be analysed as:
Gross Margin per SKU = Revenue per SKU minus COGS per SKU.
Gross Margin Percentage per SKU = Gross Margin per SKU divided by Revenue per SKU.
Use dashboards and reports to view gross margin by product family, SKU, customer or channel.
Step 5: Introduce mix and seasonality
To understand mix effects, create drivers that allocate total demand between SKUs or SKU groups, for example:
Mix Percentage - Key SKUs vs Tail SKUs.
Mix by Channel for each product family.
Seasonal multipliers per month or quarter.
Implement total demand drivers such as:
Total Units - Product Family A.
Mix Split - Key 1, Key 2, Tail.
Then compute units per SKU using:
Units - SKU Key 1 = Total Units - Family A × Mix Percentage - SKU Key 1.
Apply seasonality via multiplicative factors that adjust units per period. This lets you see how product mix and seasonality interact in different scenarios.
Step 6: Calibrate to historical data
To ensure the model is credible:
Import historical revenue and COGS per SKU family or SKU where data is available.
Use these to calibrate unit prices, discounts, unit costs and mix drivers.
Reconcile historical periods so that model outputs align with reported P&L.
This gives you a defensible starting point for forward looking SKU and margin forecasts.
Step 7: Use scenarios for pricing, cost and mix strategies
Clone the base model into scenario models to test:
Price increases by SKU family or region.
Changes in discount policies.
Cost inflation or changes in supplier terms.
Shifts in product mix toward higher or lower margin SKUs.
New product introductions or product retirements.
In each scenario, adjust:
Unit price and discount drivers.
Unit cost drivers.
Mix and volume drivers.
Then compare scenarios using:
Gross margin by product, customer or channel.
Total gross profit and EBITDA.
Cash and working capital when combined with inventory models.
Valuation metrics where relevant.
Check your work
SKU and product groupings balance precision with maintainability.
Price, discount and cost assumptions reconcile with historical data and commercial plans.
Gross margin profiles match internal expectations by product and customer.
Scenario results are intuitive to both commercial and finance teams.
Troubleshooting
Related guides
Last updated