Skip to content

RETAILER🛍️

img

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.

multi_tier_bar

The Electronics Division delivered 181k less margin vs plan, while the Personal Care Division delivered 79k more.

multi_tier_bar (1)

Compared with the previous year the Electronics Division is up 10% in sales...

multi_tier_bar

...and 15 % in margin.

multi_tier_bar (2)

The year end outlook shows some improvement in terms of sales..

multi_tier_bar

...but not really in terms of margins.

multi_tier_bar (1)

Geographical spread of sales...

stackedbar

...is not forecasted to change significantly in the last two months of the year.

stackedbar (1)

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

marimekko (1)

Barmekko Plot

See how two metrics - here sales and margins - play along a given dimension, here division.

barmekko (1)

marimekko

Major Regions are on Plan in terms of sales....

multi_tier_bar (2)

...and growing relative to last year

multi_tier_bar (1)

Most regions are under plan in terms of margins...

multi_tier_bar

..but generally beating last year's results.

multi_tier_bar (3)

Year end, most Regions are aligned to or over Plan in terms of sales...

multi_tier_bar (2)

...and do not improve in terms of margins.

multi_tier_bar (3)

barmekko (4)

barmekko

marimekko (1)

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

visualization (6)

marimekko (4)

marimekko (3)

marimekko (5)

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.

waterfall (1)

..and is over previous year sales.

waterfall

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.

waterfall (2)

The loss comes from the Mobile, Comp and Video Lines of the Electronics Division...

waterfall (3)

...that all did relatively well compared to the previous year.

waterfall

Small multiples

Slice variance by dimension.

Mobile and Comp Lines experienced higher COGS and somewhat weaker volumes vs plan.

waterfall (4)

Mobile Line COGS increased particularly steeply relatively to revenues.

visualization

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...

multi_tier_bar (3)

... 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.

waterfall (5)

While lower than Plan indirect costs increased substantially relative to the previous year more than offsetting and improvement of gross margin.

waterfall (5)

Plan vs Actual timeline

See when Actuals beat Plan and vice versa.

ac_vs_py (1)

ac_vs_py (2)

multi_tier_column

multi_tier_column (1)

ac_vs_py (3)

ac_vs_py (4)

ac_vs_py (1)

ac_vs_py

Strong improvement relative to previous year in the most recent quarter.

image-20220111152439605

Most Product Groups of the Electronic Division underperformed in term of gross margin. Some had a proportionally greater COGS increase.

visualization (11)

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.

waterfall

image-20220107212144700

Relatively to the previous year the picture is different

waterfall (2)

image-20220107212456949

Here is a different view that highlights the change by line.

waterfall

image-20220107212819199

WHAT CHANGED IN MARGINS

The loss in margins in the Electronics Division came from Mobile, Comp and Video.

waterfall (1)

image-20220107213136810

Here is a slightly different view that gives a few more details.

waterfall (3)

image-20220107213453279

Here is the vs previous year view showing a significant increase in indirect costs.

waterfall (4)

image-20220107214010877