Revenue waterfall decomposition
Intermediate FP&ARevenue variance analysis helps FP&A teams explain why sales moved between two periods. Instead of stopping at the total change, you can split the movement into the part caused by unit volume and the part caused by pricing.
This two-part waterfall, often called a volume-price decomposition, is one of the most common ways to turn a headline revenue change into a useful business explanation.
In this exercise, the volume effect shows what changed because unit sales moved up or down. The price effect shows what changed because prices moved up or down. For volume, use the prior year price. For price, use the current year units.
In the product table, columns D and G already show prior year revenue and current year revenue for each product.
For the decomposition rows, a common approach is to use SUMPRODUCT so you can multiply row-by-row differences by a comparison range and total the result in one step. For example, a generic pattern like =SUMPRODUCT((B2:B6-A2:A6), C2:C6) adds up a volume-style effect across several rows without needing a helper column.
Your task:
- In B10, calculate total prior year revenue.
- In B11, calculate the total volume effect across all four products.
- In B12, calculate the total price effect across all four products.
- In B13, calculate the total revenue change.
- In B14, calculate total current year revenue.
- In B15, build the check so the bridge balances to zero.
Use the labels in rows 10 through 15 to guide what each amount should represent.
Need some help?
Hint 1
For each product, volume effect is based on the change in units multiplied by the prior year price. After that, add the four product effects together.
Hint 2
For each product, price effect is based on the change in price multiplied by current year units. This convention is a common FP&A approach for a basic revenue bridge.
Related function(s)
Revenue waterfall decomposition
Intermediate FP&ARevenue variance analysis helps FP&A teams explain why sales moved between two periods. Instead of stopping at the total change, you can split the movement into the part caused by unit volume and the part caused by pricing.
This two-part waterfall, often called a volume-price decomposition, is one of the most common ways to turn a headline revenue change into a useful business explanation.
In this exercise, the volume effect shows what changed because unit sales moved up or down. The price effect shows what changed because prices moved up or down. For volume, use the prior year price. For price, use the current year units.
In the product table, columns D and G already show prior year revenue and current year revenue for each product.
For the decomposition rows, a common approach is to use SUMPRODUCT so you can multiply row-by-row differences by a comparison range and total the result in one step. For example, a generic pattern like =SUMPRODUCT((B2:B6-A2:A6), C2:C6) adds up a volume-style effect across several rows without needing a helper column.
Your task:
- In B10, calculate total prior year revenue.
- In B11, calculate the total volume effect across all four products.
- In B12, calculate the total price effect across all four products.
- In B13, calculate the total revenue change.
- In B14, calculate total current year revenue.
- In B15, build the check so the bridge balances to zero.
Use the labels in rows 10 through 15 to guide what each amount should represent.
Need some help?
Hint 1
For each product, volume effect is based on the change in units multiplied by the prior year price. After that, add the four product effects together.
Hint 2
For each product, price effect is based on the change in price multiplied by current year units. This convention is a common FP&A approach for a basic revenue bridge.