13 Calculate ABC Class

Now, we’re going to create a code to calculate the classic ABC class. 3 classes, defined as follow :

Class A : group of items which represent the first 80% of the sales.
Class B : group of items which represent the next 15% of the sales.
Class C : group of items which represent the remaining 5%.

For this, we will :

calculate the percentage of the total (of a variable) that a value represents.
use the function cumsum()to accumulate the percentage of the total.
use the function case_when() to affect the ABC class.

Let’s start by uploading the needed libraries. We will use the usual tidyverse, and also the R package planr, to use the built-in demo data frame called “blueprint”.

# ETL
library(tidyverse)

# Supply Chain
library(planr)

14 Get demo data frame

We will use a demo data frame, “blueprint”, from the package planr .

The original data frame has :

10 products (called DFU, standing for Demand Forecast Unit).
a period of time.
some values related to Demand and Supply : Demand (Sales Forecasts), Opening (Inventories), Supply.
and some stocks levels parameters : Min.Cov and Max.Cov.

In this example, we’re interested in calculating the ABC class of this portfolio, so we then just need 2 variables : the products (DFU) and the total amount of Sales Forecasts of each of them, over the horizon of time.

Let’s start by aggregating the Demand by DFU :

# upload data
data("blueprint")

# set a working df
df1 <- blueprint

# replace missing values by zero
df1$Demand <- df1$Demand |> replace_na(0)

# aggregate
df1 <- df1 |> group_by(DFU) |>
  summarise(Demand = sum(Demand)
            )

glimpse(df1)

Rows: 10
Columns: 2
$ DFU    <chr> "Item 000001", "Item 000002", "Item 000003", "Item 000004", "It…
$ Demand <dbl> 20294, 60747, 5975, 68509, 119335, 101810, 13823, 207511, 3177,…

We obtain a small data frame with 2 variables :

10 products.
their related amount of sales.

15 Calculate ABC class

Now we’re going to calculate a classic ABC classification, as follow :

sort the products by decreasing (total of) Demand, with the function arrange().
calculate the percentage of the total Demand that each product represents.
calculate the accumulated percentage of Demand with the function cumsum().
affect the ABC class with the function case_when().

# sort
df1 <- df1 |> arrange(desc(Demand))
    
# calculate % of total Demand
df1$Demand_pc <- df1$Demand / sum(df1$Demand)
    
# calculate accumulated Demand
df1$acc_Demand_pc <- cumsum(df1$Demand_pc)
    
# add ABC Class
df1 <- df1 |> mutate(ABC.Class = case_when(
  acc_Demand_pc <= 0.8 ~ "A",
  acc_Demand_pc <= 0.95 ~ "B",
  TRUE ~ "C") # close case_when
  )
    
    
# Get Results
Summary_data <- df1

# display
Summary_data

# A tibble: 10 × 5
   DFU         Demand Demand_pc acc_Demand_pc ABC.Class
   <chr>        <dbl>     <dbl>         <dbl> <chr>    
 1 Item 000008 207511   0.335           0.335 A        
 2 Item 000005 119335   0.193           0.528 A        
 3 Item 000006 101810   0.164           0.692 A        
 4 Item 000004  68509   0.111           0.803 B        
 5 Item 000002  60747   0.0981          0.901 B        
 6 Item 000001  20294   0.0328          0.934 B        
 7 Item 000010  18122   0.0293          0.963 C        
 8 Item 000007  13823   0.0223          0.985 C        
 9 Item 000003   5975   0.00965         0.995 C        
10 Item 000009   3177   0.00513         1     C

We notice that the variable ABC.Class gives a (ABC) class to each product, following our criterias. The class A, with 3 products ,represents ~80% of the total sales, the classes A and B together ~95%, and the class C, with 4 items, the remaining 5%.