# Get Started

    library(tidyverse)
    library(dplyr)
    library(lubridate)
    library(tidyverse)
    library(shiny)

    # for the tables
    library(reactable)
    library(reactablefmtr)

    # for the charts
    library(highcharter)

    # the library planr
    library(planr)

Let’s present the 3 functions:

- light_proj_inv() : to calculate projected inventories & coverages

- proj_inv() : to calculate & analyze projected inventories vs min & max targets

- drp() : to calculate a replenishment plan

## Part 1 : Projected Inventories & Coverages Calculation

### 1.1) Data Template

    Period <- c(
    "1/1/2020", "2/1/2020", "3/1/2020", "4/1/2020", "5/1/2020", "6/1/2020", "7/1/2020", "8/1/2020", "9/1/2020", "10/1/2020", "11/1/2020", "12/1/2020","1/1/2021", "2/1/2021", "3/1/2021", "4/1/2021", "5/1/2021", "6/1/2021", "7/1/2021", "8/1/2021", "9/1/2021", "10/1/2021", "11/1/2021", "12/1/2021")

    Demand <- c(360, 458,300,264,140,233,229,208,260,336,295,226,336,434,276,240,116,209,205,183,235,312,270,201)

    Opening <- c(1310,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)

    Supply <- c(0,0,0,0,0,2500,0,0,0,0,0,0,0,0,0,2000,0,0,0,0,0,0,0,0)

    # assemble
    my_demand_and_suppply <- data.frame(Period,
                      Demand,
                      Opening,
                      Supply)

    # let's add a Product
    my_demand_and_suppply$DFU <- "Product A"

    # format the Period as a date
    my_demand_and_suppply$Period <- as.Date(as.character(my_demand_and_suppply$Period), format = '%m/%d/%Y')

    # let's have a look at it
    head(my_demand_and_suppply)
    #>       Period Demand Opening Supply       DFU
    #> 1 2020-01-01    360    1310      0 Product A
    #> 2 2020-02-01    458       0      0 Product A
    #> 3 2020-03-01    300       0      0 Product A
    #> 4 2020-04-01    264       0      0 Product A
    #> 5 2020-05-01    140       0      0 Product A
    #> 6 2020-06-01    233       0   2500 Product A

It contains some basic features:

- 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 : for example monthly or weekly buckets

- a Demand : could be some sales forecasts, expressed in units

- an Opening Inventory : what we hold as available inventories at the beginning of the horizon, expressed in units

- a Supply Plan : the supplies that we plan to receive, expressed in units

### 1.2) Calculation

Let’s apply the light_proj_inv().

We are going to calculate 2 new features for each DFU:

- projected inventories

- projected coverages, based on the Demand Forecasts

    # calculate
    calculated_projection <- light_proj_inv(dataset = my_demand_and_suppply,
                                            DFU = DFU,
                                            Period = Period,
                                            Demand =  Demand,
                                            Opening = Opening,
                                            Supply = Supply)
    #> Joining with `by = join_by(DFU, Period)`

    # see results
    head(calculated_projection)
    #>         DFU     Period Demand Opening Calculated.Coverage.in.Periods
    #> 1 Product A 2020-01-01    360    1310                            2.7
    #> 2 Product A 2020-02-01    458       0                            1.7
    #> 3 Product A 2020-03-01    300       0                            0.7
    #> 4 Product A 2020-04-01    264       0                            0.0
    #> 5 Product A 2020-05-01    140       0                            0.0
    #> 6 Product A 2020-06-01    233       0                            7.4
    #>   Projected.Inventories.Qty Supply
    #> 1                       950      0
    #> 2                       492      0
    #> 3                       192      0
    #> 4                       -72      0
    #> 5                      -212      0
    #> 6                      2055   2500

### 1.3) A nicer display

We will use the libraries reactable and reactablefmtr to create a nice table.

    # set a working df
    df1 <- calculated_projection

    # keep only the needed columns
    df1 <- df1 %>% select(Period,
                          Demand,
                          Calculated.Coverage.in.Periods,
                          Projected.Inventories.Qty,
                          Supply)

    # create a f_colorpal field
    df1 <- df1 %>% mutate(f_colorpal = case_when( Calculated.Coverage.in.Periods > 6 ~ "#FFA500",
                                                  Calculated.Coverage.in.Periods > 2 ~ "#32CD32",
                                                  Calculated.Coverage.in.Periods > 0 ~ "#FFFF99",
                                                  TRUE ~ "#FF0000" ))

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

                    ),

                  Calculated.Coverage.in.Periods = colDef(
                    name = "Coverage (Periods)",
                    maxWidth = 90,
                    cell= color_tiles(df1, color_ref = "f_colorpal")
                  ),

                  f_colorpal = colDef(show = FALSE), # hidden, just used for the coverages

                    `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"
                        )
                      }
                    ),

                  Supply = colDef(
                    name = "Supply (units)",
                    cell = data_bars(df1,
                                     fill_color = "#3CB371",
                                     text_position = "outside-end"
                                     )
                    )

                  ), # close columns lits

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

                  )

        ) # close reactable

### 1.4) A little chart

    # set a working df
    df1 <- calculated_projection

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

    # create a value.index
    df1$Value.Index <- if_else(df1$Projected.Inventories.Qty < 0, "Shortage", "Stock")

    # spread
    df1 <- df1 %>% spread(Value.Index, 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

## Part 2 : Calculation & Analysis

Now, let’s consider some parameters such as : - a target of minimum stock level - a target of maximum stock level

And then: - calculate the projected inventories and coverages - analyze those values vs those defined targets

First, let’s add some parameters to our initial database.

### 2.1) Data Template

Define min & max coverages, through 2 parameters: - Min.Cov - Max.Cov

Expressed in number of periods of coverages. The periods can be in monthly buckets, weekly buckets, etc…

    my_data_with_parameters <- my_demand_and_suppply

    my_data_with_parameters$Min.Cov <- 2
    my_data_with_parameters$Max.Cov <- 4

    head(my_data_with_parameters)
    #>       Period Demand Opening Supply       DFU Min.Cov Max.Cov
    #> 1 2020-01-01    360    1310      0 Product A       2       4
    #> 2 2020-02-01    458       0      0 Product A       2       4
    #> 3 2020-03-01    300       0      0 Product A       2       4
    #> 4 2020-04-01    264       0      0 Product A       2       4
    #> 5 2020-05-01    140       0      0 Product A       2       4
    #> 6 2020-06-01    233       0   2500 Product A       2       4

### 2.2) Calculation

Let’s apply the proj_inv() function

    df1 <- proj_inv(data = my_data_with_parameters,
                    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)`

    # see results
    calculated_projection_and_analysis <- df1

    head(calculated_projection_and_analysis)
    #>         DFU     Period Demand Opening Calculated.Coverage.in.Periods
    #> 1 Product A 2020-01-01    360    1310                            2.7
    #> 2 Product A 2020-02-01    458       0                            1.7
    #> 3 Product A 2020-03-01    300       0                            0.7
    #> 4 Product A 2020-04-01    264       0                            0.0
    #> 5 Product A 2020-05-01    140       0                            0.0
    #> 6 Product A 2020-06-01    233       0                            7.4
    #>   Projected.Inventories.Qty Supply Min.Cov Max.Cov Safety.Stocks Maximum.Stocks
    #> 1                       950      0       2       4           758           1162
    #> 2                       492      0       2       4           564            937
    #> 3                       192      0       2       4           404            866
    #> 4                       -72      0       2       4           373            810
    #> 5                      -212      0       2       4           462            930
    #> 6                      2055   2500       2       4           437           1033
    #>    PI.Index Ratio.PI.vs.min Ratio.PI.vs.Max SSOS_qty
    #> 1        OK            1.25            0.82        0
    #> 2     Alert            0.87            0.53        0
    #> 3     Alert            0.48            0.22        0
    #> 4  Shortage           -0.19           -0.09        0
    #> 5  Shortage           -0.46           -0.23        0
    #> 6 OverStock            4.70            1.99     1022

### 2.3) A nicer display

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%"
      ))
    }

And now let’s create a reactable:

    # set a working df
    df1 <- calculated_projection_and_analysis

    # remove not needed column
    df1 <- df1[ , -which(names(df1) %in% c("DFU"))]

    # create a f_colorpal field
    df1 <- df1 %>% mutate(f_colorpal = case_when( Calculated.Coverage.in.Periods > 6 ~ "#FFA500",
                                                  Calculated.Coverage.in.Periods > 2 ~ "#32CD32",
                                                  Calculated.Coverage.in.Periods > 0 ~ "#FFFF99",
                                                  TRUE ~ "#FF0000" ))

    #-------------------------
    # Create Table

    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,
                                       #round_edges = TRUE
                                       #value <- format(value, big.mark = ","),
                                       #number_fmt = big.mark = ",",
                                       fill_color = "#3fc1c9",
                                       #fill_opacity = 0.8,
                                       text_position = "outside-end"
                      )

                    ),

                    Calculated.Coverage.in.Periods = colDef(
                      name = "Coverage (Periods)",
                      maxWidth = 90,

                      cell= color_tiles(df1, color_ref = "f_colorpal")
                    ),

                    f_colorpal = colDef(show = FALSE), # hidden, just used for the coverages

                    `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"
                        )
                      }
                    ),

                    Supply = colDef(
                      name = "Supply (units)",
                      cell = data_bars(df1,

                                       #round_edges = TRUE
                                       #value <- format(value, big.mark = ","),
                                       #number_fmt = big.mark = ",",
                                       fill_color = "#3CB371",
                                       #fill_opacity = 0.8,
                                       text_position = "outside-end"
                      )
                      #format = colFormat(separators = TRUE, digits=0)
                      #number_fmt = big.mark = ","
                    ),

                    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%)"
                        )
                        PI.Index <- status_PI.Index(color = color)
                        tagList(PI.Index, value)
                      }),

                    `Safety.Stocks`= colDef(
                      name = "Safety Stocks (units)",
                      format = colFormat(separators = TRUE, digits=0)
                    ),

                    `Maximum.Stocks`= colDef(
                      name = "Maximum Stocks (units)",
                      format = colFormat(separators = TRUE, digits=0)
                    ),

                    `Opening`= colDef(
                      name = "Opening Inventories (units)",
                      format = colFormat(separators = TRUE, digits=0)
                    ),

                    `Min.Cov`= colDef(name = "Min Stocks Coverage (Periods)"),

                    `Max.Cov`= colDef(name = "Maximum Stocks Coverage (Periods)"),

                    # ratios
                    `Ratio.PI.vs.min`= colDef(name = "Ratio PI vs min"),

                    `Ratio.PI.vs.Max`= colDef(name = "Ratio PI vs Max")

                  ), # close columns lits

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

                    colGroup(name = "Stocks Levels Parameters", columns = c("Min.Cov",
                                                                            "Max.Cov",
                                                                            "Safety.Stocks",
                                                                            "Maximum.Stocks")),

                    colGroup(name = "Analysis Features", columns = c("PI.Index",
                                                                            "Ratio.PI.vs.min",
                                                                            "Ratio.PI.vs.Max"))

                  )

        ) # close reactable

Compared to the previous table, we have here some additional information available: the calculated fields [Analysis Features] - based on safety & maximum stocks targets - useful for a mass analysis (Cockpit / Supply Risks Alarm), but perhaps too detailed for a focus on a SKU

We also can notice that the minimum and maximum stocks coverages, initially expressed in Periods (of coverage) are converted in units. It’s quite useful to chart the projected inventories vs those 2 thresholds for example.

### 2.4) A little chart

    # set a working df
    df1 <- calculated_projection_and_analysis

    # Chart
    p <- highchart() %>%
          hc_add_series(name = "Max", color = "crimson", data = df1$Maximum.Stocks) %>%
          hc_add_series(name = "min", color = "lightblue", data = df1$Safety.Stocks) %>%
          hc_add_series(name = "Projected Inventories", color = "gold", data = df1$Projected.Inventories.Qty) %>%

          hc_title(text = "Projected Inventories") %>%
          hc_subtitle(text = "in units") %>%
          hc_xAxis(categories = df1$Period) %>%
          #hc_yAxis(title = list(text = "Sales (units)")) %>%
          hc_add_theme(hc_theme_google())

        p

We can visualize the periods when we are in Alert & OverStock, comparing to the minimum and Maximum stocks levels.

## Part 3) Replenishment Plan

### 3.1) Data Template

Let’s now add a few parameters to the initial database “my_demand_and_suppply”

    df1 <- my_demand_and_suppply

    df1$SSCov <- 2
    df1$DRPCovDur <- 3
    df1$MOQ <- 1
    df1$FH <- c("Frozen", "Frozen", "Frozen", "Frozen","Frozen","Frozen","Free","Free","Free","Free","Free","Free","Free","Free","Free","Free","Free","Free","Free","Free","Free","Free","Free","Free")

    # get Results
    my_drp_template <- df1

    head(my_drp_template)
    #>       Period Demand Opening Supply       DFU SSCov DRPCovDur MOQ     FH
    #> 1 2020-01-01    360    1310      0 Product A     2         3   1 Frozen
    #> 2 2020-02-01    458       0      0 Product A     2         3   1 Frozen
    #> 3 2020-03-01    300       0      0 Product A     2         3   1 Frozen
    #> 4 2020-04-01    264       0      0 Product A     2         3   1 Frozen
    #> 5 2020-05-01    140       0      0 Product A     2         3   1 Frozen
    #> 6 2020-06-01    233       0   2500 Product A     2         3   1 Frozen

### 3.2) Calculation

Apply drp()

    # set a working df
    df1 <- my_drp_template

    # calculate drp
    demo_drp <- drp(data = df1,
               DFU = DFU,
               Period = Period,
               Demand =  Demand,
               Opening = Opening,
               Supply = Supply,
               SSCov = SSCov,
               DRPCovDur = DRPCovDur,
               MOQ = MOQ,
               FH = FH
    )
    #> Joining with `by = join_by(DFU, Period)`
    #> Joining with `by = join_by(DFU, Period)`
    #> Joining with `by = join_by(DFU, Period)`

    glimpse(demo_drp)
    #> Rows: 24
    #> Columns: 15
    #> $ DFU                                <chr> "Product A", "Product A", "Product …
    #> $ Period                             <date> 2020-01-01, 2020-02-01, 2020-03-01…
    #> $ Demand                             <dbl> 360, 458, 300, 264, 140, 233, 229, …
    #> $ Opening                            <dbl> 1310, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
    #> $ Supply                             <dbl> 0, 0, 0, 0, 0, 2500, 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> 758, 564, 404, 373, 462, 437, 468, …
    #> $ Maximum.Stocks                     <dbl> 1395, 1166, 1074, 1070, 1266, 1328,…
    #> $ DRP.Calculated.Coverage.in.Periods <dbl> 2.7, 1.7, 0.7, 0.0, 0.0, 7.4, 6.4, …
    #> $ DRP.Projected.Inventories.Qty      <dbl> 950, 492, 192, -72, -212, 2055, 182…
    #> $ DRP.plan                           <dbl> 0, 0, 0, 0, 0, 2500, 0, 0, 0, 0, 0,…

### 3.3) A nicer display

    # set a working df
    df1 <- demo_drp

    # keep only the needed columns
    df1 <- df1 %>% select(Period,
                          Demand,
                          DRP.Calculated.Coverage.in.Periods,
                          DRP.Projected.Inventories.Qty,
                          DRP.plan)

    # replace missing values by zero
    df1$DRP.plan[is.na(df1$DRP.plan)] <- 0
    df1$DRP.Projected.Inventories.Qty[is.na(df1$DRP.Projected.Inventories.Qty)] <- 0

    # 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 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 = "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 (units)",
                    cell = data_bars(df1,
                                     fill_color = "#3CB371",
                                     text_position = "outside-end"
                                     )
                    )

                  ), # close columns lits

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

                  )

        ) # close reactable

### 3.4) A little chart

    # set a working df
    df1 <- demo_drp

    # Chart
    p <- highchart() %>%
          hc_add_series(name = "Max", color = "crimson", data = df1$Maximum.Stocks) %>%
          hc_add_series(name = "min", color = "lightblue", data = df1$Safety.Stocks) %>%
          hc_add_series(name = "Projected Inventories", color = "gold", data = df1$DRP.Projected.Inventories.Qty) %>%

          hc_title(text = "(DRP) Projected Inventories") %>%
          hc_subtitle(text = "in units") %>%
          hc_xAxis(categories = df1$Period) %>%
          #hc_yAxis(title = list(text = "Sales (units)")) %>%
          hc_add_theme(hc_theme_google())

        p