DEPARTMENT STORE
THE DATASET
Microsoft offers a few interesting sample datasets. These datasets, provided by obvience.com, are based on real business data that has been anonymized. One is the Retail Analysis sample. To run the dataset in the app, type "retail analysis sample" in the "choose a sample dataset" widget.
THE COMPANY
Multi-tier bar chart
See how a dimension - here chain - plays out in the two periods on a metric - here amount.
Both chains are under plan in sales and margin.
...and in margin.
Marimekko Plot
See how a metric, here revenue, interacts along a pair of dimensions, here category and chain.
The company operates two retail chains that sell goods in various categories.
Barmekko Plot
See how two metrics - here sales and margins - play along a given dimension, here category.
Dumbbell Plot
See how a dimension - here chain - plays out in the two periods on a set of metrics - here amount and gross margin.
AMOUNT AND MARGIN BY CATEGORY
The women category is not doing well, neither in sales,...
...nor in terms of margins.
Dumbbell Plot
See how a dimension - here size - plays out in the two periods on a set of metrics - here amount and gross margin.
Medium sized shops are doing better vs plan in terms of margin.
Sales variance analysis
See aggregated sales variance or split variance between its components (volume, price, mix, drivers,..).
Sales narrowly missed plan, due to lower price and volume.
Margin variance analysis
See aggregated margin variance or split variance between its components (volume, price, unit cost,..).
Margins were narrowly under plan...
Waterfall chart format
Whenever possible, charts are built in accordance to the IBCS standard. Green is "good", red is "bad", white is "plan", grey is "previous period" and black is "actual".
...due to the impact of lower pricing.
If the dataset contains also indirect cost expense data for the two scenario, the app will return an EBITDA bridge connecting the net margins values of the two scenarios.
Let's assume that the overhead was 1M, up from 950k of Plan.
The way this renders as an EBITDA bridge is the following with the negative 50k margin variance increase and the AC and PL margin values netted from overhead costs .
Both sales in amount and sales in units did not meet plan.
Plan vs Actual timeline
See when Actuals beat Plan and vice versa.
Sales were under plan on and off throughout the year
WHAT CHANGED IN SALES
Variable dimension variance - Sales
Variable dimension variance helps see the interaction between the change in sales across the different dimensions. This first waterfall shows a few important facts - the loss of Womens, the fall of Lindsey in small stores,... - in one consistent picture.
FIRST RESULT COMBINATION
Sales in the Womens category fell significantly in amount and in % both in newly opened and in established stores. Lindsays sales in Small shops fell much less abruptly. Medium shops doing OK.
The first and second row result of the first waterfall documents the debacle of the Womens category in both recently opened and established stores.
The third row result of the first waterfall shows that small stores of the Lindsay chain underperform also in the other categories. The Lindsay chain operates virtually only with small stores.
Every dataset is different
This use case is based on a fake dataset. Test run the app with your data to confirm the advantages of the variable dimension variance approach in your specific use case.
The next two row results of the first waterfall explain show the flat performance of the second chain, Fashions Direct, net of Womens sales: the positive performance of medium stores in Ohio and the disappointing performance of the other shops and regions netted out.
SECOND RESULT COMBINATION
See another angle
This second waterfall shows variance split in its price and volume components.
The loss in sales in the Womens category was largely an issue of volumes. Junior Same Store sales fell due to an issue of price only partly offset by volumes.
The second waterfall splits the sales variance in its price and volume components.
The second row result draws attention to the fact that Juniors have a significant issue of negative price variance in established stores, that might lead to negative pressure on margins.
See all variance components of a combination
To see the non-filtered initial value of all variance components of a given combination, filter on the combination and run fix dimension variance. The example charts on the left shows price and volume variance for Juniors, split by store type.
A disadvantage of variable dimension variance is that it only shows the top-ranked components of each combination. In our example both volume and price variance are shown for Juniors, but only volume variance is shown for Women, and only price variance is shown for Kids and Home.
The third row result shows how Home has a positive price variance in all types of store.
WHAT CHANGED IN MARGINS
THIRD RESULT COMBINATION
The margin impact largely mirrors the sales variance, with the largest loss coming from the Womens category.
Show small multiple plots
After setting the appropriate filters set the "Run" widget to "Variable dimension bridge". Set the "Choose dimension for small multiples" widget to brand. Hit the 🚀 Submit button.
The first and second row result of the third waterfall show that the negative impact on margins is largely driven by Womens sales - whose margins fell by a 30%.
The margin dropped with the same rate in established and in new stores. Given the prevalence of established stores, it is there that the largest loss is found.
How to filter
Set the first filter on the "category" column and "exclude" womens.
Excluding Womens, sales margins shine in medium sized stores.
FORTH RESULT COMBINATION
Juniors and Kids had a positive volume impact on margins especially in Same Stores. However, margins from Same Store sales suffered due to higher costs and lower prices.
Consistency of results
Alternative result sets are always consistent. The different sets of results combinations might show similar elements from slightly different angles, potentially helping to unlock insights.
The forth waterfall identifies higher volumes in established stored in the Juniors and Kid categories as the drivers of higher margins, and identifies higher units costs in established stores across all categories as the main driver of lower margins.
Finding patterns in data
'Mparanza uses variable dimension variance to help find patterns in the data that could be missed with a traditional slice and dice approach.
Filtering only on the Womens category, sales fell overall, albeit in a somewhat steeper fashion in Large Fashions Direct stores.
Percentage-wise, the Womens category fall was very similar across territories....
Womens category sales suffered due to lower volumes that were somewhat offset by better prices.
Womens category margins fell overall, even though the fall was somewhat steeper in Large selling areas.
Womens category margins were impacted negatively by lower volumes and by higher unit costs, partially offset by higher prices.
