ABC Analysis

1. Overview

1.1 Concept

The full name of ABC classification should be ABC classification inventory control method. It is also called Pareto analysis or Barreto analysis, Plato analysis, primary and secondary factor analysis, ABC analysis, and ABC management. It is usually called the "80 to 20" rule.

The core idea of classification: a few projects contributed most of the value. Take styles and sales as an example: Style A accounts for 10% of the total, but it contributes 80% of sales.

Note: The 2-8 analysis is roughly the same as the ABC classification, and it is a small number of projects that contribute most of the value. The difference is that the ABC classification method divides the objects into three categories, while the 2-8 analysis is divided into two categories, A and B. Products of category A account for a small part of the total and contribute 80% of sales.

1.2 Problems solved

According to the main technical or economic characteristics of things, classify and sort them to distinguish the key points from the general ones, so as to determine the management mode differently. It divides the analyzed objects into three categories: A, B, and C. There is no clear numerical limit for the three categories of items.

1.3 Expected effect

In the "Pareto Chart of Merchandise Sales" component of the "Pareto Analysis of Shopping Malls", you can view the sales of each brand's merchandise and the corresponding cumulative proportion of sales. According to ABC analysis method, brand goods are arranged in descending order according to sales volume, and are divided into three categories: Class A, class B and class C, with sales accounting for 80%, 10% and 10%, which are displayed in column charts of different colors, as shown in the following figure:

1.4 Implementation ideas

For the existing data, use the self-service dataset for data processing, or use the formula to add calculation indicators when making components to calculate the proportion of cumulative sales indicators. When making components, divide the objects into ABC proportion according to the proportion of cumulative sales (2-8 analysis will divide the objects into 2-8 proportion). Finally, reflect the analysis results through column charts of different colors. The data processing idea is shown in the figure below:

The key to achieve the division of ABC proportions is to calculate the "Proportion of cumulative sales" indicator, and then divide it according to the proportion of cumulative sales indicator. This article introduces two methods for calculating the proportion of cumulative sales.

Example 1: The "Proportion of cumulative sales" indicator is obtained by adding calculation indicator in the dashboard and using formulas for data processing.

Example 2: The indicator of "Proportion of cumulative sales" is obtained by data processing in the self-service dataset. The obtained indicator can be used repeatedly in multiple components.

2. Example 1: Implementation of adding calculation indicator to the dashboard

2.1 Create a self-service dataset

1) Under "Data Preparation", select "Retail industry" and add "Self-Service Dataset", as shown in the figure below:

2) Select fields such as "Brand description" and "Sales" from the "Retail industry" business package. Create a self-service dataset "Pareto data analysis", as shown in the following figure:

3) Update the data and check the effect at "Data Preview", as shown in the figure below:

2.2 Making components

2.2.1 Create components

Select "Pareto data analysis" and "Create component", enter the dashboard information, and click "OK", as shown in the figure below:

2.2.2 Add proportion of cumulative sales indicator

Click "+" to add a calculation indicator, name it "Proportion of cumulative sales", and enter the formula ACC_SUM(SUM_AGG(Sales)/TOTAL(SUM_AGG(Sales),0,"SUM")). Click "OK", as shown in the figure below:

The formula description is shown in the following table:

In addition, the effect can also be achieved by calculating the cumulative sales first and then calculating the proportion. The formula is as follows: ACC_SUM(SUM_AGG(Sales),0)/TOTAL(SUM_AGG(Sales),0,"SUM")

2.2.3 Making components

Drag the field of the area to be analyzed into the corresponding horizontal and vertical axis, select "Custom Chart", and set "Sales" as a column chart, and "Proportion of cumulative sales" as a line chart, as shown in the following figure:

2.2.4 Set value axis

Set the value axis for the "Proportion of cumulative sales" field, as shown in the figure below:

Select "right-value axis" for the shared axis, check the "Axis Scale Customization", and set the "maximum value" and "minimum value", as shown in the figure below:

For detailed settings, please refer to: Chart Setting Axis.

2.2.5 Sort

Arrange "Brand description" in "Descending" according to "Sales(Sum)", as shown in the figure below:

2.2.6 Add "ABC Classification" indicator

1) Add a calculation indicator, name it "ABC Classification", and enter the formula IF (Proportion of cumulative sales<0.8,1, IF (Proportion of cumulative sales>0.9,3,2)) , where "1" represents category A commodities, "2" represents category B commodities, and "3" represents category C commodities. As shown below:

Note: For the 2-8 analysis, the steps are roughly the same as those of ABC analysis. The analysis results can be obtained by modifying the formula here and classifying by different colors. The formula can be modified to "IF (Proportion of cumulative sales < 0.8,1,2)". A few brands that contribute 80% of "Sales" are analyzed and divided into class A. The corresponding formula returns a value of 1, and the rest represent class B and the corresponding value is 2.

2) Divide different types of "Brand descriptions" by color. Drag the "ABC Classification" field into the "color" box under "Graphic Properties> Sales", and select "Custom" for the gradient interval. Select the number of color intervals according to the type of division. Since there are three types of products, select "3" for the number of intervals and set the corresponding color, as shown in the following figure:

2.2.7 Set cordon

Set the cordon on the Pareto chart as shown in the figure below:

At the same time, you can set up a dynamic Pareto chart, add filter components and other required component types. Take the effect chart in section 1.1.2 of this article.

2.3 Effect view

For details of the effect, please refer to section 1.3 of this article.

3. Example 2: Implementation of self-service dataset

Take the "Sales schedule" and "Brand dimension table" under the "Retail industry" business package as examples to conduct a Pareto analysis of the sales of major brands and get the company's most important brands. Realize analysis effect by making self-service dataset.

3.1 Create a self-service dataset

3.1.1 Add field

1) Click "Add table", add a self-service dataset, as shown in the figure below:

3.1.2 Group summary

Click on "+", Select "Group Summary", drag "Category description" into the "Group" box, and drag "Sales" into the "Summary" box, as shown in the figure below:

3.1.3 Sort

Click onClick "+", choose "Sort". Click "Add a sort",choose "Sales" Field and select "Descending" as shown in the figure below:

3.1.4 Calculating total sales

1) Click "+", choose "New column". As shown below:

2) Name the newly added column "Total sales", select "all values/within group". The value rule is "all values", the value comes from "Sales", and the statistical method is "SUM", as shown in the figure below:

3.1.5 Calculte the cumulative total

1) Name the new column "Cumulative total" and select cumulative value/within group. The value rule is "cumulative value", the value is from "Sales", click "OK". As shown below:

2) Get the "Cumulative total" field as shown in the figure below:

3.1.6 Calculate the cumulative proportion

1) Add a new column, name it "Cumulative proportion", enter the formula Cumulative total / Total sales.Click "OK". As shown below:

Note: The cumulative total and total sales in the formula cannot be entered manually, you need to click on the field name of the numeric field.

2) Get the "Cumulative proportion" field as shown in the figure below:

3.1.7 Effect view

Name the self-service dataset "Pareto Analysis Table" and click "OK". Enter the data preparation interface, click "update data". As shown below:

3.2 Making chart components

Refer to section 2.2 of this article for the calculation method of chart components.

3.3 Effect view

For details, please refer to section 1.3 of this article.

4. Conclusion analysis

Creating the dashboard came to the following conclusions: