CORRECTNESS OF CALCULATIONS
Variance calculations can be tricky. We put a great deal of effort into testing, but datasets have their idiosyncrasies and we cannot always guarantee that the calculation is correct.
CHECKING THE CALCULATION
The good news is that there is a way to for you check.
Upload your file, select "Run" ➡️ "Fixed-dimension bridge analysis" and hit the submit 🚀 button.
If the waterfall does not have a "residual" item the calculation is correct, like in this example.
By "correct" we mean that all variance calculations are correct, also using the "Run" ➡️ "Flex-dimension bridge analysis" setting. Under this setting a smallish "balance" item is not an indication of an error, but simply shows that the tool cannot cover the whole value of variance with five row combinations.
If you find other errors, please do let us know by opening an issue here and we will do our best to correct it.
Please note that an error is generally limited to a specific variance calculation. To be sure that your dataset's variance is calculated correctly in each variance calculation option, you need to run your dataset with all the available options available under this menu.
NEGATIVE VOLUME AND AMOUNT VALUES
As mentioned, if there is a "balance" item while using the "Run" ➡️ "Fixed-dimension bridge analysis" setting there might be a problem. The sum of the variance metrics does not add up to the total variance.
This does not mean there is necessarily a calculation error.
For instance, negative values in the dataset can complicate the variance calculation and lead to a "balance" value.
In the case above the positive balance depends on the presence of negative values in the dataset that can hinder the mix variance calculation.
To minimize the chance of errors we do an "automatic clean-up" of your dataset. We drop all rows where both the amount and the quantity columns are zero and we drop all rows where the amount and the quantity columns have opposite sign. The variance calculation does not work correctly in these cases.
For good measure, we also drop all rows where either the amount column or the quantity column are less or equal to zero. This ensures that the Price Variance result is the same for all variance calculations.
Now the chart should not show a balance value.
EXTREME PRICES IN MIX VARIANCE CALCULATION
Another case in which the presence of a balance value does not necessarily indicate an error is the sales Mix Variance calculation. Mix Variance measures the sales impact of a change of the relative proportion of the different products bought.
For instance, when Tesla launched the Model E, its sales mix changed from being made up only of high end Model S and Model X cars to being made up predominantly by lower priced Model E cars.
Tesla experienced a negative Mix variance that was of course more than offset by the positive volume variance of the much larger number of products sold. Mix variance measures the impact of the fact that - everything else (volume and prices) constant - now "instead" of Model S, Tesla sells Model E.
If however 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.
Calculating Mix Variance makes sense if it refers to a change in the proportion of products that in some way share similar characteristics. Mix Variance calculation can return misleading results when calculated across a too wide specter of prices. 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 if the customers start buying less or more expensive sofas.
As an illustration look at this price distribution. The two peaks represent the low cost accessories, at 1 to 30 euros, and the actual products, from 50 all the way to 200 Euros.
In this case, when calculating Mix variance, you want the app to drop the highest and lowest price quartiles. Otherwise, you might get a negative Mix Variance value and a positive Volume Variance value that are misleading.
To drop the price outliers, click on the ➕ sign below the "Show variance as" widget and set the "Drop price outliers from mix variance calculation" widget to True.
You can also choose the quantile price values to exclude.
Having dropped the price outliers, your result might have a non-zero Balance value like in this example. The negative 65.7 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.
If you are plotting small multiples, you will not see a "balance" value, but the totals of the sub-plot will be different and reflect the fact that some rows of the dataset have been dropped.
After you have finished calculating your Mix Variance rerun the app as explained below. Otherwise, the app might continue to use your dataset with dropped rows!
IF IN DOUBT, RERUN
In case of problems it is always a good idea to try to simply rerun the application. Open the hamburger menu on the top right and click on "Rerun" or simply key in the letter "r".
Then hit the submit button again to get the corrected chart.