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

multi_tier_bar (1)

multi_tier_bar

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

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.

barmekko

Divisions operate across all continents.

marimekko (4)

All Regions where on Plan in terms of sales....

multi_tier_bar (2)

and, with the exception of North America, e few points under plan in terms of margins.

multi_tier_bar (3)

Some product lines, such as Wearable or Audio, have high margins. Others, such as Baby Care, less so.

barmekko (2)

Ormtan, the most important product in terms of sales and margins,...

barmekko (3)

...is sold by both Divisions,...

marimekko (2)

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)

A few important product groups,....

marimekko (3)

...and a few important products are also sold by both divisions.

marimekko (5)

A few sales persons also work for both Divisions.

marimekko (6)

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

newplot

Small multiples

Slice variance by dimension.

...with both Divisions.

newplot (1)

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.

newplot (2)

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

newplot (3)

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

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

Say indirect costs have improved 500k vs Plan...

image-20211123141401893

...the EBITDA bridge will look like this, with a 500k improvement tied to overhead reduction at the net margin -aka after indirect costs- level.

image-20211123141501883

Plan vs Actual timeline

See when Actuals beat Plan and vice versa.

The Electronics Division was below plan on margins across the whole period....

ac_vs_py

...and had higher COGS vs plan in every month except June.

ac_vs_py (2)

The Mobile line was below plan in margins for the whole year.

ac_vs_py (1)

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

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.

image-20210609180233581

4/5 of Ormtan revenue comes from the Electronics Division. In % terms, Ormtan grew more in Personal Care.

image-20210609181131925

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.

image-20210603105642206

THIRD RESULT COMBINATION

Excluding Baby Care, Personal Care grew sales by a heathy 11%. Smartphones lost 7% sales.

image-20210603105106240

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.

waterfall (11)

FORTH RESULT COMBINATION

A few combinations experienced sale losses of over 50% and might deserve attention.

image-20210603105857356

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.

waterfall (12)

WHAT CHANGED IN MARGINS

FIFTH RESULT COMBINATION

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

image-20210603110320947

In the mobile line, Hatimshy products, Tablets and Imosa Smartphones did not do well in terms of margins.

image-20210603111203183

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.

waterfall (13)

SIXTH RESULT COMBINATION

The margin loss in Electronics is due to higher unit COGS vs plan...

image-20210603112703549

...and can be broken down further.

image-20210603112916605

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.

waterfall (14)

Back to top