CODEWITHSUNDEEP
rdplyrstatistics
user3639100
user3639100

Reputation: 346

Rolling stock distribution in R

I've got a data frame containing stock on hand by month, number of arriving goods and expected consumption.

I need to calculate the number i need to buy each month to maintain my stock above certain safety stock each month.

For example: I have 3 units on stock, 4 arriving the first month, expected consumption of 0. therefore i don't need to buy any units this month and i'm left with 3+4-0=7.

That means next month I'll start with 7, say consumption will be 7 and arriving 1 unit. therefore I'll fall short on 3 below my safety stock of 4 so I need to program a purchase of 3 units that month.

Month 3 I start with my 4 units of the safety stock, 1 unit arriving, consumption 5 so I'm at 0, so I need to program to purchase 4 to replenish my safety stock.

As an example i need to replicate the following where "to purchase" is what i need to calculate

df <- data.frame(type = c("a","a","a","a","a"),
date = as.Date(c("2020-01-01", "2020-02-01", "2020-03-01", "2020-04-01", "2020-05-01")),
stock= c(3,0,0,0,0),
arriving = c(4,1,1,3,2),
consumption = c(0,7,5,5,3),
safety_stock = c(4,4,4,4,4),
to_purchase= c(0,3,4,2,1))

Upvotes: 1

Views: 83

Answers (1)

LC-datascientist
LC-datascientist

Reputation: 2096

Assuming these stocks are non-perishable, then the initial stock for each month should equal to the amount you have left from the previous month.

Your to_purchase equation for each month should be
to_purchase = safety_stock - (stock - consumption + arriving),
which is basically demand minus available supply.

library(tidyverse)

df <- data.frame(
  type = c("a","a","a","a","a"),
  date = as.Date(c("2020-01-01", "2020-02-01", "2020-03-01", "2020-04-01", "2020-05-01")),
  stock = c(3,7,4,4,4),
  arriving = c(4,1,1,3,2),
  consumption = c(0,7,5,5,3),
  safety_stock = c(4,4,4,4,4)
) %>% 
  mutate(
    to_purchase = safety_stock - (stock - consumption + arriving), # purchase equation
    to_purchase = ifelse(to_purchase < 0, 0, to_purchase) # if negative, change value to zero
  )

However, a problem with this is that, if you are trying to calculate how much you need to buy (to_purchase), you will likely also need to calculate how much you have left over from the previous month (stock). Thus, your code should include calculations for the initial stock of each month, as well.

df <- data.frame(
  type = c("a","a","a","a","a"),
  date = as.Date(c("2020-01-01", "2020-02-01", "2020-03-01", "2020-04-01", "2020-05-01")),
  stock = c(3,NA,NA,NA,NA),
  arriving = c(4,1,1,3,2),
  consumption = c(0,7,5,5,3),
  safety_stock = c(4,4,4,4,4),
  to_purchase = NA
) 

for(i in 2:nrow(df)){
  # determine if you need to purchase anything from the previous month
  h = i-1
  df$to_purchase[h] = df$safety_stock[h] - (df$stock[h] - df$consumption[h] + df$arriving[h])
  df$to_purchase[h] = ifelse(df$to_purchase[h] < 0, 0, df$to_purchase[h])
  # determine initial stock of the current month
  df$stock[i] = df$stock[h] + df$arriving[h] - df$consumption[h] + df$to_purchase[h]
  # determine if you need to purchase anything for the current month
  df$to_purchase[i] = df$safety_stock[i] - (df$stock[i] - df$consumption[i] + df$arriving[i])
  df$to_purchase[i] = ifelse(df$to_purchase[i] < 0, 0, df$to_purchase[i])
}

df
#>   type       date stock arriving consumption safety_stock to_purchase
#> 1    a 2020-01-01     3        4           0            4           0
#> 2    a 2020-02-01     7        1           7            4           3
#> 3    a 2020-03-01     4        1           5            4           4
#> 4    a 2020-04-01     4        3           5            4           2
#> 5    a 2020-05-01     4        2           3            4           1

Upvotes: 1

Related Questions