I am trying to replace an obsolete Excel report currently used for sales forecasting and inventory projections by our supply chain team and I am using R for this. The desired output is a data frame with one of the columns being the projected closing inventory positions for each week across a span of N weeks.

The part I am struggling with is the recursive calculation for the closing inventory positions. Below is a subset of the data frame with dummy data where "stock_projection" is the desire result.

I've just started learning about recursion in R so I am not really sure on how to implement this here. Any help will be much appreciated!

Update

I have managed to modify the solution explained here and have replicated the above outcome:

inventory<- tibble(week = 1, opening_stock = 100)
forecast<-  tibble(week = 2:10, forecast = c(11, 12, 10, 11, 10, 12, 11, 9, 12) )

dat <- full_join(inventory, forecast)

dat2 <- dat %>%
  mutate(forecast = -forecast) %>%
  gather(transaction, value, -week) %>%
  arrange(week) %>%
  mutate(value = replace_na(value, 0))


dat2 %>%
  mutate(value = cumsum(value)) %>%
  ungroup() %>%
  group_by(week) %>%
  summarise(stock_projection = last(value))

Despite working like a charm, I am wondering whether there is another way to achieve this?

CodePudding user response：

I think in the question above, you don't have to worry too much about recursion because the stock projection looks just like the opening stock minus the cumulative sum of the forecast. You could do that with:

library(dplyr)
dat <- tibble(
  week = 1:10, 
  forecast = c(10,11,12,10,11,10,12,11,9,12), 
  opening_stock = c(100, rep(NA, 9))
)

dat <- dat %>% 
  mutate(fcst = case_when(week == 1 ~ 0, 
                          TRUE ~ forecast), 
    stock_projection = case_when(
    week == 1 ~ opening_stock, 
    TRUE ~ opening_stock[1] - cumsum(fcst))) %>% 
  dplyr::select(-fcst)

dat
# # A tibble: 10 × 4
#    week forecast opening_stock stock_projection
#   <int>    <dbl>         <dbl>            <dbl>
# 1     1       10           100              100
# 2     2       11            NA               89
# 3     3       12            NA               77
# 4     4       10            NA               67
# 5     5       11            NA               56
# 6     6       10            NA               46
# 7     7       12            NA               34
# 8     8       11            NA               23
# 9     9        9            NA               14
# 10    10       12            NA                2