Calculate Reordering Point in R

Max Molina 0
Viewed 40

I am trying to create an algorithm that can help me calculate reording point (ROP) based on:

  • Ordering Dates (OD)
  • Delivery Dates (DD)
  • Store Forecast (Fcst)

I have been trying for weeks but cannot find the way to do this efficiently because I will be running this simulations for 1 million item-store combinations.

The logic is simple:

We have to sum the forecast from the nearest delivery date upto the following delivery date. (Starting from the ordering dates)

Nearest delivery date will never be the same ordering date

The example:

library(data.table)
set.seed(1)

sim_days   <- 30
start_Date <- as.Date("2022-01-01")
seq_days   <- seq.Date(start_Date,start_Date + 30, by="days")

OD <- c(0,0,1,0,0,1,0,0,1,1,0,0,1,0,1,0,1,0,0,1,0,0,0,1,0,0,1,0,0,0,1)
DD <- c(0,0,0,0,1,0,1,0,0,1,1,0,0,1,0,1,0,0,1,0,1,0,0,0,0,1,0,1,0,0,0)
Forecast <- c(100,230,300,417,170,414,430,331,320,121,172,161,340,234,369,274,351,447,233,372,427,174,328,143,193,235,104,233,404,219,268)
Expected_Result <- c("","",1014,"","",1202,"","",293,907,"","",877,"","","",1032,"","",1500,"","","",572,"","",1124,"","","","")

OD <- rbind(as.character(seq_days),as.numeric(OD)); colnames(OD) <- as.character(seq_days); OD <- OD[-1,]
DD <- rbind(as.character(seq_days),as.numeric(DD)); colnames(DD) <- as.character(seq_days); DD <- DD[-1,]
rbind(OD,DD,Forecast,Expected_Result)[]
data.table r
1 Answers

Here is one approach, which starts by putting your data into a data.table:

df = data.table(
  date=seq_days, OD = OD, DD=DD,Forecast = Forecast
)

Now, we first add the forecast sum for each delivery date, which includes the sum of Forecast from that date to the next delivery date:

df[,ind:=cumsum(DD)]
df[,fsum:=sum(Forecast),ind]
df[,fsum:=fifelse(ind!=shift(ind,-1),fsum+shift(Forecast,-1),fsum)]
df[,fsum:=max(fsum,na.rm=T),ind]

Then, create a vector of ordering dates

od = df[OD==1,date]

Finally, for each of the ordering date rows, take the forecast sum from df where DD==1 and OD!=1 and the date exceeds this ordering date

df[OD==1, result:=lapply(od, \(o) df[DD==1 & date>o,fsum[1]])][,`:=`(fsum=NULL,ind=NULL)]

Output:

          date OD DD Forecast result
 1: 2022-01-01  0  0      100       
 2: 2022-01-02  0  0      230       
 3: 2022-01-03  1  0      300   1014
 4: 2022-01-04  0  0      417       
 5: 2022-01-05  0  1      170       
 6: 2022-01-06  1  0      414   1202
 7: 2022-01-07  0  1      430       
 8: 2022-01-08  0  0      331       
 9: 2022-01-09  1  0      320    293
10: 2022-01-10  1  1      121    907
11: 2022-01-11  0  1      172       
12: 2022-01-12  0  0      161       
13: 2022-01-13  1  0      340    877
14: 2022-01-14  0  1      234       
15: 2022-01-15  1  0      369   1305
16: 2022-01-16  0  1      274       
17: 2022-01-17  1  0      351   1032
18: 2022-01-18  0  0      447       
19: 2022-01-19  0  1      233       
20: 2022-01-20  1  0      372   1500
21: 2022-01-21  0  1      427       
22: 2022-01-22  0  0      174       
23: 2022-01-23  0  0      328       
24: 2022-01-24  1  0      143    572
25: 2022-01-25  0  0      193       
26: 2022-01-26  0  1      235       
27: 2022-01-27  1  0      104   1124
28: 2022-01-28  0  1      233       
29: 2022-01-29  0  0      404       
30: 2022-01-30  0  0      219       
31: 2022-01-31  1  0      268     NA
langtang0
Related