When a business sells products of different margin, price and cost, the mix of what you sell can affect results. It's worth understanding this. At least it can explain changes in a way you can diagnose. Targeting better mix needs a mix-effect KPI to drive profit growth. Here, we show you how to measure the mix effect. Please note that this is an advanced topic. There is a link to a spreadsheet showing how it works.

I learnt this technique from Richard Coppoolse when he was Pricing Officer for Philips Lighting at the global HQ in the Netherlands (He contributed to the text Innovation in Pricing)

These calculations are an example of a synthesised KPI, and do not qualify as a genuine business driver. Another example (also from Richard) is Purchase Efficiency, a KPI for measuring how well procurement is doing.

GrowthPath specialises in helping business use advanced metrics into customer and product profitability, and in adapting forecasting tools to use business drivers. This topic is an example of Profit Engineering, which is a Level 3 topic in our maturity model. Many small and medium sized business can become data driven businesses by deploying cloud ERPs and analytics. Read more about cloud ERPs here

Measuring product mix and profitability

Why measure mix effect?

  • Diagnostic purposes: price and mix effect can explain changes in sales (and margin)
  • Performance management: if investment in marketing or new products is supposed to improve the mix, then these KPIs can be used to set targets in advance, and to evaluate performance

noun 30209 bigpictureThe Big Picture

Changes to product mix affect sales and profitability even if there is no change in product pricing. This is equally true of margins and costs.

For an example, the Australian supermarket price war on milk drove higher unit volumes but had two serious effects on category profitability. The price war was conducted on private-label (home brand) milk products. There are many branded added-value milk products: low-fat, enhanced calcium and so on.

Even if pricing on those products was not changed, the value gap increases as the entry level price point becomes much cheaper, and some consumers will move to buying cheaper products when the premium products become relatively too expensive. This shift of demand between products in a category causes a mix effect, on top of the price reductions in the generic products. Lion Nathan announced that its Dairy division suffered a big profit impact as the mix shifted against the higher-margin products it was using to subsidise its high overhead costs. This is an example of an opportunity for  the mix-effect KPI. Lion Nathan could have better modelled the effects of the price war, and they may have detected the consequences earlier.

For another point of view, consider that companies usually fight commoditisation and maturing products with innovation. Sales and margin targets should be set as proof that the innovation is delivering benefits. Positive mix effect can become a targetted KPI. Price erosion is a market fact and usually out of the control of a given business, but mix improvement should be a KPI since it is influenceable. Therefore, it's valuable to separate changes in price from changes in mix. 

Slides and Examples

There are some slides in this GrowthPath Masterclass on Simplified Forecasting, delivered by Tim Richardson via CPA Australia (start at slide 55 "Synthesised KPIs")

Mix effect rarely occurs on its own

Mix effect is not the only effect. Inside the mix, products will have different amounts of price erosion, and changes in quantity (also known as volume).

Sales is of course quantity * price, but it only really this simple if we sell just one product, and always for the same price. In the real world, revenue is the sum of individual quantities and prices for many products and prices.

The method here is to isolate sales changes due to three effects:

Price effect

Keeping qty the same, price effect changes sales. For reasons of convention, we use the new quantities as the constant quantities.

Mix effect

Even keeping total quantities and prices the same, sales can increase if we sell a higher proportion of expensive products

 Quantity effect

Selling more products, even at the same mix and price, will increase sales

To calculate these effects, we need sales and quantity-sold information.

A formula to approximately isolate the change from old sales to new sales

Effects are usually shown as a number like 4% or –2%

To use them to relate old sales to new sales, the formula is 

New sales = old_sales * (100% + price_effect%)*(100% + mix_effect% + qty_effect%). This is accurate.

However, because the effects are usually close to 0 over a short period of time, it is approximately correct to simply add them: new sales – old_sales = (old_sales*price_effect% + old_sales*mix_effect% + old_sales*qty_effect%).

This simplified approach makes it easy to give a monetary value to each of the effects. 

For instance, we can say that of the total increase in sales, the amount due to price effect is old_sales * price_effect.

If sales last year were $200m and the price effect is 1%, then the price effect increases sales by $2m. 

This approximation results in only a small error when the total change is not massive.

The connection between sales and profit

We really only care about sales because there is a connection between sales and profit: the contribution margin. 

Simply looking at sales does not directly tell us about the margin except in the case of price increases. Understanding the mix effect on profit is quite tricky. The gross margin is also impacted by a fourth effect, the cost price effect.

Understanding profit impact of mix and cost price effect requires a new data source: gross margin. 

Once you understand how to apply this analysis to sales, it is easy to expand it to margin mix and cost of goods sold mix. There are some more tips below, under the heading "Extension Topics"

 

Approximate profit impact (ok if effects are < about 5% in magnitude)

1. Price effect

Profit = old_sales * price_effect

2. Mix effect

Profit  = change in total gross margin – 1 – 3

However, this includes an effect from changes in cost price

3. Quantity effect

Profit = old_sales*qty_effect*old_gm%

Cost price effect

Like (1) but calculated at cost of goods sold level.

 

The formulas in an example spreadsheet

The spreadsheet

Please refer to this example Google Sheets spreadsheet (open in browser)

You can copy this to your own spreadsheet. I have chosen starting values which show only a mix effect, but you can explore by changing prices and quantities.

Total change in sales: The Volume Effect

Calculate the total change in sales: new_sales / old_sales - 1

This change is due to all factors. 

Price effect

is (new_qty * new_prices) / (new_qty * old_prices) - 1

That is, did our actual new sales grow due to price increases?

Hold quantities at the new level, look at the effect of change in price (so it is isolated from other effects). This could also be called price erosion.

The spreadsheet formula conceptually looks like this: = (salesNewtotal)/sum(new_quantities * old_prices) ) –1.

This type of formula is an array formula, which requires a special technique to create. 

The example spreadsheet uses sumproduct() instead, which is equivalent in this case, and does not need an array formula. 

Quantity effect

Quantity effect = new_qty/old_qty – 1

(So this is an high-level average. That may not make sense, until you read the next effect).

Note: sales quantity is often referred to as sales volume. I dislike this terminology since it is easily confused with volume as in the physical space needed to transport items.

The Mix effect

Even if quantity and prices stayed the same, the sales value could change if the mix changes. Since both quantities and prices do change, as well as mix, separating a mix effect is not so easy. We do it by calculating the combined effect of mix and quantity changes, and then subtracting the quantity effect (also known as the pieces effect). What is left is an effect not explained by price changes or by quantity changes: we call that the mix effect. 

A simple mix effect ia;

Volume effect - price effect - qty effect = mix effect

A more precise definition is:

 (1+volume_effect)/(1_pieces_effect) – 1 

but this is more work, and for small effects, the error of the approximation is not too big.

More Extension Topics

Improved measures of profit effect

  • Measuring mix effect profit impact

The three effects can be calculated using the gross margin value per item, instead of the net selling price per item. In this case, mix effect applied to the old gross margin correctly shows the effect of mix on profit. Note that changes in cost price (standard cost or my preferred measure, variable cost) also contribute to effects calculated using gross margin. To isolate these effects, a calculation needs to be done at cost-price level also.

  • Isolating changes due to cost price and transfer price changes

The three effects can be calculated with cost prices rather than selling prices.

In this case, price effect measures the impact of changes in cost prices on the contribution margin (if you are sensibly using variable costs rather than standard costs for your cost price).

  • Exchange rate effect

The three effects can be calculated using local currency. In this case the difference between the head-office reporting currency (e.g. Euro) price effect and the local currency price effect is the exchange rate effect.