How to forecast based on aggregated data over irregular intervals?

I am trying to forecast the sales of products in vending machine. The problem is that the machine is filled at irregular intervals and at every fill we only can record the aggregated sales since the last fill of the machine (i.e. we don't have daily sales data). So basically we have data for aggregated sales at irregular intervals. The intervals usually are between 2 days and 3 weeks. Here is example data for one vending machine and one product:

27/02/2012 48
17/02/2012 24
09/02/2012 16
02/02/2012 7
25/01/2012 12
16/01/2012 16
05/01/2012 16
23/12/2011 4
16/12/2011 14
09/12/2011 4
02/12/2011 2

Our current naive algorithm is to calculate average sales per day by dividing the total quantity sold during the last 90 days by 90.

Do you have any idea how to improve the forecast of the sales per day? I need to forecast what will be sold at the next visit of the machine. Is it possible to use some sort of exponential smoothing algorithm given the nature of our data?

UPDATE: Thanks a lot for all the answers and comments. Let me try to give a bit more context (the business case behind the question - very simplified of course). We have hundreds of vending machines. Every day we have to decide which 20 of them to visit for refill. In order to do so we are trying to predict what is the current status of the machines and to select the "emptiest" 20 machines. For each machine and product we are calculating the average sales per day (SPD) using the naive algorithm described above. Then we multiply the SPD by the number of days since the last fill of the machine and the result is the predicted quantity sold.

• I think it's an interesting question. My answer to your exact question and dataset is : does it matter? and: get more data. Also, I think it's useful to know what the capacity is for the vending machine. – Adam Mar 2 '12 at 6:10
• @Adam The capacity for this product is 50 bottles. Maybe my description of the problem is not clear enough. I will try to edit it a bit to give more context. Basically I am looking for ideas how to solve the general problem and I am very grateful for all the comments so far. The specific dataset is given just as example what the data looks like. For other vending machines I can provide data for much longer period. – Ivan Dimitrov Mar 2 '12 at 7:49
• @IvanDimitrov: What exactly is column two in your data? – Kyle Brandt Mar 5 '12 at 1:13
• @KyleBrandt Column two is the number of bottles sold since the last visit to the vending machine. So the number 48 on the top row means that 48 bottles were sold between 17/02 and 27/02 – Ivan Dimitrov Mar 6 '12 at 15:56

Let's focus on the business problem, develop a strategy to address it, and begin implementing that strategy in a simple way. Later, it can be improved if the effort warrants it.

The business problem is to maximize profits, of course. That is done here by balancing the costs of refilling machines against the costs of lost sales. In its current formulation, the costs of refilling the machines are fixed: 20 can be refilled each day. The cost of lost sales therefore depends on the frequency with which machines are empty.

A conceptual statistical model for this problem can be obtained by devising some way to estimate the costs for each of the machines, based on previous data. The expected cost of not servicing a machine today approximately equals the chance it has run out times the rate at which it is used. For example, if a machine has a 25% chance of being empty today and on average sells 4 bottles per day, its expected cost equals 25% * 4 = 1 bottle in lost sales. (Translate that into dollars as you will, not forgetting that one lost sale incurs intangible costs: people see an empty machine, they learn not to rely on it, etc. You can even adjust this cost according to a machine's location; having some obscure machines run empty for a while might incur few intangible costs.) It's fair to assume that refilling a machine will immediately reset that expected loss to zero--it should be rare that a machine will get emptied every day (don't you wish...). As time goes by, the expected loss starts to rise until eventually it reaches a limiting value equal to the expected daily sales: an example is shown in the second figure below.

A simple statistical model along these lines proposes that fluctuations in a machine's use appear random. This suggests a Poisson model. Specifically, we may posit that a machine has an underlying daily sales rate of $\theta$ bottles and that the number sold during a period of duration $x$ days has a Poisson distribution with parameter $\theta x$. (Other models can be formulated to handle the possibility of clusters of sales; this one supposes that sales are individual, intermittent, and independent of each other.)

In the present example, the observed durations are $x=(7, 7, 7, 13, 11, 9, 8, 7, 8, 10)$ and the corresponding sales were $y=(4, 14, 4, 16, 16, 12, 7, 16, 24, 48)$. Maximizing the likelihood gives $\hat{\theta} = 1.8506$: this machine has been selling about two bottles per day. The data history is not long enough to suggest that any more complicated model is needed; this is an adequate description of what has been observed so far. The red dots show the sequence of sales; the blue dots are estimates based on the maximum likelihood estimate of the typical sales rate.

Armed with an estimated sales rate, we can go on to compute the chance that a machine may be empty after $t$ days: it is given by the complementary cumulative distribution function (CCDF) of the Poisson distribution, as evaluated at the machine's capacity (presumed to be 50 in the next figure and the examples below). Multiplying by the estimated sales rate gives a plot of the expected daily loss in sales versus time since last refill: Naturally this curve is rising fastest near the time at $50/1.85 = 27$ days when the machine is most likely to run out. What it adds to our understanding is to show that an appreciable rise actually begins a week earlier than that. Other machines with other rates will have steeper or shallower rises: that will be useful information.

Given a chart like this for each machine (of which it seems there are a couple hundred), you can easily identify the 20 machines currently experiencing the greatest expected loss: servicing them is the optimal business decision. (Note that each machine will have its own estimated rate and will be at its own point along its curve, depending on when it was last serviced.) Nobody actually has to look at these charts: identifying the machines to service on this basis is easily automated with a simple program or even with a spreadsheet.

This is just the beginning. Over time, additional data may suggest modifications to this simple model: you might account for weekends and holidays or other anticipated influences on sales; there may be a weekly cycle or other seasonal cycles; there may be long-term trends to include in the forecasts. You might want to track outlying values representing unexpected one-time runs on the machines and incorporate this possibility in the loss estimates, etc. I doubt, though, that it will be necessary to worry much about serial correlation of sales: it's hard to think of any mechanism to cause such a thing.

Oh, yes: how does one obtain the ML estimate? I used a numerical optimizer, but in general you will get very close simply by dividing total sales over a recent period by the length of the period. For these data that's 163 bottles sold from 12/9/2011 through 2/27/2012, a period of 87 days: $\hat{\theta} = 1.87$ bottles per day. Close enough to $1.8506$, and extremely simple to implement, so anyone can start these calculations right away. (R and Excel, among others, will readily compute the Poisson CCDF: model the calculations after

1-POISSON(50, Theta * A2, TRUE)

for Excel (A2 is a cell containing the time since last refill and Theta is the estimated daily sales rate) and

1 - ppois(50, lambda = (x * theta))

for R.)

The fancier models (which incorporate trends, cycles, etc) will need to use Poisson regression for their estimates.

NB For the aficionados: I am purposely avoiding any discussion of uncertainties in the estimated losses. Handling these can significantly complicate the calculations. I suspect that directly using these uncertainties would not add appreciable value to the decision. However, being aware of the uncertainties and their sizes could be useful; that might be depicted by means of error bands in the second figure. In closing, I just want to re-emphasize the nature of that figure: it plots numbers that have a direct and clear business meaning; namely, expected losses; it does not plot more abstract things such as confidence intervals around $\theta$, which may be of interest to the statistician but will just be so much noise to decision maker.

• To me the assumption that sales/events are independent of each other would appear to be illogical as these are measurements taken over time on the same nachine . How would your approach deal with auto-correlated sales data which might have had a change in level ? Just curious ? – IrishStat Mar 6 '12 at 19:45
• I believe I have sketched out a route to assessing and dealing with likely forms of "autocorrelation." They would not be manifested as such in a traditional time series analysis, even if regularly spaced data were available. Temporal correlation would result from correlation in external factors, such as weekly or seasonal cycles. An interesting form would occur from temporal clustering of purchases (e.g., just after lunch or on weekends). "Net changes of level" can be handled by trend fitting, if necessary, but simply using a running mean would deal with that automatically. – whuber Mar 6 '12 at 20:15

I think you will usually have the first step of converting to a regular time series. You said you take the average of 90 days. Since you have data that is more frequent than that, I think it makes more sense to use the most of what you have by taking the days between each observation and dividing it by the number of items sold for that period (assuming that is what your second column is).

As a disclaimer I am a total amateur, so you want to take the advice of an expert like IrishStat over the following code (for example, he said ETS is a bad model, so only treat this as a toy example), but in hopes that this saves you some time, here is some R code you can play with:

library("xts")
library("forecast")

x = read.table(text="27/02/2012 48
17/02/2012 24
09/02/2012 16
02/02/2012 7
25/01/2012 12
16/01/2012 16
05/01/2012 16
23/12/2011 4
16/12/2011 14
09/12/2011 4
02/12/2011 2")

#Convert the data into an XTS object which works with irregular time series
x.xts = xts(x[,2], as.POSIXct(x[,1], format="%d/%m/%Y"))

#Conver to a daily rate by taking the observed data and dividing it by
#the number of days between observations
daily_rate <- lag(x.xts) / diff(index(x.xts))

#Generate a daily time series for the dates
dummy_dates <- seq(from=index(x.xts), to=tail(index(x.xts), 1), by="day")

#Combine daily series with observered daily rate
m.xts <- merge(daily_rate, dummy_dates)

#Interpolate the daily sales -- kind of evil because we "invent" data
m.xts.interpolate <- na.approx(m.xts)

#Convert to regular time series
m.ts <- ts(m.xts.interpolate, freq=365, start=c(2011, 336))
#Clean up dimnames in case of stl forecast (just an R thing when converting from dataframes)
dim(m.ts) <- NULL

#Fit TS to an ETS model (Rudely ignoring IrishStat's advice that it is a bad model, but this is just an example)
fit <- ets(m.ts)

#Forecast and Plot
plot(forecast(fit, h=30))

The resulting plot is: • Did you read my response " We have solved this by converting demand to a rate by dividing the actual demand by the number of days in the interval between servicing" . Rather than assuming a model as you are , I identified a useful model. Your forecasts are explosive as they believe the recent "anomalous rates" rather than adjusting for them. – IrishStat Mar 5 '12 at 2:28
• @IrishStat: Yes, I even have a comment in my code pointing to the fact that you said it was a bad model, I just thought some R code might help people by giving a basis to experiment with. – Kyle Brandt Mar 5 '12 at 2:32

What you have is an "Intermittent Demand Problem". We have solved this by converting demand to a rate by dividing the actual demand by the number of days in the interval between servicing. This rate can then be modelled as a Transfer Function in order to predict a rate given the prediction of the interval. This predicted rate can then be converted to a demand . Care should be taken to detect structural shifts in the rate via Intervention Detection . Try googling "Intermittent Demand modelling approach using a Transfer function methodology" . Sray very clear of the model presumptive approaches of Croston or Exponential Smoothing as they are quite deficient.

When I modelled Rate as a function of Interval I obtained the following. Using a prediction of INTERVAL using it's past this equation can then predict rate , which can the be used to predict demand. This kind of model allows autoregressive structure in rate to be incorporated as well as allowing for Pulses , Level Shifts and/or Local Time Trends in rate.

MODEL COMPONENT       LAG    COEFF     STANDARD      P       T

# (BOP) ERROR VALUE VALUE

Differencing                  1
1CONSTANT                          .295       .840E-01   .0246     3.51

INPUT SERIES X1 INTERVAL

Differencing                  1
2Omega (input) -Factor #  1    0   .685E-01   .346E-01   .1193     1.98

INPUT SERIES X2 I~P00002 12/03/11 PULSE

Differencing                  1
3Omega (input) -Factor #  2    0   1.43       .168       .0010     8.52

INPUT SERIES X3 I~P00007 12/08/11 PULSE

Differencing                  1
4Omega (input) -Factor #  3    0  -.935       .168       .0051    -5.57

INPUT SERIES X4 I~P00010 12/11/11 PULSE

Differencing                  1
5Omega (input) -Factor #  4    0   1.37       .260       .0062     5.27
• I think this slightly misunderstands the question: he does not have intermittent demand he has intermittently observed demand, and he's apparently happy to assume constant underlying demand per day. Indeed the transfer function approach seems broadly to be the one he's trying here, albeit with a linearity assumption, a 90 day window, and no covariates. FWIW regressing demand against the (exogenous) days since last reading gives about 2.2 items demanded per day, but high uncertainty around that estimate. – conjugateprior Mar 1 '12 at 9:40
• @ConjugatePrior He can't observe the actual demand perday and samples at infrequent intervals thus is de facto equivalent to an intermittent demand problem.His "rate" can be characterized as a random walk with 3 exceptions (pulses)at 12/16;2/02;2/27. Our idea here is to model the rate as a function of the interval,then predict the interval andthen predict the demand.You appear to have modelled the demand as a function of the interval obtaining the simple average rate which in this case is severely/adversely effected by the 3 anomolous data points & doesn't reflect the autoregressive structure. – IrishStat Mar 1 '12 at 15:44
• Surely it can't be a random walk because sales can only go up or stay the same (equivalently stocks can only decline or stay at the same level) -- something more like a renewal process. Also I'm not sure how you figured out that exactly 3 points are outliers. (BTW the 'analysis' I suggested is only a generalised version of his own, with an unsustainable linearity assumption, so I personally wouldn't be content with it.) – conjugateprior Mar 1 '12 at 22:22
• @ConjugatePrior The rate as a function of interval showed that the rate was strongly dependent on previous rates and also impacted by three "unusual rates". No level shifts were identified . What I should have said was that the rate was a random walk without a constant thus there is no discernable trend. – IrishStat Mar 2 '12 at 13:53
• @IrishStat Thanks a lot for the answer. I will read about the Intermittent Demand using a Transfer function. One small correction: We don't have to predict the INTERVAL. We have to decide when to visit the machine based on the prediction of the rate. For example one business rule might be "visit the machine when it is 60% empty" – Ivan Dimitrov Mar 6 '12 at 16:30