Skip to content

BETTER SALES ANALISYS

Sales analysis - understanding "how", and possibly "why", sales and margins changed between one period and another - is a common activity that often takes significant time.

Fortunately, the structure of sales datasets often similar: a number of dimension columns, a date column, and a few metric columns. This is the intuition behind our brand new tool, 'Mparanza.

'Mparanza makes it possible to standardize and automate a large part of the sales analysis pipeline: 'Mparanza helps you work faster, eliminate errors and identify insights that you might otherwise have missed.

Let's dive in with a quick example.

We use a test dataset that ships with Tableau. It contains information about products, sales and profits. To run it, open the "choose a sample dataset" widget and load the "Tableau Superstore" file.

PLOTTING SALES AND MARGINS

As a first step lets run a few plots to understand more about our fictitious company.

Give a look at the charts below.

In a nutshell, the company sells three categories of products (Technology, Office Supplies and Furniture). Technology is growing the fastest and brings in the largest share of profits. Furniture has a problem of low margins.

Copiers, the most profitable Sub-category, is also the one with the highest CAGR. Unfortunately, the company also has a large number of non profitable products that bite into profits.

In terms of Segment, the company operates in the Home Office, Corporate and Consumer Segment with all three of its Categories. The three segments have similar profit margins. Home Office is growing the fastest.

In terms of Geography, the South and the West Region are driving growth, with the West enjoying the highest 4-year CAGR. Margins are significantly lower in the South and Central Regions.

Products shipped First Class and Same-Day are growing the most. Same-Day deliveries are significantly less profitable.

image-20210913220633359

image-20210916121850656

Technology and Office Supplies have similar CAGR over four years.

image-20210914120839402

image-20210914104621858

Copiers have the highest CAGR over four years

image-20210914121024710

image-20210914112039080

image-20210914112300578

image-20210914123058890

image-20210914123417952

image-20210914123917949

image-20210914124101078

image-20210915140513224

image-20210916104659108

image-20210914124939330

image-20210913221347346

image-20210914101133953

image-20210914104919155

In terms of 4 year CAGR, Home Office and Corporate are aligned.

image-20210914121232538

image-20210914111609138

image-20210914101657314

image-20210914101909693

image-20210914105200620

Across the four year period the South's CAGR has been low.

image-20210914121410623

image-20210914113004816

image-20210914102232196

image-20210914105818686

image-20210914102359485

image-20210914105446831

First Class and Same Day Shipping 4-year CAGR also much higher

image-20210914121555003

image-20210914170659293

image-20210914102606257

Most customers buy products in two or in all three categories.

image-20210914114457105

VARIANCE CALCULATION

Let's calculate variance in terms of sales and margins, both aggregated and split in its different components (volume, price, mix).

In a nutshell, the company's 20% sales increase was driven by higher volumes, at flat prices and unchanged mix and discount levels. The growth was driven by new offerings.

Margins remained sluggish and grew less than sales, driven by volumes.

image-20210913174133418

image-20210913175035287

image-20210913175618566

image-20210913180056988

image-20210913175953980

image-20210914100344923

VARIABLE DIMENSION SALES VARIANCE

Variance is generally calculated along a given dimension, for instance by Country, or by Channel.

Unfortunately there is no guarantee that a single given dimension will always best "explain" the change in sales.

For example, say that "increase of sales in China" is the most important driver. The second most important might not well be not the change of sales in a different Country, but rather "increase of sales in the Direct Channel (China excluded)". Sometimes a combination of dimensions - for instance "increase of sales in China in the Direct Channel" - provides the best explanation of the change of sales.

This is "variable dimension" variance.

Below an example. Office Supplies Category sales, with 34% change, represent the top driver of growth. The second factor, however, are Home Office Segment sales that, net of Office Supplies, grew 93%. Each result row is detailed in its elements.

It is possible to plot the data of a given result row. This is helpful, for instance, to understand precisely which "non Office Supplies" Home Office Sub Categories had a 93% growth rate.

The tool generates alternative sets of results, which helps not to miss hidden insights. For instance, we might discover that First Class Shipping orders grew 70%, more than 3x the average 20% rate.

If a dimension, for example Shipping Mode, is not relevant, it can be excluded from the analysis: we get yet another set of results high-lighting the growth of the West Region (+33%).

image-20210914141928445

image-20210914180643725

image-20210914142853159


image-20210914145058441


image-20210914145745830


image-20210914150144054


image-20210914150621132

image-20210914181609366

image-20210914182455081

VARIABLE DIMENSION MARGIN VARIANCE

Margin variance can be similarly calculated.

In the example below, sales in the West Region are an important driver of the increase in Margins. They are mostly tied to Technology and Office Supplies Category products.

image-20210914183317811

image-20210914183902143