Aller au contenu

Comment construire un pont de revenus sur de grands jeu de données ?

Nous expliquons ce qu’est un pont de revenus, comment il peut être utilisé et comment en préparer un sur de grands ensembles de données de facturation à l’aide de MSSQL.

Un pont de revenus est une analyse financière qui relie les revenus de l’année précédente aux revenus de l’année en cours. Ce faisant, il procède une étape à la fois, afin que le lecteur puisse comprendre les facteurs ayant eu une incidence sur la variation des revenus entre les deux années.

Dans cet article, nous ferons une analyse simple des revenus avec les facteurs les plus courants. Selon l’entreprise que vous analysez, vous pouvez ajouter plus de facteurs au pont (nous en dirons plus si cela à la fin).

En termes plus simples, le changement de quantité et de prix entraîne la variation des revenus entre deux années. Cependant, le changement de quantité et de prix peut être motivé par divers facteurs. Celles-ci peuvent être les suivantes :

Changement de quantité :

  • Un client arrête ses achats au cours de l’année
  • Un nouveau client qui a commencé à acheter au cours de la nouvelle année
  • Un nouveau produit
  • Un produit discontinué
  • Clients existants achetant plus (ou moins) de produits existants

Changement de prix :

  • Clients existants payant plus (ou moins) pour le même nombre de produits.

Attention: Ce qui rend le pont de revenus compliqué, c’est l’isolement des facteurs. Si vous ne pouvez pas isoler correctement les facteurs, cela entraînera un double comptage (ou une mauvaise capture des facteurs), donc à la fin de votre pont, il y aura un écart (ou un dépassement) que vous ne pourrez pas expliquer.

Parmi ces facteurs, les plus difficiles à isoler sont les deux :

QUANTITÉ : Clients existants achetant plus (ou moins) de produits existants

PRIX : Clients existants payant plus (ou moins) pour le même nombre de produits.

Expliquons cela dans un graphique simple :

Dans le graphique ci-dessus, le bloc douteux est celui marqué d’un astérisque – ce bloc est-il généré en raison d’une augmentation de prix ou d’une augmentation de volume ? La vraie réponse est les deux. Cependant, nous choisissons la quantité plutôt que le prix, car si l’article n’était pas vendu, la réalisation du prix ne se produirait pas non plus.

Écrivons maintenant le code MSSQL qui nous aidera à préparer les colonnes de notre passerelle de revenus :

-- 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

Nous pouvons maintenant mettre la sortie dans un graphique en cascade dans Excel pour voir clairement le pont.

Vous pouvez préparer votre ensemble de données comme suit :

Et puis insérez un graphique en cascade. Vous pouvez lire les graphiques en cascade dans Excel – Guide du débutant | GoSkills pour apprendre à créer un graphique en cascade dans Excel.

Et voilà.

Si vous souhaitez ajouter plus de marches à votre pont, c’est possible, mais vous devez vous assurer qu’elles sont isolées. Par exemple, vous pouvez ventiler vos augmentations de prix en « Augmentation de prix due à l’augmentation du catalogue de prix » et « Variations de remise », ou vous pouvez diviser la variation de quantité en lignes de produits.

Étiquettes:

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *