# ETL
library(tidyverse)
# for the tables
library(reactable)
library(reactablefmtr)
# for the charts
library(highcharter)
# for the Demand & Supply Planning calculations : the library planr
library(planr)
# Others
library(htmltools)one item
First, let’s load a few libraries :
We’re going to present here how to use the drp() function from the R package planr.
More info on : https://github.com/nguyennico/planr
This function allows to calculate a Replenishment Plan, also called DRP (Distribution Requirement Planning).
We need a dataset with the 5 classic variables to use this function :
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
And a few new parameters added to this initial dataset :
SSCov : the Safety Stock Coverage, expressed in number of periods
DRPCovDur : the Frequency of Supply, expressed in number of periods
MOQ : the Multiple Order Quantity, expressed in units, 1 by default or a multiple of a Minimum Order Quantity
FH : defines the Frozen and Free Horizon. It has 2 values: Frozen or Free. If Frozen : no calculation of Replenishment Plan yet, the calculation starts when the period is defined as Free. We can use this parameter to consider some defined productions plans or supplies (allocations, workorders,…) in the short-term for example.
We are going to :
apply the drp() on this simple template
create 2 nice visuals of tables and charts, using the R packages reactable, reactablefmtr and highcharter.
1) Create Data Template
First, let’s create a dataset which contains the basic features, i.e. the 5 variables :
Period
Demand
Opening
Suply
DFU
We’ll call it my_demand_and_suppply.
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 ANow, let’s add the 4 new parameters, that we will use to calculate the DRP :
SSCov : here 2 periods, means that we want to keep always, as a safety stocks, 2 months of coverage
DRPCovDur : here 3 periods, means that we want to replenish every 3 periods (it’s the frequency of replenishment)
MOQ : here 1 unit, by default, so we keep a flexible Minimum Order Quantity
FH : here 6 first periods defined as Frozen, which means that the DRP calculation will start from the Period n°7, mentioned as Free
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 Frozen2) DRP Calculation
Now let’s apply the drp() function to this dataset :
the initial dataset, my_drp_template, has 9 variables
the new one, called below demo_drp, will have 15 variables
The 5 new variables are :
Safety.Stocks : the projected safety stocks, in units
Maximum.Stocks : the projected maximum stocks, in units.
- Maximum Stock = Safety Stocks (SSCov) + Frequency of Supply (DRPCovDur)
DRP.Calculated.Coverage.in.Periods : the calculated projected inventories, expressed in periods of coverage
DRP.Projected.Inventories.Qty : the calculated projected inventories, based on the [DRP.plan]
DRP.plan : the calculated Replenishment Plan
- in the Frozen Horizon : the existing plan, which is “frozen”
- in the Free Horizon : the calculated plan, based on the parameters (SSCov, DRPCovDur, MOQ)
- in the Frozen Horizon : the existing plan, which is “frozen”
# set a working df
df1 <- my_drp_template
# calculate drp
demo_drp <- planr::drp(data = df1,
DFU = DFU,
Period = Period,
Demand = Demand,
Opening = Opening,
Supply = Supply,
SSCov = SSCov,
DRPCovDur = DRPCovDur,
MOQ = MOQ,
FH = FH)
# formatting
demo_drp <- as.data.frame(demo_drp)
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,…3) Table
First, 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%"
))
}Now let’s create a table, using the packages reactable and reactablefmtr :
# set a working df
df1 <- demo_drp
# keep only the needed columns
df1 <- df1 |> select(Period, FH, Demand, DRP.Calculated.Coverage.in.Periods, DRP.Projected.Inventories.Qty, DRP.plan)
# replace missing values by zero
df1$DRP.plan <- df1$DRP.plan |> replace_na(0)
df1$DRP.Projected.Inventories.Qty <- df1$DRP.Projected.Inventories.Qty |> replace_na(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(
FH = colDef(
name = "Frozen Horizon",
cell = function(value) {
color <- switch(
value,
Free = "hsl(154, 64%, 50%)",
Frozen = "hsl(3, 69%, 50%)"
)
PI.Index <- status_PI.Index(color = color)
tagList(PI.Index, value)
}),
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 reactable4) A little chart
We can also visualize the result through a chart, using the library highcharter.
a) Line Chart
# 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())
pb) Bar Chart
With Positive & Negative Projected Inventories.
# set a working df
df1 <- demo_drp
# keep only the needed columns
df1 <- df1 |> select(Period, DRP.Projected.Inventories.Qty)
# create a value.index
df1$Value.Index <- if_else(df1$DRP.Projected.Inventories.Qty < 0, "Shortage", "Stock")
# spread
df1 <- df1 |> spread(Value.Index, DRP.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