VARIANCE CALCULATION METHOD
The app computes the variance "bottom up" as the sum of the variance of the single rows of the dataset. This makes it possible to slice and dice the data and calculate variable dimension variance. It also avoids that the price variance value reflects changes of mix, instead of only changes of price.
However, since we reason by dataset row, we calculate Price and Cost variance only for the row combinations that are referenced in both periods. If a combination is observed only in one period, all the variance will be considered as Volume variance.
The reason is that each dataset row contains the values of our metrics (Amount, Quantity, Costs) for a given combination of dimensions in the first and/or the second period. If a given combination is not referenced in both periods, we cannot calculate the price and the cost variance, and, as mentioned, all the variance will be booked as volume variance.
CHOICE OF VARIANCE CALCULATION
Choose the variance calculations that can best answer your business question:
- after COGS shows margin variance after COGS. Use when you just want to know which combinations impacted on margin change, but are not interested on whether it was volume, price or cost.
- price, volume & mix, margin rate shows impact on margin of price, volume and margin rate variance. Use when products have very different margin rates, since this approch normalises marginality.
- price, volume & mix, costs shows price, volume and cost impact on gross margin change.
- price, volume & mix, discounts, COGS shows price, volume, discounts and cogs impact on margin change. Since the formula does not perform allocation of "common" values, result is biased towards volume variance.
- after discounts shows variance after discounts. Use when you just want to know which combinations impacted on change of variance after discounts, but are not interested on whether it was volume, price or discounts.
- price, volume & mix, discounts shows price, volume and discount impact on net sales.
- price & volume & mix shows sum of price and volume variance.
- price, volume & mix details price and volume variance.
- price, new, lost, changed details volume variance in new, lost and changed volumes. "New" volumes are combinations with no sales in the first period. "Lost" volumes are combinations with no sales in the second period.
- price, volume, mix details volume variance, price variance and variance due to the change of product mix.
- price, volume & mix, drivers details volume variance, price variance, and variance and variance due to the impact of your driver column/s
- price, volume, mix, drivers details volume variance, price variance, variance due to the change of product mix, and variance due to the impact of your driver column/s.