Skip to content

VARIANCE CALCULATION

VARIANCE CALCULATION APPROACH

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

image-20210927130217425

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.

CONJUGATE VARIANCE ALLOCATION

Variance calculation inevitable results in a "common" area that has to be attributed in some way.

ALLOCATION ON TWO DIMENSIONS

When variance is calculated on two dimensions - price and volume - the common area that must be attributed can be depicted as a rectangle. The area, represented in yellow below, represents the intersection of the volume and of the price variance effect.

IMG

One method is simply to attribute all this common area either to volume or to price variance. This reflects a widespread but somewhat arbitrary practice.

img

'Mparanza avoids this issue by allocating the common area equally between price and volume variance.

image-20210217163912655

The formula we use is the following

image-20210217164328889

ON THREE DIMENSIONS

Things get more complicated when variance is calculated on three dimensions -for instance price, volume and margin rate"- to identify the factors behind margin change.

The common rectangle area becomes a tri-dimensional box, or rather a collection of boxes in the tridimensional space.

We can visualize the initial situation as a blue Duplo parallelepiped.

img

The simple, one-dimensional, variances are represented as the green, yellow and red blocks. For simplicity, we are showing positive variances :-).

img

We can visualize the pairwise common areas as the orange, grey and white blocks. These blocks need each to be divided in two, into triangular-based prisms. The volume of each prism must be attributed to the corresponding variance dimension.

img

We can visualize the threesome common area as the pink block. This volume needs to be divided in three equal slices, and each slice needs to be attributed to a variance dimension.

img

It can be difficult to visualize a cube divided by three, so here you go.

img

We use this approach to calculate three-dimensional variance in the options 2,3,6 above. Option 4, being four-dimensional, would be too complicated to calculate with attributions, so we use a simpler, somewhat volume-variance-biased, formula.

A huge thanks to Ludovico Ruggeri Laderchi for the explanation, Duplo pictures and formulas.

MIX VARIANCE

If you know about Tesla, you know what mix variance is.

Price variance is the impact of the change of price on revenues, keeping volumes constant. Volume variance is the impact of the change of volume on revenues, keeping prices constant.

MIX VARIANCE ON SALES

What kind of variance is it when prices and volumes do not change but customers shift their purchases towards more expensive or less expensive things? That is mix variance.

You are still selling 10 jars of nutella, prices per SKU have not changed, but instead of selling 5 small jars and 5 large jars, you are suddenly selling 2 small jars and 8 large ones. In this case your mix variance will simply be the difference of the price between large and small nutella jars times 3. Mix variance is very important . It is also tedious to calculate as the number of products increases.

Let's start super simple with our fake Tesla dataset (type "Tesla" on 'Mparanza dataset list). We all know that Tesla is selling a lot more cars, but at an average lower price per car, since now it is selling mostly Model E and a lower proportion of Model X and Model S.

Set Run as "Variable dimension bridge" and Show Variance as "price, volume, mix" and hit submit. You get the report below.

The 3.8M (fake number 🤥) increase in the volume variance tied to number of cars sold is partially offset by negative 2.3M mix variance value, reflecting the fact that now the mix is poorer. On a very theoretical line 💭 it would have been "better" if Tesla had sold as many cars as it is actually selling, but all top-of-the-line Model S 💰 and Model X 💰. The 300k price variance shows the impact of the price increase of the different models.

waterfall (5)

table (6)

You can see what is going on by plotting the dataset. Choose Plot Entire Dataset in the Choose Row to Plot parameter. Model S 💰 and Model X 💰 cost more than twice as much as a Model E. But Tesla is selling a lot more Model E cars and a lower number of Model S and Model X.

visualization (2)

MANAGING "EXTREME" PRICES

Tesla experienced a negative Mix variance that was more than offset by the positive volume variance of the much larger number of products sold. For Tesla Mix variance measures the impact of the fact that - everything else (volume and prices) constant - now "instead" of Model S, Tesla sells Model E.

Now imagine if Tesla started selling also 1000$ home charging stations, or, for that matter, if Tesla merged with Space X and started selling also 10M$ space launches. Nobody would say that people that are buying Tesla batteries are buying them "instead" of buying a Model E - "impoverishing" the mix - nor that Nasa is buying space launches "instead" of buying a Model X - "enriching" the mix.

In this case, it would not make sense to speak about "Mix" variance. Adding batteries or space flights to Tesla's mix should not impact on the "Mix" variance metric. In the same way, if a company that sells furniture starts selling a few more kits of screws to mount the furniture, there is no relevant change of Mix variance. The screws were never meant to be sofas. There is a change in Mix variance only if the customers start buying less or more expensive sofas.

Calculating Mix variance makes sense if it refers to a change in the sales proportion of the products that in some way share similar characteristics. Mix variance calculation can return misleading results when calculated across a too wide spectrum of prices.

For this reason, when calculating Mix variance, the app allows you to drop the highest and lowest price quartiles.

This might result in a Balance value like in this example. The balance value is the sum of the rows that have been dropped because their price was too high or too low relative to the "significant" range of prices.

waterfall (12)

To enable this behavior, click on the ➕ sign below the "Show variance as" widget and set the "Drop price outliers from mix variance calculation" widget to True.

image-20210407132949951

You can also change the quartile range of dropped prices with the "Quantile value to exclude" widget. Top and Low quantiles will be dropped.

image-20210407142449246

By default, with the setting on False, the app will return a result with no balance. However if the price range is too wide, the indicated Mix variance might not be "real"/"factual" in business terms.

waterfall (13)

After you have finished calculating your Mix Variance you might want to rerun the app. Otherwise, the app might continue to use your dataset with dropped rows! Click on "rerun" in the hamburger menu on the top right or simply key in the letter "r", like "rerun". Then hit the submit button again to get the corrected chart.

NEW AND LOST VARIANCE

"New" variance is tied to combinations that have sales only in the second period. "Lost" variance is tied to combinations that have sales only in the first period. You can choose this option with the "show variance as" widget.

image-20210312124008990

Example. A combination might be the following:

Product: Shirts, Color: Red, Brand: Armani, City: Milan.

If this combination exists in the first year but this precise combination does not exist in the second year, the system will return a "Lost" variance value. Similarly if this exact combination does not exist in the first year but exists in the second, the system will return a "New" variance value. Changed volume variance refer to combinations that can be found with positive sales in both periods.

waterfall (14)

In case of "New" many things might have happen:

  1. We introduced the "shirts" product line
  2. We introduced "red" color in the "shirts" product line
  3. We started selling "Armani" brand apparel
  4. We opened a shop in Milan
  5. All or any combination of the above.

In other words, "Lost" and "New" simply mean that "something changed", but does not directly tell you anything about "what" exactly changed. It gives some indication of "stability". Please let us know if you find it useful.

"LOST" AND "NEW" VS PRICE VARIANCE

The app calculates variance "bottom" up, summing up the variance values of all combinations. If a combinations is "Lost" or "New" for that combination it is impossible to calculate the price component of the variance, because for one period units are zero and there is no price. Therefore for all Lost and New combinations, all the variance is booked as volume variance. This means that the price variance value calculated with this approach will generally be different from the price variance value calculated simply summing up all the rows and then computing the variance.

MIX VARIANCE AND NEW AND LOST COMBINATIONS

Please not that, by necessity, mix variance only measures the effect on variance of the change in relative proportions of the combinations that exists in both periods. If a combination exists only in one period, the impact will be entirely booked as positive or negative volume variance.

Going back to are Tesla example, let's imagine that the Model E were sold only in the second period. In that case it is obviously impossible to calculate the Model E price for the first year, and therefore it is impossible to calculate a Mix variance tied to the greater number of Model E sold in the second year. The average price will go down, indicating an "impoverishment" of the Mix, but as far as the variance calculation is concerned, all that that has happened is that Tesla has sold more Model E, and that translates into volume variance.

Here is the result with a dataset with no Model E in the first period. The period A revenue has gone down by just .4M - the sales of Model E in the first year - but the negative Mix variance has all but disappeared.

waterfall (5)

It is all New volume. Please note that this happens not only when there is a new "product", but, at a less obvious level, when there is a new combination. For instance, if the Model E exists in the first year but is only sold in the USA and not in China, the app would book the effect of the sale of a large number of Model E as mix variance in the USA, but as volume variance in China. Because Model E sales in China are considered New volume, and in the USA are considered Changed volume.

waterfall (7)

DRIVER VARIANCE

Variance can also be a function of drivers, such as the number of customers, and their conversion rate, or the number of shops, and the average sales per shop.

Let's illustrate this with an example. Load the Travel dataset. The dataset looks like this.

img

The "amount" is the monetary value of sales. Nights is the "units" sold column . Visits is the number of visits to the site. Bookings is the number of "checkouts". Amount minus Commission is Net Sales. Net Sales minus COGS is Margin.

Hit on submit and you get the first report. An increase of travelers coming to France represents the most important item in terms of Price and Volume Variance. Variance Running Total converges at 33,000 which equals the difference between the two years.waterfall (8)

image-20210705164618597

Let's play with drivers now. Set "Show variance as" to "Price, Volume & Mix, Drivers" variance and hit submit. The increase average ticket sale fell, but was compensated by more visits and more checkouts of customers wishing to travel to France. .

waterfall (9)

image-20210705164752506

We can drill down the first row result to understand more of the reduction in average ticket amount. It mainly concerns travelers to France, whose number and conversion rate as we know swelled.

waterfall (10)

image-20210705164940302

If we choose "Price, Volume, Mix, Drivers Variance" we get a slightly different results that splits the change in average ticket sale (shown as "average ticket" variance) from the overall impact of the "mix" of accommodations we are selling (shown as "mix" variance). Both are going down in this case.

waterfall (11)

image-20210705165115095

VARIANCE ON MARGINS

To see the impact of the change of product mix on gross margin, select "show variance as" => "price, volume & mix, costs". Submit.

If you are running a fix dimension bridge analysis you will get something like this

waterfall (15)

In this example, unit costs have increased as have prices , partially offsetting each other. Volume increase (everything else fixed) impacted positively on margins.

The application shows the % value of margin on revenues in the two periods. This calculation has not be fully validated yet and should be considered as a purely indicative. The algorithm is illustrated below.

PLOTTING VARIANCE IN TERMS OF SHARE

Click on the cross below the "Show variance as" widget to access to the "Show variance as share of total" widget.

image-20210312133513789

Set the "Show variance as share of total" widget to True.

image-20210422180148329

Then filter the dataset to the subset you want to measure the share change on.

image-20210422180037523

Choose if you want the analysis as a fix or a variable dimension bridge.

image-20210422180131759

Click on the submit button.

image-20210312133156263

You should get a % share chart like this.

waterfall (12)

image-20210705171947240

The impact of market change element shows the effect of the change of the total "market" (total dataset sum) size. This will be zero if there was no change in the total market size between year zero and year one. In the case above, the share would have grown by about 33/34 points if the total market of year zero and of year one had stayed the same. Since the market grew in size, the share actually shrunk by six points, the difference between -40 and 34.

VARIANCE ON MARGINS IN PERCENT

waterfall (21)

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.

image-20210422181911132

Selecting Run => "Variable dimension bridge" will return the combinations that contribute to the change of margin as % of revenues.

waterfall (13)

table (9)

CALCULATION APPROACH

Please note that the calculation has not been validated. The algorithm is shown below. Any suggestions are most welcome.

  1. We group-by the dataset so that every row has a unique combination of dimensions.

img

  1. For each row, we calculate the margin in value in period zero and in period one

img

  1. 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.img

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

img

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

img

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

img