To show how to use the ssl() function from the planr package.
SSL stands for “Short Shelf Life”.
The idea is to calculate, based on the Demand Forecasts and the different Expiry Dates of the stocks, the SSL quantity, which is the amount of remaining stocks that we won’t be able to sell.
More details on the planr package on : https://github.com/nguyennico/planr
The planr package provides tools (functions) for Supply Chain Management (Demand & Supply Planning and S&OP process).
Let’s use the demo dataset slob from the planr package.
slob stands for SLow (moving) OBsolescence risks
It’s a small dataframe with :
2 DFUs (Demand Forecasts Unit) : the Product (or Product x Location)
3 main variables : Period / Demand / Opening
We are here in Monthly Buckets (but it could be in weekly bucket as well).
The [Opening] variable contains the details of the Expiry Dates (or minimum Remaining Shelf Life for Sale) of the different batches which composed the Opening Inventories.
The quantities are placed in the Period of time when they will be expired (or won’t have enough Remaining Shelf Life for Sale).
# A tibble: 6 × 4
DFU Period Demand Opening
<chr> <date> <dbl> <dbl>
1 Item 1 2023-01-01 200 NA
2 Item 1 2023-02-01 200 NA
3 Item 1 2023-03-01 200 1000
4 Item 1 2023-04-01 200 NA
5 Item 1 2023-05-01 200 NA
6 Item 1 2023-06-01 200 NALet’s rename the variable [Opening] as [Opening.Expiry] and keep this dataset as initial_dataset.
Because later on we will calculate some projected inventories and will use a variable Opening, which is the total of all the Opening inventories.
# set a working df
df1 <- slob
# rename
df1 <- df1 |> rename(Opening.Expiry = Opening)
# replace missing values by zero
df1$Opening.Expiry <- df1$Opening.Expiry |> replace_na(0)
# keep results
initial_dataset <- df1
glimpse(initial_dataset)Rows: 44
Columns: 4
$ DFU <chr> "Item 1", "Item 1", "Item 1", "Item 1", "Item 1", "Item…
$ Period <date> 2023-01-01, 2023-02-01, 2023-03-01, 2023-04-01, 2023-0…
$ Demand <dbl> 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, …
$ Opening.Expiry <dbl> 0, 0, 1000, 0, 0, 0, 1000, 0, 0, 1000, 0, 0, 0, 0, 0, 0…To apply the ssl() function we need :
the Demand Forecasts
the different Expiry Dates of the stocks
As a result, we will get the SSL quantity, which is the amount of remaining stocks that we won’t be able to sell, at a particular period of time (when the stocks will expire).
# set a working df
df1 <- initial_dataset
# rename
df1 <- df1 |> rename(Opening = Opening.Expiry)
# apply ssl() function
calculated_ssl <- planr::ssl(
dataset = df1,
DFU,
Period,
Demand,
Opening)
# see results
head(calculated_ssl) DFU Period Demand Opening SSL.Qty
1 Item 1 2023-01-01 200 0 0
2 Item 1 2023-02-01 200 0 0
3 Item 1 2023-03-01 200 1000 400
4 Item 1 2023-04-01 200 0 0
5 Item 1 2023-05-01 200 0 0
6 Item 1 2023-06-01 200 0 0Let’s identify the Start.Date.
We need this date to place the Total of the Opening Inventories at the beginning of the horizon (which is then the Start.Date).
We will create 2 templates to calculate 2 different types of Projected Inventories :
without considering the SSL quantities
considering the SSL quantities
The idea is to show how impacting is this parameter (when the data are available) in our calculation of the Projected Inventories and Replenishment Plan (DRP).
#-------------------------------
# Get Start Date
#-------------------------------
Start.Date <- min(initial_dataset$Period)
Start.Date[1] "2023-01-01"#-------------------------------
# add Opening
#-------------------------------
# set a working df
df1 <- initial_dataset
# aggregate
df1 <- df1 |> group_by(DFU) |>
summarise(Opening = sum(Opening.Expiry))
# add Period
df1$Period <- Start.Date
# merge w/ previous dataset
df1 <- left_join(initial_dataset, df1)
# replace missing values by zero
df1$Opening <- df1$Opening |> replace_na(0)
#-------------------------------
# Add Supply
#-------------------------------
# add Supply Variable
# let's put zero by default
df1$Supply <- 0
# remove Opening.Expiry
df1 <- df1 |> select(-Opening.Expiry)
# keep results
demo_dataset <- df1
# check results
glimpse(demo_dataset)Rows: 44
Columns: 5
$ DFU <chr> "Item 1", "Item 1", "Item 1", "Item 1", "Item 1", "Item 1", "I…
$ Period <date> 2023-01-01, 2023-02-01, 2023-03-01, 2023-04-01, 2023-05-01, 2…
$ Demand <dbl> 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 20…
$ Opening <dbl> 3000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ Supply <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…#-------------------------------
# add Opening
#-------------------------------
# set a working df
df1 <- calculated_ssl
# aggregate
df1 <- df1 |> group_by(DFU) |>
summarise(Opening = sum(Opening))
# add Period
df1$Period <- Start.Date
# remove previous Opening variable
calculated_ssl <- calculated_ssl |> select(-Opening)
# merge w/ previous dataset
df1 <- left_join(calculated_ssl, df1)
# replace missing values by zero
df1$Opening <- df1$Opening |> replace_na(0)
#-------------------------------
# Add Supply
#-------------------------------
# add Supply Variable
# let's put zero by default
df1$Supply <- 0
# keep results
calculated_ssl <- df1
glimpse(calculated_ssl)Rows: 44
Columns: 6
$ DFU <chr> "Item 1", "Item 1", "Item 1", "Item 1", "Item 1", "Item 1", "I…
$ Period <date> 2023-01-01, 2023-02-01, 2023-03-01, 2023-04-01, 2023-05-01, 2…
$ Demand <dbl> 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 20…
$ SSL.Qty <dbl> 0, 0, 400, 0, 0, 0, 200, 0, 0, 400, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ Opening <dbl> 3000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ Supply <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…Usual calculation, considering the Demand as it is and w/o considering the inventories shelf lives.
# set a working df
df1 <- demo_dataset
# calculate projected inventories
df1 <- planr::light_proj_inv(data = df1,
DFU = DFU,
Period = Period,
Demand = Demand,
Opening = Opening,
Supply = Supply)
# rename
df1 <- df1 |> rename(Coverage.wo.SSL = Calculated.Coverage.in.Periods,
PI.wo.SSL = Projected.Inventories.Qty)
# keep only needed variables
df1 <- df1 |> select(DFU, Period,
Coverage.wo.SSL,
PI.wo.SSL)
# keep results
PI_wo_SSL_DB <- df1
glimpse(PI_wo_SSL_DB)Rows: 44
Columns: 4
$ DFU <chr> "Item 1", "Item 1", "Item 1", "Item 1", "Item 1", "Ite…
$ Period <date> 2023-01-01, 2023-02-01, 2023-03-01, 2023-04-01, 2023-…
$ Coverage.wo.SSL <dbl> 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0, 0, 0…
$ PI.wo.SSL <dbl> 2800, 2600, 2400, 2200, 2000, 1800, 1600, 1400, 1200, …Here we need to consider the SSL.Qty as an additional Demand.
Its a way to consume the stocks :
though they won’t be sold, they will become expired for sale
by adding the SSL.Qty to the initial Demand, we will then remove this quantity from the Projected Inventories later on
# set a working df
df1 <- calculated_ssl
#-------------------------------
# Calculate Adjusted Demand : initial + SSL.Qty
#-------------------------------
# add the 2 types of demand
df1$Demand <- df1$Demand + df1$SSL.Qty
# remove SSL.Qty
df1 <- df1 |> select(-SSL.Qty)
#-------------------------------
# Calculate projected inventories
#-------------------------------
# calculate projected inventories
df1 <- planr::light_proj_inv(data = df1,
DFU = DFU,
Period = Period,
Demand = Demand,
Opening = Opening,
Supply = Supply)
# rename
df1 <- df1 |> rename(Coverage.with.SSL = Calculated.Coverage.in.Periods,
PI.with.SSL = Projected.Inventories.Qty)
# keep only needed variables
df1 <- df1 |> select(DFU, Period,
Coverage.with.SSL,
PI.with.SSL)
# keep results
PI_with_SSL_DB <- df1
glimpse(PI_with_SSL_DB)Rows: 44
Columns: 4
$ DFU <chr> "Item 1", "Item 1", "Item 1", "Item 1", "Item 1", "I…
$ Period <date> 2023-01-01, 2023-02-01, 2023-03-01, 2023-04-01, 202…
$ Coverage.with.SSL <dbl> 9, 8, 7, 6, 5, 4, 3, 2, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0…
$ PI.with.SSL <dbl> 2800, 2600, 2000, 1800, 1600, 1400, 1000, 800, 600, …Now we add both calculated Projected Inventories to the initial dataset.
Doing so we will have a compact dataframe with all the variables we need to compare.
# merge
df1 <- left_join(initial_dataset, calculated_ssl)
df1 <- left_join(df1, PI_wo_SSL_DB)
df1 <- left_join(df1, PI_with_SSL_DB)
# keep only needed variables
df1 <- df1 |> select(DFU,
Period,
Demand,
Opening.Expiry,
SSL.Qty,
Opening,
Coverage.wo.SSL, PI.wo.SSL,
Coverage.with.SSL, PI.with.SSL,
Supply
)
# keep results
Calculated_PI_DB <- df1
glimpse(Calculated_PI_DB)Rows: 44
Columns: 11
$ DFU <chr> "Item 1", "Item 1", "Item 1", "Item 1", "Item 1", "I…
$ Period <date> 2023-01-01, 2023-02-01, 2023-03-01, 2023-04-01, 202…
$ Demand <dbl> 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 20…
$ Opening.Expiry <dbl> 0, 0, 1000, 0, 0, 0, 1000, 0, 0, 1000, 0, 0, 0, 0, 0…
$ SSL.Qty <dbl> 0, 0, 400, 0, 0, 0, 200, 0, 0, 400, 0, 0, 0, 0, 0, 0…
$ Opening <dbl> 3000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
$ Coverage.wo.SSL <dbl> 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0, 0,…
$ PI.wo.SSL <dbl> 2800, 2600, 2400, 2200, 2000, 1800, 1600, 1400, 1200…
$ Coverage.with.SSL <dbl> 9, 8, 7, 6, 5, 4, 3, 2, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0…
$ PI.with.SSL <dbl> 2800, 2600, 2000, 1800, 1600, 1400, 1000, 800, 600, …
$ Supply <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…It’s a summary table which can be useful for a cockpit to analyze the SSL risks.
It indicates :
where (which item)
when
how much
#----------------------
# Create Summary
#----------------------
# aggregate
df1 <- Calculated_PI_DB |> group_by(DFU) |>
summarise(Opening = sum(Opening),
SSL.Qty = sum(SSL.Qty)
)
# calculate % with risk of SSL
df1$SSL.Qty.pc <- df1$SSL.Qty / df1$Opening
# keep results
Summary_DB <- df1
#----------------------
# Create Sparkline
#----------------------
# aggregate
df1 <- Calculated_PI_DB |> group_by(DFU, Period) |>
summarise(SSL.Qty = sum(SSL.Qty)
)
df1 <- df1 |> group_by(DFU) |>
summarise(Quantity = list(SSL.Qty)
)
# keep results
Sparkline_DB <- df1
#----------------------
# Combine
#----------------------
# merge
df1 <- left_join(Summary_DB, Sparkline_DB)
# keep results
SSL_Summary_DB <- df1
glimpse(SSL_Summary_DB)Rows: 2
Columns: 5
$ DFU <chr> "Item 1", "Item 2"
$ Opening <dbl> 3000, 4000
$ SSL.Qty <dbl> 1000, 1600
$ SSL.Qty.pc <dbl> 0.3333333, 0.4000000
$ Quantity <list> <0, 0, 400, 0, 0, 0, 200, 0, 0, 400, 0, 0, 0, 0, 0, 0, 0, 0…# set a working df
df1 <- SSL_Summary_DB
# create reactable
reactable(df1,compact = TRUE,
defaultSortOrder = "desc",
defaultSorted = c("DFU"),
columns = list(
`DFU` = colDef(name = "Product",
minWidth = 170),
`Opening`= colDef(
name = "Opening (units)",
aggregate = "sum", footer = function(values) formatC(sum(values),format="f", big.mark=",", digits=0),
format = colFormat(separators = TRUE, digits=0),
style = list(background = "yellow",fontWeight = "bold")
),
`SSL.Qty`= colDef(
name = "SSL (units)",
aggregate = "sum",
footer = function(values) formatC(sum(values),format="f", big.mark=",", digits=0),
format = colFormat(separators = TRUE, digits=0),
style = function(value) {
if (value < 0) {
color <- "#008000"
} else if (value > 0) {
color <- "#e00000"
} else {
color <- "#777"
}
list(color = color, fontWeight = "bold")
}
),
`SSL.Qty.pc` = colDef(
name = "SSL (%)",
format = colFormat(percent = TRUE, digits = 1)
),
Quantity = colDef(
name = "",
cell = function(value, index) {
sparkline(df1$Quantity[[index]])
})
), # close columns list
defaultColDef = colDef(footerStyle = list(fontWeight = "bold"))
) # close reactableWe here can see that somehow we have a large part of our Opening Inventories with Obsolescence Risks.
this analysis can be useful to point out some necessary actions to reduce those risks
it also gives a feeling of the importance to consider those SSL quantities when we calculate our Projected Inventories and DRP
Example for one selected SKU.
Let’s compare the 2 calculations :
the “original” data : where we don’t consider the details of the expiry dates of the Opening inventories.
with the SSL : where we consider the obsolescence risks.
# set a working df
df1 <- Calculated_PI_DB
#--------------
# Select Item
#--------------
# filter
df1 <- filter(df1, df1$DFU %in% c("Item 1"))
#--------------
# Transform
#--------------
# keep only the needed columns
df1 <- df1 %>% select(Period,
Demand,
SSL.Qty,
Coverage.wo.SSL,
PI.wo.SSL,
Coverage.with.SSL,
PI.with.SSL,
Supply)
#--------------
# create a f_colorpal field
#--------------
df1 <- df1 %>% mutate(f_colorpal_wo_SSL = case_when(
Coverage.wo.SSL > 8 ~ "#FFA500",
Coverage.wo.SSL > 2 ~ "#32CD32",
Coverage.wo.SSL > 0 ~ "#FFFF99",
TRUE ~ "#FF0000" ))
df1 <- df1 %>% mutate(f_colorpal_with_SSL = case_when(
Coverage.with.SSL > 8 ~ "#FFA500",
Coverage.with.SSL > 2 ~ "#32CD32",
Coverage.with.SSL > 0 ~ "#FFFF99",
TRUE ~ "#FF0000" ))
#--------------
# Create Table
#--------------
# create reactable
reactable(df1, resizable = TRUE, showPageSizeOptions = TRUE,
striped = TRUE, highlight = TRUE, compact = TRUE,
defaultPageSize = 20,
columns = list(
Demand = colDef(
name = "Demand (units)",
cell = data_bars(df1,
fill_color = "#3fc1c9",
text_position = "outside-end"
)
),
SSL.Qty = colDef(
name = "SSL (units)",
cell = data_bars(df1,
fill_color = "#e00000",
text_position = "outside-end"
)
),
#-----------------------
# without SSL (Original)
Coverage.wo.SSL = colDef(
name = "Original Coverage (Periods)",
maxWidth = 90,
cell= color_tiles(df1, color_ref = "f_colorpal_wo_SSL")
),
f_colorpal_wo_SSL = colDef(show = FALSE), # hidden, just used for the coverages
`PI.wo.SSL`= colDef(
name = "Original Projected Inventories (units)",
format = colFormat(separators = TRUE, digits=0),
style = function(value) {
if (value > 0) {
color <- "#008000"
} else if (value < 0) {
color <- "#e00000"
} else {
color <- "#777"
}
list(color = color
#fontWeight = "bold"
)
}
),
#-----------------------
# with SSL
Coverage.with.SSL = colDef(
name = "Coverage w/ SSL (Periods)",
maxWidth = 90,
cell= color_tiles(df1, color_ref = "f_colorpal_with_SSL")
),
f_colorpal_with_SSL = colDef(show = FALSE), # hidden, just used for the coverages
`PI.with.SSL`= colDef(
name = "Projected Inventories w/ SSL (units)",
format = colFormat(separators = TRUE, digits=0),
style = function(value) {
if (value > 0) {
color <- "#008000"
} else if (value < 0) {
color <- "#e00000"
} else {
color <- "#777"
}
list(color = color
#fontWeight = "bold"
)
}
),
Supply = colDef(
name = "Supply (units)",
cell = data_bars(df1,
fill_color = "#3CB371",
text_position = "outside-end"
)
)
), # close columns lits
columnGroups = list(
colGroup(name = "Original Projected Inventories",
columns = c("Coverage.wo.SSL",
"PI.wo.SSL")),
colGroup(name = "Projected Inventories w/ SSL",
columns = c("Coverage.with.SSL",
"PI.with.SSL"))
)
) # close reactableThis illustrates that :
in the original case : we have an Opening Coverage of 14 months
when we consider the SSL quantities : we have an Opening Coverage of 9 months
a more accurate (and important) calculation
which will lead to an earlier calculation of a Replenishment Plan (DRP), as we will see in the section 8.1
# set a working df
df1 <- Calculated_PI_DB
#--------------
# Select Item
#--------------
# filter
df1 <- filter(df1, df1$DFU %in% c("Item 1"))
# keep only the needed columns
df1 <- df1 |> select(Period, PI.wo.SSL)
# create a value.index
df1$Value.Index <- if_else(df1$PI.wo.SSL < 0, "Shortage", "Stock")
# spread
df1 <- df1 |> spread(Value.Index, PI.wo.SSL)
#----------------------------------------------------
# Chart
u <- highchart() |>
hc_title(text = "Projected Inventories w/o SSL") |>
hc_subtitle(text = "in units") |>
hc_add_theme(hc_theme_google()) |>
hc_xAxis(categories = df1$Period) |>
hc_add_series(name = "Stock",
color = "#32CD32",
#dataLabels = list(align = "center", enabled = TRUE),
data = df1$Stock) |>
hc_add_series(name = "Shortage",
color = "#dc3220",
#dataLabels = list(align = "center", enabled = TRUE),
data = df1$Shortage) |>
hc_chart(type = "column") |>
hc_plotOptions(series = list(stacking = "normal"))
u # set a working df
df1 <- Calculated_PI_DB
#--------------
# Select Item
#--------------
# filter
df1 <- filter(df1, df1$DFU %in% c("Item 1"))
# keep only the needed columns
df1 <- df1 |> select(Period, PI.with.SSL)
# create a value.index
df1$Value.Index <- if_else(df1$PI.with.SSL < 0, "Shortage", "Stock")
# spread
df1 <- df1 |> spread(Value.Index, PI.with.SSL)
#----------------------------------------------------
# Chart
u <- highchart() |>
hc_title(text = "Projected Inventories considering SSL") |>
hc_subtitle(text = "in units") |>
hc_add_theme(hc_theme_google()) |>
hc_xAxis(categories = df1$Period) |>
hc_add_series(name = "Stock",
color = "#32CD32",
#dataLabels = list(align = "center", enabled = TRUE),
data = df1$Stock) |>
hc_add_series(name = "Shortage",
color = "#dc3220",
#dataLabels = list(align = "center", enabled = TRUE),
data = df1$Shortage) |>
hc_chart(type = "column") |>
hc_plotOptions(series = list(stacking = "normal"))
u As we did in the Part 4, we are now going to calculate the DRP in 2 different situations :
without considering the SSL quantities
considering the SSL quantities
Usual calculation, considering the Demand as it is and w/o considering the inventories shelf lives.
# set a working df
df1 <- demo_dataset
#--------------
# Add DRP parameters
#--------------
df1$SSCov <- 2
df1$DRPCovDur <- 3
df1$MOQ <- 1
df1$FH <- if_else(df1$Period <= '2023-03-01', "Frozen", "Free")
#--------------
# Calculate DRP
#--------------
# calculate DRP
df1 <- planr::drp(dataset = df1,
DFU,
Period,
Demand,
Opening,
Supply,
SSCov,
DRPCovDur,
MOQ,
FH)
# rename
df1 <- df1 |> rename(Coverage.wo.SSL = DRP.Calculated.Coverage.in.Periods,
PI.wo.SSL = DRP.Projected.Inventories.Qty,
DRP.plan.wo.SSL = DRP.plan)
# keep only needed variables
df1 <- df1 |> select(DFU, Period,
Coverage.wo.SSL,
PI.wo.SSL,
DRP.plan.wo.SSL)
# keep results
DRP_wo_SSL_DB <- df1
glimpse(DRP_wo_SSL_DB)Rows: 44
Columns: 5
$ DFU <chr> "Item 1", "Item 1", "Item 1", "Item 1", "Item 1", "Ite…
$ Period <date> 2023-01-01, 2023-02-01, 2023-03-01, 2023-04-01, 2023-…
$ Coverage.wo.SSL <dbl> 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 5, 4, 3, 5, 4…
$ PI.wo.SSL <dbl> 2800, 2600, 2400, 2200, 2000, 1800, 1600, 1400, 1200, …
$ DRP.plan.wo.SSL <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 600, 0, 0, 600, 0,…Here we need to consider the SSL.Qty as an additional Demand
# set a working df
df1 <- calculated_ssl
#-------------------------------
# Calculate Adjusted Demand : initial + SSL.Qty
#-------------------------------
# add the 2 types of demand
df1$Demand <- df1$Demand + df1$SSL.Qty
# remove SSL.Qty
df1 <- df1 |> select(-SSL.Qty)
#--------------
# Add DRP parameters
#--------------
df1$SSCov <- 2
df1$DRPCovDur <- 3
df1$MOQ <- 1
df1$FH <- if_else(df1$Period <= '2023-03-01', "Frozen", "Free")
#--------------
# Calculate DRP
#--------------
# calculate DRP
df1 <- planr::drp(dataset = df1,
DFU,
Period,
Demand,
Opening,
Supply,
SSCov,
DRPCovDur,
MOQ,
FH)
# rename
df1 <- df1 |> rename(Coverage.with.SSL = DRP.Calculated.Coverage.in.Periods,
PI.with.SSL = DRP.Projected.Inventories.Qty,
DRP.plan.with.SSL = DRP.plan)
# keep only needed variables
df1 <- df1 |> select(DFU, Period,
Coverage.with.SSL,
PI.with.SSL,
DRP.plan.with.SSL)
# keep results
DRP_with_SSL_DB <- df1
glimpse(DRP_with_SSL_DB)Rows: 44
Columns: 5
$ DFU <chr> "Item 1", "Item 1", "Item 1", "Item 1", "Item 1", "I…
$ Period <date> 2023-01-01, 2023-02-01, 2023-03-01, 2023-04-01, 202…
$ Coverage.with.SSL <dbl> 9, 8, 7, 6, 5, 4, 3, 5, 4, 3, 5, 4, 3, 5, 4, 3, 0, 0…
$ PI.with.SSL <dbl> 2800, 2600, 2000, 1800, 1600, 1400, 1000, 1400, 1200…
$ DRP.plan.with.SSL <dbl> 0, 0, 0, 0, 0, 0, 0, 600, 0, 0, 600, 0, 0, 600, 0, 0…Now we add both calculated Projected Inventories & Replenishment Plan to the initial dataset.
Doing so we will have a compact dataframe with all the variables we need to compare.
# merge
df1 <- left_join(initial_dataset, calculated_ssl)
df1 <- left_join(df1, DRP_wo_SSL_DB)
df1 <- left_join(df1, DRP_with_SSL_DB)
# keep only needed variables
df1 <- df1 |> select(DFU,
Period,
Demand,
Opening.Expiry,
SSL.Qty,
Opening,
Coverage.wo.SSL,
PI.wo.SSL,
DRP.plan.wo.SSL,
Coverage.with.SSL,
PI.with.SSL,
DRP.plan.with.SSL
)
# replace missing values by zero
df1$PI.wo.SSL <- df1$PI.wo.SSL |> replace_na(0)
df1$PI.with.SSL <- df1$PI.with.SSL |> replace_na(0)
df1$DRP.plan.wo.SSL <- df1$DRP.plan.wo.SSL |> replace_na(0)
df1$DRP.plan.with.SSL <- df1$DRP.plan.with.SSL |> replace_na(0)
# keep results
Calculated_DRP_DB <- df1
glimpse(Calculated_DRP_DB)Rows: 44
Columns: 12
$ DFU <chr> "Item 1", "Item 1", "Item 1", "Item 1", "Item 1", "I…
$ Period <date> 2023-01-01, 2023-02-01, 2023-03-01, 2023-04-01, 202…
$ Demand <dbl> 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 20…
$ Opening.Expiry <dbl> 0, 0, 1000, 0, 0, 0, 1000, 0, 0, 1000, 0, 0, 0, 0, 0…
$ SSL.Qty <dbl> 0, 0, 400, 0, 0, 0, 200, 0, 0, 400, 0, 0, 0, 0, 0, 0…
$ Opening <dbl> 3000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
$ Coverage.wo.SSL <dbl> 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 5, 4, 3, 5,…
$ PI.wo.SSL <dbl> 2800, 2600, 2400, 2200, 2000, 1800, 1600, 1400, 1200…
$ DRP.plan.wo.SSL <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 600, 0, 0, 600, …
$ Coverage.with.SSL <dbl> 9, 8, 7, 6, 5, 4, 3, 5, 4, 3, 5, 4, 3, 5, 4, 3, 0, 0…
$ PI.with.SSL <dbl> 2800, 2600, 2000, 1800, 1600, 1400, 1000, 1400, 1200…
$ DRP.plan.with.SSL <dbl> 0, 0, 0, 0, 0, 0, 0, 600, 0, 0, 600, 0, 0, 600, 0, 0…Example for one selected SKU.
We notice that the Replenishment Plan starts (obviously!) earlier in the case when we consider the SSL.
It leads to a more accurate Replenishment Plan, and avoid to be in shortage.
# set a working df
df1 <- Calculated_DRP_DB
#--------------
# Select Item
#--------------
# filter
df1 <- filter(df1, df1$DFU %in% c("Item 1"))
# remove the end horizon (where the calculation is not effective)
df1 <- filter(df1, df1$Period <= '2024-04-01')
#--------------
# Transform
#--------------
# keep only the needed columns
df1 <- df1 %>% select(Period,
Demand,
SSL.Qty,
Coverage.wo.SSL,
PI.wo.SSL,
DRP.plan.wo.SSL,
Coverage.with.SSL,
PI.with.SSL,
DRP.plan.with.SSL
)
#--------------
# create a f_colorpal field
#--------------
df1 <- df1 %>% mutate(f_colorpal_wo_SSL = case_when(
Coverage.wo.SSL > 8 ~ "#FFA500",
Coverage.wo.SSL > 2 ~ "#32CD32",
Coverage.wo.SSL > 0 ~ "#FFFF99",
TRUE ~ "#FF0000" ))
df1 <- df1 %>% mutate(f_colorpal_with_SSL = case_when(
Coverage.with.SSL > 8 ~ "#FFA500",
Coverage.with.SSL > 2 ~ "#32CD32",
Coverage.with.SSL > 0 ~ "#FFFF99",
TRUE ~ "#FF0000" ))
#--------------
# Create Table
#--------------
# create reactable
reactable(df1, resizable = TRUE, showPageSizeOptions = TRUE,
striped = TRUE, highlight = TRUE, compact = TRUE,
defaultPageSize = 20,
columns = list(
Demand = colDef(
name = "Demand (units)",
cell = data_bars(df1,
fill_color = "#3fc1c9",
text_position = "outside-end"
)
),
SSL.Qty = colDef(
name = "SSL (units)",
cell = data_bars(df1,
fill_color = "#e00000",
text_position = "outside-end"
)
),
#-----------------------
# without SSL (Original)
Coverage.wo.SSL = colDef(
name = "Original Coverage (Periods)",
maxWidth = 90,
cell= color_tiles(df1, color_ref = "f_colorpal_wo_SSL")
),
f_colorpal_wo_SSL = colDef(show = FALSE), # hidden, just used for the coverages
`PI.wo.SSL`= colDef(
name = "Original Projected Inventories (units)",
format = colFormat(separators = TRUE, digits=0),
style = function(value) {
if (value > 0) {
color <- "#008000"
} else if (value < 0) {
color <- "#e00000"
} else {
color <- "#777"
}
list(color = color
#fontWeight = "bold"
)
}
),
#-----------------------
# with SSL
Coverage.with.SSL = colDef(
name = "Coverage w/ SSL (Periods)",
maxWidth = 90,
cell= color_tiles(df1, color_ref = "f_colorpal_with_SSL")
),
f_colorpal_with_SSL = colDef(show = FALSE), # hidden, just used for the coverages
`PI.with.SSL`= colDef(
name = "Projected Inventories w/ SSL (units)",
format = colFormat(separators = TRUE, digits=0),
style = function(value) {
if (value > 0) {
color <- "#008000"
} else if (value < 0) {
color <- "#e00000"
} else {
color <- "#777"
}
list(color = color
#fontWeight = "bold"
)
}
),
DRP.plan.wo.SSL = colDef(
name = "Original Replenishment Plan (units)",
cell = data_bars(df1,
fill_color = "#3CB371",
text_position = "outside-end"
)
),
DRP.plan.with.SSL = colDef(
name = "Replenishment Plan w/ SSL (units)",
cell = data_bars(df1,
fill_color = "#3CB371",
text_position = "outside-end"
)
)
), # close columns lits
columnGroups = list(
colGroup(name = "Original Projected Inventories",
columns = c("Coverage.wo.SSL",
"PI.wo.SSL",
"DRP.plan.wo.SSL")),
colGroup(name = "Projected Inventories w/ SSL",
columns = c("Coverage.with.SSL",
"PI.with.SSL",
"DRP.plan.with.SSL"))
)
) # close reactable