Revenue Breakdown Dashboard - Configuration Details
- Štěpán Vacek
Fields Definition
In the tables below the following terminology is used (field definitions taken from SIP_AdvancedConfiguration):
Field | Description |
|---|---|
T1 | Prior period |
T2 | Final period |
Column Definition
The following columns are available on the dashboard:
Column | Description |
|---|---|
Comparison revenue in [Quarter-Year] (1) | Total invoice price of comparison period (T1) |
Revenue in {T1} | Provides a revenue summary from the first period. |
Volume Effect | Difference in revenue between T1 and T2 is attributed to a difference in volume only (the impact of changes in volume). Total change in volume multiplied by the difference of the inter-period weighted average price and cost. This can be positive or negative. |
Price Effect | Difference in revenue between the T1 and T2 that can be attributed solely to changes in price (the impact of changes in specific prices). The average volume multiplied by the weighted average change in prices where the weighting uses the average volume weighting across the two periods. The result can be negative or positive. |
Portfolio Mix Effect | Difference in revenue between T2 and T1 for transactions involving customers that appear in both T1 and T2. These transactions are also included in the Price Effect and Volume Effect calculations, ensuring consistency across all three categories. Mix captures the impact of changes in the product portfolio composition and is defined by the average volume and the sum of the differences in individual products' average prices across the two periods, multiplied by their change in portfolio mix contribution between the two periods. |
New Business | Total revenue from transactions in T2 from customers that did not buy anything in the T1, expressed as a positive number. Always positive. |
Lost Business | Total revenue from transactions in T1 from customers that did not buy anything in the T2, expressed as a negative number. Always negative. |
Other Effects | Other effects that may influence the revenue that are none of the above. |
Revenue in {T2} | Provides a summary of revenue from the second period. |
Field Calculation
Effects are calculated in the following way:
Effect | Calculation |
|---|---|
Volume | SUM(T2.Volume - T1.Volume ) * SUM((T2.Mix * T2.InvoicePricePerUnit + T1.Mix * T1.InvoicePricePerUnit ) / 2) |
Price | SUM((T2.Volume + T1.Volume) / 2) * SUM((T2.Mix + T1.Mix) / 2 * (T2.InvoicePerUnit - T1.InvoicePerUnit) |
Portfolio Mix | SUM((T2.Volume + T1.Volume) / 2) * SUM((T2.Mix - T1.Mix) * (T2.InvoicePerUnit + T1.InvoicePricePerUnit) / 2) |
Lost Business | SUM( Invoice price of products traded in T1 but not traded in T2) |
New Business | SUM( Invoice price of products traded in T2 but not traded in T1) |
Other | T2.InvoicePrice - (T1.InvoicePrice + lostBusinessEffect + priceEffect + volumeEffect + mixEffect + newBusinessEffect) |
Mix Definition
Field | Formula |
|---|---|
The ratio of volume for the particular product in the scope of all products volume within the period | Volume per product / Volume per all products |
T1.Mix | T1.Mix = Volume per product in T1 / volume per all products in T1 |
T2.Mix | T2.Mix = Volume per product in T2 / volume per all products in T2 |
With the 1.7.0 version, the effects calculation formulas has been changed to address the business point of view more adequatelly. The previous formulas are referred to as "Legacy" while the new ones are referred to as "Standard" (default) in configuration.
For the "Legacy" formulas you can refer to the archived documentation of the previous versions - the latest of these: Accelerate_Sales_Insights_Package-1.6.1.pdf (Revenue Breakdown Dashboard - Fields Definition chapter).
To switch between these two (if needed) follow the configuration guide in the Installation (SIP) page: Installation (Sales Insights) Revenue/Margin Breakdowns Definition.
Default Filters
There are some default filters applied on various fields to ensure proper calculations. These are:
Only entries with not null invoicePrice are considered.
Only entries with invoicePrice not equal to 0 are considered.
Only entries with not null quantity are considered.
Only entry sets with SUM(quantity) > 0 are considered (aggregation "having" filter is applied).
Only entry sets with SUM(invoicePrice) > 0 are considered (aggregation "having" filter is applied).
Some of the definitions on this page were taken from the web article Normative decomposition of the profit bridge into the impact of changes in marketing variables.
Used Advanced Configuration Fields
Path
Administration > Configuration > System Configuration > Advanced Configuration Options
List of Advanced Configuration Fields
datamartNamepricingDateproductIdcustomerId(optional)quantityinvoicePriceproductDimensionscustomerDimensions(optional)breakdownMode
Example
Name | Value |
|---|---|
| {
"datamartName":"Standard_Sales_Data",
"productId":"ProductId",
"productName":"ProductName",
"customerId":"CustomerId",
"customerName":"name",
"invoicePrice":"InvoicePrice",
"quantity":"Quantity",
"grossMargin":"GrossMargin",
"costs":"OtherCOGS",
"pricingDate":"PricingDate",
"pricingDateYear":"PricingDateYear",
"productDimensions":[
],
"customerDimensions":[
],
"continent":"Region",
"country":"Country",
"region":"City",
"localListPrice":"LocalListPrice",
"globalListPrice":"GlobalListPrice",
"netPrice":"NetPrice",
"breakdownMode":"Standard",
"firstDayOfWeek":"Sunday"
} |