RETAILER
You are the Controller of Zebra Consumer Goods. Zebra CG is a large retailer operating across multiple categories in many different regions under its well known brand.
Zebra CG runs a state-of-the-art Actual vs Plan reporting cycle (see PowerBI example here). You need to prepare the report for the October 2018 closing.
You have been provided a 130k row file that covers three years of sales, with AC (actual) and PL (plan) data (to run the dataset in the app, type "ZebraBI Sales Dashboard" in the "choose a sample dataset" widget). The dataset has 10 dimension columns and contains revenue and direct cost data, but no volumes.
THE COMPANY
Multi-tier bar chart
See how a dimension - here division - plays out in the two periods on a metric - here amount.
AMOUNT AND MARGIN BY DIVISION
Both Divisions met plan on sales. The Electronics Division delivered 181k less margin vs plan, while the Personal Care Division delivered 79k more.
Marimekko Plot
See how a metric, here revenue, interacts along a pair of dimensions, here division and line.
Zebra CG operates with two divisions across different product lines...
Barmekko Plot
See how two metrics - here sales and margins - play along a given dimension, here division.
The Electronics Division is larger and more profitable than Personal Care.
Divisions operate across all continents.
All Regions where on Plan in terms of sales....
and, with the exception of North America, e few points under plan in terms of margins.
Some product lines, such as Wearable or Audio, have high margins. Others, such as Baby Care, less so.
Ormtan, the most important product in terms of sales and margins,...
...is sold by both Divisions,...
Dumbbell Plot
See how a dimension - here division - plays out in the two periods on a set of metrics - here amount and gross margin.
...and is a rare example of a category that beat the plan.
BY PRODUCT CATEGORY DUMBBELL PLOT
A few important product groups,....
...and a few important products are also sold by both divisions.
A few sales persons also work for both Divisions.
Sales variance analysis
See aggregated sales variance or split variance between its components (volume, price, mix, drivers,..).
In terms of sales, Zebra CG is aligned on plan...
Small multiples
Slice variance by dimension.
...with both Divisions.
Margin variance analysis
See aggregated margin variance or split variance between its components (volume, price, unit costs,..).
The 181k margin loss vs plan of the Electronics Division is due to a larger COGS increase than the increase in sales.
The loss comes from the Mobile, Comp and Video Lines of the Electronics Division.
Mobile and Comp Lines experienced higher COGS and somewhat weaker volumes vs plan.
Mobile Line COGS increased particularly steeply relatively to revenues.
EBITDA bridge analysis
Adding the indirect cost variation to the margin variance calculation returns the EBITDA margin bridge.
Say indirect costs have improved 500k vs Plan...
...the EBITDA bridge will look like this, with a 500k improvement tied to overhead reduction at the net margin -aka after indirect costs- level.
Plan vs Actual timeline
See when Actuals beat Plan and vice versa.
The Electronics Division was below plan on margins across the whole period....
...and had higher COGS vs plan in every month except June.
The Mobile line was below plan in margins for the whole year.
Most Product Groups of the Electronic Division underperformed in term of gross margin. Some had a proportionally greater COGS increase.
WHAT CHANGED IN SALES
FIRST RESULT COMBINATION
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".
Sales were right on budget with minimal deviations vs plan. Ormtan, the most important product category, did well. Baby Care less so.
4/5 of Ormtan revenue comes from the Electronics Division. In % terms, Ormtan grew more in Personal Care.
The first row result of the first waterfall correctly identifies the Ormtan product category as the driver of growth in sales with 5% over plan.
The second row result shows that the flat sales performance of baby care products in the personal care division, net of Ormtan sales, is in fact 5% negative.
The rest of the electronics division, net of Ormtan sales, is 3% over plan. Interestingly, the rest of the personal care division, net of Ormtan and of baby care sales, is almost 20% over plan.
SECOND RESULT COMBINATION
See another angle
This second waterfall shows some examples of products that did especially bad or well.
A few products in the Ormtan category gained (or lost) considerable ground.
THIRD RESULT COMBINATION
Excluding Baby Care, Personal Care grew sales by a heathy 11%. Smartphones lost 7% sales.
This third waterfall drops the product category detail.
As a result, the first row result shows the aggregate decrease of all personal care baby care products, including the successful Ormtan category. The second row result shows the performance all non baby care personal care products. The third, forth and fifth row result split the performance of the electronics business unit by line.
FORTH RESULT COMBINATION
A few combinations experienced sale losses of over 50% and might deserve attention.
The forth waterfall shows a few combinations with significant positive or negative % growth rate and might therefore require remedial action / further investigation to understand the reasons behind positive performance.
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 salesperson. Hit the 🚀 Submit button.
For example below is the detail of electronics Etcer sales by sales person. The app picked Shery Shutter 80% revenue loss as the first result.
WHAT CHANGED IN MARGINS
FIFTH RESULT COMBINATION
The loss in margins in the Electronics Division came from Mobile, Comp and Video.
In the mobile line, Hatimshy products, Tablets and Imosa Smartphones did not do well in terms of margins.
The fifth waterfall explains the loss of margins vs plan in the aggregate, without breaking margin variance into its price, volume and unit cost components.
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 personal care division did well, while the disappointing performance of the electronics division can be ascribed to the mobile, computer and video lines.
SIXTH RESULT COMBINATION
The margin loss in Electronics is due to higher unit COGS vs plan...
...and can be broken down further.
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 sixth waterfall explains the loss of margins vs plan by breaking margin variance into its price, volume and unit cost components.
Higher than plan unit cost in the electronics division was responsible for a significant loss in margins. Baby care unit cost was better than plan but baby care volume contribution to margins tanked. Higher volumes on non baby care products drove a relatively significant increase in margins, while unit cost of non baby care products increased more than in plan.
