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 also two months of Forecast data (November and December 2018). 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 are meeting Plan on sales.
The Electronics Division delivered 181k less margin vs plan, while the Personal Care Division delivered 79k more.
Compared with the previous year the Electronics Division is up 10% in sales...
...and 15 % in margin.
The year end outlook shows some improvement in terms of sales..
...but not really in terms of margins.
Geographical spread of sales...
...is not forecasted to change significantly in the last two months of the year.
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.
Major Regions are on Plan in terms of sales....
...and growing relative to last year
Most regions are under plan in terms of margins...
..but generally beating last year's results.
Year end, most Regions are aligned to or over Plan in terms of sales...
...and do not improve in terms of margins.
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
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 with both Divisions.
..and is over previous year sales.
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...
...that all did relatively well compared to the previous year.
Small multiples
Slice variance by dimension.
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.
Indirect costs have improved 500k vs Plan...
... and the EBITDA bridge will look like this, with a 500k improvement tied to overhead reduction at the net margin -aka after indirect costs- level.
While lower than Plan indirect costs increased substantially relative to the previous year more than offsetting and improvement of gross margin.
Plan vs Actual timeline
See when Actuals beat Plan and vice versa.
Strong improvement relative to previous year in the most recent quarter.
Most Product Groups of the Electronic Division underperformed in term of gross margin. Some had a proportionally greater COGS increase.
WHAT CHANGED IN SALES
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.
Relatively to the previous year the picture is different
Here is a different view that highlights the change by line.
WHAT CHANGED IN MARGINS
The loss in margins in the Electronics Division came from Mobile, Comp and Video.
Here is a slightly different view that gives a few more details.
Here is the vs previous year view showing a significant increase in indirect costs.
