# 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)one item
We’re going to present here how to use, on one product, the light_proj_inv() from the R package planr.
More info on : https://github.com/nguyennico/planr
This function allows to calculate projected inventories & coverages.
We just need 5 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
As a result, we will get 2 additional variables, showing the calculated projected inventories and coverages.
We are going to :
apply the light_proj_inv() on a simple template
create 2 nice visuals of tables and charts, using the R packages reactable, reactablefmtr and highcharter.
First, let’s load a few libraries :
1) Create Data Template
Let’s create a dataset which contains those 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.
# let's create 4 variables
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 those variables in a dataframe
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 A2) Calculate Projected Inventories & Coverages
Let’s apply the light_proj_inv().
We are going to calculate 2 new features for the DFU :
projected inventories
projected coverages, based on the Demand Forecasts
# calculate
calculated_projection <- planr::light_proj_inv(dataset = my_demand_and_suppply,
DFU = DFU,
Period = Period,
Demand = Demand,
Opening = Opening,
Supply = Supply)
# 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 25003) A nicer display of table
We will use the libraries reactable and reactablefmtr to create a nice table.
#-------------------
# Get data
#-------------------
# set a working df
df1 <- calculated_projection
#-------------------
# Transform
#-------------------
# 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" ))
#-------------------
# 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"
)
),
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 reactable4) A little chart
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.
#-------------------
# Get data
#-------------------
# set a working df
df1 <- calculated_projection
#-------------------
# Transform
#-------------------
# 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"))
# display chart
u