Building a Customer and Product Profitability Model in Anaplan: Price Waterfall, Cost-to-Serve, and Margin Scenarios

Most finance teams can tell you revenue by customer. Far fewer can tell you margin by customer after discounts, rebates, freight, returns and support effort. That gap is where unprofitable accounts hide. This tutorial walks through a customer and product profitability model in Anaplan that answers the question at the level where pricing decisions are made.

1. Define the grain before you build

Decide up front what the model must report: customer x product x month is typical. Resist invoice-line grain inside the planning model. Aggregate in your data hub or integration layer and load a summarized transaction set. Keep these lists:

• Customer (with a hierarchy: segment, region, account group)
• Product (family, brand, SKU group)
• Cost Activity (order handling, freight, returns, technical support)
• Scenario (via versions, not a separate list)

If customer x product is sparse, that is fine. Hyperblock handles sparsity well, but avoid dimensions that are never used together in a line item. Split modules by dimensionality instead.

2. Load actuals into a data module

Create a data module DATA Invoice Actuals with Customer, Product and Time. Line items: Gross Sales, Volume, On-Invoice Discount, Off-Invoice Rebate, Returns, Freight Billed. Load with a mapped import, and keep a reconciliation check that totals tie back to the general ledger revenue.

3. Build the pocket-price waterfall

In CALC Price Waterfall, derive per-unit values so they can be compared across customers:

• List Price Revenue = Volume * List Price
• Invoice Price Revenue = Gross Sales
• Pocket Revenue = Gross Sales - On-Invoice Discount - Off-Invoice Rebate - Returns
• Pocket Price per Unit = Pocket Revenue / Volume, guarded with an IF on zero volume

Use a line item for each waterfall step so the dashboard can chart leakage between list and pocket price. Set summary methods carefully: amounts sum, but per-unit prices should use a formula summary (ratio), not an average.

4. Cost of goods and cost-to-serve

COGS comes from a standard cost table by product. Cost-to-serve is harder. Use activity-based drivers:

1. Create a module ASSUMP Activity Rates with the cost pool and total driver quantity per activity, giving a rate per driver unit.
2. Create DATA Activity Drivers by customer: number of orders, number of deliveries, return lines, support tickets.
3. In CALC Cost to Serve, multiply drivers by rates and sum across activities. Keep Cost Activity as a dimension only if users need the breakdown. Otherwise sum with a SUM across the list to save size.

Reconcile total allocated cost to total cost pools. Any difference is a bug, not a rounding issue.

5. Customer margin and ranking

In REP Profitability, calculate Gross Margin, Contribution Margin after cost-to-serve, and margin percent. Add RANK or RANKCUMULATIVE on contribution margin to produce a whale curve: cumulative profit by customer sorted from most to least profitable. Flag customers below a hurdle margin with a Boolean line item that drives conditional formatting.

6. Scenario the price changes

Use a Forecast version. Create INPUT Price Actions with a price change percent by customer segment and product family, plus an elasticity assumption for volume response. Then:

• Forecast Volume = Baseline Volume * (1 + Price Change % * Elasticity)
• Forecast Price = Baseline Pocket Price * (1 + Price Change %)

Compare the forecast margin against actuals in a variance module. Elasticity is the weakest assumption. Show a sensitivity table across a few values so decision makers see the range rather than a single point.

7. Dashboard and process

Build a UX page with a customer and segment filter, the waterfall chart, the whale curve, and a grid of accounts below hurdle. Add a worksheet where account owners enter a proposed action with a comment. Use access drivers so owners edit only their own accounts.

Common pitfalls

• Averaging prices instead of ratio summaries, which skews roll-ups.
• Allocating cost to every customer x product cell, which bloats the model. Allocate to customer, then spread only where needed.
• Skipping reconciliation so nobody trusts the numbers.
• Treating elasticity as fact. Label it as an assumption.

Wrap-up

A profitability model turns pricing from instinct into a managed process. If you want help scoping one, or need an experienced Anaplan model builder to design it with your team, get in touch.