Using R in Inventory Management and Demand Forecasting

Introduction

Demand & Supply Planning requires forecasting techniques to determine the inventory needed to fulfill future orders. With R, we can build end-to-end supply chain monitoring processes to identify potential issues and run scenario testing.

In a 3-part series, I will walk through a Demand & Supply Planning workflow:

1. Using R in Inventory Management and Demand Forecasting: an introduction of projected inventory and coverage methodology (this post)
2. Analyzing Projected Inventory Calculations Using R: an analysis of a demo dataset using the planr package
3. Visualizing Projected Calculations with reactable and shiny: once the analysis is done, how would you present your results to your boss?

By the end of the series, you will understand how and why to use R for Demand & Supply Planning calculations. Let’s begin!

The “problem” we aim to solve

When we work in Demand & Supply Planning, it’s pretty common that we need to calculate projected inventories (and related projected coverages). We often have three options to perform this calculation, using:

1. an APS (Advanced Planning System) software
2. an ERP, such as SAP or JDE
3. and of course…Excel!

All are fine and have different pros and cons. For example, we simply sometimes don’t have all the data in our ERP either APS, like when we work with third-party distributors or we want to model a supply chain network that relies on different systems with unconnected data.

How about using R to perform these calculations? How simple and fast could they be? And, could we do more than just the calculations?

For example, could we get an analysis of the projected situation of a portfolio (as an output of a function), so we don’t have to look at each product one by one and can instead:

• Easily get a summary view of the portfolio?
• Then zoom on the products with risks of shortages or overstocks?

In a series of posts, I will demonstrate how R can help us in Demand & Supply planning. This first post introduces the proj_inv() and light_proj_inv() functions for projected inventory and coverage calculations.

• proj_inv(): to calculate projected inventories and coverages with some analysis features
• light_proj_inv(): to calculate projected inventories and coverages (only)
  • Runs faster than the previous function (as it’s lighter and doesn’t provide any analysis features)

With R, we have an efficient way to run end-to-end supply chain monitoring processes.

Methodology

Projected inventory calculations in R

Now, let’s see how the above is done using two functions from the planr package. First, let’s create a tibble of data for the example shown above (we will cover Min.Stocks.Coverage and Max.Stocks.Coverage more thoroughly in another post):

# Install the planr package
# remotes::install_github("nguyennico/planr")

library(planr)
library(dplyr)

Planr_Example <-
  tibble::tribble(
          ~DFU,      ~Period, ~Demand, ~Opening.Inventories, ~Supply.Plan, ~Min.Stocks.Coverage, ~Max.Stocks.Coverage,
    "Item0001", "2022-01-01",    100L,                1000L,           0L,                   0L,                   0L,
    "Item0001", "2022-02-01",    100L,                   0L,           0L,                   0L,                   0L,
    "Item0001", "2022-03-01",    100L,                   0L,         400L,                   0L,                   0L,
    "Item0001", "2022-04-01",    800L,                   0L,           0L,                   0L,                   0L,
    "Item0001", "2022-05-01",    100L,                   0L,           0L,                   0L,                   0L,
    "Item0001", "2022-06-01",    300L,                   0L,           0L,                   0L,                   0L,
    "Item0001", "2022-07-01",    100L,                   0L,         400L,                   0L,                   0L
    )

Now apply the proj_inv() function:

Calculated_Inv <- proj_inv(
  data = Planr_Example,
  DFU = DFU,
  Period = Period,
  Demand = Demand,
  Opening.Inventories = Opening.Inventories,
  Supply.Plan = Supply.Plan,
  Min.Stocks.Coverage = Min.Stocks.Coverage,
  Max.Stocks.Coverage = Max.Stocks.Coverage
)

We can look at the output, which provides a lot of useful information including projected inventories and coverages:

glimpse(Calculated_Inv)
## Rows: 7
## Columns: 14
## Groups: DFU [1]
## $ DFU                            <chr> "Item0001", "Item0001", "Item0001", "It…
## $ Period                         <chr> "2022-01-01", "2022-02-01", "2022-03-01…
## $ Demand                         <dbl> 100, 100, 100, 800, 100, 300, 100
## $ Opening.Inventories            <int> 1000, 0, 0, 0, 0, 0, 0
## $ Calculated.Coverage.in.Periods <dbl> 2.9, 1.9, 2.7, 1.7, 0.7, 0.0, 99.0
## $ Projected.Inventories.Qty      <dbl> 900, 800, 1100, 300, 200, -100, 200
## $ Supply.Plan                    <int> 0, 0, 400, 0, 0, 0, 400
## $ Min.Stocks.Coverage            <int> 0, 0, 0, 0, 0, 0, 0
## $ Max.Stocks.Coverage            <int> 0, 0, 0, 0, 0, 0, 0
## $ Safety.Stocks                  <dbl> 0, 0, 0, 0, 0, 0, NA
## $ Maximum.Stocks                 <dbl> 0, 0, 0, 0, 0, 0, NA
## $ PI.Index                       <chr> "OverStock", "OverStock", "OverStock", …
## $ Ratio.PI.vs.min                <dbl> 0, 0, 0, 0, 0, 0, NA
## $ Ratio.PI.vs.Max                <dbl> 0, 0, 0, 0, 0, 0, NA

Focusing on the Projected.Inventories.Qty column, we see that it matches our example calculation in Figure 1.

Calculated_Inv %>% 
  select(Projected.Inventories.Qty)
## # A tibble: 7 × 2
## # Groups:   DFU [1]
##   DFU      Projected.Inventories.Qty
##   <chr>                        <dbl>
## 1 Item0001                       900
## 2 Item0001                       800
## 3 Item0001                      1100
## 4 Item0001                       300
## 5 Item0001                       200
## 6 Item0001                      -100
## 7 Item0001                       200

We can also take a look at projected coverage. It matches our example calculation in Figure 2: the opening coverage is 2.9 months.

Calculated_Inv %>% 
  select(Calculated.Coverage.in.Periods)
## # A tibble: 7 × 2
## # Groups:   DFU [1]
##   DFU      Calculated.Coverage.in.Periods
##   <chr>                             <dbl>
## 1 Item0001                            2.9
## 2 Item0001                            1.9
## 3 Item0001                            2.7
## 4 Item0001                            1.7
## 5 Item0001                            0.7
## 6 Item0001                            0  
## 7 Item0001                           99

Very easy to calculate!

The proj_inv() and light_proj_inv() functions can also be used and combined to perform more complex tasks. I’ve described several use cases in the appendix.

Moving forward, these functions form the basis for the classic DRP (Distribution Requirement Planning) calculation, where, based on some parameters (usually minimum and maximum levels of stock, a reorder quantity, and a frozen horizon), we calculate a Replenishment Plan.

Conclusion

Thank you for reading this introduction of projected inventories and coverages in Demand & Supply Planning! I hope that you enjoyed reading how this methodology translate into R.

Appendix: Use cases and examples

The proj_inv() and light_proj_inv() functions can easily be used and combined to perform more complex tasks, such as:

• Modeling of a Supply Chain Network
• Calculation of projected inventories from Raw Materials to Finished Goods
• A multi-echelon distribution network: from a National Distribution Center to Regional Wholesalers to Retailers

Becoming a useful tool:

• To build an End-to-End Supply Chain monitoring process
• To support the S&OP (Sales and Operations Planning) process, allowing us to run some scenarios quickly:
  • Change of Sales plan
  • Change of Supply (Production) plan
  • Change of stock level parameters
  • Change of Transit Time
  • Etc.

Here are some detailed use cases for the functions.