You are BI consultant that has just been hired by Maven Toys, a chain of toy stores in Mexico. As they look to expand their business with new stores, they've brought you in to analyze interesting patterns and trends in their data and help them make informed decisions.
The dataset contains the units sold in over 800,000 sales transactions from January 2017 to October 2018 (to run the analysis, type "Maven toys challenge" in the app). Since we have only 9 month of data of the current year, we compare the most recent quarter to the corresponding quarter of the previous year.
Q3-vs-Q3 sales went up nicely, but COGS increased even more.
See how a metric, here revenue, interacts along a pair of dimensions, here product category and product name.
Product categories are generally dominated by one or two product lines.
Magic Sand is doing well in terms of sales, but the "marginal" products seem to be more profitable. It is a pity that the high margin Colorbuds are losing sales.
See how two metrics - here sales and margins - play along a given dimension, here product category.
The Toy category has high sales and a lower margin rate than Electronics.
Multi-tier bar chart
See how a dimension - here product category - plays out in the two periods on a metric - here amount.
Arts & Crafts is over-performing both in Sales...
and in margins...
...as has been the case throughout the year.
Colorbuds, Deck of Cards and Action Figures have relatively low sales and relatively high margins...
....all three, however, lost sales and margin relative to Q3 of the preceding year...
See how a dimension - here product name - plays out in the two periods on a set of metrics - here amount, COGS and gross margin.
...with Colorbuds and Action Figure halving their margins...
...leading to a drop of the margin rate on sales from 31 to 26%
New shops - ie shops opened in the last three years - and old shops have a very similar breakdown of products sold.
10 out of the 12 new stores have been opened in cities where the company already had a presence.
Store locations have a very similar product breakdown...
...where commercial locations disappointed due to a flat margins growth.
BY STORE LOCATION DOT PLOT
Product breakdown is also similar across cities
Given the similar mix, store profitability is reasonably similar across stores...
...with the Monterrey store performing especially well.
Sales variance analysis
See aggregated sales variance or split variance between its components (volume, price, mix, drivers,..).
Sales growth was driven largely by "new" offerings.
Margin variance analysis
See aggregated margin variance or split variance between its components (volume, price, unit costs,..).
Margin growth was driven by volumes, with unchanged prices and costs, and was quite a bit softer than sales growth,...
...due to the negative impact of a less "rich" sales mix.
Slice variance by dimension.
New shops are growing less quickly than the more established locations...
...however, in terms of margins, new shops are doing ok and seem to have much less of a problem of mix.
In fact, old shops were "responsible" for most of the fall of margin in %.
There seems to be a seasonality in sales, that dips during the summer,...
...and seems more accentuated for old shops...
...and for shops in downtown locations.
WHAT CHANGED IN SALES
FIRST RESULT COMBINATION
In terms of sales, Art & Crafts are the absolute winner, up almost 200%. In the Electronics category, Colorbuds disappointed, down 50%. Sports & Outdoors Nerf Guns shot up 160% while sports & outdoors Splash Balls did not do so well.
The first row result of the first waterfall correctly identifies the Arts & Crafts product category as the driver of growth in sales with a 360k change and a 188% growth rate. The second row result shows the notable performance of Toys.
The last three row results identify products, in their respective categories, noteworthy for good or bad performance such as Colorbuds, Nerf Gun and Splash Balls.
SECOND RESULT COMBINATION
In Arts & Crafts, Magic Sand did really well growing 6000% from 3k to 230k. Dino Egg and, to a lesser extent, Mr. Potatohead were very successful in the Toys category.
The second waterfall points to the explosive performance of a few products in the top performing Art & Crafts and Toys categories such as Magic Sand and Dino Egg.
THIRD RESULT COMBINATION
In terms of location, Downtown shops grew 36%, 4 points more than the company average.
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 second result row shows that, excluding downtown shops, Toys are growing 42%.
It is interesting and insightful that the third row is a subset of Arts & Crafts, the already noted Arts & Crafts, Magic Sand that excluding downtown is growing over 7000%. Colorbuds are not performing.
WHAT CHANGED IN MARGINS
FORTH RESULT COMBINATION
Art & Crafts drove the growth of margins. The loss of margins was tied to Electronics Colorbuds and Toy Action Figures....
The 68k of margin loss in Electronic Colorbuds spread largely proportionally among the different location types.
The forth waterfall identifies Arts & Crafts as the driver of margin growth. Colorbuds and Action Figures are impacting negatively on margin change. Net of Action Figures, Toys margins are improving.
FIFTH RESULT COMBINATION
Excluding the negative impact of Electronics Colorbuds, all types of shop Locations, and Downtown in particular, contributed to the margin growth.
The fifth waterfall starts off with the negative impact of Colorbuds on margins and then explains the change of margins net of Colorbuds by store location.
SIXTH RESULT COMBINATION
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.
The loss of margin due to sales of a less profitable mix ate up half of the margin increase of Downtown shops.
The sixth waterfall points to two interesting facts: Downtown stores sold a lot more in terms of volumes, but shifted towards less profitable items, halving the positive margin volume impact by 50%. Net of Downtown, Colorbuds had a 40k negative impact on margins due to lower volumes.
SEVENTH RESULT COMBINATION
Toys were also negatively impacted by mix change, while Arts & Crafts experienced a positive margin mix increase.
The seventh waterfall splits margin variance on product category, showing that the negative mix impact is especially tied to Toys.