# ETL
library(tidyverse)
library(sparkline)
library(htmltools)
# Charts
library(highcharter)
# Tables
library(reactable)
library(reactablefmtr)
# special Supply Chain calculations
library(planr)Slow Moving Obsolescence risks analysis
Keywords
R programming, supply chain, demand supply planning, S&OP, planr
Context
We analyze our projected inventories for our products (SKUs) portfolio and aim to identify :
the SKUs which are in an overstock situation, slow moving items with obsolescence risks
the amount of overstock for each SKU
For this, we define for each SKU a maximum stock target, defined as coverage (a number of periods of time), and the overstocks quantity is simply the difference between the projected inventories and this (maximum inventories) value.
Then we will use the R package planr to calculate and analyze the projected inventories.
And finally, we will be able to :
sum all those overstocks quantities and display them as a chart : to get a time projection for the overall portfolio
create a summary table (cockpit) for a detailed analysis at SKU level
#————————————————————————
Upload libraries
#————————————————————————
Part 1 : Get demo dataset
Let’s use a demo dataset from the package planr, to calculate some weekly projected inventories.
It contains 10 SKUs and some useful Demand and Supply variables :
[Demand] | [Opening] | [Supply plan]
stocks targets : [Min.Cov] and [Max.Cov], expressed in weeks
The [Max.Cov] will be our threshold to estimate the part of projected inventories which are in an overstock situation.
# get demo data, from the package planr
df1 <- blueprint
glimpse(df1)Rows: 520
Columns: 7
$ DFU <chr> "Item 000001", "Item 000001", "Item 000001", "Item 000001", "I…
$ Period <date> 2022-07-03, 2022-07-10, 2022-07-17, 2022-07-24, 2022-07-31, 2…
$ Demand <dbl> 364, 364, 364, 260, 736, 859, 859, 859, 273, 349, 349, 349, 20…
$ Opening <dbl> 6570, 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, 5000, 0, 0, 0, 0, 0,…
$ Min.Cov <dbl> 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4,…
$ Max.Cov <dbl> 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12…#————————————————————————
Part 2 : Calculate Projected Inventories
Let’s apply the proj_inv() function.
This function calculates the projected inventories of each SKU, and also performs an analysis :
projected shortages, alerts, overstocks
projected overstock quantity of each SKU
# set a working df
df1 <- blueprint
df1 <- as.data.frame(df1)
# calculate
df1 <- planr::proj_inv(data = df1,
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)`# let's shorter the horizon of analysis
df1 <- df1 |> filter(Period <= "2022-10-01")
# keep results
calculated_projection_and_analysis <- df1
glimpse(calculated_projection_and_analysis)Rows: 130
Columns: 15
$ DFU <chr> "Item 000001", "Item 000001", "Item 000…
$ Period <date> 2022-07-03, 2022-07-10, 2022-07-17, 20…
$ Demand <dbl> 364, 364, 364, 260, 736, 859, 859, 859,…
$ Opening <dbl> 6570, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
$ Calculated.Coverage.in.Periods <dbl> 16.8, 15.8, 14.8, 13.8, 12.8, 11.8, 10.…
$ Projected.Inventories.Qty <dbl> 6206, 5842, 5478, 5218, 4482, 3623, 276…
$ Supply <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
$ Min.Cov <dbl> 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, …
$ Max.Cov <dbl> 12, 12, 12, 12, 12, 12, 12, 12, 12, 12,…
$ Safety.Stocks <dbl> 1724, 2219, 2714, 3313, 2850, 2340, 183…
$ Maximum.Stocks <dbl> 5821, 5471, 5132, 4904, 4185, 3693, 334…
$ PI.Index <chr> "OverStock", "OverStock", "OverStock", …
$ Ratio.PI.vs.min <dbl> 3.60, 2.63, 2.02, 1.58, 1.57, 1.55, 1.5…
$ Ratio.PI.vs.Max <dbl> 1.07, 1.07, 1.07, 1.06, 1.07, 0.98, 0.8…
$ SSOS_qty <dbl> 385, 371, 346, 314, 297, 0, 0, 0, 0, 0,…#————————————————————————
Part 3 : Identify Overstocks Quantities
We will need to differentiate the part of inventories which is over the Maximum Coverage level.
For this we will use the SSOS_qty variable.
3.1) Create dataframe
Note : by default, we can have some negative projected inventories. As here we want to focus on the overstocks, and estimate the share of the total projected inventories that they represent, we will need to replace the negative projected inventories by zero.
We will also calculate the percentage of total inventories that the overstocks quantities represent.
It will be the variable [SSOS_pc] below :
# set a working df
df1 <- calculated_projection_and_analysis
# replace missing values by zero
df1$SSOS_qty <- df1$SSOS_qty |> replace_na(0)
df1$Projected.Inventories.Qty <- df1$Projected.Inventories.Qty |> replace_na(0)
# replace negative projected inventories by zero
# as here we're not interested in the projected shortages, but only the positive (existing) projected inventories
df1$Projected.Inventories.Qty <- if_else(df1$Projected.Inventories.Qty < 0, 0, df1$Projected.Inventories.Qty)
# aggregate
df1 <- df1 |> group_by(Period) |>
summarise(SSOS_qty = sum(SSOS_qty),
not_SSOS_qty = sum(Projected.Inventories.Qty) - sum(SSOS_qty) # we remove the overstocks quantity from the projected inventories
)
# let's calculate the % of overstocks
df1$SSOS_pc <- df1$SSOS_qty / (df1$SSOS_qty + df1$not_SSOS_qty)
df1$SSOS_pc <- 100 * df1$SSOS_pc
glimpse(df1)Rows: 13
Columns: 4
$ Period <date> 2022-07-03, 2022-07-10, 2022-07-17, 2022-07-24, 2022-07-…
$ SSOS_qty <dbl> 1976, 1588, 1287, 1026, 791, 470, 436, 3909, 5146, 1176, …
$ not_SSOS_qty <dbl> 57282, 43504, 43439, 54326, 58094, 52068, 42005, 38941, 7…
$ SSOS_pc <dbl> 3.3345709, 3.5216890, 2.8775209, 1.8535916, 1.3432963, 0.…3.2) Create chart (volume)
We can chart the weekly projected inventories for the whole portfolio, split into 2 parts :
inventories as overstocks
inventories below the maximum threshold
highchart() |>
hc_title(text = "Projected Overstocks") |>
hc_subtitle(text = "in units") |>
hc_add_theme(hc_theme_google()) |>
hc_xAxis(categories = df1$Period) |>
hc_add_series(name = "overstock",
color = "red",
dataLabels = list(align = "center", enabled = TRUE),
data = df1$SSOS_qty) |>
hc_add_series(name = "below max",
color = "mediumseagreen",
#dataLabels = list(align = "center", enabled = TRUE),
data = df1$not_SSOS_qty) |>
hc_chart(type = "column") |>
hc_plotOptions(series = list(stacking = "normal"))3.3) Create chart (percentage)
We also can look at it as a percentage of total projected inventories.
We can notice below that :
the week of Aug 21st 2022 has the highest level of overstocks with close to 9% of the total inventories
this percentage will then decrease to close to 0% by the end of September
# chart
highchart() |>
hc_title(text = "Share of Overstocks") |>
hc_subtitle(text = "as % of total projected inventories") |>
hc_add_theme(hc_theme_google()) |>
hc_xAxis(categories = df1$Period) |>
hc_yAxis(labels = list(format = "{value}%")) |>
hc_add_series(name = "share of Overstocks",
color = "orange",
data = df1$SSOS_pc,
dataLabels = list(enabled = TRUE, format = '{point.y:.1f}%')
) |>
hc_tooltip(pointFormat = '{point.y:.1f}%')We got an overview, at products portfolio level, of our total projected overstocks quantities.
Now, let’s get a more detailed picture, at SKU level, using a kind of cockpit table.
It will allow us to spot quickly :
which products will be in an overstock situation
when it will happen, and how much quantity
if we can take some actions to avoid those overstocks quantities
#————————————————————————
Part 4 : Cockpit table
Let’s create a cockpit table which
displays the Demand and Supply activity
provides a focus on the Overstocks situations within a selected horizon of time
informs about the situation of the ending inventories : shortages, alerts, overstocks
4.1) Create dataframe
#-----------------
# Get Summary of variables
#-----------------
# set a working df
df1 <- calculated_projection_and_analysis
# aggregate
df1 <- df1 |> group_by(DFU) |>
summarise(Demand = sum(Demand),
Opening = sum(Opening),
Supply = sum(Supply)
)
# let's calculate the share of Demand
df1$Demand.pc <- df1$Demand / sum(df1$Demand)
# keep Results
Value_data <- df1
#-----------------
# Get Sparklines Demand
#-----------------
# set a working df
df1 <- calculated_projection_and_analysis
# replace missing values by zero
df1$Demand <- df1$Demand |> replace_na(0)
# aggregate
df1 <- df1 |> group_by(DFU, Period) |> summarise(Quantity = sum(Demand))`summarise()` has grouped output by 'DFU'. You can override using the `.groups`
argument.# generate Sparkline
df1 <- df1 |> group_by(DFU) |> summarise(Demand.Quantity = list(Quantity))
# keep Results
Demand_Sparklines_data <- df1
#-----------------
# Get Sparklines Supply
#-----------------
# set a working df
df1 <- calculated_projection_and_analysis
# replace missing values by zero
df1$Supply <- df1$Supply |> replace_na(0)
# aggregate
df1 <- df1 |> group_by(DFU, Period) |> summarise(Quantity = sum(Supply))`summarise()` has grouped output by 'DFU'. You can override using the `.groups`
argument.# generate Sparkline
df1 <- df1 |> group_by(DFU) |> summarise(Supply.Quantity = list(Quantity))
# keep Results
Supply_Sparklines_data <- df1
#-----------------
# Get Projected Inventories
#-----------------
# set a working df
df1 <- calculated_projection_and_analysis
# replace missing values by zero
df1$Projected.Inventories.Qty <- df1$Projected.Inventories.Qty |> replace_na(0)
# aggregate
df1 <- df1 |> group_by(DFU, Period) |> summarise(Quantity = sum(Projected.Inventories.Qty))`summarise()` has grouped output by 'DFU'. You can override using the `.groups`
argument.# generate Sparkline
df1 <- df1 |> group_by(DFU) |> summarise(Proj.Inv.Quantity = list(Quantity))
# keep Results
Proj_Inv_Sparklines_data <- df1
#-----------------
# Get Projected Overstocks
#-----------------
# set a working df
df1 <- calculated_projection_and_analysis
# replace missing values by zero
df1$SSOS_qty <- df1$SSOS_qty |> replace_na(0)
# aggregate
df1 <- df1 |> group_by(DFU, Period) |> summarise(Quantity = sum(SSOS_qty))`summarise()` has grouped output by 'DFU'. You can override using the `.groups`
argument.# generate Sparkline
df1 <- df1 |> group_by(DFU) |> summarise(SSOS.Quantity = list(Quantity))
# keep Results
SSOS_Sparklines_data <- df1
#-----------------
# Get End of Horizon elements
#-----------------
# set a working df
df1 <- calculated_projection_and_analysis
# filter to end of the horizon
df1 <- df1 |> filter(Period == max(df1$Period))
# replace missing values by zero
df1$SSOS_qty <- df1$SSOS_qty |> replace_na(0)
# keep only needed variables
df1 <- df1 |> select(DFU,
Period,
Projected.Inventories.Qty,
SSOS_qty,
Calculated.Coverage.in.Periods,
PI.Index)
# replace negative values by zero
df1$Projected.Inventories.Qty <- if_else(df1$Projected.Inventories.Qty < 0, 0, df1$Projected.Inventories.Qty)
# rename
df1 <- df1 |> rename(end_horizon_Projected.Inventories.Qty = Projected.Inventories.Qty,
end_horizon_SSOS_qty = SSOS_qty,
end_horizon_Calculated.Coverage.in.Periods = Calculated.Coverage.in.Periods,
end_horizon_PI.Index = PI.Index)
# keep results
end_horizon_data <- df1
#-----------------
# Merge dataframes
#-----------------
# merge
df1 <- left_join(Value_data, Demand_Sparklines_data)Joining with `by = join_by(DFU)`df1 <- left_join(df1, Supply_Sparklines_data)Joining with `by = join_by(DFU)`df1 <- left_join(df1, Proj_Inv_Sparklines_data)Joining with `by = join_by(DFU)`df1 <- left_join(df1, SSOS_Sparklines_data)Joining with `by = join_by(DFU)`df1 <- left_join(df1, end_horizon_data)Joining with `by = join_by(DFU)`# reorder columns
df1 <- df1 |> select(DFU,
Demand, Demand.pc, Demand.Quantity, Opening,
Supply, Supply.Quantity,
Proj.Inv.Quantity,
SSOS.Quantity,
Period,
end_horizon_Projected.Inventories.Qty,
end_horizon_SSOS_qty,
end_horizon_Calculated.Coverage.in.Periods,
end_horizon_PI.Index
)
# get results
cockpit_data <- df1
glimpse(cockpit_data)Rows: 10
Columns: 14
$ DFU <chr> "Item 000001", "Item 000002…
$ Demand <dbl> 6185, 18458, 1314, 12336, 2…
$ Demand.pc <dbl> 0.042589379, 0.127100204, 0…
$ Demand.Quantity <list> <364, 364, 364, 260, 736, …
$ Opening <dbl> 6570, 5509, 2494, 7172, 175…
$ Supply <dbl> 0, 15120, 0, 10000, 30000, …
$ Supply.Quantity <list> <0, 0, 0, 0, 0, 0, 0, 0, 0…
$ Proj.Inv.Quantity <list> <6206, 5842, 5478, 5218, 4…
$ SSOS.Quantity <list> <385, 371, 346, 314, 297, …
$ Period <date> 2022-09-25, 2022-09-25, 20…
$ end_horizon_Projected.Inventories.Qty <dbl> 385, 2171, 1180, 4836, 1780…
$ end_horizon_SSOS_qty <dbl> 0, 0, 111, 0, 0, 0, 0, 0, 0…
$ end_horizon_Calculated.Coverage.in.Periods <dbl> 4.8, 2.7, 13.3, 3.7, 9.0, 6…
$ end_horizon_PI.Index <chr> "OK", "Alert", "OverStock",…4.2) Create 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 the reactable :
#-----------------
# Preparation
#-----------------
# keep ending date
end_date <- unique(df1$Period)
# remove not needed variable
df1 <- df1 |> select(-Period)
#-----------------
# Create Table
#-----------------
reactable(df1,compact = TRUE,
defaultSortOrder = "desc",
defaultSorted = c("Demand"),
defaultPageSize = 20,
columns = list(
`DFU` = colDef(name = "DFU"),
`Demand`= colDef(
name = "Total Demand (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")
),
`Demand.pc`= colDef(
name = "Share of Demand (%)",
format = colFormat(percent = TRUE, digits = 1)
), # close %
`Supply`= colDef(
name = "Total Supply (units)",
aggregate = "sum", footer = function(values) formatC(sum(values),format="f", big.mark=",", digits=0),
format = colFormat(separators = TRUE, digits=0)
),
`Opening` = colDef(
name = "Opening Inventories (units)",
aggregate = "sum",
footer = function(values) formatC(sum(values),
format = "f",
big.mark = ",",
digits=0),
format = colFormat(separators = TRUE, digits=0)
),
Demand.Quantity = colDef(
name = "Projected Demand",
cell = function(value, index) {
sparkline(df1$Demand.Quantity[[index]])
}),
Supply.Quantity = colDef(
name = "Projected Supply",
cell = function(values) {
sparkline(values, type = "bar"
#chartRangeMin = 0, chartRangeMax = max(chickwts$weight)
)
}),
Proj.Inv.Quantity = colDef(
name = "Projected Inventories",
cell = function(value, index) {
sparkline(df1$Proj.Inv.Quantity[[index]])
}),
SSOS.Quantity = colDef(
name = "Projected Overstocks",
cell = function(value, index) {
sparkline(df1$SSOS.Quantity[[index]])
}),
`end_horizon_Projected.Inventories.Qty` = colDef(
name = "Ending Inventories (units)",
aggregate = "sum",
footer = function(values) formatC(sum(values),
format = "f",
big.mark = ",",
digits=0),
format = colFormat(separators = TRUE, digits=0)
),
`end_horizon_SSOS_qty` = colDef(
name = "o/w Ending Overstocks (units)",
aggregate = "sum",
footer = function(values) formatC(sum(values),
format = "f",
big.mark = ",",
digits=0),
format = colFormat(separators = TRUE, digits=0)
),
`end_horizon_Calculated.Coverage.in.Periods` = colDef(
name = "Ending Coverage (weeks)",
format = colFormat(separators = TRUE, digits = 1)
),
end_horizon_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%)"
)
end_horizon_PI.Index <- status_PI.Index(color = color)
tagList(end_horizon_PI.Index, value)
})
), # close columns list
defaultColDef = colDef(footerStyle = list(fontWeight = "bold")),
columnGroups = list(
colGroup(name = "Demand",
columns = c("Demand",
"Demand.pc",
"Demand.Quantity")),
colGroup(name = "Supply",
columns = c("Supply", "Supply.Quantity")),
colGroup(name = "Projected Inventories",
columns = c("Proj.Inv.Quantity", "SSOS.Quantity")),
colGroup(name = paste(end_date, ": Inventories", sep = " "),
columns = c("end_horizon_Projected.Inventories.Qty",
"end_horizon_SSOS_qty",
"end_horizon_Calculated.Coverage.in.Periods",
"end_horizon_PI.Index"))
)
) # close reactableThis simple cockpit informs us of :
the projected Demand & Supply activity of each SKU
the projected inventories as a result, highlighting the eventual overstocks situations
It also can give us some actions to be taken on the Supply side.
For example, we can notice that :
on items 4 and 5 : we have some overstocks triggered by some projected supplies. If possible, it would be useful to postpone or reduce those coming supplies
on items 1, 3 and 9 : the overstocks situations are in the short term and will slowly resolve
Overall, very limited amount of overstocks at the end of the horizon (only the item “000003” with 111 units).
For more examples about the R package planr for Supply Chain, please visit the website : planr - Demand & Supply Planning | S&OP :: with R