MARGIN VARIANCE IN % OF SALES
The application shows the % value of margin on revenues in the two periods. This calculation has not be validated yet and should be considered as a purely indicative and subject to errors. The algorithm is illustrated below.
Variance in % charts
Select Run => "Fix dimension bridge". Select Show variance as => "after COGS", click on the expander menu below and set Variance as % of revenues to True.
Selecting Run => "Variable dimension bridge" will return the combinations that contribute to the change of margin as % of revenues.
Please note that the calculation has not been validated. We are not sure it is correct. The algorithm is shown below. Any suggestions are most welcome.
- We group-by the dataset so that every row has a unique combination of dimensions.
- For each row, we calculate the margin in value in period zero and in period one
For each row, we calculate margin change (difference between margin in P1 and margin in P0), in value, of that specific row. This margin change can be seen as the "contribution" of that specific row to the total change of margin.
We calculate the ratio between the value of the row margin of each period and the total sales of the period. The total of each column is equal to the % margin on revenues of each period.
- We calculate the % margin change between the two periods for each row, which is the difference between the period one % margin on revenues and the period zero % margin on revenues. The total of this column is equal to the total margin change. Each row represents the contribution of a particular combination to the total change of margin in %. The advantage is that the values of this column can be added together and sum up to the total % change. We use these values to return the result
- However it must be noted that, for a reason that I do not quite understand, equal % of margins do not correspond with equal margin changes in value. This might indicate that this approach is not correct.