Margin 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 | First period data |
T2 | Second period data |
Margin | SUM(grossMargin) |
Volume | SUM(quantity) |
InvoicePricePerUnit | SUM(invoicePrice) / SUM(quantity) |
MarginPerUnit | SUM(grossMargin) / SUM(quantity) |
CostPerUnit | SUM(InvoicePrice - GrossMargin) / SUM(quantity) "Cost" for the purpose of this dashboard is defined as the gap between Revenues and Gross Margin; it would be cumbersome to declare another column or parameter summing up all "waterfall cost components". |
T1Volume | Total Volume for T1 |
T2Volume | Total Volume for T2 |
Column Definition
The following columns are available on the dashboard:
Column | Description |
|---|---|
Margin in { T1 } | Provides a margin summary from the first period. |
Volume Effect | Difference in margin 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 margin. This can be positive or negative. |
Price Effect | Difference in margin between the T2 and T1 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 quantity weighting across the two periods. The result can be negative or positive. |
Portfolio Mix Effect | Difference in margin between T2 and T1 for transactions for customers that appear in both T1 and T2 but are not yet included in the Price Effect nor Volume Effect categories (impact of changes in the product portfolio mix). It is defined by the average volume and the sum of the differences in individual products of their average price and cost across the two periods multiplied by their change in portfolio mix contribution between the two periods. |
New Business | Total margin from transactions in T2 from customers that did not buy anything in the T1, expressed as a positive number. Always positive. |
Lost Business | Total margin from transactions in T1 from customers that did not buy anything in the T2, expressed as a negative number. Always negative. |
Cost Effect | Difference in margin between T1 and T2 is attributed to a difference in cost only (the impact of changes in specific costs). The average volume multiplied by weighted average change in costs where the weighting uses the average quantity weighting across the two periods. |
Other Effects | This value should always be zero. If it is not, the relationship "Invoice - Cost = Gross Margin" is not fulfilled. Hence this component does not need a bar to be represented. |
Margin in { T2 } | Provides a margin summary from the second period. |
Field Calculation
Effects are calculated in the following way:
Effects | Formula |
|---|---|
Volume | SUM(T2.Volume - T1.Volume) * SUM((T2.Mix * T2.MarginPerUnit + T1.Mix * T1.MarginPerUnit) / 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.MarginPerUnit + T1.MarginPerUnit) / 2) |
Cost | SUM(T2.Volume + T1.Volume)Â / 2) * SUM((T2.Mix + T1.Mix) / 2 * (T2.CostPerUnit - T1.CostPerUnit))) |
Mix Definition
Field | Formula |
|---|---|
Quantity ratio for the particular product in the scope of all products quantity within the period | Quantity per product / Quantity per all products |
T1.Mix | T1.Volume/T1Volume |
T2.Mix | T2.Volume/T2Volume |
With the 1.7.0 version, the effects calculation formulas have been changed to address the business point of view more adequately. The previous formulas are referred to as "Legacy", while the new ones are referred to as "Standard" (default) in the configuration.
For more information on the Legacy formulas see the archived documentation of the previous versions (Margin Breakdown Dashboard - Fields Definition chapter).
To switch between these two (if needed), follow the configuration guide in the Installation (Sales Insights) page.
Default Filters
There are some default filters applied on various fields to ensure proper calculations. These are:
Only entries with not null grossMargin are considered.
Only entries with not null invoicePrice are considered.
Only entries with not null quantity are considered.
Only entry sets with SUM(quantity) > 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)grossMarginquantityinvoicePricecostsproductDimensionscustomerDimensions(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"
} |