ABC analyst concepts

So let’s pratice !!!

Firstly, I will copy the original dataset to other object. I think it’s a good habit because sometime you code wrong leading to wrong result and you want to run again but the previous object is changed to new one and you have to import the data again. It’s not smooth.

So adjusting the data to standardize form to suitable for coding in R. Remember df have a column refer to datetime class, you should divide into 2 cols: date and time cols separately. You should download this data procurement_data before.

Code 
## Copy into new object:
df<-procurement_data 

## Adjusting:
df<-df %>% 
  # standardize column name syntax
    janitor::clean_names() %>% 
    distinct() 

df <- df %>% 
  # break the datetime PO into date and time cols separately
    mutate(po_date = as.Date(df$po_date_time),
           po_time = hms(format(df$po_date_time,"%H:%M:%S")))

df$money<-runif(nrow(df),1000,10000)

ABC analyst:

In supply chain management, you typically faces the task of ABC analysis to classify your major customers by comparing their revenue or purchase frequency. So to impelemnt this task, I create a data contains the vendor’s name and the total revenue caculated by the amount of PO sign the total value of PO.

In R, we have package {ABCanalysis} to encounter this task by simply one function: ABCanalysis showed below.

Code 
# Sum total PO of each vendor:
ABC<-df %>% 
  group_by(vendor_name) %>% 
  summarise(revenue = round(sum(po_amount * money)/10^3,0))

library(ABCanalysis)
abc = ABCanalysis(ABC$revenue,PlotIt=TRUE)

Code 
setA = ABC$revenue[abc$Aind]
setB = ABC$revenue[abc$Bind]
setC = ABC$revenue[abc$Cind]

abc.df<-cbind(c(setA,setB,setC),
              c(rep("A",length(setA)),
                rep("B",length(setB)),
                rep("C",length(setC)))
) %>% 
  as.data.frame() 

colnames(abc.df)<-c("revenue","group")

abc.df$revenue<-as.numeric(abc.df$revenue)

ABC<-left_join(abc.df,
               ABC,
               by = "revenue")

Although it’s result is accurate but I don’t like the default plot because it’s too ugly and color is terrible.

So I suggest another package {gt} in R help us a lot to present fancy result.

Code 
# Finally plot the result:
A<-ABC %>%  
  filter(group == "A") %>% 
  select(3,1,2)
  
library(gt)
library(gtExtras)
gt(A[order(A$revenue,decreasing = TRUE),][1:68,]) %>% #Reorder the column in data
  cols_label(vendor_name = md("**Name of vendor**"),
             group = "Class",
             revenue = md("**Total revenue**")) %>% 
  tab_header(
    title = md("Result of ABC analyst"),
    subtitle = md("Source: Xuan Loc, Rstudio")
  ) %>% 
  cols_align(
    align = "left",
    columns = "vendor_name"
  ) %>% 
  cols_align(
    align = "center",
    columns = "revenue"
  ) %>% 
  fmt_number(
    columns = 'revenue', 
    decimals = 2, 
    locale = 'de',
    pattern = '{x}$'
  ) %>% grand_summary_rows(
    columns = revenue,
    fns =  list(total = ~sum(., na.rm = TRUE),
                avg = ~mean(., na.rm = TRUE),
                s.d. = ~sd(., na.rm = TRUE)),
    fmt = list(~ fmt_currency(.,
      suffixing = TRUE,
      locale = list(decimals = 3,
                    locale = "fr_BE",
                    curency = "USD")$locale))) %>% 
  tab_options(
    data_row.padding = px(0),
    summary_row.padding= px(0),
    row_group.padding = px(0),
    column_labels.padding = px(1),
    heading.padding = px(1)) %>% 
  gt_theme_excel()
gt(A[order(A$revenue,decreasing = TRUE),][69:137,]) %>% #Reorder the column in data
  cols_label(vendor_name = md("**Name of vendor**"),
             group = "Class",
             revenue = md("**Total revenue**")) %>% 
  tab_header(
    title = md("Result of ABC analyst"),
    subtitle = md("Source: Xuan Loc, Rstudio")
  ) %>% 
  cols_align(
    align = "left",
    columns = "vendor_name"
  ) %>% 
  cols_align(
    align = "center",
    columns = "revenue"
  ) %>% 
  fmt_number(
    columns = 'revenue', 
    decimals = 2, 
    locale = 'de',
    pattern = '{x}$'
  ) %>% grand_summary_rows(
    columns = revenue,
    fns =  list(total = ~sum(., na.rm = TRUE),
                avg = ~mean(., na.rm = TRUE),
                s.d. = ~sd(., na.rm = TRUE)),
    fmt = list(~ fmt_currency(.,
      suffixing = TRUE,
      locale = list(decimals = 3,
                    locale = "fr_BE",
                    curency = "USD")$locale))) %>% 
  tab_options(
    data_row.padding = px(0),
    summary_row.padding= px(0),
    row_group.padding = px(0),
    column_labels.padding = px(1),
    heading.padding = px(1)) %>% 
  gt_theme_excel()

Result of ABC analyst

Source: Xuan Loc, Rstudio

Name of vendor

Total revenue

 Class
Kenyan Elemental Sailing Oy 53.951.290,00$ A
Japanese Joomba Electric Co-Op 20.762.945,00$ A
Far East Golden Oil & Gas S.A. 18.631.097,00$ A
PacificNorth Oceana Electric Incorporated 16.250.200,00$ A
East Coast Oceana Disposal S.E. 14.332.902,00$ A
PacificNorth Grade Office Services P.C. 12.388.764,00$ A
North Texas BigHaul Software Company 11.563.756,00$ A
American Oceana Law Services Company 10.292.280,00$ A
Korean Joomba Tire & Auto P.C. 10.276.077,00$ A
California Golden Tire & Auto S.A.P.I 10.101.186,00$ A
South East Gopher Sailing Co. 9.241.639,00$ A
Panhandle Gopher Computers P.C. 8.976.148,00$ A
California Golden Architects Company 8.291.100,00$ A
Hungarian Golden Oil & Gas S.A. 8.032.052,00$ A
PacificNorth Gopher Architects Company 7.352.153,00$ A
The Bay Joomba Architects UD 7.105.259,00$ A
Global Elemental Law Services Partners 7.024.219,00$ A
Panhandle Solutions Equestrian UD 5.146.810,00$ A
Frankfurter BigHaul Concierge P.C. 5.133.610,00$ A
East Coast Slick Consulting G.P. 5.024.861,00$ A
East Coast Joomba Disposal S.A.P.I 4.407.388,00$ A
Kenyan Tactical Computers UD 4.396.834,00$ A
California Oceana Disposal P.C. 4.271.337,00$ A
California Brothers Cafeteria Services P.C. 3.807.094,00$ A
Global Tactical Shipping P.C. 3.619.175,00$ A
East Coast Brothers Illustrations O.S. 3.531.826,00$ A
East Coast Solutions Law Services G.P. 2.911.958,00$ A
Global Big Tree Airlines Incorporated 2.857.772,00$ A
Hungarian Joomba Concierge Incorporated 2.857.564,00$ A
China Joomba Cafeteria Services L.L.C. 2.780.962,00$ A
The Bay Elemental Law Services UD 2.780.399,00$ A
Hungarian De'Angelo Sailing L.L.C. 2.663.413,00$ A
North Texas Seaside Computers Distributed 2.625.364,00$ A
East Coast Joomba Sailing S.A.P.I 2.516.608,00$ A
South East Tactical Equestrian O.S. 2.513.868,00$ A
California Elemental Equestrian L.L.C. 2.343.778,00$ A
Seine Convo Publishing Oy 2.304.214,00$ A
South African Golden Shipping Distributed 2.244.448,00$ A
Frankfurter Oceana Cafeteria Services S.A.P.I 2.180.394,00$ A
Panhandle Tactical Electric S.A. 1.955.658,00$ A
Kenyan Olympic Airlines S.A.P.I 1.872.171,00$ A
South African Oceana Shipping G.P. 1.849.184,00$ A
Japanese Grade Office Services Oy 1.801.002,00$ A
The Bay Grade Publishing Limited 1.680.301,00$ A
Hungarian Solutions Sailing Incorporated 1.659.202,00$ A
South East BigHaul Oil & Gas L.L.C. 1.648.525,00$ A
East Coast Oceana Airlines L.P. 1.639.955,00$ A
American De'Angelo Consulting UD 1.627.552,00$ A
China De'Angelo Software S.A.P.I 1.562.136,00$ A
Kenyan Elemental Software L.P. 1.510.894,00$ A
American Golden Computers O.S. 1.463.200,00$ A
East Coast Tactical Land Development Pte Ltd. 1.461.306,00$ A
Seine Pizaz Equestrian Incorporated 1.454.493,00$ A
The Bay Pizaz Software Inc. 1.447.414,00$ A
Kenyan De'Angelo Tire & Auto L.L.C. 1.424.183,00$ A
East Coast Elemental Office Services Limited 1.415.790,00$ A
North Texas League Architects Partners 1.413.347,00$ A
Far East Oceana Computers Inc. 1.407.851,00$ A
Kenyan Elemental Tire & Auto Distributed 1.373.737,00$ A
Bavarian Brothers Electric Company 1.372.374,00$ A
Kenyan Grade Land Development Oy 1.364.962,00$ A
Frankfurter Oceana Law Services L.L.C. 1.354.335,00$ A
Hungarian Elemental Publishing Inc. 1.308.421,00$ A
Global Seaside Airlines Inc. 1.238.428,00$ A
Global BigHaul Airlines Company 1.214.401,00$ A
PacificNorth Holders Architects Distributed 1.195.517,00$ A
Kenyan Gopher Architects Distributed 1.193.733,00$ A
Far East Grade Cafeteria Services O.S. 1.181.872,00$ A
total — €350,58M —
avg — €5,16M —
s.d. — €7,46M —

Result of ABC analyst

Source: Xuan Loc, Rstudio

Name of vendor

Total revenue

 Class
Frankfurter Brothers Sailing Pte Ltd. 1.172.574,00$ A
Bavarian Tactical Computers Incorporated 1.122.298,00$ A
Korean Oceana Architects Inc. 1.102.064,00$ A
Frankfurter Brothers Equestrian Inc. 1.086.910,00$ A
Frankfurter Olympic Cafeteria Services P.C. 1.086.571,00$ A
Hungarian Olympic Sailing L.P. 1.078.703,00$ A
South African BigHaul Architects Inc. 1.076.663,00$ A
East Coast Seaside Architects L.P. 1.036.226,00$ A
South African Holders Law Services Distributed 1.030.244,00$ A
North Texas Joomba Architects P.C. 1.025.562,00$ A
Kenyan Pizaz Equestrian Incorporated 1.015.967,00$ A
California Big Tree Law Services Distributed 1.002.867,00$ A
North Texas Oceana Airlines UD 1.000.637,00$ A
North Texas Pizaz Equestrian O.S. 997.886,00$ A
American League Land Development S.A.P.I 948.043,00$ A
East Coast Holders Publishing Limited 924.121,00$ A
The Bay Seaside Land Development S.A. 888.003,00$ A
Hungarian Brothers Tire & Auto Company 882.589,00$ A
Panhandle Elemental Office Services Distributed 853.022,00$ A
Seine BigHaul Law Services G.P. 844.400,00$ A
Frankfurter Olympic Equestrian G.P. 794.628,00$ A
China Big Tree Software Oy 789.923,00$ A
Hungarian Slick Tire & Auto Oy 781.540,00$ A
South African Holders Disposal L.L.C. 770.663,00$ A
East Coast Convo Airlines Partners 759.823,00$ A
East Coast Pizaz Shipping G.P. 752.674,00$ A
North Texas Joomba Law Services S.E. 746.092,00$ A
Bavarian Golden Cafeteria Services O.S. 742.827,00$ A
Bavarian Convo Concierge Distributed 737.590,00$ A
East Coast Slick Computers Limited 729.763,00$ A
Global Brothers Concierge Partners 712.040,00$ A
South African Oceana Law Services Distributed 706.701,00$ A
Global BigHaul Tire & Auto Pte Ltd. 704.878,00$ A
Far East Oceana Architects Incorporated 693.325,00$ A
East Coast BigHaul Concierge S.A.P.I 685.347,00$ A
China Brothers Electric S.A.P.I 657.213,00$ A
Korean Gopher Illustrations Limited 656.052,00$ A
Bavarian Oceana Electric Oy 651.420,00$ A
Seine Slick Oil & Gas G.P. 643.759,00$ A
Kenyan BigHaul Computers Partners 638.494,00$ A
Hungarian Seaside Law Services S.E. 634.071,00$ A
California Brothers Publishing P.C. 625.677,00$ A
PacificNorth Oceana Publishing S.A.P.I 621.977,00$ A
American Tactical Architects S.E. 619.037,00$ A
East Coast Golden Office Services Limited 617.692,00$ A
East Coast Slick Equestrian Co-Op 614.409,00$ A
Kenyan Elemental Concierge L.L.C. 601.059,00$ A
Far East Golden Architects S.A.P.I 599.667,00$ A
South African Brothers Law Services Distributed 596.603,00$ A
China Grade Airlines Partners 595.785,00$ A
Frankfurter De'Angelo Shipping P.C. 593.416,00$ A
Panhandle Brothers Illustrations Co. 591.704,00$ A
Seine Gopher Computers L.L.C. 588.333,00$ A
Korean Gopher Publishing Co. 587.720,00$ A
American Holders Law Services G.P. 584.399,00$ A
Bavarian Gopher Law Services L.P. 552.088,00$ A
North Texas De'Angelo Airlines S.A. 547.706,00$ A
Far East BigHaul Shipping S.E. 545.405,00$ A
Far East Convo Illustrations L.L.C. 531.713,00$ A
Panhandle Olympic Sailing Co. 525.243,00$ A
East Coast Holders Tire & Auto Inc. 519.684,00$ A
South African Solutions Sailing Inc. 512.268,00$ A
Far East Slick Architects UD 509.585,00$ A
Japanese BigHaul Consulting Inc. 507.120,00$ A
Global BigHaul Disposal Company 499.338,00$ A
South East Olympic Office Services Incorporated 498.542,00$ A
Bavarian Golden Architects G.P. 497.093,00$ A
East Coast Pizaz Disposal G.P. 495.760,00$ A
China League Cafeteria Services Company 494.394,00$ A
total — €50,84M —
avg — €736,78K —
s.d. — €197,31K —

Now we have classified all the companies on our list, but we need to be prepared for any future collaborations. When considering collab with a new company, we will need to know which classification it falls under in order to decide whether to sign a long-term contract. In this section, I’d like to introduce the Random Forests method, a machine learning algorithm that combines the outputs of multiple decision trees to produce a single result. For more details, you can refer to this page: Random Forest

In R, we can easily implement this using the following code. It’s important to note a few things:

  • The rpart function has default parameters that can limit the growth of the tree, so we should consider adjusting them. For example:

    • The argument “minsplit” represents the minimum number of observations required in a node for a split to be attempted.

    • The argument “minbucket” represents the minimum number of observations in any terminal node. We should observe the impact of overriding these parameters.

  • Package {parttree} maybe not avaliable in your Rstudio version. You can update your version or directly install from online source by function remote::install_github("https://github.com/grantmcdermott/parttree")
Code 
df1<-left_join(ABC %>% select(c(vendor_name, group)),
               df,
               by = "vendor_name")

library(rpart)
library(parttree)
tree<-rpart(po_amount ~ po_saving_amount + money, data = df1)

ggplot(data = df1,
       aes(x = po_saving_amount,
           y = money))+
  geom_parttree(data = tree,
                aes(fill = po_amount),
                alpha = 0.3)+
  geom_point(aes(col = po_amount))+
  theme_minimal() +
  scale_fill_distiller(
    limits = range(df1$po_amount, na.rm = TRUE), 
    aesthetics = c('colour', 'fill')
  )

The cluster of customer group based on the total PO amount

Next we will move to the next section is about: Report concepts