Using R in Inventory Management and Demand Forecasting

2022-10-20

by Nico Nguyen

Nicolas Nguyen works in the Supply Chain industry, in the area of Demand and Supply Planning, S&OP and Analytics, where he enjoys developing solutions using R and Shiny. Outside his job, he teaches data visualization in R at the Engineering School EIGSI and Business School Excelia in the city of La Rochelle, France.

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:

Using R in Inventory Management and Demand Forecasting: an introduction of projected inventory and coverage methodology (this post)
Analyzing Projected Inventory Calculations Using R: an analysis of a demo dataset using the planr package
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:

an APS (Advanced Planning System) software
an ERP, such as SAP or JDE
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

How to calculate projected inventories

First, let’s have a look at an example of how to calculate projected inventories. Consider that the field Demand = Sales Forecasts.

We start with some Opening Inventory of 1000 units.
During month M, we sell 100 units (the Demand). At the end of the 1st period (Month M), the inventory is 900 units.
Then, there’s a demand of 800 units at the end of the following period (Month M+1).
During the period (Month M+2), we get a Supply of 400 units, and sell 100: it is now 1100 units in stock.

That’s all, this is how we calculate projected inventories ☺

Figure 1: Describes the mechanism of the calculation of projected inventories based on Opening Inventories, Demand and Supply

How to calculate projected coverages

Now, let’s have a look at how to calculate projected coverages. The idea: we look forward.

We consider the projected inventories at the end of a period and evaluate the related coverage based on the Upcoming Demand. See the example below:

Figure 2: Description of the calculation of projected coverages, considering the inventories at a point in time and the Upcoming Demand

If we use Excel, we often see a “shortcut” to estimate the related coverages, like considering an average of the Demand over the next 3 or 6 months. This can lead to incorrect results if the Demand is not constant (if we have some seasonality or a strong trend, for example). However, these calculations become very easy through the proj_inv() and light_proj_inv() functions.

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.

ASCM (formerly APICS) guidelines

In the beginning of 2019, the Association for Supply Chain Management (ASCM) published an article about the usage of R (and Python) in Supply Chain Planning, and more precisely for the Sales & Operations Planning (S&OP) process, which is related to Demand and Supply Planning.

Figure 3: An extract from the ASCM article regarding the S&OP and Digital Supply Chain. It shows how R and Python are becoming more and more used for demand & supply planning and are great tools to run a S&OP process.

In the example above, we can see how R helps build the digital environment useful to run the S&OP process, which involves a lot of data processing. The planr package aims to support this process by providing functions that calculate projected inventories.

Stay tuned for more on projected calculations using R

Thank you for reading the introduction on how to use R for projected inventory calculations in Demand & Supply Planning! I hope you enjoyed this introduction to the planr package.

My series of posts will continue with:

Analyzing Projected Calculations Using R (using a demo dataset)
Visualizing Projected Calculations with reactable and Shiny (or, what your boss wants to see)

In the meantime, check out these useful links:

planr package GitHub repository: https://github.com/nguyennico/planr
URL for a Shiny app showing a demo of the proj_inv() and light_proj_inv() functions: Demo: app proj_inv() function (shinyapps.io)

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.

Third-party distributors

We sometimes work with third-party distributors to distribute our products. A common question is: how much stock do our partners hold?

If we have access to their opening inventories and Sales IN & OUT Forecasts, we can quickly calculate the projected inventories by applying the proj_inv() or light_proj_inv() functions. Then, we anticipate any risks of shortages or overstocks, and create a collaborative workflow.

Figure 4: Illustration of a SiSo (Sales IN Sales OUT) situation. We have some stocks held at a storage location, for example, a third-party distributor, and know what will be sold out of this location (the sales out) and what will be replenished to it (the sales in), as well as the opening inventory. The aim is to calculate the projected inventories and coverages at this location.

From raw materials to finished goods

In the example below, we produce olive oil (but it could be shampoo, liquor, etc.).

We start with a raw material, the olive oil, that we use to fill up different sizes of bottles (35cl, 50cl, etc…), on which we then apply (stick) a label (and back label). There are different labels, depending on the languages (markets where the products are sold).

Once we have a labelled bottle, we put it inside an outer box, ready to be shipped and sold. There are different dimensions of outer boxes, where we can put, for examplem 4, 6 or 12 bottles. They are the Finished Goods.

We have two groups of products here:

Finished Goods
Semi-Finished: at different steps, filled bottle (not yet labelled) or labelled bottle

We might be interested in looking at the projected inventories at different levels / steps of the manufacturing process:

Finished Goods
Raw Materials: naked bottles, labels, or liquid (olive oil)
Semi-Finished Products

For this, we can apply the proj_inv() or light_proj_inv() functions on each level of analysis.

Figure 5: Illustration of a production flow, starting from raw material (liquid of olive oil) which fills bottles (of different countenances) and then labelled with different stickers. The labelled bottles are placed inside different outer boxes, which are the finished goods (called SKU -Stock Keeping Unit - ) .

Multi-echelon networks

In the example below, we are looking at the distribution network within a country.

The products are stored at different locations:

At a Central Stock: managed by the company selling the products, for example, a National Distribution Center.
At some third parties:
- Who buy those products to distribute and sell them in more specific areas or regions
- A network of Wholesalers and Sub-Wholesalers

If we have access to the data of the 3rd parties, such as [Opening Inventories] and [Sales IN & OUT Forecasts], we could apply the proj_inv() or light_proj_inv() functions on each level and visualize the complete projected inventories within the Distribution Network.

Figure 6: Illustration of a distribution network, within a country, with different levels. The first level (level 1) is the main storage location, from which the products are shipped to the wholesalers (level 2), which then supply their local partners, identified here as Sub-Wholesalers (level 3). The aim is to visualize (and manage) the inventories (also Demand & Supply) throughout the whole network.

From production capacity to sales

In the example below, we have one or several Factories (within the box “Production Capacities”) which supply a [Global Stocks], which is then used to supply markets and a [Regional Stocks] directly. The [Regional Stocks] is then supplying other markets and a Third Party Distributor.

We want to manage the Monthly S&OP process:

Linking Sales & Manufacturing Operations through simple modeling.
To balance Demand & Supply over the medium-long term horizon.
Visualizing some impacts through the whole distribution network

And make some simulations and drive some decisions:

Change of Sales Plan
Change of Supply Plan
Change of Inventories Level
Change of Transit Lead Time
Change of Product Mix
Etc.

We could apply the proj_inv() or light_proj_inv() functions on each level and visualize the complete projected inventories within the Distribution Network.

Figure 7: Illustration of a distribution network with a wider geographical presence. The first level (level 1) is the global storage location; it receives products from some factories and holds stocks that are used to supply several markets in the world, and also a regional stock, which is level 2. Then the regional stock is used to supply other markets, usually closer geographically, which is level 3. As described in the picture, the markets can be affiliates (where the stocks belong to the company) and third-party distributors.