A sales bridge (or price volume mix analysis) is a report which shows the gap between budgeted and actual sales, and the explanation for that variation. Basically, there are three type of effects or components that should be considered in order to explain the gap:
- Price effect: deviation due to apply higher or lower selling prices.
- Volume effect: variation in the turnover due to the total units sold.
- Mix effect: measures the impact in the sales amount resulting from a change in the mix of the quantities sold (% of units sold per reference over the total).
This report becomes a really effective tool when it is performed as a bottom-up analysis, as we can identify the origin of the variations at different levels: product, product family, customer, etc.
Below we can see an example of a sales bridge and how to build it in Excel. To perform the analysis, it is needed to have the list of products with sales information (quantity and price) as follows:
- Blue columns contain budget data (units, units mix (%), price and turnover).
- Green columns show the same information for actual sales.
- And finally, in the last column (deviation) we can find the gap between budget and actual turnover.
Now some calculated columns are added in order to show the cause of the deviation for each product:
- Volume effect. In our example, the units sold (125) are lower than budget (150), therefore we will have a negative volume effect. To quantify this effect by product, we need to compare budget and actual turnover at budget mix, which is calculated as the sales amount in case the company had sold the 125 units with the same mix and price than budget.
Example: volume effect for product T RED.
Step 1. In budget, the units of product T RED are 20% of the total. Therefore, the actual units at budget mix are obtained as the 20% of 125 = 25 units.
Step 2. The actual turnover at budget mix is the result of multiplying the previous units by budget price: 25 x 200 = 5.000 EUR.
Step 3. Volume effect: difference between actual turnover at budget mix and budget turnover: 5.000 – 6.000 = -1.000 EUR.
- Mix effect. This component is calculated as the difference between actual units and actual units at budget mix, multiply by the budget price.
Example: mix effect for product T RED.
Step 1. Mix effect on quantities: 40 (actual units) – 25 (actual units at budget mix) = 15 units.
Step 2. Mix effect: 15 (mix effect on quantities) x 200 (budget price) = 3.000 EUR.
- Price effect. It is calculated as the difference between actual and budget price, multiplied by the actual units. In our example, the products T RED and T GREEN has no price effect, as the budget and actual prices are exactly the same. However, T BLACK has a positive price impact.
Example: price effect for product T BLACK.
Step 1. 90 (actual price) – 80 (budget price) = 10 EUR/unit
Step 2. Price effect: 10 x 75 (actual units) = 750 EUR.
Once we have got all the effects, it is highly recommended to show the result in a waterfall graph:
You can download the excel file with the example above here
In this post we simulate different scenarios in order to evaluate how a change in volume and/or price impact over the total sales, and understand better the meaning of every component.