proj_git

Objectives

To show how to use the proj_git() function from the planr package.

proj_git stands for “Projection Goods In Transit”.

The idea is to calculate, considering a Transit Time, the projection of the inventories in transit.

More details on the planr package on : https://github.com/nguyennico/planr

• The planr package provides tools (functions) for Supply Chain Management (Demand & Supply Planning and S&OP process)

Part 1 : Demo dataset

1.1) Overview

Let’s use the demo dataset demo_in_transit from the planr package.

It contains 6 variables :

• DFU, a location and an item

  • there are 3 DFUs :Entity 1_Product A / Entity 2_Product B / Entity 3_Product C

• Period, a date in weekly bucket format

• ETA.Current

  • some quantities currently in transit displayed at their ETA date in units

  • ETA stands for Estimated Time of Arrival

• ETA.Next

  • some quantities to be shipped, not yet in transit, displayed at their ETA date in units

• ETD.Next

  • some quantities to be shipped, not yet in transit, displayed at their ETD date in units

  • ETD stands for Estimated Time of Departure

• TLT, the Transit Lead Time, expressed in weeks,

  • represents the difference between ETA and ETD dates

There are 2 types of in transit : the current in transit and the next one, not yet shipped.

Note that the difference between ETD and ETA is the Transit Time.

The idea is to use this dataset to project the Goods In Transit.

We can apply on this dataset the proj_git() function, it will calculate the Proj.GIT which gathers the current and next In Transit quantities.

data("demo_in_transit")

head(demo_in_transit)

# A tibble: 6 × 6
  DFU                Period     ETA.Current ETA.Next ETD.Next   TLT
  <chr>              <date>           <dbl>    <dbl>    <dbl> <dbl>
1 Entity 1_Product A 2024-02-18           0        0        0     8
2 Entity 1_Product A 2024-02-25        1008        0      500     8
3 Entity 1_Product A 2024-03-03           0        0        0     8
4 Entity 1_Product A 2024-03-10         252        0        0     8
5 Entity 1_Product A 2024-03-17           0        0        0     8
6 Entity 1_Product A 2024-03-24           0        0      800     8

1.2) DFUs x Transit Time

Let’s get a summary of the different DFU : - Total quantity In Transit over the horizon - related Transit Lead Times

We have 3 different couples Entity x Product, with 3 different Transit Times :

• a long one : 8 weeks, for Entity 1_Product A

• a medium one : 4 weeks, for Entity 2_Product B

• a short one : 2 weeks, Entity 3_Product C

Also, for each couple, the Total quantity In Transit over the horizon is similar.

# keep only unique variables
df1 <- demo_in_transit |> group_by(DFU) |>
  summarise(ETA.Total = sum(ETA.Current) + sum(ETA.Next),
            TLT = mean(TLT)
            )

df1

# A tibble: 3 × 3
  DFU                ETA.Total   TLT
  <chr>                  <dbl> <dbl>
1 Entity 1_Product A      3160     8
2 Entity 2_Product B      3160     4
3 Entity 3_Product C      3160     2

Part 2 : Calculate Projected In Transit

Using the proj_git() function, we can calculate the Projected In Transit for all the different couples Entity x Product contained into the dataset.

# apply proj_git()
Calculated_GIT_DB <- planr::proj_git(dataset = demo_in_transit, 
                       DFU, 
                       Period, 
                       ETA.Current, 
                       ETA.Next, 
                       ETD.Next, 
                       TLT)

glimpse(Calculated_GIT_DB)

Rows: 447
Columns: 11
$ DFU              <chr> "Entity 1_Product A", "Entity 1_Product A", "Entity 1…
$ Period           <date> 2024-02-18, 2024-02-25, 2024-03-03, 2024-03-10, 2024…
$ ETA.Current      <dbl> 0, 1008, 0, 252, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
$ ETA.Next         <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 500, 0, 0, 0, 800, 0, 0, 0…
$ ETD.Next         <dbl> 0, 500, 0, 0, 0, 800, 0, 0, 0, 600, 0, 0, 0, 0, 0, 0,…
$ TLT              <dbl> 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8,…
$ Current.GIT      <dbl> 1260, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ ETD.Current      <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ Proj.Current.GIT <dbl> 1260, 252, 252, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
$ Proj.Future.GIT  <dbl> 0, 500, 500, 500, 500, 1300, 1300, 1300, 1300, 1400, …
$ Proj.GIT         <dbl> 1260, 752, 752, 500, 500, 1300, 1300, 1300, 1300, 140…

We got 5 new variables:

• Current.GIT : the total inventories currently in transit

• ETD.Current : when the current inventories in transit were shipped (estimated Departure Time)

• Proj.Current.GIT : projected inventories of the current in transit

• Proj.Future.GIT : projected inventories of the future in transit

• Proj.GIT : total projected inventories, current and future in transit

Those variables will be useful to display the projection of the inventories in transit.

Part 3 : Visualize

3.1) Individual Projections

For our 3 different DFUs, let’s visualize the projection of the In Transit inventories.

There are 2 components to consider :

• Current in transit

• Future in transit

We observe (as expected!) that the longer the transit time, the higher the projected in transit.

a) Entity 1 x Product A

# set a working df
df1 <- Calculated_GIT_DB

# filter
df1 <- filter(df1, df1$DFU == "Entity 1_Product A")

# make the horizon shorter
df1 <- filter(df1, df1$Period <= (min(df1$Period) + 120) )

#--------------------
# Chart


u <- highchart() |> 
      hc_title(text = "Projected In Transit") |>
      hc_subtitle(text = "in units") |> 
      hc_add_theme(hc_theme_google()) |>
      
      hc_xAxis(categories = df1$Period) |> 
      
      hc_add_series(name = "Current", 
                    color = "steelblue",
                    dataLabels = list(align = "center", enabled = TRUE),
                    data = df1$Proj.Current.GIT) |> 
      
      hc_add_series(name = "Future", 
                    color = "gold",
                    dataLabels = list(align = "center", enabled = TRUE),
                    data = df1$Proj.Future.GIT) |> 
      
      hc_chart(type = "column") |> 
      
      hc_plotOptions(series = list(stacking = "normal"))
    
    u 

b) Entity 2 x Product B

# set a working df
df1 <- Calculated_GIT_DB

# filter
df1 <- filter(df1, df1$DFU == "Entity 2_Product B")

# make the horizon shorter
df1 <- filter(df1, df1$Period <= (min(df1$Period) + 120) )

#--------------------
# Chart


u <- highchart() |> 
      hc_title(text = "Projected In Transit") |>
      hc_subtitle(text = "in units") |> 
      hc_add_theme(hc_theme_google()) |>
      
      hc_xAxis(categories = df1$Period) |> 
      
      hc_add_series(name = "Current", 
                    color = "steelblue",
                    dataLabels = list(align = "center", enabled = TRUE),
                    data = df1$Proj.Current.GIT) |> 
      
      hc_add_series(name = "Future", 
                    color = "gold",
                    dataLabels = list(align = "center", enabled = TRUE),
                    data = df1$Proj.Future.GIT) |> 
      
      hc_chart(type = "column") |> 
      
      hc_plotOptions(series = list(stacking = "normal"))
    
    u 

c) Entity 3 x Product C

# set a working df
df1 <- Calculated_GIT_DB

# filter
df1 <- filter(df1, df1$DFU == "Entity 3_Product C")

# make the horizon shorter
df1 <- filter(df1, df1$Period <= (min(df1$Period) + 120) )

#--------------------
# Chart


u <- highchart() |> 
      hc_title(text = "Projected In Transit") |>
      hc_subtitle(text = "in units") |> 
      hc_add_theme(hc_theme_google()) |>
      
      hc_xAxis(categories = df1$Period) |> 
      
      hc_add_series(name = "Current", 
                    color = "steelblue",
                    dataLabels = list(align = "center", enabled = TRUE),
                    data = df1$Proj.Current.GIT) |> 
      
      hc_add_series(name = "Future", 
                    color = "gold",
                    dataLabels = list(align = "center", enabled = TRUE),
                    data = df1$Proj.Future.GIT) |> 
      
      hc_chart(type = "column") |> 
      
      hc_plotOptions(series = list(stacking = "normal"))
    
    u 

3.2) Comparison

# set a working df
df1 <- Calculated_GIT_DB

# filter : make the horizon shorter
df1 <- filter(df1, df1$Period <= (min(df1$Period) + 120) )

# keep only needed variables
df1 <- df1 |> select(DFU, Period, Proj.GIT)

# spread
df1 <- df1 |> spread(DFU, Proj.GIT)


#--------------------
# Chart

p <- highchart() |>
  
  hc_add_series(name = "8 weeks", 
                color = "gold", 
                dataLabels = list(align = "center", enabled = TRUE),
                data = df1$`Entity 1_Product A`) |>
  
  hc_add_series(name = "4 weeks", 
                color = "steelblue", 
                dataLabels = list(align = "center", enabled = TRUE),
                data = df1$`Entity 2_Product B`) |>
  
  hc_add_series(name = "2 weeks", 
                color = "mediumseagreen", 
                dataLabels = list(align = "center", enabled = TRUE),
                data = df1$`Entity 3_Product C`) |>
  
  hc_title(text = "Comparison Projected In Transit") |>
  hc_subtitle(text = "in units") |> 
  
  hc_xAxis(categories = df1$Calendar.Month.abb) |>
  
  hc_add_theme(hc_theme_google())
  
# display chart  
p

Obviously, the longer the Transit Lead Time, the bigger are the projected inventories in transit.

This calculation makes also easy the projection of the Total Inventories of a supply network.

For example if Entity 1 supplies a Product A to the Entity 2, we could :

• project the inventories at the Entity 1

• project the inventories at the Entity 2

• project the inventories in transit between both entities

3.3) All In Transit

For the 3 couples Entity x Product.

# set a working df
df1 <- Calculated_GIT_DB

# filter : make the horizon shorter
df1 <- filter(df1, df1$Period <= (min(df1$Period) + 120) )

# keep only needed variables
df1 <- df1 |> select(DFU, Period, Proj.GIT)

# spread
df1 <- df1 |> spread(DFU, Proj.GIT)


#--------------------
# Chart

u <- highchart() |> 
  
  
  hc_title(text = "Projected All In Transit") |>
  hc_subtitle(text = "in units") |> 
  hc_add_theme(hc_theme_google()) |>
  
  hc_xAxis(categories = df1$Period) |> 
  
  
  hc_add_series(name = "8 weeks", 
                color = "gold", 
                dataLabels = list(align = "center", enabled = TRUE),
                data = df1$`Entity 1_Product A`) |>
  
  
  hc_add_series(name = "4 weeks", 
                color = "steelblue", 
                dataLabels = list(align = "center", enabled = TRUE),
                data = df1$`Entity 2_Product B`) |>
  
  hc_add_series(name = "2 weeks", 
                color = "mediumseagreen", 
                dataLabels = list(align = "center", enabled = TRUE),
                data = df1$`Entity 3_Product C`) |>
  
  hc_chart(type = "column") |> 
  
  hc_plotOptions(series = list(stacking = "normal"))

# display chart  
u 

Interesting to visualize where are the most important projected inventories in transit for a supply network :

• which products

• between which entities

• how much inventories

Part 4 : Calculate DRP

Let’s consider the Entity 1 x Product A and calculate a DRP.

The idea is to then visualize together 2 parts :

• local projected inventories

• projected in-transit

  • which often, once shipped, belongs financially to the receiving Entity 1

It’s an easy way to simulate, based on some DRP parameters (and also the Demand Forecasts) the total (local + in transit) projected inventories of an Entity.

4.1) Add Demand / Opening / Supply

# set a working df
df1 <- Calculated_GIT_DB

# filter
df1 <- filter(df1, df1$DFU == "Entity 1_Product A")

# add Demand
df1$Demand <- 100

# add Supply
df1$Supply <- 0

# add Opening
df1$Opening <- if_else(df1$Period == min(df1$Period), 600, 0)
df1$Opening <- df1$Opening |> replace_na(0)

# keep only needed variables
df1 <- df1 |> select(DFU,
                     Period,
                     Demand,
                     Opening,
                     Supply)

glimpse(df1)

Rows: 149
Columns: 5
$ DFU     <chr> "Entity 1_Product A", "Entity 1_Product A", "Entity 1_Product …
$ Period  <date> 2024-02-18, 2024-02-25, 2024-03-03, 2024-03-10, 2024-03-17, 2…
$ Demand  <dbl> 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 10…
$ Opening <dbl> 600, 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, 0, 0, 0, 0, 0, 0, 0,…

4.2) Add DRP parameters

df1$SSCov <- 2
df1$DRPCovDur <- 3

df1$MOQ <- 1
df1$FH <- if_else(df1$Period <= '2024-04-07', "Frozen", "Free")

glimpse(df1)

Rows: 149
Columns: 9
$ DFU       <chr> "Entity 1_Product A", "Entity 1_Product A", "Entity 1_Produc…
$ Period    <date> 2024-02-18, 2024-02-25, 2024-03-03, 2024-03-10, 2024-03-17,…
$ Demand    <dbl> 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, …
$ Opening   <dbl> 600, 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, 0, 0, 0, 0, 0, 0, …
$ SSCov     <dbl> 2, 2, 2, 2, 2, 2, 2, 2, 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, 3, 3, 3, 3, 3, 3, 3, 3, …
$ MOQ       <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
$ FH        <chr> "Frozen", "Frozen", "Frozen", "Frozen", "Frozen", "Frozen", …

4.3) Calculate DRP

Now let’s calculate the DRP using the drp() function of the planr package.

We will get especially :

• the local projected inventories

• the DRP Replenishment Plan

  • that we will use to calculate a new projection of the in transit

# calculate DRP
Calculated_DRP_DB <- planr::drp(dataset = df1, 
                  DFU, 
                  Period, 
                  Demand, 
                  Opening, 
                  Supply, 
                  SSCov, 
                  DRPCovDur, 
                  MOQ, 
                  FH)

glimpse(Calculated_DRP_DB)

Rows: 149
Columns: 15
$ DFU                                <chr> "Entity 1_Product A", "Entity 1_Pro…
$ Period                             <date> 2024-02-18, 2024-02-25, 2024-03-03…
$ Demand                             <dbl> 100, 100, 100, 100, 100, 100, 100, …
$ Opening                            <dbl> 600, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
$ Supply                             <dbl> 0, 0, 0, 0, 0, 0, 0, 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> 200, 200, 200, 200, 200, 200, 200, …
$ Maximum.Stocks                     <dbl> 500, 500, 500, 500, 500, 500, 500, …
$ DRP.Calculated.Coverage.in.Periods <dbl> 5, 4, 3, 2, 1, 0, -1, -2, 5, 4, 3, …
$ DRP.Projected.Inventories.Qty      <dbl> 500, 400, 300, 200, 100, 0, -100, -…
$ DRP.plan                           <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 800, 0, 0, …

4.4) Project In Transit

We will keep the current in transit from the initial dataset demo_in_transit.

Therefore, the variables :

• DFU Period

• ETA.Current TLT

And we will add the new variables coming from the DRP Calculation :

• ETA.Next

• ETD.Next

a) Create Template

#--------------------
# Get Current In Transit Components
#--------------------

# set a working df
df1 <- Calculated_GIT_DB

# select DFU
df1 <- filter(df1, df1$DFU == "Entity 1_Product A")


# keep only the needed columns
df1 <- df1 |> select(DFU,
                     Period,
                     ETA.Current,
                     TLT)

# keep results
Current_GIT_DB <- df1


#--------------------
# Get Next In Transit Components
#--------------------

# set a working df
df1 <- Calculated_DRP_DB

# select DFU
df1 <- filter(df1, df1$DFU == "Entity 1_Product A")


# keep only the needed columns
df1 <- df1 |> select(DFU,
                     Period,
                     DRP.plan)

# rename
df1 <- df1 |> rename(ETA.Next = DRP.plan)

# keep results
Next_ETA_GIT_DB <- df1


#--------------------
# Assemble to calculate the ETD.Next
#--------------------

# merge
df1 <- left_join(Next_ETA_GIT_DB, Current_GIT_DB)

# keep only the needed variables
df1 <- df1 |> select(DFU, Period, ETA.Next, TLT)

# calculate ETD.Period
df1$ETD.Period <- df1$Period - (df1$TLT * 7)

# get the beginning of the week for the Period
# to ensure we are following an english standard
df1$ETD.Period <- floor_date(as.Date(df1$ETD.Period, "%Y-%m-%d"), unit = "week")

# keep only the needed variables
df1 <- df1 |> select(DFU, ETD.Period, ETD.Period, ETA.Next)

# rename
df1 <- df1 |> rename(Period = ETD.Period,
                     ETD.Next = ETA.Next)

# keep results
Next_ETD_GIT_DB <- df1


#--------------------
# Get Template
#--------------------

# merge
df1 <- left_join(Current_GIT_DB, Next_ETA_GIT_DB)
df1 <- left_join(df1, Next_ETD_GIT_DB)

# keep results
Template_GIT_DB <- df1


glimpse(Template_GIT_DB)

Rows: 149
Columns: 6
$ DFU         <chr> "Entity 1_Product A", "Entity 1_Product A", "Entity 1_Prod…
$ Period      <date> 2024-02-18, 2024-02-25, 2024-03-03, 2024-03-10, 2024-03-1…
$ ETA.Current <dbl> 0, 1008, 0, 252, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ TLT         <dbl> 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8…
$ ETA.Next    <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 800, 0, 0, 300, 0, 0, 300, 0, 0, 3…
$ ETD.Next    <dbl> 800, 0, 0, 300, 0, 0, 300, 0, 0, 300, 0, 0, 300, 0, 0, 300…

b) Calculate Projected In Transit

Now we’re ready to calculate a new projection of the in transit inventories.

# apply proj_git()
New_Calculated_GIT_DB <- planr::proj_git(dataset = Template_GIT_DB, 
                       DFU, 
                       Period, 
                       ETA.Current, 
                       ETA.Next, 
                       ETD.Next, 
                       TLT)

glimpse(New_Calculated_GIT_DB)

Rows: 149
Columns: 11
$ DFU              <chr> "Entity 1_Product A", "Entity 1_Product A", "Entity 1…
$ Period           <date> 2024-02-18, 2024-02-25, 2024-03-03, 2024-03-10, 2024…
$ ETA.Current      <dbl> 0, 1008, 0, 252, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
$ TLT              <dbl> 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8,…
$ ETA.Next         <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 800, 0, 0, 300, 0, 0, 300, 0,…
$ ETD.Next         <dbl> 800, 0, 0, 300, 0, 0, 300, 0, 0, 300, 0, 0, 300, 0, 0…
$ Current.GIT      <dbl> 1260, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ ETD.Current      <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ Proj.Current.GIT <dbl> 1260, 252, 252, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
$ Proj.Future.GIT  <dbl> 800, 800, 800, 1100, 1100, 1100, 1400, 1400, 600, 900…
$ Proj.GIT         <dbl> 2060, 1052, 1052, 1100, 1100, 1100, 1400, 1400, 600, …

4.5) Visualize results

a) Proj Inv table

Just a visual of the local projected inventories through the DRP calculation.

# set a working df
df1 <- Calculated_DRP_DB

#--------------
# Select Item
#--------------

# select DFU
df1 <- filter(df1, df1$DFU == "Entity 1_Product A")

# remove the end horizon (where the calculation is not effective)
df1 <- filter(df1, df1$Period <= min(df1$Period) + 120)


#--------------
# Transform
#--------------

# keep only the needed columns
df1 <- df1 |> select(Period,
                      Demand,

                      DRP.Calculated.Coverage.in.Periods,
                      DRP.Projected.Inventories.Qty,
                      DRP.plan
                      )

#--------------
# 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 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"
                  )

                ),
                

              DRP.Calculated.Coverage.in.Periods = colDef(
                name = "Projected 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 Plan (units)",
                cell = data_bars(df1,
                                 fill_color = "#3CB371",
                                 text_position = "outside-end"
                                 )
                )

              ), # close columns lits

              columnGroups = list(
                
   
                colGroup(name = "Projected Inventories & Coverages", 
                         columns = c("DRP.Calculated.Coverage.in.Periods",
                                     "DRP.Projected.Inventories.Qty"
                                     ))

              )

    ) # close reactable

​

Projected Inventories & Coverages

​

Period

Demand (units)

Projected Coverage (Periods)

Projected Inventories (units)

Replenishment Plan (units)

2024-02-18

100

5

500

0

2024-02-25

100

4

400

0

2024-03-03

100

3

300

0

2024-03-10

100

2

200

0

2024-03-17

100

1

100

0

2024-03-24

100

0

-0

0

2024-03-31

100

-1

-100

0

2024-04-07

100

-2

-200

0

2024-04-14

100

5

500

800

2024-04-21

100

4

400

0

2024-04-28

100

3

300

0

2024-05-05

100

5

500

300

2024-05-12

100

4

400

0

2024-05-19

100

3

300

0

2024-05-26

100

5

500

300

2024-06-02

100

4

400

0

2024-06-09

100

3

300

0

2024-06-16

100

5

500

300

1–18 of 18 rows

Show 102550100

b) Local Proj Inv

Display of the local projected inventories calculated through DRP.

# set a working df
df1 <- Calculated_DRP_DB

#--------------
# Select Item
#--------------

# select DFU
df1 <- filter(df1, df1$DFU == "Entity 1_Product A")

# remove the end horizon (where the calculation is not effective)
df1 <- filter(df1, df1$Period <= min(df1$Period) + 120)


#--------------
# Transform
#--------------

# keep only the needed columns
df1 <- df1 |> select(Period,
                      Demand,
                      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 

c) Local + In Transit

Now we’re going to display the Total Projected Inventories for the Entity 1 & Product A :

• local projected inventories (calculated based on the DRP)

• projected in-transit (calculated based on the DRP’s Replenishment Plan)

#------------------
# Get Local Projected Inventories
#------------------

# set a working df
df1 <- Calculated_DRP_DB

# select DFU
df1 <- filter(df1, df1$DFU == "Entity 1_Product A")

# remove the end horizon (where the calculation is not effective)
df1 <- filter(df1, df1$Period <= min(df1$Period) + 120)

# keep only the needed columns
df1 <- df1 |> select(Period,
                      DRP.Projected.Inventories.Qty
                      )

# keep results
Local_PI_DB <- df1




#------------------
# Get Projected in Transit
#------------------

# set a working df
df1 <- New_Calculated_GIT_DB

# select DFU
df1 <- filter(df1, df1$DFU == "Entity 1_Product A")

# remove the end horizon (where the calculation is not effective)
df1 <- filter(df1, df1$Period <= min(df1$Period) + 120)

# keep only the needed columns
df1 <- df1 |> select(Period,
                     Proj.GIT)

# keep results
GIT_PI_DB <- df1




#------------------
# Combine
#------------------

# merge
df1 <- left_join(Local_PI_DB, GIT_PI_DB)

# calculate ratio In Transit vs Total
df1$ratio <- df1$Proj.GIT / (df1$DRP.Projected.Inventories.Qty + df1$Proj.GIT)


#------------------
# Chart
#------------------


u <- highchart() |> 
      hc_title(text = "Projected Local & In Transit Inventories") |>
      hc_subtitle(text = "in units") |> 
      hc_add_theme(hc_theme_google()) |>
      
      hc_xAxis(categories = df1$Period) |> 
      
      hc_add_series(name = "Local", 
                    color = "steelblue",
                    dataLabels = list(align = "center", enabled = TRUE),
                    data = df1$DRP.Projected.Inventories.Qty) |> 
      
      hc_add_series(name = "In Transit", 
                    color = "gold",
                    dataLabels = list(align = "center", enabled = TRUE),
                    data = df1$Proj.GIT) |> 
      
      hc_chart(type = "column") |> 
      
      hc_plotOptions(series = list(stacking = "normal"))

# display chart    
u 

Due to the long transit time (8weeks), we can notice the importance of the in transit inventories within the total inventories hold by the entity.

It’s about 60% of the total weekly inventories of this entity, on this product.

This value could change, depending on the DRP parameters, especially :

• (local) safety stocks level : SSCov

• frequency of supply : DRPCovDur