Loading Data
wb_sheets <- readxl::excel_sheets(wb_source)
print(wb_sheets)
## [1] "DANH MỤC MÃ VÀNG" "DATA ĐẶT HÀNG TỪ KH" "ISSUE 4-8(2019)"
## [4] "ISSUE 9-10(2019)" "ISSUE 1-4 (2020)" "LEAD TIME"
# Load everything into the Global Environment
wb_sheets %>%
purrr::map(function(sheet){ # iterate through each sheet name
assign(x = sheet,
value = readxl::read_xlsx(path = wb_source, sheet = sheet),
envir = .GlobalEnv)
})
## [[1]]
## # A tibble: 6,099 x 5
## `MÃ XMAN` `MÃ SAP` TÊN DVT PPSX
## <chr> <chr> <chr> <chr> <chr>
## 1 GY6500.A1009059 G1M1001116.0… CHI 0.9MM - KHAC MAY EWT5-6500-01 CM DAY M…
## 2 GY7200.A0000206 G1A4013399.0… Áo thun - 72Y EA DUC
## 3 GW4160.A2023096 G1A1000381.0… B.KHOEN NAM-KM CARO (BONG 9510) 4… EA DUC
## 4 GW5860.E2023096 G1A4013654.0… B.KHOEN NAM-KM CARO (BONG 9510) 5… EA DUC
## 5 GY7500.A2023096 G1D1000409.0… B.KHOEN NAM-KM CARO (BONG 9510) 7… EA DUC
## 6 GW7500.A2023096 G1D1000410.0… B.KHOEN NAM-KM CARO (BONG 9510) 7… EA DUC
## 7 GY4160.A2023096 G1D1000351.0… B.KHOEN NAM-KM CARO (BONG 9510)-4… EA DUC
## 8 GY6800.A2004014 G1D1000149.0… BAN LE DUC MO 3.0 (XIMEL) 6800-01 EA DUC
## 9 GW7500.A2004009 G1D1000276.0… BẢN LỀ 3.5 ( MO ) 7500-02 EA DUC
## 10 GW4160.A2004016 G1D1000278.0… BẢN LỀ 5.0 ( MO ) 4160-02 EA DUC
## # … with 6,089 more rows
##
## [[2]]
## # A tibble: 11,487 x 6
## Order Material `Material description` Q.Ty UOM Tháng
## <chr> <chr> <chr> <dbl> <chr> <chr>
## 1 100457238 G1M1000541.0… CHI 0.6 7200-01 5000 CM 5-20…
## 2 100457237 G1A1000777.0… BASS 5 DÂY OMEGA 6100-01 100 EA 6-20…
## 3 100457236 G1A1000775.0… BAS 5 6100-01 230 EA 6-20…
## 4 100457235 G1A1000774.0… BAS 4 6100-01 780 EA 6-20…
## 5 100457234 G1A1000773.0… BAS 2 6100-01 1600 EA 6-20…
## 6 100457233 G1A1000776.0… BAS MO DAT 0.35x25 khuôn 1068) 61… 100 EA 6-20…
## 7 100457232 G1A1000771.0… BAS MO 6100-01 70 EA 6-20…
## 8 100457231 G1A1000772.0… BAS 1 -THE DAU 6100-01 600 EA 6-20…
## 9 100456854 G1A1001013.0… LUOI GA CHO VONG XIMEN CN 2.5MM 68… 180 EA 5-20…
## 10 100456853 G1A1000850.0… CHAU TRON 3.0 2 LO TRƠN 6500-01 250 EA 5-20…
## # … with 11,477 more rows
##
## [[3]]
## # A tibble: 53,537 x 5
## `Tháng Issue` `Item Code` `Item Name` SL UOM
## <chr> <chr> <chr> <dbl> <chr>
## 1 8-2019 G1A1000302.000 KHOEN OVAL BC 3760-02 10 EA
## 2 5-2019 G1A1000302.000 KHOEN OVAL BC 3760-02 5 EA
## 3 5-2019 G1A1000302.000 KHOEN OVAL BC 3760-02 5 EA
## 4 5-2019 G1A1000302.000 KHOEN OVAL BC 3760-02 13 EA
## 5 4-2019 G1A1000302.000 KHOEN OVAL BC 3760-02 30 EA
## 6 5-2019 G1A1000305.000 CHÂU 4.0 2 LO 3760-02 40 EA
## 7 8-2019 G1A1000309.000 KHOA MO VIT NHO 3760-02 51 EA
## 8 8-2019 G1A1000309.000 KHOA MO VIT NHO 3760-02 10 EA
## 9 8-2019 G1A1000309.000 KHOA MO VIT NHO 3760-02 10 EA
## 10 8-2019 G1A1000309.000 KHOA MO VIT NHO 3760-02 30 EA
## # … with 53,527 more rows
##
## [[4]]
## # A tibble: 69,585 x 5
## `Tháng Issue` `Item Code` `Item Name` SL UOM
## <chr> <chr> <chr> <dbl> <chr>
## 1 12-2019 G1A1000309.000 KHOA MO VIT NHO 3760-02 1 EA
## 2 11-2019 G1A1000309.000 KHOA MO VIT NHO 3760-02 1 EA
## 3 11-2019 G1A1000309.000 KHOA MO VIT NHO 3760-02 5 EA
## 4 11-2019 G1A1000309.000 KHOA MO VIT NHO 3760-02 22 EA
## 5 10-2019 G1A1000309.000 KHOA MO VIT NHO 3760-02 8 EA
## 6 10-2019 G1A1000309.000 KHOA MO VIT NHO 3760-02 2 EA
## 7 10-2019 G1A1000309.000 KHOA MO VIT NHO 3760-02 5 EA
## 8 10-2019 G1A1000309.000 KHOA MO VIT NHO 3760-02 40 EA
## 9 9-2019 G1A1000309.000 KHOA MO VIT NHO 3760-02 1 EA
## 10 9-2019 G1A1000309.000 KHOA MO VIT NHO 3760-02 40 EA
## # … with 69,575 more rows
##
## [[5]]
## # A tibble: 42,914 x 5
## `Tháng Issue` `Item Code` `Item Name` SL UOM
## <chr> <chr> <chr> <dbl> <chr>
## 1 1-2020 G1A1000158.000 BTP Tròn Rỗng Không Khoen 6500-01 60 EA
## 2 1-2020 G1A1000244.000 BTP Tròn Rỗng Không Khoen 6800-01 10 EA
## 3 3-2020 G1A1000309.000 KHOA MO VIT NHO 3760-02 1 EA
## 4 3-2020 G1A1000309.000 KHOA MO VIT NHO 3760-02 1 EA
## 5 2-2020 G1A1000309.000 KHOA MO VIT NHO 3760-02 5 EA
## 6 2-2020 G1A1000309.000 KHOA MO VIT NHO 3760-02 5 EA
## 7 2-2020 G1A1000309.000 KHOA MO VIT NHO 3760-02 5 EA
## 8 2-2020 G1A1000309.000 KHOA MO VIT NHO 3760-02 1 EA
## 9 3-2020 G1A1000317.000 BAS 0-BAS M0 4160-02 14 EA
## 10 3-2020 G1A1000317.000 BAS 0-BAS M0 4160-02 40 EA
## # … with 42,904 more rows
##
## [[6]]
## # A tibble: 2,935 x 9
## `Old Material N… Material `New Desc` UOM `CHỦNG LOẠN` PPSX LEADTIME MOQ
## <chr> <chr> <chr> <chr> <chr> <chr> <dbl> <dbl>
## 1 GW4160.A2023096 G1A1000… B.KHOEN N… EA OTHER DUC 5 10
## 2 GY6800.A2004014 G1D1000… BAN LE D… EA BAN LE DUC 4 10
## 3 GW4160.A2004002 G1D1000… BAN LE DU… EA BAN LE DUC 4 10
## 4 GY6500.A2004002 G1D1000… BAN LE DU… EA BAN LE DUC 4 10
## 5 GY6800.A2004002 G1D1000… BAN LE DU… EA BAN LE DUC 4 10
## 6 GY7500.A2004002 G1D1000… BAN LE DU… EA BAN LE DUC 4 10
## 7 GW7500.A2004002 G1D1000… BAN LE DU… EA BAN LE DUC 4 10
## 8 GX7500.A2004002 G1D1000… BAN LE DU… EA BAN LE DUC 4 10
## 9 GY6500.A2004003 G1D1000… BAN LE DU… EA BAN LE DUC 4 10
## 10 GY6800.A2004003 G1D1000… BAN LE DU… EA BAN LE DUC 4 10
## # … with 2,925 more rows, and 1 more variable: EOQ <dbl>
binded_df = bind_rows(`ISSUE 1-4 (2020)`,`ISSUE 4-8(2019)`,`ISSUE 9-10(2019)`)
binded_df
## # A tibble: 166,036 x 5
## `Tháng Issue` `Item Code` `Item Name` SL UOM
## <chr> <chr> <chr> <dbl> <chr>
## 1 1-2020 G1A1000158.000 BTP Tròn Rỗng Không Khoen 6500-01 60 EA
## 2 1-2020 G1A1000244.000 BTP Tròn Rỗng Không Khoen 6800-01 10 EA
## 3 3-2020 G1A1000309.000 KHOA MO VIT NHO 3760-02 1 EA
## 4 3-2020 G1A1000309.000 KHOA MO VIT NHO 3760-02 1 EA
## 5 2-2020 G1A1000309.000 KHOA MO VIT NHO 3760-02 5 EA
## 6 2-2020 G1A1000309.000 KHOA MO VIT NHO 3760-02 5 EA
## 7 2-2020 G1A1000309.000 KHOA MO VIT NHO 3760-02 5 EA
## 8 2-2020 G1A1000309.000 KHOA MO VIT NHO 3760-02 1 EA
## 9 3-2020 G1A1000317.000 BAS 0-BAS M0 4160-02 14 EA
## 10 3-2020 G1A1000317.000 BAS 0-BAS M0 4160-02 40 EA
## # … with 166,026 more rows
binded_df <- rename(binded_df, Month_Issue = 'Tháng Issue')
binded_df <- rename(binded_df, Item_Code = 'Item Code')
df <- binded_df
Classification - Overal Picture
df2 <- df %>% group_by(Item_Code) %>% summarise(Amount = sum(SL))
Top & Bottom
TOP100 <- df2 %>% top_n(100, wt = Amount) # highest values
BOTTOM100 <- df2 %>% top_n(-100, wt = Amount) # highest values
Classification
Data Preparation
df2 <- df2[order(-df2$Amount), ]
# Get the total sales
total_Amount <- sum(df2$Amount)
# Create an empty vector for sales percentages
percentage <- c()
# Get the sales percentage for each item
for (i in df2$Amount){
percentage <- append(percentage, i/total_Amount * 100)
}
# Import dplyr package
library(dplyr)
# Add the percentage column to inventory data frame
df2 <- mutate(df2, percentage)
# Get the cumulative sum of the products sales' percentages
cumulative <- cumsum(df2$percentage)
# Add the cumulative column to inventory data frame
df2 <- mutate(df2, cumulative)
# Create an empty vector for items classification
classification <- c()
ABC Classification
for (i in df2$cumulative){
if (i < 70) {
classification <- append(classification, "A")
} else if (i >= 70 & i < 90) {
classification <- append(classification, "B")
} else {
classification <- append(classification, "C")
}
}
# Add the classification column to inventory data frame
df2 <- mutate(df2, classification)
ItemName <- binded_df
ItemName$Month_Issue <- NULL
ItemName$SL <- NULL
ItemName$UOM <- NULL
ItemName <- ItemName[!duplicated(ItemName[ , c("Item_Code")]),]
df3 <- merge(df2,ItemName, by = "Item_Code")
library(writexl)
write_xlsx(x = df3, path = "df3.xlsx",col_names = TRUE)
## [1] "/Users/admin/Documents/Linh-R Studio/ABC Inventory Analysis/df3.xlsx"
Viz & summary
# Import ggplot2 package
library(ggplot2)
table(df2$classification)
##
## A B C
## 117 223 1397
# 3 - Build inventory classification graph----
ggplot(data = df2, aes(x = classification)) +
geom_bar(aes(fill = classification)) +
labs(title = "Inventory ABC Distribution",
subtitle = "",
x = "ABC Classification",
y = "Count") + theme_bw() + theme(panel.border = element_blank(), panel.grid.major = element_blank(),panel.grid.minor = element_blank(), axis.line = element_line(colour = "black")) #stack bar
# Print results
summary <- count(df2, classification)
summary$percentage <- summary$n / sum(summary$n) * 100
summary
## # A tibble: 3 x 3
## classification n percentage
## <chr> <int> <dbl>
## 1 A 117 6.74
## 2 B 223 12.8
## 3 C 1397 80.4
write_xlsx(x = summary, path = "summary.xlsx",col_names = TRUE)
## [1] "/Users/admin/Documents/Linh-R Studio/ABC Inventory Analysis/summary.xlsx"
Classification - Month over Month
df1 <- df %>% group_by(Month_Issue, Item_Code) %>% summarise(Amount = sum(SL))
Top & Bottom
# Top & Bottom
for (i in df1$Month_Issue){
m_TOP10 <- df1 %>% top_n(2, wt = Amount)
}
for (i in df1$Month_Issue){
m_BOTTOM10 <- df1 %>% top_n(-2, wt = Amount)
}
Classification
Data Preparation
df1 <- df1[order(-df1$Amount), ]
df1 <- df1 %>% group_by(Month_Issue) %>% mutate(m_total_Amount = sum(Amount))
df1 <- df1 %>% group_by(Month_Issue) %>% mutate(percentage = (Amount/m_total_Amount) * 100)
df1 <- df1 %>% group_by(Month_Issue) %>% mutate(cumulative = cumsum(percentage))
ABC Classification
df1 <- df1 %>% group_by(Month_Issue) %>% mutate(classification = ifelse(cumulative < 70,"A", ifelse(cumulative >= 70 & cumulative < 90,"B","C")))
Viz & summary
df1_plot <- df1 %>% group_by(Month_Issue,classification) %>% summarise(N = n())
ggplot(df1_plot, aes(x = Month_Issue, y = N)) +
geom_bar(
aes(color = classification, fill = classification),
stat = "identity", position = position_stack()
) +
scale_color_manual(values = c("#00AFBB", "#E7B800", "#FC4E07"))+
scale_fill_manual(values = c("#00AFBB", "#E7B800", "#FC4E07")) + theme_bw() + theme(panel.border = element_blank(), panel.grid.major = element_blank(),panel.grid.minor = element_blank(), axis.line = element_line(colour = "black")) #stack bar
# Print results
summary1 <- count(df1, classification)
summary1 <- summary1 %>% group_by(Month_Issue) %>% mutate(percentage = (n/sum(n) * 100))
summary1
## # A tibble: 39 x 4
## # Groups: Month_Issue [13]
## Month_Issue classification n percentage
## <date> <chr> <int> <dbl>
## 1 2019-04-01 A 57 12.0
## 2 2019-04-01 B 74 15.6
## 3 2019-04-01 C 343 72.4
## 4 2019-05-01 A 91 12.4
## 5 2019-05-01 B 114 15.6
## 6 2019-05-01 C 526 72.0
## 7 2019-06-01 A 74 11.5
## 8 2019-06-01 B 97 15.0
## 9 2019-06-01 C 475 73.5
## 10 2019-07-01 A 70 10.3
## # … with 29 more rows
Top & Bottome Change Over Time
Top SKU change
##
## Attaching package: 'gridExtra'
## The following object is masked from 'package:dplyr':
##
## combine
library(grid)
p1 <- ggplot(data=df1[which(df1$Item_Code == "G1M1000508.000"),], aes(x=Month_Issue, y=Amount)) +
geom_line() +
xlab("") + theme_bw() + theme(panel.border = element_blank(), panel.grid.major = element_blank(),panel.grid.minor = element_blank(), axis.line = element_line(colour = "black")) + labs(title = "G1M1000508.000")
p2 <- ggplot(data=df1[which(df1$Item_Code == "G1M1000529.000"),], aes(x=Month_Issue, y=Amount)) +
geom_line() +
xlab("") + theme_bw() + theme(panel.border = element_blank(), panel.grid.major = element_blank(),panel.grid.minor = element_blank(), axis.line = element_line(colour = "black")) + labs(title = "G1M1000529.000")
p3 <- ggplot(data=df1[which(df1$Item_Code == "G1M1000473.000"),], aes(x=Month_Issue, y=Amount)) +
geom_line() +
xlab("") + theme_bw() + theme(panel.border = element_blank(), panel.grid.major = element_blank(),panel.grid.minor = element_blank(), axis.line = element_line(colour = "black")) + labs(title = "G1M1000473.000")
p4 <- ggplot(data=df1[which(df1$Item_Code == "G1M1000557.000"),], aes(x=Month_Issue, y=Amount)) +
geom_line() +
xlab("") + theme_bw() + theme(panel.border = element_blank(), panel.grid.major = element_blank(),panel.grid.minor = element_blank(), axis.line = element_line(colour = "black")) + labs(title = "G1M1000557.000")
grid.arrange(p1, p2, p3, p4, ncol=2)
Bottom SKU changes
library(gridExtra)
library(grid)
p1 <- ggplot(data=df1[which(df1$Item_Code == "G1A1000966.000"),], aes(x=Month_Issue, y=Amount)) +
geom_line() +
xlab("") + theme_bw() + theme(panel.border = element_blank(), panel.grid.major = element_blank(),panel.grid.minor = element_blank(), axis.line = element_line(colour = "red")) + labs(title = "G1A1000966.000")
p2 <- ggplot(data=df1[which(df1$Item_Code == "G1O1000266.000"),], aes(x=Month_Issue, y=Amount)) +
geom_line() +
xlab("") + theme_bw() + theme(panel.border = element_blank(), panel.grid.major = element_blank(),panel.grid.minor = element_blank(), axis.line = element_line(colour = "red")) + labs(title = "G1O1000266.000")
p3 <- ggplot(data=df1[which(df1$Item_Code == "G1A1000312.000"),], aes(x=Month_Issue, y=Amount)) +
geom_line() +
xlab("") + theme_bw() + theme(panel.border = element_blank(), panel.grid.major = element_blank(),panel.grid.minor = element_blank(), axis.line = element_line(colour = "red")) + labs(title = "G1A1000312.000")
p4 <- ggplot(data=df1[which(df1$Item_Code == "G1A1000490.000"),], aes(x=Month_Issue, y=Amount)) +
geom_line() +
xlab("") + theme_bw() + theme(panel.border = element_blank(), panel.grid.major = element_blank(),panel.grid.minor = element_blank(), axis.line = element_line(colour = "red")) + labs(title = "G1A1000490.000")
grid.arrange(p1, p2, p3, p4, ncol=2)
