library(tidyverse)
library(dplyr)
library(lubridate)
library(tidyverse)
library(shiny)
# for the tables
library(reactable)
library(reactablefmtr)
# for the charts
library(highcharter)
# the library planr
library(planr)Let’s present the 3 functions:
light_proj_inv() : to calculate projected inventories & coverages
proj_inv() : to calculate & analyze projected inventories vs min & max targets
drp() : to calculate a replenishment plan
Period <- c(
"1/1/2020", "2/1/2020", "3/1/2020", "4/1/2020", "5/1/2020", "6/1/2020", "7/1/2020", "8/1/2020", "9/1/2020", "10/1/2020", "11/1/2020", "12/1/2020","1/1/2021", "2/1/2021", "3/1/2021", "4/1/2021", "5/1/2021", "6/1/2021", "7/1/2021", "8/1/2021", "9/1/2021", "10/1/2021", "11/1/2021", "12/1/2021")
Demand <- c(360, 458,300,264,140,233,229,208,260,336,295,226,336,434,276,240,116,209,205,183,235,312,270,201)
Opening <- c(1310,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
Supply <- c(0,0,0,0,0,2500,0,0,0,0,0,0,0,0,0,2000,0,0,0,0,0,0,0,0)
# assemble
my_demand_and_suppply <- data.frame(Period,
Demand,
Opening,
Supply)
# let's add a Product
my_demand_and_suppply$DFU <- "Product A"
# format the Period as a date
my_demand_and_suppply$Period <- as.Date(as.character(my_demand_and_suppply$Period), format = '%m/%d/%Y')
# let's have a look at it
head(my_demand_and_suppply)
#> Period Demand Opening Supply DFU
#> 1 2020-01-01 360 1310 0 Product A
#> 2 2020-02-01 458 0 0 Product A
#> 3 2020-03-01 300 0 0 Product A
#> 4 2020-04-01 264 0 0 Product A
#> 5 2020-05-01 140 0 0 Product A
#> 6 2020-06-01 233 0 2500 Product AIt contains some basic features:
a Product: it’s an item, a SKU (Storage Keeping Unit), or a SKU at a location, also called a DFU (Demand Forecast Unit)
a Period of time : for example monthly or weekly buckets
a Demand : could be some sales forecasts, expressed in units
an Opening Inventory : what we hold as available inventories at the beginning of the horizon, expressed in units
a Supply Plan : the supplies that we plan to receive, expressed in units
Let’s apply the light_proj_inv().
We are going to calculate 2 new features for each DFU:
projected inventories
projected coverages, based on the Demand Forecasts
# calculate
calculated_projection <- light_proj_inv(dataset = my_demand_and_suppply,
DFU = DFU,
Period = Period,
Demand = Demand,
Opening = Opening,
Supply = Supply)
#> Joining with `by = join_by(DFU, Period)`
# see results
head(calculated_projection)
#> DFU Period Demand Opening Calculated.Coverage.in.Periods
#> 1 Product A 2020-01-01 360 1310 2.7
#> 2 Product A 2020-02-01 458 0 1.7
#> 3 Product A 2020-03-01 300 0 0.7
#> 4 Product A 2020-04-01 264 0 0.0
#> 5 Product A 2020-05-01 140 0 0.0
#> 6 Product A 2020-06-01 233 0 7.4
#> Projected.Inventories.Qty Supply
#> 1 950 0
#> 2 492 0
#> 3 192 0
#> 4 -72 0
#> 5 -212 0
#> 6 2055 2500We will use the libraries reactable and reactablefmtr to create a nice table.
# set a working df
df1 <- calculated_projection
# keep only the needed columns
df1 <- df1 %>% select(Period,
Demand,
Calculated.Coverage.in.Periods,
Projected.Inventories.Qty,
Supply)
# create a f_colorpal field
df1 <- df1 %>% mutate(f_colorpal = case_when( Calculated.Coverage.in.Periods > 6 ~ "#FFA500",
Calculated.Coverage.in.Periods > 2 ~ "#32CD32",
Calculated.Coverage.in.Periods > 0 ~ "#FFFF99",
TRUE ~ "#FF0000" ))
# 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"
)
),
Calculated.Coverage.in.Periods = colDef(
name = "Coverage (Periods)",
maxWidth = 90,
cell= color_tiles(df1, color_ref = "f_colorpal")
),
f_colorpal = colDef(show = FALSE), # hidden, just used for the coverages
`Projected.Inventories.Qty`= colDef(
name = "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"
)
}
),
Supply = colDef(
name = "Supply (units)",
cell = data_bars(df1,
fill_color = "#3CB371",
text_position = "outside-end"
)
)
), # close columns lits
columnGroups = list(
colGroup(name = "Projected Inventories", columns = c("Calculated.Coverage.in.Periods",
"Projected.Inventories.Qty"))
)
) # close reactable
# set a working df
df1 <- calculated_projection
# keep only the needed columns
df1 <- df1 %>% select(Period,
Projected.Inventories.Qty)
# create a value.index
df1$Value.Index <- if_else(df1$Projected.Inventories.Qty < 0, "Shortage", "Stock")
# spread
df1 <- df1 %>% spread(Value.Index, Projected.Inventories.Qty)
#----------------------------------------------------
# Chart
u <- highchart() %>%
hc_title(text = "Projected Inventories") %>%
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 Now, let’s consider some parameters such as : - a target of minimum stock level - a target of maximum stock level
And then: - calculate the projected inventories and coverages - analyze those values vs those defined targets
First, let’s add some parameters to our initial database.
Define min & max coverages, through 2 parameters: - Min.Cov - Max.Cov
Expressed in number of periods of coverages. The periods can be in monthly buckets, weekly buckets, etc…
my_data_with_parameters <- my_demand_and_suppply
my_data_with_parameters$Min.Cov <- 2
my_data_with_parameters$Max.Cov <- 4
head(my_data_with_parameters)
#> Period Demand Opening Supply DFU Min.Cov Max.Cov
#> 1 2020-01-01 360 1310 0 Product A 2 4
#> 2 2020-02-01 458 0 0 Product A 2 4
#> 3 2020-03-01 300 0 0 Product A 2 4
#> 4 2020-04-01 264 0 0 Product A 2 4
#> 5 2020-05-01 140 0 0 Product A 2 4
#> 6 2020-06-01 233 0 2500 Product A 2 4Let’s apply the proj_inv() function
df1 <- proj_inv(data = my_data_with_parameters,
DFU = DFU,
Period = Period,
Demand = Demand,
Opening = Opening,
Supply = Supply,
Min.Cov = Min.Cov,
Max.Cov = Max.Cov)
#> Joining with `by = join_by(DFU, Period)`
#> Joining with `by = join_by(DFU, Period)`
# see results
calculated_projection_and_analysis <- df1
head(calculated_projection_and_analysis)
#> DFU Period Demand Opening Calculated.Coverage.in.Periods
#> 1 Product A 2020-01-01 360 1310 2.7
#> 2 Product A 2020-02-01 458 0 1.7
#> 3 Product A 2020-03-01 300 0 0.7
#> 4 Product A 2020-04-01 264 0 0.0
#> 5 Product A 2020-05-01 140 0 0.0
#> 6 Product A 2020-06-01 233 0 7.4
#> Projected.Inventories.Qty Supply Min.Cov Max.Cov Safety.Stocks Maximum.Stocks
#> 1 950 0 2 4 758 1162
#> 2 492 0 2 4 564 937
#> 3 192 0 2 4 404 866
#> 4 -72 0 2 4 373 810
#> 5 -212 0 2 4 462 930
#> 6 2055 2500 2 4 437 1033
#> PI.Index Ratio.PI.vs.min Ratio.PI.vs.Max
#> 1 OK 1.25 0.82
#> 2 Alert 0.87 0.53
#> 3 Alert 0.48 0.22
#> 4 Shortage -0.19 -0.09
#> 5 Shortage -0.46 -0.23
#> 6 OverStock 4.70 1.99First, let’s create a function status_PI.Index()
# create a function status.PI.Index
status_PI.Index <- function(color = "#aaa", width = "0.55rem", height = width) {
span(style = list(
display = "inline-block",
marginRight = "0.5rem",
width = width,
height = height,
backgroundColor = color,
borderRadius = "50%"
))
}And now let’s create a reactable:
# set a working df
df1 <- calculated_projection_and_analysis
# remove not needed column
df1 <- df1[ , -which(names(df1) %in% c("DFU"))]
# create a f_colorpal field
df1 <- df1 %>% mutate(f_colorpal = case_when( Calculated.Coverage.in.Periods > 6 ~ "#FFA500",
Calculated.Coverage.in.Periods > 2 ~ "#32CD32",
Calculated.Coverage.in.Periods > 0 ~ "#FFFF99",
TRUE ~ "#FF0000" ))
#-------------------------
# Create Table
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,
#round_edges = TRUE
#value <- format(value, big.mark = ","),
#number_fmt = big.mark = ",",
fill_color = "#3fc1c9",
#fill_opacity = 0.8,
text_position = "outside-end"
)
),
Calculated.Coverage.in.Periods = colDef(
name = "Coverage (Periods)",
maxWidth = 90,
cell= color_tiles(df1, color_ref = "f_colorpal")
),
f_colorpal = colDef(show = FALSE), # hidden, just used for the coverages
`Projected.Inventories.Qty`= colDef(
name = "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"
)
}
),
Supply = colDef(
name = "Supply (units)",
cell = data_bars(df1,
#round_edges = TRUE
#value <- format(value, big.mark = ","),
#number_fmt = big.mark = ",",
fill_color = "#3CB371",
#fill_opacity = 0.8,
text_position = "outside-end"
)
#format = colFormat(separators = TRUE, digits=0)
#number_fmt = big.mark = ","
),
PI.Index = colDef(
name = "Analysis",
cell = function(value) {
color <- switch(
value,
TBC = "hsl(154, 3%, 50%)",
OverStock = "hsl(214, 45%, 50%)",
OK = "hsl(154, 64%, 50%)",
Alert = "hsl(30, 97%, 70%)",
Shortage = "hsl(3, 69%, 50%)"
)
PI.Index <- status_PI.Index(color = color)
tagList(PI.Index, value)
}),
`Safety.Stocks`= colDef(
name = "Safety Stocks (units)",
format = colFormat(separators = TRUE, digits=0)
),
`Maximum.Stocks`= colDef(
name = "Maximum Stocks (units)",
format = colFormat(separators = TRUE, digits=0)
),
`Opening`= colDef(
name = "Opening Inventories (units)",
format = colFormat(separators = TRUE, digits=0)
),
`Min.Cov`= colDef(name = "Min Stocks Coverage (Periods)"),
`Max.Cov`= colDef(name = "Maximum Stocks Coverage (Periods)"),
# ratios
`Ratio.PI.vs.min`= colDef(name = "Ratio PI vs min"),
`Ratio.PI.vs.Max`= colDef(name = "Ratio PI vs Max")
), # close columns lits
columnGroups = list(
colGroup(name = "Projected Inventories", columns = c("Calculated.Coverage.in.Periods",
"Projected.Inventories.Qty")),
colGroup(name = "Stocks Levels Parameters", columns = c("Min.Cov",
"Max.Cov",
"Safety.Stocks",
"Maximum.Stocks")),
colGroup(name = "Analysis Features", columns = c("PI.Index",
"Ratio.PI.vs.min",
"Ratio.PI.vs.Max"))
)
) # close reactableCompared to the previous table, we have here some additional information available: the calculated fields [Analysis Features] - based on safety & maximum stocks targets - useful for a mass analysis (Cockpit / Supply Risks Alarm), but perhaps too detailed for a focus on a SKU
We also can notice that the minimum and maximum stocks coverages, initially expressed in Periods (of coverage) are converted in units. It’s quite useful to chart the projected inventories vs those 2 thresholds for example.
# set a working df
df1 <- calculated_projection_and_analysis
# Chart
p <- highchart() %>%
hc_add_series(name = "Max", color = "crimson", data = df1$Maximum.Stocks) %>%
hc_add_series(name = "min", color = "lightblue", data = df1$Safety.Stocks) %>%
hc_add_series(name = "Projected Inventories", color = "gold", data = df1$Projected.Inventories.Qty) %>%
hc_title(text = "Projected Inventories") %>%
hc_subtitle(text = "in units") %>%
hc_xAxis(categories = df1$Period) %>%
#hc_yAxis(title = list(text = "Sales (units)")) %>%
hc_add_theme(hc_theme_google())
pWe can visualize the periods when we are in Alert & OverStock, comparing to the minimum and Maximum stocks levels.
Let’s now add a few parameters to the initial database “my_demand_and_suppply”
df1 <- my_demand_and_suppply
df1$SSCov <- 2
df1$DRPCovDur <- 3
df1$MOQ <- 1
df1$FH <- c("Frozen", "Frozen", "Frozen", "Frozen","Frozen","Frozen","Free","Free","Free","Free","Free","Free","Free","Free","Free","Free","Free","Free","Free","Free","Free","Free","Free","Free")
# get Results
my_drp_template <- df1
head(my_drp_template)
#> Period Demand Opening Supply DFU SSCov DRPCovDur MOQ FH
#> 1 2020-01-01 360 1310 0 Product A 2 3 1 Frozen
#> 2 2020-02-01 458 0 0 Product A 2 3 1 Frozen
#> 3 2020-03-01 300 0 0 Product A 2 3 1 Frozen
#> 4 2020-04-01 264 0 0 Product A 2 3 1 Frozen
#> 5 2020-05-01 140 0 0 Product A 2 3 1 Frozen
#> 6 2020-06-01 233 0 2500 Product A 2 3 1 FrozenApply drp()
# set a working df
df1 <- my_drp_template
# calculate drp
demo_drp <- drp(data = df1,
DFU = DFU,
Period = Period,
Demand = Demand,
Opening = Opening,
Supply = Supply,
SSCov = SSCov,
DRPCovDur = DRPCovDur,
MOQ = MOQ,
FH = FH
)
#> Joining with `by = join_by(DFU, Period)`
#> Joining with `by = join_by(DFU, Period)`
#> Joining with `by = join_by(DFU, Period)`
glimpse(demo_drp)
#> Rows: 24
#> Columns: 15
#> $ DFU <chr> "Product A", "Product A", "Product …
#> $ Period <date> 2020-01-01, 2020-02-01, 2020-03-01…
#> $ Demand <dbl> 360, 458, 300, 264, 140, 233, 229, …
#> $ Opening <dbl> 1310, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
#> $ Supply <dbl> 0, 0, 0, 0, 0, 2500, 0, 0, 0, 0, 0,…
#> $ SSCov <dbl> 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,…
#> $ DRPCovDur <dbl> 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3,…
#> $ Stock.Max <dbl> 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5,…
#> $ MOQ <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…
#> $ FH <chr> "Frozen", "Frozen", "Frozen", "Froz…
#> $ Safety.Stocks <dbl> 758, 564, 404, 373, 462, 437, 468, …
#> $ Maximum.Stocks <dbl> 1395, 1166, 1074, 1070, 1266, 1328,…
#> $ DRP.Calculated.Coverage.in.Periods <dbl> 2.7, 1.7, 0.7, -0.5, -0.9, 7.4, 6.4…
#> $ DRP.Projected.Inventories.Qty <dbl> 950, 492, 192, -72, -212, 2055, 182…
#> $ DRP.plan <dbl> 0, 0, 0, 0, 0, 2500, 0, 0, 0, 0, 0,…
# set a working df
df1 <- demo_drp
# keep only the needed columns
df1 <- df1 %>% select(Period,
Demand,
DRP.Calculated.Coverage.in.Periods,
DRP.Projected.Inventories.Qty,
DRP.plan)
# replace missing values by zero
df1$DRP.plan[is.na(df1$DRP.plan)] <- 0
df1$DRP.Projected.Inventories.Qty[is.na(df1$DRP.Projected.Inventories.Qty)] <- 0
# create a f_colorpal field
df1 <- df1 %>% mutate(f_colorpal = case_when( DRP.Calculated.Coverage.in.Periods > 8 ~ "#FFA500",
DRP.Calculated.Coverage.in.Periods > 2 ~ "#32CD32",
DRP.Calculated.Coverage.in.Periods > 0 ~ "#FFFF99",
TRUE ~ "#FF0000" ))
# 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"
)
),
DRP.Calculated.Coverage.in.Periods = colDef(
name = "Coverage (Periods)",
maxWidth = 90,
cell= color_tiles(df1, color_ref = "f_colorpal")
),
f_colorpal = colDef(show = FALSE), # hidden, just used for the coverages
`DRP.Projected.Inventories.Qty`= colDef(
name = "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"
)
}
),
DRP.plan = colDef(
name = "Replenishment (units)",
cell = data_bars(df1,
fill_color = "#3CB371",
text_position = "outside-end"
)
)
), # close columns lits
columnGroups = list(
colGroup(name = "Projected Inventories", columns = c("DRP.Calculated.Coverage.in.Periods",
"DRP.Projected.Inventories.Qty"))
)
) # close reactable
# set a working df
df1 <- demo_drp
# Chart
p <- highchart() %>%
hc_add_series(name = "Max", color = "crimson", data = df1$Maximum.Stocks) %>%
hc_add_series(name = "min", color = "lightblue", data = df1$Safety.Stocks) %>%
hc_add_series(name = "Projected Inventories", color = "gold", data = df1$DRP.Projected.Inventories.Qty) %>%
hc_title(text = "(DRP) Projected Inventories") %>%
hc_subtitle(text = "in units") %>%
hc_xAxis(categories = df1$Period) %>%
#hc_yAxis(title = list(text = "Sales (units)")) %>%
hc_add_theme(hc_theme_google())
p