Skip to content

ELECTRONICS

You are an engagement manager at a top consulting firm. Your customer, a large Private Equity firm, has been looking into acquiring Contoso. Contoso is a fictional conglomerate operating in consumer electronics. Yesterday, after a meeting of Contoso's board in which the CEO expressed interest for the acquisition, things have picked up speed.

img

The Private Equity fund wants you to do a quick due diligence. By tomorrow, they want a document for their board, to help them decide whether to start negotiations. Contoso has provided the Private Equity fund with a 1M row dataset (to run the dataset in the app, type "Contoso" in the "choose a sample dataset" widget) with the company's sales, volumes, discounts and direct costs for the last two years. The file does not include indirect costs that you understand have changed only marginally.

Your colleague Giancarlo mentioned a variance analysis tool that might help in a case like this. For lack of a better options, you decide to try it out.

ANSWER FIRST

If you can't wait, here is a quick preview of the findings.

Contoso is growing nicely in both revenues and margins, driven by sales and higher mix of the newly launched Fabrikam Brand Camcorders. Deluxe Cell Phones also performed nicely.

North America and Asia are performing well while Europe is struggling in terms of growth. Excluding the new Fabrikam Camcorders, Europe lost 50M sales.

Regular Class products performed well in terms of Sales, but suffered from a high increase in COGS.

Stores represent the largest Channel with significant growth. Small and Super Stores appear to be thriving while Medium Stores are performing relatively less well.

MAIN METRICS OVERVIEW

Contoso is now selling over 6M products per year, at an average price of around 270$.

visualization (26)

Here are few things worth noting at a high level. Europe is loosing both sales and margins...

visualization (37)

...No Discount sales and margins are down...

visualization (38)

... and Projectors and Digital SLR Cameras are experiencing weakening sales and margins.

visualization (40)

Product prices go all the way up to 1000$, but are generally below 300$. Similar profile between the two years.

distribution

The correlation between Units and Price reveals a, weak, positive relationship that can be ignored both because of the weakness of the correlation index...

visualization (2)

...and because of the shape of residual plot.

visualization (3)

AGGREGATE VARIANCE ON SALES

In terms of Aggregate Variance, Contoso is doing well. Aggregate Variance is simply the sales difference between two periods. Sales grew by 17% to 1.7B.

newplot (5)

Here is a set of four facts that explain the sales growth in terms of total variance.

1️⃣ In all Regions, Fabrikam Camcorders drove growth by 190M.

2️⃣ All other products in North America Region grew by 67M.

3️⃣ All other products in Europe Region fell by 49M.

4️⃣ All other products in Asia Region grew by 33M.

*️⃣ This leaves zero balance tied to other issues.

image-20210426165641415

PRICE AND VOLUME VARIANCE

Price and volume variance splits variance in its volume and its price component.

Contoso's growth is driven by volume.

newplot (4)

Here is a set of five facts that explain sales growth in terms of Price and Volume & Mix variance.

1️⃣ Fabrikam Camcorders drove growth by 187M in volume at constant prices.

2️⃣ Excluding Fabrikam Camcorders, Regular Class products grew by 54M in volume at constant prices.

3️⃣ The De Luxe Touch Screen Phone by The Phone Company grew by 8M in volume at constant prices.

4️⃣ In the Store Channel, in all other products, Medium Store Sizes lost 17M in volume at constant prices.

5️⃣ In all other products, Small Store Sizes gained 8M in volume at constant prices .

*️⃣ This leaves zero balance tied to other issues.

image-20210426180345554

MIX VARIANCE

Mix Variance splits the impact of volume into "pure" volume change and change of mix. The change of mix is the change in the proportion of the products sold in both periods. It is equivalent to the change in average price, net of the impact of the price change of the items. Mix variance is only calculated for the combinations of dimensions that are present in both periods.

For Contoso, product mix has remained stable in terms of average price. Revenue growth has been driven by pure volume growth.

waterfall (1)

Here is a set of five facts that explain gross sales growth in terms of Price, Volume and Mix variance.

1️⃣ Fabrikam Camcorders drove growth by 153M in volume at constant prices and mix.

2️⃣ All Cameras and Camcorders products drove growth by 92M in mix at constant volume and prices.

3️⃣ Excluding Fabrikam Camcorders, other Camera and Camcorders fell by 68M in volume at constant prices and mix.

4️⃣ Excluding Cameras and Camcorders, Small Store Sizes increased sales by 58M in volume at constant prices and mix.

5️⃣ The De Luxe Touch Screen Phone by The Phone Company grew by 13M in Medium Store Sizes in volume.

*️⃣ This leaves a negative 10M balance tied to other issues.

image-20210426180614664

Below is the detail of the row three result (67M volume loss after Mix gain) by Subcategory and by Brand. The issue appears to impact mainly Digital SLR cameras.

visualization (19)

visualization (35)

visualization (36)

Note that the net volume loss of non-Fabrikam Cameras and Camcorders is -9M. The row three -67M result is "after" a positive change in mix.

image-20210506095050603

NEW AND LOST VOLUME VARIANCE

New and Lost Volume Variance shows the impact of "New" and "Lost" combinations - combinations that exist only in one of the two periods. A combination of a given period is "New"/"Lost" if at least one item of that combination does not exist in any combination of the other period.

For Contoso, growth is driven by "New" combinations, such as new products, or existing products sold in new markets, or existing products, sold in the same markets in promotions that did not exist the previous year. Volumes of products sold in both years have decreased.

waterfall

Here is a set of five facts that explain gross sales growth in terms of New and Lost variance.

1️⃣ New offerings in North America drove growth by 900M in volume at constant prices.

2️⃣ Lost offerings of Regular Class products impacted revenues negatively by 480M.

3️⃣ Existing offerings at No Discount impacted revenues negatively by 400M.

4️⃣ Excluding North America, New offerings of Regular Class products drove growth by 250M.

5️⃣ Lost offerings of De Luxe Class products impacted revenues negatively by 175M.

*️⃣ This leaves a positive 150M balance tied to other issues.

image-20210427111415411

PRICE, VOLUME & MIX, DISCOUNTS VARIANCE

Price, Volume & Mix, Discounts Variance splits the impact of change of Net Sales into these three components.

For Contoso, discounts are growing, but at a healthy ratio compared to the increase in Gross Sales.

waterfall

Here is a set of five facts that explain net sales growth in terms of Price, Volumes & Mix, Discounts variance.

1️⃣ Fabrikam Camcorders drove net sales growth by 187M in volume at constant prices.

2️⃣ Excluding Fabrikam Camcorders, Regular Class products net sales grew by 52M in volume at constant prices.

3️⃣ In the Store Channel, in all other products, Medium Store Sizes net sales lost 16M in volume at constant prices.

4️⃣ In the Store Channel, for all products, higher discounts led to a loss of net sales of 11M.

5️⃣ Sales in Small Stores, not of Camcorders, not Regular, and not in the Stores Channel, gained 10M in volume at constant prices.

*️⃣ This leaves a negative 4M balance tied to other issues.

image-20210426182139668

AGGREGATE VARIANCE ON MARGIN

After COGS Variance shows the aggregated margin variance net of direct costs.

Contoso's gross margins are healthy, are growing in absolute terms...

waterfall (11)

...and are stable in terms of % of revenues

waterfall (12)

Here is a set of five facts that explain gross margin growth in aggregate terms.

1️⃣ Fabrikam Camcorders drove margin growth by 107M.

2️⃣ Excluding Fabrikam Camcorders, sales in Sales in Small Stores drove 22M increase in margins.

3️⃣ Excluding Fabrikam Camcorders, Medium Store Sizes sales negatively impacted margins by 19M.

4️⃣ Excluding Fabrikam Camcorders, Super Store Sizes sales positively impacted margins by 11M.

5️⃣ Excluding Fabrikam Camcorders, Medium Online Store Sizes sales positively impacted margins by 9M.

*️⃣ This leaves a negative 15M balance tied to other issues.

image-20210426182351460

PRICE, VOLUME & MIX, COSTS VARIANCE

Price, Volume & Mix, Costs Variance splits the effect of price, volume & mix, costs (COGS and discount) on the change of margin.

Contoso's margin change is driven by volume.

waterfall (20)

Here is a set of five facts that explain gross margin growth in terms of Price, Volume, Mix and Cost variance impact.

1️⃣ Increased volumes of Fabrikam Camcorders drove margin up by 107M.

2️⃣ Excluding Fabrikam Camcorders, higher volumes in Small POS drove margin up by 22M.

3️⃣ Excluding Fabrikam Camcorders, lower volumes in Medium stores drove margin down by 19M

4️⃣ Excluding Fabrikam Camcorders, higher volumes in Super POS drove margin up by 11M.

5️⃣ Excluding Fabrikam Camcorders, higher Online volumes in Medium POS drove margin up by 9M.

*️⃣ This leaves a negative 11M balance tied to other issues.

image-20210426182752710

ANNEX - SLICING BY DIMENSION

In this section the data is analyzed with a more traditional slicing-by-dimension approach.

The dataset contains nine different dimensions, some more relevant than others.

image-20210426115250846

SLICING BY CHANNEL

Contoso operates in four different Channels: Store, Online, Reseller and Catalog. The Store Channel is by far the largest, and brings two thirds of sales.

visualization (27)

Prices and volumes increased in all Channels.

visualization (1)

Store Channel pricing profiles are similar among each other and have not changed between the two years.

newplot (34)

The correlation between price and units is both nonsensical, since it is positive, and extremely low. It is just noise.

visualization (4)

AGGREGATE VARIANCE ON SALES

Channels are sorted by absolute value of change.

All Channels are growing. The Store Channel is growing at a healthy 14%. Smaller channels are growing faster. The Catalog Channel, the smallest and the top performer in terms of growth rate, is growing at 27%.

waterfall (8)

PRICE AND VOLUME VARIANCE

For all Channels, Volume & Mix, not Price, is the engine of growth.

waterfall

MIX VARIANCE

Mix Variance, sign of average price change net of the impact of the price change of the single items, was marginally negative for all Channels except Catalog.

waterfall (2)

NEW AND LOST VOLUME VARIANCE

In all Channels, growth is driven by "New" combinations, such as new products, or existing products sold in new markets. Negative growth is driven mainly by discontinued offerings. For the Store Channel, negative growth is also tied to lower volumes of offerings present in both periods.

waterfall (1)

PRICE, VOLUME & MIX, DISCOUNTS VARIANCE

In all Channels, discounts are growing at a healthy ratio relative to the increase in volumes.

waterfall (1)

AGGREGATE VARIANCE ON MARGIN

Healthy margin growth in all channels.

waterfall (13)

PRICE, VOLUME & MIX, COSTS VARIANCE

For all Channels margin change driven by volume.

waterfall (21)

SLICING BY STORE SIZE

Contoso operates in four types of Stores - Super, Large, Medium, and Small. Most sales come from Medium stores.

visualization (28)

On the aggregate, average price similar across Store Sizes,...

visualization (14)

...with similar price profiles.

newplot (35)

AGGREGATE VARIANCE ON SALES

Store Sizes are sorted by absolute value of change.

All Store Sizes are growing. Small Size Stores are growing the most, at 25%. Medium Size Stores are growing at 11%. Super Size Stores at only 5%, and Large Size Stores at 18%.

waterfall (29)

PRICE AND VOLUME VARIANCE

Volume & Mix, not Price, is the engine of growth for all Store Sizes.

waterfall (7)

MIX VARIANCE

Sales mix change plays a marginally negative role in driving sales, for all Store Sizes.

waterfall (9)

NEW AND LOST VOLUME VARIANCE

In all Store Sizes, growth is driven by "New" things, such as new products, or existing products sold in new markets. Lower volumes are driven by discontinued offerings and also by offerings present in both periods.

Super Size Stores is the only exception with positive Changed Volume offset by equivalent Lost and New volumes.

waterfall

PRICE, VOLUME & MIX, DISCOUNTS VARIANCE

For all Store Sizes discounts are growing at a healthy ratio compared to the increase in volumes. For Super Size Stores, discounts are actually decreasing.

waterfall (9)

AGGREGATE VARIANCE ON MARGIN

Healthy growth of margins for all Store Sizes with the exception of Large.

waterfall (10)

PRICE, VOLUME, MIX, COSTS VARIANCE

Margin change driven by volume.

waterfall (22)

SLICING BY PROMOTION

Contoso sometimes changes promotion campaigns between years.

No Discount Sales fell significantly, more than compensated by the newly launched North America Holiday and Back to School promotions.

visualization (29)

Promotions and No Discount are now clustered together.

visualization (9)

Similar price profiles between promotions.

newplot (38)

AGGREGATE VARIANCE ON SALES

Promotions are sorted by absolute value of change.

New Promotions contributed by almost 900M of sales. Discontinued Promotions, No Discount, and Promotions that run in both years delivered 700M less sales in the most recent year.

waterfall (30)

PRICE AND VOLUME VARIANCE

By definition, Promotions held only in one period have no price impact.

waterfall (2)

MIX VARIANCE

By definition, Promotions held only in one period have no mix impact.

waterfall (4)

NEW AND LOST VOLUME VARIANCE

Promotions held only in one period have either all "New" or all "Lost" volumes. All products sold under those Promotions were not sold under those same promotions in the other year, because those promotions did not exist.

waterfall (3)

PRICE, VOLUME & MIX, DISCOUNTS VARIANCE

Limited impact of discounts.

waterfall (3)

AGGREGATE VARIANCE ON MARGIN

Margin change driven by volume.

waterfall (15)

PRICE, VOLUME, MIX, COSTS VARIANCE

All Promotions had a healthy Volume-to-Cost change ratio.

waterfall (23)

SLICING BY CONTINENT

Contoso operates in three geographical areas. Most sales and most growth comes from the North American Region.

visualization (30)

Both average prices and volumes increased in Asia, the smallest Region in terms of revenues.

visualization (10)

Very similar price profiles between Continents.

newplot (36)

AGGREGATE VARIANCE ON SALES

Continents sorted by absolute value of change.

Growth driven by North America and to a lesser extent by Asia. Europe loosing sales.

waterfall (31)

PRICE AND VOLUME VARIANCE

Volume & Mix is the engine of growth for all Continents. Price not relevant.

waterfall (3)

MIX VARIANCE

Sales mix change plays a significant role in the loss of Europe sales. Irrelevant for other Continents.

waterfall (5)

NEW AND LOST VOLUME VARIANCE

In all Continents, growth is driven by "New" things, such as new products, or existing products sold in new markets. In North America, lower volumes driven by discontinued offerings and by the reduction of offerings present in both periods. In Asia offerings present in both periods are driving part of the growth.

waterfall (4)

PRICE, VOLUME & MIX, DISCOUNTS VARIANCE

For all Continents, discounts are growing at a healthy ratio compared to the increase in volumes. For Asia, discounts are actually decreasing.

waterfall (4)

AGGREGATE VARIANCE ON MARGIN

Healthy growth of margins for North America and Asia Continents. Reduction of margin for Europe.

waterfall (16)

PRICE, VOLUME, MIX, COSTS VARIANCE

Change of margins driven by volumes.

waterfall (24)

SLICING BY CLASS

Contoso sells Economy, Regular and Deluxe products. Most sales come from Regular that is growing briskly as is Deluxe .

visualization (31)

Regular grew in terms of volumes and of average price.

visualization (12)

Similar price profiles in the two years.

newplot (2)

AGGREGATE VARIANCE ON SALES

Classes sorted by absolute value of change.

Growth driven by Regular and Deluxe. Deluxe growing fastest. Economy flat.

waterfall (32)

PRICE AND VOLUME VARIANCE

Volume & Mix, not Price, is the engine of growth for all Classes.

waterfall (5)

MIX VARIANCE

Sales mix change generally irrelevant, plays a negative role for Regular and Economy Classes.

waterfall (7)

NEW AND LOST VOLUME VARIANCE

In all Classes, all growth is driven by "New" things, such as new products, or existing products sold in new markets. Lower volumes driven by discontinued offerings and also by offerings present in both periods.

waterfall (6)

PRICE, VOLUME & MIX, DISCOUNTS VARIANCE

For Regular and Deluxe Classes discounts are growing at a healthy ratio compared to the increase in volumes. For Economy the ratio of increase in discounts to increase in volumes is a less positive 2 to 3, reflecting a more challenging business.

waterfall (6)

AGGREGATE VARIANCE ON MARGIN

Healthy growth of margins for all Deluxe and Regular Classes. Decreasing margin for Economy.

waterfall (18)

PRICE, VOLUME, MIX, COSTS VARIANCE

Margin change driven by volume.

waterfall (25)

SLICING BY PRODUCT CATEGORY

Contoso operates sells its products in four main categories: Cameras and Camcorders, Cell Phones, Computers, Tv and Video. Most sales come from Cameras and Camcorders.

visualization (32)

On the aggregate, the average price of Cameras and Camcorders increased significantly,...

visualization (13)

...while the price profile remained broadly similar .

newplot (39)

AGGREGATE VARIANCE ON SALES

Product Categories sorted by absolute value of change.

Growth driven by Cameras and Camcorders and by Tv and Video. Others Categories flat.

waterfall (33)

PRICE AND VOLUME VARIANCE

Volume & Mix, not Price, is the engine of growth for all Product Categories.

waterfall (6)

MIX VARIANCE

Negative sales mix change almost completely offsets volume growth for Computers, and has marginal impact for other Product Categories.

waterfall (8)

NEW AND LOST VOLUME VARIANCE

In all Product Categories, growth is entirely driven by "New" things, such as new products, or existing products sold in new markets. Lower volumes driven by discontinued offerings and by offerings present in both periods.

waterfall (8)

PRICE, VOLUME & MIX, DISCOUNTS VARIANCE

For high growth Product Categories discounts are growing at a healthy ratio compared to the increase in volumes. For Computers less so, reflecting challenging market conditions.

waterfall (7)

AGGREGATE VARIANCE ON MARGIN

Healthy growth of margins for Cameras and Camcorders, Tv and Video and Cell Phones. Essentially flat margins for Computers.

waterfall (19)

PRICE, VOLUME, MIX, COSTS VARIANCE

Margins driven by volumes.waterfall (26)

SLICING BY PRODUCT SUBCATEGORY

Camcorders are the largest subcategory, and the one that is growing the most both in price and in units.

visualization (33)

Significant average price increase for Camcorders.

visualization (11)

Camcorder prices span between 200 and 1000$.

newplot

AGGREGATE VARIANCE ON SALES

Product Subcategories sorted by absolute value of change.

Growth driven by Camcorders and Home Theater Systems.

waterfall (34)

PRICE AND VOLUME VARIANCE

Volume & Mix, not Price, is the engine of growth for all Product Subcategories.

waterfall (4)

MIX VARIANCE

Sales mix change has a marginal impact, and is positive only for the Camcorder Product Subcategory.

waterfall (6)

NEW AND LOST VOLUME VARIANCE

In all Product Subcategories, all growth is driven by "New" things, such as new products, or existing products sold in new markets. Lower volumes are driven by discontinued offerings and also by offerings present in both periods.

waterfall (5)

PRICE, VOLUME & MIX, DISCOUNTS VARIANCE

For the top Product Subcategories discounts are growing at a healthy ratio compared to the increase in volumes. For Other Product Subcategories, the ratio of the increase in discounts to the increase in volumes is actually much higher, about 1 to 3.

waterfall (5)

AGGREGATE VARIANCE ON MARGIN

Healthy growth of margins for all Camcorder and Home Theater Product Subcategories. Margin reduction for Projectors and Digital Cameras. Others show slight increase.

waterfall (17)

PRICE, VOLUME, MIX, COSTS VARIANCE

Margins driven by volumes.

waterfall (27)

SLICING BY BRAND

Contoso sells under different brands. Fabrikam, the largest, grew the most, driven by price and volume.

visualization (34)

Fabrikam went from 600 to 700$ average price, with an increase of volumes. Contoso Brand increased volumes with weakening prices.

image-20210323133914920

Fabrikam sells products at a high price point.

newplot (3)

It is interesting to notice that even when filtering down to Fabricam Camcorders, the non sensical positive correlation persists, and actually becomes more accentuated.

visualization (5)

This is not the fault of the data anymore, since the Residual plot and the QQ plot seem ok.

visualization (6)

visualization (6)

It simply states the fact that a larger number of expensive quality camcorders are bought compared to a tiny quantity of low price low quality ones.

This remains the same even if we filter down to the Regular Class, where most of the action is. The correlation falls to zero. About the same number of Camcorders are bought at the different price points...

visualization (8)

.. as can be seen from the distribution plot

distribution (1)

AGGREGATE VARIANCE ON SALES

Brands sorted by absolute value of change.

Growth driven by Fabrikam Brand. Litware also performing well. All growth driven by 4 brands that represent one third of sales.

waterfall

PRICE AND VOLUME VARIANCE

Volume & mix, not price, is the engine of growth for all Brands.

image-20210323145237958

MIX VARIANCE

Mix marginally richer for Fabrikam, poorer for all others. Significantly impoverished mix for Contoso Brand.

waterfall (3)

NEW AND LOST VOLUME VARIANCE

In all Brands, all growth is driven by "New" things, such as new products, or existing products sold in new markets. Lower volumes driven by discontinued offerings and offerings present in both periods.

waterfall (2)

PRICE, VOLUME & MIX, DISCOUNTS VARIANCE

For all Brands discounts are growing at a healthy ratio compared to the increase in volumes.

waterfall (2)

AGGREGATE VARIANCE ON MARGIN

Healthy growth of margins for Fabrikam, Litware and Southridge Brands. Others Brands: negative or flat margin growth.

waterfall (14)

PRICE, VOLUME, MIX, COSTS VARIANCE

Margins driven by volume.

waterfall (28)