Skip to content

How to build a revenue bridge on large datasets?

We explain what a revenue bridge is, how it can be used, and how to prepare one on large invoice datasets using MSSQL.

A revenue bridge is a financial analysis that bridges previous year’s revenue to current year’s revenue. In doing so, it goes one step at a time, so that the reader can understand the factors impacted the revenue variation between the two years.

In this post, we will do a simple revenue analysis with most common factors. Depending on the business you are analyzing you can add more factors to the bridge (we will tell more if this at the end).

In most simple terms, the change of quantity and price leads to the variation of revenue between two years. However, the change of quantity and price can be driven by various factors. These can be as follows:

Change of quantity:

  • A customer stopping purchases over year
  • A new customer who started buying in the new year
  • A new product
  • A discontinued product
  • Existing customers buying more (or less) of existing products

Change of price:

  • Existing customers paying more (or less) for the same number of products.

Attention: What makes revenue bridge complicated is the isolation of factors. If you cannot isolate the factors properly, this will lead to double counting (or not capturing factors properly), so at the end of your bridge there will be a gap (or a surpass) that you will not be able to explain.

Among these factors, the most difficult to isolate are the two:

  • QUANTITY: Existing customers buying more (or less) of existing products
  • PRICE: Existing customers paying more (or less) for the same number of products.

Let’s explain this in a simple graph:

In the above graph, the questionable block is the on marked with asterisk – is that block generated due to price increase or volume increase? The real answer is both. However, we pick quantity over price, because if the item was not sold the price realization would not occur either.

Now let’s write the MSSQL code that will help us prepare the columns for our revenue bridge:

-- it's important to have reiterable code, so we copy lines into a new table, and we start by dropping the table if it exists
DROP TABLE Rev_Bridge_Summary_Table;
GO
-- Please note the CASE/WHEN use below. This helps us split the revenue and quantity into a column each per year.
SELECT
	T1.Product_Level_0,
	T1.Product_Level_1,
	T1.Product_Level_2,
	T1.Product_Level_3,
	T1.Product_Level_4,
	T1.SKU,
	T1.Customer_No,
	T1.Customer_Name,
	T1.Customer_Group,
	T1.Sales_Region,
	SUM(T1.Qty_2021) AS Qty_2021,
	SUM(T1.Qty_2022) AS Qty_2022,
	SUM(T1.Rev_USD_2021) AS Rev_USD_2021,
	SUM(T1.Rev_USD_2022) AS Rev_USD_2022
	INTO Rev_Bridge_Summary_Table
	FROM
	( SELECT
		   IL.SKU AS SKU
		  ,IL.Product_Level_0 AS Product_Level_0
		  ,IL.Product_Level_1 AS Product_Level_1
		  ,IL.Product_Level_2 AS Product_Level_2
		  ,IL.Product_Level_3 AS Product_Level_3
		  ,IL.Product_Level_4 AS Product_Level_4
		  ,IL.[Customer Account Nbr] AS Customer_No
		  ,IL.[Customer Name] AS Customer_Name
		  ,IL.[Large Customer Account] AS Customer_Group
		  ,IL.[New L4] AS Sales_Region
		  ,CASE WHEN Invoice_Year = '2021' THEN IL.Quantity ELSE 0 END Qty_2021
		  ,CASE WHEN Invoice_Year = '2022' THEN IL.Quantity ELSE 0 END Qty_2022
		  ,CASE WHEN Invoice_Year = '2021' THEN IL.Revenue_USD ELSE 0 END Rev_USD_2021
		  ,CASE WHEN Invoice_Year = '2022' THEN IL.Revenue_USD ELSE 0 END Rev_USD_2022
		  
	 FROM Invoice_Lines IL
	 WHERE IL.Invoice_Year IN ('2021','2022') AND IL.Quantity > 0 -- We exclude returns because it distorts the analysis
	 ) T1 
	 GROUP BY
		T1.Product_Level_0,
		T1.Product_Level_1,
		T1.Product_Level_2,
		T1.Product_Level_3,
		T1.Product_Level_4,
		T1.SKU,
		T1.Customer_No,
		T1.Customer_Name,
		T1.Customer_Group,
		T1.Sales_Region
GO
ALTER TABLE Rev_Bridge_Summary_Table ADD Stop_Cust varchar(1), New_Cust varchar(1), EOL_prod varchar(1), NPD_prod varchar(1), Var_Price float
GO
-- We mark all invoice lines that came from customers that stopped buying in the current year.
UPDATE Rev_Bridge_Summary_Table
SET Stop_Cust = 'X' 
WHERE Customer_No IN
	(SELECT
		CX.Customer_No 
		FROM
			(SELECT 
					Customer_No, 
					SUM(Rev_USD_2021) AS Rev_USD_2021,
					SUM(Rev_USD_2022) AS Rev_USD_2022
					FROM Rev_Bridge_Summary_Table
					GROUP BY Customer_No
			) CX
		WHERE CX.Rev_USD_2022 <= 0 AND CX.Rev_USD_2021 > 0
	) 
GO
-- We mark all invoice lines that came from customers that (re)started buying first time in the current year.
UPDATE Rev_Bridge_Summary_Table
SET New_Cust = 'X' 
WHERE Customer_No IN
	(SELECT
		CX.Customer_No 
		FROM
			(SELECT 
					Customer_No, 
					SUM(Rev_USD_2021) AS Rev_USD_2021,
					SUM(Rev_USD_2022) AS Rev_USD_2022
					FROM Rev_Bridge_Summary_Table
					GROUP BY Customer_No
			) CX
		WHERE CX.Rev_USD_2022 > 0 AND CX.Rev_USD_2021 <= 0
	) 
GO
-- We mark all invoice lines that came from products that stopped selling in the current year.
UPDATE Rev_Bridge_Summary_Table
SET EOL_prod = 'X' 
WHERE SKU IN
	(SELECT
		CX.SKU 
		FROM
			(SELECT 
					SKU, 
					SUM(Rev_USD_2021) AS Rev_USD_2021,
					SUM(Rev_USD_2022) AS Rev_USD_2022
					FROM Rev_Bridge_Summary_Table
					GROUP BY SKU
			) CX
		WHERE CX.Rev_USD_2022 <= 0 AND CX.Rev_USD_2021 > 0 AND Stop_Cust IS NULL AND New_Cust IS NULL
	)
GO
-- We mark all invoice lines that came from products that started selling in the current year. Please note that many companies mark "New Product Development" as revenue generated by new products launched in the last 3 years, but this analysis takes only current year's launches into account.
UPDATE Rev_Bridge_Summary_Table
SET NPD_prod = 'X' 
WHERE SKU IN
	(SELECT
		CX.SKU 
		FROM
			(SELECT 
					SKU, 
					SUM(Rev_USD_2021) AS Rev_USD_2021,
					SUM(Rev_USD_2022) AS Rev_USD_2022
					FROM Rev_Bridge_Summary_Table
					GROUP BY SKU
			) CX
		WHERE CX.Rev_USD_2022 > 0 AND CX.Rev_USD_2021 <= 0 AND Stop_Cust IS NULL AND New_Cust IS NULL
	)
GO

ALTER TABLE Rev_Bridge_Summary_Table ADD Var_Stop_Cust float, Var_New_Cust float, Var_EOL_prod float, Var_NPD_prod float, Var_Qty_Imp float
GO
-- Below we run the isolations. Any line picked up by one of the previous factors will not be picked up again by another factor.
UPDATE Rev_Bridge_Summary_Table SET Var_Stop_Cust = -1 * Rev_USD_2021 WHERE Stop_Cust = 'X';
UPDATE Rev_Bridge_Summary_Table SET Var_New_Cust = Rev_USD_2022 WHERE New_Cust = 'X';
UPDATE Rev_Bridge_Summary_Table SET Var_EOL_prod = -1 * Rev_USD_2021 WHERE EOL_prod = 'X';
UPDATE Rev_Bridge_Summary_Table SET Var_NPD_prod = Rev_USD_2022 WHERE NPD_prod = 'X';
GO
UPDATE Rev_Bridge_Summary_Table 
SET Var_Qty_Imp = (Qty_2022 - Qty_2021) * (Rev_USD_2021 / Qty_2021)
WHERE Stop_Cust IS NULL AND New_Cust IS NULL AND EOL_prod IS NULL AND NPD_prod IS NULL AND Qty_2021 > 0
GO
UPDATE Rev_Bridge_Summary_Table 
SET Var_Qty_Imp = Rev_USD_2022 WHERE Qty_2021 = 0 AND EOL_prod IS NULL AND NPD_prod IS NULL AND New_Cust IS NULL AND Stop_Cust is NULL
GO
UPDATE Rev_Bridge_Summary_Table 
SET Var_Price = ((Rev_USD_2022 / Qty_2022) - (Rev_USD_2021 / Qty_2021)) * Qty_2022
WHERE Stop_Cust IS NULL AND New_Cust IS NULL AND EOL_prod IS NULL AND NPD_prod IS NULL AND Qty_2021 > 0 AND Qty_2022 > 0

Bonus

We can now put the output into a waterfall chart in Excel to see the bridge clearly.

You can prepare your data set as follows:

And then insert a Waterfall chart. You can read Waterfall Charts in Excel – A Beginner’s Guide | GoSkills to learn how to do a waterfall chart in Excel.

And here it goes.

If you like to add more steps to your bridge it is possible, but you have to make sure these are isolated. For example you might want to breakdown your price increases into “Price Increase due to price book increase” and “discount variations”, or you might split the quantity variation into product lines.

Leave a Reply

Your email address will not be published. Required fields are marked *