# ETL
library(tidyverse)
# Charts
library(highcharter)
# Tables
library(gt)
# Supply Chain
library(planr)31 Conversion Monthly to Weekly bucket customized
Let’s upload the libraries we’re going to use :
We’re going to present here how to use the month_to_weekx() function from the R package planr.
This function allows to :
split a Demand (for ex.: Sales Forecasts) from monthly into weekly buckets.
and to define the weekly pattern.
We need a data frame with the 3 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 : here in monthly buckets only.
a Demand : could be some sales forecasts, expressed in units.
And also 4 variables, which are coefficients :
W1 : percentage of Demand of the month occurring during the first week.
W2 : percentage of Demand of the month occurring during the second week.
W3 : percentage of Demand of the month occurring during the third week.
W4 : percentage of Demand of the month occurring during the fourth week.
32 Demo dataset
Let’s create a data frame with 4 products (A, B, C , D) with an identical Demand expressed in monthly bucket.
We’re going to transform the Monthly Demand into Weekly Bucket, using as example 4 types of distributions :
demand evenly distributed among each week of the month.
demand following a “L pattern” (occurring mainly during the beginning of the month).
demand following a “J pattern” (occurring mainly during the end of the month).
demand following a random pattern (with some peaks during some weeks).
Let’s assemble the demand of those 4 products :
32.1 Even pattern
It will be our Product A.
#--------------------------------
# Input values for the test
#--------------------------------
# Create a vector of Monthly Period
Period <- c("2023-06-01", "2023-07-01", "2023-08-01")
# Create a vector of Demand
Demand <- c(1000, 2000, 1000)
# assemble
input <- data.frame(Period,
Demand)
# let's add a Product
input$DFU <- "Product A"
# format the Period as a date
input$Period <- as.Date(as.character(input$Period), format = '%Y-%m-%d')
# define weekly distribution values
input$W1 <- 0.25
input$W2 <- 0.25
input$W3 <- 0.25
input$W4 <- 0.25
# keep results
input_ProdA <- input
glimpse(input_ProdA)Rows: 3
Columns: 7
$ Period <date> 2023-06-01, 2023-07-01, 2023-08-01
$ Demand <dbl> 1000, 2000, 1000
$ DFU <chr> "Product A", "Product A", "Product A"
$ W1 <dbl> 0.25, 0.25, 0.25
$ W2 <dbl> 0.25, 0.25, 0.25
$ W3 <dbl> 0.25, 0.25, 0.25
$ W4 <dbl> 0.25, 0.25, 0.2532.2 L pattern
This will be the Product B.
#--------------------------------
# Input values for the test
#--------------------------------
# Create a vector of Monthly Period
Period <- c("2023-06-01", "2023-07-01", "2023-08-01")
# Create a vector of Demand
Demand <- c(1000, 2000, 1000)
# assemble
input <- data.frame(Period,
Demand)
# let's add a Product
input$DFU <- "Product B"
# format the Period as a date
input$Period <- as.Date(as.character(input$Period), format = '%Y-%m-%d')
# define weekly distribution values
input$W1 <- 0.75
input$W2 <- 0.15
input$W3 <- 0.05
input$W4 <- 0.05
# keep results
input_ProdB <- input
glimpse(input_ProdB)Rows: 3
Columns: 7
$ Period <date> 2023-06-01, 2023-07-01, 2023-08-01
$ Demand <dbl> 1000, 2000, 1000
$ DFU <chr> "Product B", "Product B", "Product B"
$ W1 <dbl> 0.75, 0.75, 0.75
$ W2 <dbl> 0.15, 0.15, 0.15
$ W3 <dbl> 0.05, 0.05, 0.05
$ W4 <dbl> 0.05, 0.05, 0.0532.3 J pattern
It will be Product C.
#--------------------------------
# Input values for the test
#--------------------------------
# Create a vector of Monthly Period
Period <- c("2023-06-01", "2023-07-01", "2023-08-01")
# Create a vector of Demand
Demand <- c(1000, 2000, 1000)
# assemble
input <- data.frame(Period,
Demand)
# let's add a Product
input$DFU <- "Product C"
# format the Period as a date
input$Period <- as.Date(as.character(input$Period), format = '%Y-%m-%d')
# define weekly distribution values
input$W1 <- 0.05
input$W2 <- 0.05
input$W3 <- 0.15
input$W4 <- 0.75
# keep results
input_ProdC <- input
glimpse(input_ProdC)Rows: 3
Columns: 7
$ Period <date> 2023-06-01, 2023-07-01, 2023-08-01
$ Demand <dbl> 1000, 2000, 1000
$ DFU <chr> "Product C", "Product C", "Product C"
$ W1 <dbl> 0.05, 0.05, 0.05
$ W2 <dbl> 0.05, 0.05, 0.05
$ W3 <dbl> 0.15, 0.15, 0.15
$ W4 <dbl> 0.75, 0.75, 0.7532.4 Random pattern
We will associate it to the Product D.
#--------------------------------
# Input values for the test
#--------------------------------
# Create a vector of Monthly Period
Period <- c("2023-06-01", "2023-07-01", "2023-08-01")
# Create a vector of Demand
Demand <- c(1000, 2000, 1000)
# assemble
input <- data.frame(Period,
Demand)
# let's add a Product
input$DFU <- "Product D"
# format the Period as a date
input$Period <- as.Date(as.character(input$Period), format = '%Y-%m-%d')
# define weekly distribution values
input$W1 <- 0.5
input$W2 <- 0.1
input$W3 <- 0.3
input$W4 <- 0.1
# keep results
input_ProdD <- input
glimpse(input_ProdD)Rows: 3
Columns: 7
$ Period <date> 2023-06-01, 2023-07-01, 2023-08-01
$ Demand <dbl> 1000, 2000, 1000
$ DFU <chr> "Product D", "Product D", "Product D"
$ W1 <dbl> 0.5, 0.5, 0.5
$ W2 <dbl> 0.1, 0.1, 0.1
$ W3 <dbl> 0.3, 0.3, 0.3
$ W4 <dbl> 0.1, 0.1, 0.1Assemble
Now let’s combine the demand and weekly distribution values of those 4 products into one data frame that we will call input .
df1 <- rbind(input_ProdA, input_ProdB, input_ProdC, input_ProdD)
# keep results
input <- df1
input Period Demand DFU W1 W2 W3 W4
1 2023-06-01 1000 Product A 0.25 0.25 0.25 0.25
2 2023-07-01 2000 Product A 0.25 0.25 0.25 0.25
3 2023-08-01 1000 Product A 0.25 0.25 0.25 0.25
4 2023-06-01 1000 Product B 0.75 0.15 0.05 0.05
5 2023-07-01 2000 Product B 0.75 0.15 0.05 0.05
6 2023-08-01 1000 Product B 0.75 0.15 0.05 0.05
7 2023-06-01 1000 Product C 0.05 0.05 0.15 0.75
8 2023-07-01 2000 Product C 0.05 0.05 0.15 0.75
9 2023-08-01 1000 Product C 0.05 0.05 0.15 0.75
10 2023-06-01 1000 Product D 0.50 0.10 0.30 0.10
11 2023-07-01 2000 Product D 0.50 0.10 0.30 0.10
12 2023-08-01 1000 Product D 0.50 0.10 0.30 0.10This data frame has 7 variables :
3 variables related to the Monthly Demand :
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 : here in monthly buckets only.
a Demand : could be some sales forecasts, expressed in units.
And also 4 variables, which are coefficients :
W1 : percentage of Demand of the month occurring during the first week.
W2 : percentage of Demand of the month occurringduring the second week.
W3 : percentage of Demand of the month occurring during the third week.
W4 : percentage of Demand of the month occurring during the fourth week.
It’s the template that we will use to apply the function month_to_weekx() and split the monthly demand into weekly bucket, with a customized weekly distribution.
For each product, the monthly demand is the same, only the weekly coefficients change.
33 Summary Table
We could summarize those weekly patterns into the following table.
To illustrate it, we display a nanoplots Table, using the library gt . We create first a data frame, and then display it using through gt table.
# create vectors
pattern <- c("even distribution", "L pattern", "J pattern", "random")
W1 <- c(0.25, 0.75, 0.05, 0.5)
W2 <- c(0.25, 0.15, 0.05, 0.1)
W3 <- c(0.25, 0.05, 0.15, 0.3)
W4 <- c(0.25, 0.05, 0.75, 0.1)
# create a data frame
patterns_data <- data.frame(pattern,
W1,
W2,
W3,
W4)
# display table
patterns_data pattern W1 W2 W3 W4
1 even distribution 0.25 0.25 0.25 0.25
2 L pattern 0.75 0.15 0.05 0.05
3 J pattern 0.05 0.05 0.15 0.75
4 random 0.50 0.10 0.30 0.10Now let’s display the nanoplots Table :
patterns_data |>
gt(rowname_col = "pattern") |>
tab_header("Examples of Weekly Distribution patterns") |>
tab_stubhead(label = md("**Pattern**")) |>
#cols_hide(columns = c(starts_with("norm"), units)) |>
cols_nanoplot(
columns = starts_with("W"),
new_col_name = "nanoplots",
new_col_label = md("*Distribution*")
) |>
cols_align(align = "center", columns = nanoplots) |>
tab_footnote(
footnote = "Sales Forecasts from Week1 through Week4.",
locations = cells_column_labels(columns = nanoplots)
)| Examples of Weekly Distribution patterns | |
| Pattern | Distribution1 |
|---|---|
| even distribution | |
| L pattern | |
| J pattern | |
| random | |
| 1 Sales Forecasts from Week1 through Week4. | |
Our example will create those 4 different patterns of weekly distribution.
34 Transform into Weekly Buckets
We now apply the month_to_weekx() function to the demo dataset input.
It will transform the Demand initially expressed in monthly buckets into weekly buckets, following the weekly coefficients W1 | W2 | W3 | W4 .
# calculate
df1 <- planr::month_to_weekx(dataset = input,
DFU = DFU,
W1 = W1,
W2 = W2,
W3 = W3,
W4 = W4,
Period = Period,
Demand = Demand
)
# keep results
calculated_data <- df1
# display results
head(calculated_data) DFU Period Demand
1 Product A 2023-05-28 107.1429
2 Product A 2023-06-04 250.0000
3 Product A 2023-06-11 250.0000
4 Product A 2023-06-18 250.0000
5 Product A 2023-06-25 214.2857
6 Product A 2023-07-02 500.0000We obtain a period of time now in weekly bucket, and its associated weekly demand.
Let’s look at it through 4 different charts, one for each pattern.
35 Charts Weekly Demand
35.1 Even pattern
This is the Product A. The monthly demand is evenly split across the different weeks of the month.
# select Product A
df1 <- calculated_data |> filter(DFU == "Product A")
# chart
highchart() |>
hc_add_series(name = "Weekly Demand",
color = "mediumseagreen",
type = 'spline',
#dataLabels = list(align = "center", enabled = TRUE),
data = df1$`Demand`) |>
hc_title(text = "Even Distribution") |>
hc_subtitle(text = "in units") |>
hc_xAxis(categories = df1$Period) |>
hc_add_theme(hc_theme_google())35.2 L pattern
This is the Product B. The weekly demand shows higher volumes at the beginning of the month.
# select Product B
df1 <- calculated_data |> filter(DFU == "Product B")
# chart
highchart() |>
hc_add_series(name = "Weekly Demand",
color = "salmon",
type = 'spline',
#dataLabels = list(align = "center", enabled = TRUE),
data = df1$`Demand`) |>
hc_title(text = "L Pattern") |>
hc_subtitle(text = "in units") |>
hc_xAxis(categories = df1$Period) |>
hc_add_theme(hc_theme_google())35.3 J pattern
This is the Product C. The weekly demand shows higher volumes at the end of the month.
# select Product B
df1 <- calculated_data |> filter(DFU == "Product C")
# chart
highchart() |>
hc_add_series(name = "Weekly Demand",
color = "steelblue",
type = 'spline',
#dataLabels = list(align = "center", enabled = TRUE),
data = df1$`Demand`) |>
hc_title(text = "J Pattern") |>
hc_subtitle(text = "in units") |>
hc_xAxis(categories = df1$Period) |>
hc_add_theme(hc_theme_google())35.4 Random pattern
This is the Product D. The weekly demand follows the custom weekly pattern that we defined.
# select Product D
df1 <- calculated_data |> filter(DFU == "Product D")
# chart
highchart() |>
hc_add_series(name = "Weekly Demand",
color = "gold",
type = 'spline',
#dataLabels = list(align = "center", enabled = TRUE),
data = df1$`Demand`) |>
hc_title(text = "Random Pattern") |>
hc_subtitle(text = "in units") |>
hc_xAxis(categories = df1$Period) |>
hc_add_theme(hc_theme_google())Choosing the appropriate distribution pattern when converting the Demand from Monthly into Weekly bucket can provide a more accurate calculation of :
projected inventories & coverages.
replenishment plan.