Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 1 of 2]

In this post, you will be learning to perform ABC analysis using the DSUM() function. There are instances when a small percentage of causes in your business lead to a large percentage of impacts – meaning that drawing your focus on these small percentages of causes could help you have better control over the large percentage of impacts.

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 1 of 2]

 

 

 

 

 

 

Now, Let us understand how we can use the DSUM() function that we have already learnt to perform ABC/Pareto Analysis….

Work Scenario

You are working on analysing the inventory of your company. The objective is to identify key SKUS out of the many SKUs that are worth focusing. One efficient way to do this is by running an ABC Analysis. Given the large dataset that needs to be analysed, you wonder how you can accomplish this using MS Access……

What is Pareto Analysis?

Pareto Analysis is an analysis technique based on the idea that only a small percentage of “causes” normally lead to a large percentage of “effects”. It is commonly referred to as a “80-20” rule. It means that in most of the activities, 80% of the effects are caused by only 20% of causes. Note that the number 80-20 is to be taken as a thumb-rule. It is important to understand that the real essence of this technique is to identify those small numbers of factors (not necessarily 20%) that are leading to a bulk of the effects (not necessarily 80%).

Some of the few examples of Pareto Analysis are:

  • 20% of the Products accounting for 80% of the company’s sales revenue. So, it makes sense that the management should focus its attention on 20% of these products.
  • 15% of the features in a software causing 82% of the bugs. This way the product manager can direct the quality team and the engineers to focus on those 15% of the features.
  • 22% of the SKUs in the inventory accounting for 85% of the materials requests from the production department. This information can enable the procurement/inventory department to maintain reasonable level of stock for these 22% of the SKUs.

As you can see from the above examples, Pareto Analysis has its applications in a wide variety of fields.

ABC Analysis – An extension of Pareto Analysis

In inventory management or in SCM in general, ABC Analysis is commonly used to classify or categorize inventory in three different classes – A Class, B Class and C Class. ‘A’ class items are those that account for 80% of the effects, ‘B’ Class are those that account for 15% of the effects whereas ‘C’ Class items account for the bottom 5% of the effects.

Example: A warehouse may classify all its SKUs based on daily average sales such that:

  • A Class SKUs – The ones accounting for 80% of the average daily sales will be classified as A Class SKUs i.e. Fast Moving SKUs.
  • B Class SKUs – The ones accounting for the next 15% of the average daily sales will be classified as B Class SKUs i.e. Medium Moving SKUs.
  • C Class SKUs – The ones accounting for the bottom 5% of the average daily sales will be classified as C Class SKUs i.e. Slow Moving SKUs.

On classifying these SKUs, you’ll often find that out of the total SKUs that are in the warehouse inventory, a very small number of SKUS will be A Class items and yet would be accounting for 80% of the average daily sales. Also, there would be large number of SKUs in the inventory accounting for just 5% of the average daily sales.

So, it naturally makes sense for the management to direct their focus on these small percentage but high impact A Class items and sort of spend less resources on managing the C Class SKUs.

Steps to Run ABC Analysis

When you receive the data, you first need to identify the following two things:

  1. The items in the data that you want to categorise as Class A,B or C. The items could be SKUs, products, customers or product features
  2. The items in the data on the basis of which you want to classify – these could be sales (categorising SKU or products on the basis of their sales) or purchases requests (categorising the customers on the  basis of the purchase requests they make and so on).

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 1 of 2]

Note that for ABC Analysis, you will be categorising data starting with the highest value down to the lowest value such that top x% of the data will fall in highest category and the bottom x% will fall in the lowest category.

So, once you get the data follow the following Steps:

  1. Create a query to arrange the data in descending Order.
  2. After this you need to Auto number the data. So, create a table based on the above query using a “Make-Table” query. (Note that we are doing this step to avoid the Running Sum error in case there are duplicate values in column [AverageDailySales]

To know more, please refer the post: Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

Once you have the new table ready, you’ll now have the data in the following format:Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 1 of 2]

In order to perform ABC Analysis, you need to perform the following steps on the above table:

STEP 1 : Calculate the Cumulative Sum or Running total using the DSUM function (See previous post for Steps)

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 1 of 2]

STEP 2 : Calculate Cumulative Percentage – In this step, you need to determine each record forms what percentage of the total AverageDailySales. So, this step 2 will consists of two sub-steps as follows:

  • Step 2a – Calculate the grand total of the field [AverageDailySales] and display that result across each record.
  • Step 2b – Across each record, calculate the running total will be what percentage of the total AverageDailySales.

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 1 of 2]

On running the above query:Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 1 of 2]

 

STEPS3: Classify into A-B-C Classes using IIF() statement – Once you have the cumulative percentages, you would now want to classify the SKUs such that the top 80% (starting from the topmost record) would be A Class, the next 15% (i.e. from 80% to 95%) would be B Class whereas the bottom 5% (i.e. from 80% to 95%) would be C Class items.

In this case, you will make use of an IIF() function. An IIF() function is a simple function. This function tests for a particular condition as provided by the user. If the condition is TRUE, then it provides a value as specified by the user. If the condition is FALSE, even then it provides a value as specified by the user. As you might have noticed, the user needs to specify the “Condition”, and the value to be returned if the condition is TRUE or FALSE. In fact, instead of providing the  value, you can also provide a Nested IIF() function that would RUN, if the values are TRUE or FALSE.

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 1 of 2]

Now using this IIF() function in the query as follows:

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 1 of 2]

On Running this query, you get the final result as follows:

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 1 of 2]

Summarize Results:

The only final thing you would now like to know from the above result is a small table summarising:

  1. The number of SKUs that fall within each class (A, B or C) and their contribution (in%) to overall sales.
  2. And sales contributed by each category and its percentage to the total sales.

So, in short you are looking at a result something like shown below:

Class

Total SKUs

SKU Split

Total Sales

Total Sales %

A

X

X%

M

M%

B

Y

Y%

N

N%

C

Z

Z%

O

O%

Total

(X+Y+Z)

100%

(M+N+O)

100%

Since, you already know that there were total 195 SKUs under analysis, (X+Y+Z) should be equal to 195 and roughly a small percentage of X should account for a relatively large percentage of M – thereby validating that indeed A class SKUs are those small % of SKUs that account for a relatively high percentage of total sales.

To do this, you would summarise the above query by writing another query.

  1. In order to count the SKUs, you could simply select the field that you want to count and in the Total row, select Count OR you could use an expression with function Count ([SKU]). Either ways, you’ll get the same result.
  2. In order to calculate the SKU split %, you would want to divide the number of SKUs in each Class by the total SKUs under consideration. To do so, you’ll use the following formula :
    Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 1 of 2]
  3. Similarly, you can use the same formula to calculate the total sales across class A/B/C as well as the percentage of sales of each class to the total sales.

The query will look as follows:

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 1 of 2]

On running the query, you’ll get the following result: Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 1 of 2]

 

In order to add a “Total” row, click the option Totals under Home. You should get the following result:

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 1 of 2]

 

As you can see, 21.54% of the SKUs account for almost 80% of the sales. More importantly in C Class, there are almost 49% of the total SKUs that account for just 5% of the total sales. You can naturally conclude that it’s better to focus on those small percept of A Class SKUs that account for 80% sales rather than high number of C Class SKUs that account for only 5% of the total sales.

So, in this way you can run Pareto Analysis/ABC Analysis using MS Access.

Next Steps:

It is important to note that the above analysis does not take into account the product category. This means that there is a possibility that there could be some product categories which may not have any SKUs in A Class at all. But there are instances, when you would want to classify the SKUs in such a way that each product category will have its own ABC class. In the next upcoming post, you’ll learn to run such analysis across different product categories i.e. each product category will have its own ABC Class.

Advertisements

One response to “Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 1 of 2]

  1. Dear Sir Could you please help me with the how to make a report of Northwind trade Monthly Sale Report ,thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s