Retailer/Channel Margin Modelling
Retailer and Channel Margin Modelling
This guide explains how to model retailer, channel and customer margins for consumer goods, FMCG and CPG manufacturers in Model Reef.
You will:
Represent retailers, channels and key customers in the branch structure.
Combine SKU level cost and price with channel specific terms.
Model discounts, rebates, logistics and trade spend by channel.
Produce margin views by retailer, channel, region and portfolio.
Use scenarios to test commercial strategies and mix shifts.
When to use this pattern
Use this pattern when:
You sell to multiple retailers, channels or regions with different terms.
You need margin visibility beyond aggregate COGS.
You want to understand which channels and customers create or destroy value.
You need scenario driven commercial strategy tools.
It builds on:
SKU Manufacturing Cost Model
Promotional Lift and Discount Impact
Multi Channel Revenue Forecasting style patterns
New Product Launch Forecast
Architecture overview
Define retailer, channel and customer branches
In the branch tree, structure commercial branches such as:
Commercial Group
Channel - Grocery
Retailer - Retailer A
Retailer - Retailer B
Channel - Convenience
Channel - Online Direct
Channel - Foodservice
Under each retailer or channel, you can create SKU group or category branches, or simply use variable naming and categories to distinguish SKUs.
Ensure this structure can be reconciled with your internal sales and margin reporting.
Attach prices and commercial terms
In the Data Library, create drivers for each SKU, retailer and channel such as:
List Price per Unit or per case.
Off Invoice Discount Percentage.
Invoice Price per Unit.
Promotional Discount patterns where you combine this with promotional modelling.
Volume Based Rebates and Year End Bonuses.
Scan or activity based allowances where applicable.
Create Revenue variables at the appropriate level, for example:
Revenue - SKU X - Retailer A.
Revenue - Category Y - Online Direct.
Use formulas such as:
Gross List Revenue = Volume × List Price.
Invoice Revenue = Volume × Invoice Price.
Rebates and Allowances = Volume × Rebate per Unit plus fixed amounts.
Net Revenue = Invoice Revenue minus Rebates and Allowances.
This gives you a gross to net waterfall at retailer and channel level.
Model logistics, freight and service costs
Create Opex and COGS variables for channel specific costs, for example:
COGS - Logistics - Grocery Channel.
Opex - Merchandising and Field Sales - Retailer A.
Opex - E Commerce Fulfilment - Online Direct.
Attach drivers such as:
Cost per Case or per Unit moved.
Cost per Delivery or per Order.
Merchandising hours and wage rates.
Field sales team size and travel costs allocated by retailer or channel.
Compute these costs using the same volume drivers used for revenue so that:
Logistics cost reflects actual and forecast throughput.
Service cost reflects actual effort required per channel or retailer.
Link SKU cost, trade spend and channels for full margin
Combine this pattern with SKU Manufacturing Cost Model and Promotional Lift and Discount Impact by ensuring that:
SKU cost per unit is available for every SKU in each channel.
Trade spend and promotional costs are allocated to the relevant retailer and channel.
Logistics and service costs are included to derive fully loaded margin.
Use custom reports and dashboards to show margin by:
Retailer and channel.
Category and SKU group.
Region and territory.
Portfolio and customer segment.
You can then answer questions such as:
Which retailers and channels generate the highest contribution per unit?
Which product and channel combinations are margin dilutive?
What is the impact of mix shifts across retailers and channels?
Use scenarios for commercial strategy and mix shifts
Clone the base model into scenario models to test:
Price increases or decreases by retailer and channel.
Changing trade spend and promotional intensity.
Shifts in mix across retailers, channels and regions.
Logistics network and service model changes.
Customer rationalisation or portfolio focus strategies.
In each scenario, adjust:
Price and trade term drivers.
Promotional calendars and depth.
Volume and mix drivers.
Logistics and service cost drivers.
Compare scenarios using:
Net revenue and fully loaded margin by retailer and channel.
Contribution margin at category and portfolio level.
Cashflow and working capital implications including receivables and inventory.
Valuation impact where channel strategy materially changes future cashflows.
Check your work
Commercial terms match actual contracts or current trading conditions.
Logistics and service cost drivers reflect how cost is actually incurred.
Gross to net waterfalls reconcile to historic margin analysis.
Scenario outcomes are realistic and actionable for sales and finance teams.
Troubleshooting
Related guides
Last updated