# Simple method of forecasting number of guests given current and historical data

I am trying to predict the number of guests a restaurant might serve in a meal period based on the volume of business that same day from prior years (3-5 years of data), trends for the same day of the week in the recent past (6-8 weeks), and the daily trend vs last year for the 21 days leading up to the day in question. For example, I would like to predict the number of lunches I might serve on Wednesday, Nov 15, 2017 using data from each of the same Wednesdays from the past 3-5 years, as well as the 6-8 Wednesdays immediately prior to 11/15/17, and the daily trends vs last year for each of the 21 days prior to 11/15/17. Ideally, I would like a to return a result showing a low end, high end, and most likely. I am not a statistician, just a restaurant manager trying to take some of the guesswork out of staffing and ordering. I am with a small company that uses Excel for most spreadsheet applications.

• The forecasting tag might be useful for you to take a look at: stats.stackexchange.com/questions/tagged/forecasting
– mkt
Nov 15, 2017 at 3:41
• Interesting problem (+1). You seem to have a lot of data and a number of possible approaches could be considered in here, but what I understand is that your main concern is that you need some kind of simple model for it, that can be easily applied by hand in Excel. To make it more likely for you to get a applicable answer, maybe you could share with us an example of your data (e.g. made-up example, of a sample of a fraction of your data etc), so we are totally clear what you're talking about. Are there any "cycles" or seasonal effects (a lot of orders in the summer etc.) in your data?
– Tim
Nov 15, 2017 at 9:17
• As for restaurants, in some cases it may make sense to add weather as a predictor. Dec 5, 2017 at 14:46
• The answers have all focused on the forecasting aspect, but there's also a decision science aspect to this: how much does will it cost if the model is under by 50 vs over by 50? How big are the swings in model predictions that would actually affect a management decision? Having answers to these questions in mind will give you a sense of how much optimizing your forecasts is worth. Dec 5, 2017 at 17:08

There are simple methods to use but they are probably profoundly wrong as daily data presents a ton of opportunities . Simply striking daily averages is both simple and useless BUT I guess if there is no analytics around it is probably better than the overall average . The absolute LAST approach would be to use the overall mean . Models need to be as simple as possible BUT never too simple.

As was nicely summarized by @Frans your problem/opportunity is a complicated one but very rewarding. Besides some of the items mentioned there are individual lead and lag effects around each holiday along with possible level shifts and changes in day-of-the-week effects and of course how to identify and treat anomalies. There are also possible week-of-the=month effects and day-of-the-month effects et al. Identifying the structure is the problem and possibly even incorporating pricing and advertising effects.

Take a look at http://autobox.com/cms/index.php/afs-university/intro-to-forecasting/doc_download/53-capabilities-presentation particularly slide 50- . I have been working with fast-food restaurant chains to even push down the forecast to 15 minute intervals and will try and give you some guidance. If you post your data and specify the country and the start date , I will try and help further. Preferably you might post 3 years of daily data as there are probably seasonal and holiday effects that might need to be identified.

In terms of being able to quickly come up with forecasts this is handled by storing and updating models and then quickly forecasting using models that have been archived. Prediction intervals should be approached via monte-carlo to provide robust estimates for the range of future values.

You have a complicated problem AND there are a lot of bad simple solutions that may be insufficient but inexpensive. if this is important then perhaps you need to muscle-up to a workable & affordable solution.

EDIT UPON RECEIPT OF DATA:

After receipt of your data I arbitrarily took the LUNCH series (you had provided both LUNCH and DINNER data )and inserted 0.0's for some missing days obtaining 1454 daily values .. start date 1/1/2013 ending 12/24/2016 and introduced the data to AUTOBOX requesting a 14 day forecast.

Here is the 14 day (arbitrarily chosen) forecast . The acf of the original data showed significant memory structure which is of course presuming no special causes while the acf of the final model's residuals showed no remaining stochastic structure in the residuals . Since the sample size is large we get "false conclusions" using the very approximate standard deviation of the acf (1/sqrt(# of observations). The plot of the final model's residuals supported the randomness conclusion or at least the suggestion that the model couldn't be rejected

How to evaluate deterministic vs stochastic components of a time series? discusses the advantages of integrating both stochastic (arima/memory) structure and event /fixed effects found via search procedures culminating in a holistic model.

Restaurant activity is a classic example of how we do things in predictable rhythms. Arrivals to a restaurant follow day-of-the-week patterns and monthly patterns albeit being very affected by holidays and other special events. To summarize the model contains 6 types of factors/features separating the observed series to signal(predictable) and noise(random) .These 6 features are 1) Baseline ; 2) day-of-the week ; 3) month-of-the-year ; 4) pre, contemporary and post holiday effects ; 5) Deterministic effects discovered via Intervention Detection ; 6) memory (previous values).

The Final model's statistics are here with Actual/Fit and Forecast here

Detailing the 6 features . First the baseline ..essentially an expectation before identified effects are introduced .

now the day-of-the week

now the month=of-the-year

now the holidays

now the identified exogenous deterministic/unattributed effects (partial list)

and finally the effect of prior observations i.e. memory reflecting unspecified variables omitted from the model . This is the conditional effect of memory GIVEN the deterministic (assignable cause) structure

The window of response around each holiday is presented using the backshift operator B ; https://en.wikipedia.org/wiki/Lag_operator

• This link has 4 years of data from 2013-2016. 1drv.ms/x/s!An6hzJ1mua8qogC39ObGOM3YUqP_ Nov 18, 2017 at 22:29
• That is a gorgeous answer. Nicely done! Aug 15, 2019 at 12:27
• tu very much. I have often wondered why the OP never responded , upvoted or accepted what I thought was a very complete and thorough response. Aug 15, 2019 at 12:30

This is not a trivial task as there are many ways to approach this problem, as well as things to take into consideration. Instead of proposing a model, I will give you some general advice.

What you are describing is a time-series and predicting the number of guests in the future is a forecasting problem. A time-series can be modeled for example using a mixed model, modeling the days as random effects because they occur multiple times in the dataset.

The first things you may want to consider are:

• Did the average number of guests remain fairly constant over the past years or has there been an increase/decrease? This determines whether the time-series is stationary or not.
• Are there any special days to take into consideration, such as national holidays? In a regression model, these could be included as dummy variables.
• What other factors might affect the number of guests on a given day? Surely the daily or weekly menu may have an effect, or perhaps there are a different number of guests depending on the season.

Some other things that might help once you want to decide on a model:

• Excel is very limited when it comes to statistical analysis. Consider using R or Python, both of which are free programs. In R, there is a package forecast with a plethora of useful models exactly for the purpose of forecasting time-series.

• If you are going with a regression model, consider that the number of guests are count data. Independent counts are Poisson distributed, but since there will be returning guests, recommendations from other guests, changes in the daily menu and many other (possibly unknown) factors affecting the number of guests, you may want to consider a distribution that can model these extra sources of variance (overdispersion) by using e.g. a negative binomial distribution.

• You mentioned you want to report an expected number of guest with a lower and upper bound. Prediction intervals can give you this lower and upper bound for a given amount of uncertainty. The expected value depends on the distribution you intend to use.

I imagine the restaurant won't wait for you to complete this analysis, so I should also note that for starters simply going with the mean number of guests might work reasonably well, especially considering the time investment to calculate it. You could even do this in Excel.

Lastly, search for questions related to yours. There are many good questions about time-series on this site.

• This answer is misleading as it mixes different types of analyses. You keep mentioning time series forecasting but what you're describing is all about non-time series techniques. With a specialised time series approach (e.g. the ARMA family of models), information such as national holidays is endogenous not exogenous and should therefore not be included in the model as external regressors. Stationarity is in fact required by statistical forecasting techniques, but what you're describing is not the right way to detect it. Dec 4, 2017 at 10:34
• A mixed model with a properly specified covariance structure is as much a specialized approach as an autoregressive moving average. As for your second point, I did not describe a method to detect stationarity, just the what might cause deviation from it. The question was very broad, so I answered to the best of my knowledge (mainly linear regression) what this user might read up on. If your knowledge of ARMA is more extensive and you can summarize it for a beginner, then I think it would be better to write an answer. I can't tell what you want me to change about the answer from your comment. Dec 4, 2017 at 11:29
• Frans, I'm not attacking you, nor am I questioning your understanding of the things you're describing, I just believe that an answer should be clear to any reader and not just the OP. Obviously, getting technical about statistical forecasting of any type of school would not be helpful to the OP in this case, but if an answer stays high level it should at least be coherent. Your answer is mixing up two different paths of analysis as well as technical with non-technical jargon, this can be confusing for a novice on the subject. Dec 4, 2017 at 12:01
• @Digio iN what sense are holidays endogenous. They are not to be predicted but rather are fixed, The response to them is part of the prediction for Y the endogenous series. All dummy indicators be they day-of-the=week, day-of-the-month, month=of-the-year et all are deterministic exogenous series . Oct 9, 2019 at 20:31