# ETL
library(tidyverse)
library(htmltools)
# for the tables
library(reactable)
library(reactablefmtr)
# for the charts
library(highcharter)
# for the Demand & Supply Planning calculations : the library planr
library(planr)one item
First, let’s load a few libraries :
We’re going to present here how to use the proj_inv() function from the R package planr.
More info on : https://github.com/nguyennico/planr
On top of the classic variables (Demand, Opening, Supply), we will consider 2 new parameters :
- a target of minimum stock level
- a target of maximum stock level
We will calculate the projected inventories and coverages and compare those values vs those defined targets.
First, let’s add 2 parameters, which define the min & max coverages, to our initial database, used as a demo for the light_proj_inv() function :
- Min.Cov
- Max.Cov
We need **7 *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
a Min.Cov : the minimum targeted coverage, expressed in number of periods of coverage
a Max.Cov : the maximum targeted coverage, expressed in number of periods of coverage
The periods can be in monthly buckets, weekly buckets, etc…
1) Create Data Template
1.1) Basic Template
Let’s create a simple dataset which contains the basic features.
This dataset will contain the 5 variables required : Period / Demand / Opening / Suply and a 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 A1.2) Add Stocks Parameters
Now let’s add the 2 new parameters Min.Cov and Max.Cov :
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 42) Calculate Projected Inventories & Coverages
Now we’re going to apply the proj_inv() function.
Same with the function light_proj_inv(), it will allows to calculate 2 features for the DFU :
projected inventories
projected coverages, based on the Demand Forecasts
And also 5 new features :
Safety.Stocks : the projected Safety Stocks quantity, in units
Maximum.Stocks :the projected Maximum Stocks quantity, in units
PI.Index : a indicator, to inform whether the Projected Inventories are in OverStock (above the Maximum Stock target), in Alert (below the Safety Stock target), in Shortage (no stocks), or OK (between the 2 targets)
Ratio.PI.vs.min : a comparison of the Projected Inventories vs Safety Stocks target
Ratio.PI.vs.Max : a comparison of the Projected Inventories vs Safety Stocks target
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 (see part 4) to display where our [Projected.Inventories] stand vs those targets.
The [PI.Index] and the 2 ratios [Ratio.PI.vs.min] & [Ratio.PI.vs.Max] are useful to filter the data on the relevant products with issues, displaying quickly which products have issues, when and how much (vs targets).
# calculate
df1 <- planr::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)`# little formatting
df1 <- as.data.frame(df1)
# see results
calculated_projection_and_analysis <- df1
glimpse(calculated_projection_and_analysis)Rows: 24
Columns: 14
$ DFU <chr> "Product A", "Product A", "Product A", …
$ Period <date> 2020-01-01, 2020-02-01, 2020-03-01, 20…
$ Demand <dbl> 360, 458, 300, 264, 140, 233, 229, 208,…
$ Opening <dbl> 1310, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
$ Calculated.Coverage.in.Periods <dbl> 2.7, 1.7, 0.7, 0.0, 0.0, 7.4, 6.4, 5.4,…
$ Projected.Inventories.Qty <dbl> 950, 492, 192, -72, -212, 2055, 1826, 1…
$ Supply <dbl> 0, 0, 0, 0, 0, 2500, 0, 0, 0, 0, 0, 0, …
$ Min.Cov <dbl> 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, …
$ Max.Cov <dbl> 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, …
$ Safety.Stocks <dbl> 758, 564, 404, 373, 462, 437, 468, 596,…
$ Maximum.Stocks <dbl> 1162, 937, 866, 810, 930, 1033, 1099, 1…
$ PI.Index <chr> "OK", "Alert", "Alert", "Shortage", "Sh…
$ Ratio.PI.vs.min <dbl> 1.25, 0.87, 0.48, -0.19, -0.46, 4.70, 3…
$ Ratio.PI.vs.Max <dbl> 0.82, 0.53, 0.22, -0.09, -0.23, 1.99, 1…3) A nicer display of 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%"
))
}And now let’s create a reactable, jusing the libraries reactable and reactablefmtr.
# set a working df
df1 <- calculated_projection_and_analysis
# remove not needed column
df1 <- df1 |> select(-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 (mentioned in the table as 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 in this type of table
4) Charts
4.1) Projected Inventories vs Min & Max
Now let’s create a chart to look at the Projected Inventories.
The idea is to quickly visualize when we have stocks, and when we project to be in shortage.
# 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_add_theme(hc_theme_google())
p4.2) Positive & Negative Projected Inventories
# set a working df
df1 <- calculated_projection_and_analysis
# 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