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.
Technology and Office Supplies have similar CAGR over four years.
Copiers have the highest CAGR over four years
In terms of 4 year CAGR, Home Office and Corporate are aligned.
Across the four year period the South's CAGR has been low.
First Class and Same Day Shipping 4-year CAGR also much higher
Most customers buy products in two or in all three categories.
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.
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%).
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.