ABC Analysis using Conditional Formatting in Excel - KING OF EXCEL

Wednesday, September 13, 2023

ABC Analysis using Conditional Formatting in Excel

 ABC analysis is an inventory management technique in which stock is classified in usually three categories on the basis of their importance. Such classification help management to concentrate their efforts on such products that are more important in terms of revenue they are generating, cost or any other measure entity is using to judge importance.

You can read in detail about ABC analysis here: What is ABC Analysis?

In simple words, ABC analysis categorizes inventory in three categories:

  1. A – most important
  2. B – important
  3. C – less important or marginally important

Lets say we are measuring importance in terms of revenue generated by the product. It is usually observed that 10-15% of the products offered by the entity generate 70-80% of the total sales revenue. With Excel classifying inventory in this fashion is really simple. Lets say:

  1. Products that generate 70% of sales be categorized as A
  2. Products that generate next 25% sales be categorized as B
  3. And products that generate rest of the revenue which is 5% are categorized as C

In short:

0% – 70% – A
70% – 95% – B
95% – 100% – C

This is what we are after in this tutorial after we done understanding how Excel helps us in categorizing products based on given data:

abc analysis excel

ABC Analysis using Excel

Step 1: Open up Excel and have above information of classification in cell A1:

00.7
0.70.95
0.951

Step 2: Copy the following data and paste it in your worksheet in cell A5

ProductAmount
Sesme – 120,000
Sesme – 217000
Sesme – 314450
Sesme – 412283
Sesme – 510441
Sesme – 68875
Sesme – 77544
Sesme – 86412
Sesme – 95450
Sesme – 104633
Sesme – 113938
Sesme – 123347
Sesme – 132845
Sesme – 142418
Sesme – 152055
Sesme – 161747
Sesme – 171485
Sesme – 181262
Sesme – 191073
Sesme – 20912
Sesme – 21775
Sesme – 22659
Sesme – 23560
Sesme – 24476
Sesme – 25405
Sesme – 26344
Sesme – 27292
Sesme – 28248
Sesme – 29211
Sesme – 30179
Sesme – 31152
Sesme – 32129
Sesme – 33110
Sesme – 3494
Sesme – 3580
Sesme – 3668
Sesme – 3758
Sesme – 3849
Sesme – 3942
Sesme – 4036
Sesme – 4131
Sesme – 4226
Sesme – 4322
Sesme – 4419
Sesme – 4516
Sesme – 4614
Sesme – 4712
Sesme – 4810
Sesme – 499
Sesme – 508

Following animation will walk you through the process:

abc 1

Step 3: Go to cell C5 and make a heading Cumulative revenue and in cell:

  • C6 put the formula: = B6
  • C7 put the formula: =C6+B7 and drag the fill handle down to cell C55 or simply double click to populate the cells down.

abc 2

Step 4: Select all cells from A6 down to A55 using mouse or keyboard.

Tip: You can do this quickly by having an active cell A6 and Press and Hold Shift + Ctrl keys and press Down arrow key. And hitting Ctrl+Backspace to come back up to active cell.

Step 5: Go to Home tab > Styles group > Click conditional formatting drop down button > click new rule > From the dialogue box click use a formula to determine which cells to format > Put the following formula:

=IF(C6<=$B$1*$C$55,TRUE)

Make any necessary changes. I went with Red fill and white font color for the first category i.e. A.

abc 3

Now we will repeat step 5 two more time to add two more conditions for category B and C. Just follow along:

Step 6: Go to Home tab > Styles group > Click conditional formatting drop down button > click new rule > From the dialogue box click use a formula to determine which cells to format > Put the following formula for category B goods:

=AND(C6>=$B$1*$C$55,C6<=$B$2*$C$55)

I chose dark orange color with white font to make category B stand out.

Following animation will help walk you through the steps:

abc 4

Step 7: Go to Home tab > Styles group > Click conditional formatting drop down button > click new rule > From the dialogue box click use a formula to determine which cells to format > Put the following formula for category C goods:

=AND(C6>=$B$2*$C$55,C6<=$B$3*$C$55)

I chose parrot green color with white font to categorize C type products.

Following illustration will prove helpful:

abc 5

Step 8: Sometimes you have to really tell Excel in what order to apply the conditional formatting as it really messes up. But sometimes it tends to understand. But I wasn’t so lucky this time.

So just to be on the safe side we better have our category A formatting at the top, Category B formatting condition in mid and Category C condition in the last as the following illustration shows:

abc 6

Now you have three bands of colors.

  1. Product 1 – 7 are category A products
  2. Product 8 – 18 are category B products
  3. Product 19 – 50 are category C products

Coming back to our ABC concept. As we have total 50 items on sale and just by looking at the colors we can observe that just 7 are generating 70% of total sales of the entity. If we take a ratio of 7 over total products of 50 it comes up 14%. In other words only 14% of all the products on offer are making 70% of total revenue of the entity.

On the other hand we have 30-31 products in category C and this makes up 30/50 x 100 = 60% of total products and generating just 5% of total revenue.

Understanding this fact, entity should reinforce their efforts towards those 7 products that are in category A and be very vigilant towards their fluctuations as it will hurt revenues significantly. On the other hand, although Category C items are more in terms of count but still not nearly as important as category A products.

Popular Posts