# Let's talk sales forecasts - integrating a time series model with subjective “predictions/ leads” from sales team

I've learned a lot about time series forecasting this previous year, but one thing that's still a bit lacking in terms of a formal system is integrating a future sales projection into an existing time series model.

I'm hoping to put this out as a general question to all models, but also reference my specific model to help nail down questions.

Say I'm using a Triple exponential smoothing model with a dampened trend to forecast sales--- so there's a level, trend, and seasonal component, as well as dampening factor.

An exploration of the data shows that the best-fit trend, supposedly, is one with a .97 dampening factor, and virtually 0 beta --- meaning that the trend (growth) is essentially not affected by recent data at all -- is in effect constant, and gradually shrinking. Our business growth is essentially slowing over the years --- but I'm not sure if a 0.0 beta trend is reasonable for a model --- a sudden up-shot would affect the level, but not the trend whatsoever.

Anyway back to the question at hand --- so for simplicity sake, this "master sales forecast" is basically an aggregate forecast consisting of, say, 30 clients. Not too uncommon.

I've considered doing a forecast for individual clients as well, but we only need to predict the >total sales< and nothing else. Individual forecasts may aid in that end, but in reality we have 500+ clients making up 10% of our business, and ensuring accurate, sane exponential smoothing models even for our top 20 clients is -- well a bit dubious. Though I'm open to the idea.

Here's where my confusion comes in .... sales has prediction for growth for EXISTING clients as well as NEW clients. Say, they predict current client abc will have 30k in growth for June 2015. And there will be new client xyz with 50k in revenue for Aug 2015.

There are a few major problems with integrating this into a time series model.

1. Of course, sales predictions might simply be wrong or biased. In my case, they are usually too high. Of course, I can correct this by multiplying them by 0.9 or 0.7 or what have you.

2. It's difficult to ascertain WHEN a new client will come on. They say June, that might mean August, or September ... or who knows? It might mean never. How do I account for this in the time series model? If they say a \$30k/ month client will come on in June 2015, with a 'maybe' probability, do I put a probability % that this is true for each month multiplied by that new revenue and bias factor? Say, 10% this happens in June, 20% in July, 30% in August ... 50% by december? The probability would be increasing because June requires a June or before sign-on, whereas december has a larger success window, Dec or before sign-on.

3. The third problem is integrating this "expected current client growth" and "Expected new client growth" with the Triple Exponential Smoothing model .... isn't this model ALREADY boosting the level and trends based on sales growth? Like ... simply slapping the "expected growth" on top of the ETS model can be "double-counting" it.

Like say I have my ETS with a September forecast, plus this sales "add-on" value. Come July, we have a HUGE sales spike with expected clients, the ETS model adapts upward, now the 'add-on' for September is --- well the growth is being counted twice.

See what I mean? Is the answer somehow with decomposing the model more?

I'm just wondering how this all is accomplished.

Surely my problem --- predicting sales numbers .... is not unique in the slightest.

Your idea of multiplying by .7 or .9 is in fact what is happening down below in my answer of using a regression type model.

You can take the historical forecasts and the future forecasts from the top 30 sales clients and use it to run a regression model. Now, don't think you can just do it in Excel and then you have an answer. You need to consider some other things.

The approach that you are using now is assuming a model form. Box-Jenkins methodology tries to identify the model. During this process, there are warts like outliers and changes in level/trend/seasonality/parameters/variance that might need to be addressed. Sound complicated? Yes, it is and just imagine trying to do that with a regression model. The thing is that when people move from using one time series to regression they like to pretend that those warts don't exist and that they are Alice in Wonderland. Don't forget the possible need for ARIMA model in this too. the ultimate goal is to have random errors proving that your model has captured the signal/model behavior.

While you may have learned a lot in forecasting, it sounds like you are an SAP customer that is being forced to play with parameters.

If the estimates are always high then the regression coefficient might be .7 meaning that the forecasts are high by a factor of(1/.7)1.42 and the model and forecast will adjust them downwards automatically. If the relationship has changed over time then the thing I mentioned above about changes in parameters using the Chow test will come in handy so that it deletes the older data under the old regime and use only the new regime (ie better estimate from 30 clients).

• I think I need to clarify a few things. Firstly, any future data from sales has never been integrated into the Time Series forecast to date. It's only recently been provided for the year, and my judgement tells me that their predictions are optimistically biased. I'm unsure how to integrate it. Secondly -- the current Time Series actually has virtually no outliers or periods of inaccuracy whatsoever (though I did fit the model to most the past data). The data is essentially a logarithmic graph in its shape ... explosive growth, slower growth, slower growth, flat line. – John Babson Feb 27 '15 at 18:38
• The time series model captures this trend which has been happening for 4-5 years. However, quantitative models like this fail to capture MAJOR model chances in the future ... which Sales is telling me "some big changes, aka a Giant Whale customer is coming" ---- historical data in the time series alone can simply not capture this. I'm wondering how to integrate this into time series, or if that's even a wise decision at all. ARIMA is even more complicated, so I'd prefer not to touch that, unless it's significantly easier to add exogenous variables like this. – John Babson Feb 27 '15 at 18:40
• My initial thought --- assuming a constant seasonality is the case (which it pretty much is) ---- is to use the sales data, or work with sales ... to make modifications to future TRENDS figures. Aka the way to forecast h periods out in a time series is current level + h*trend * seasonality period. Instead, I would do current level + H (sales growth for period) * seasonality period. I hate overcomplicating things, though. – John Babson Feb 27 '15 at 18:43
• John, The model you are playing with is a particular form of an ARIMA model so you are actually half way there. The reason I brought up ARIMA is because you often need to have that as part of the regression equation to account for lag relationships. I am a big fan of bottom up forecasting and doing those 500 forecasts. When you say creating exponential smoothing models for those 500 is something you don't want to do....that is the problem. Exponential smoothing model is a particular model. You want to allow the data to "speak"...quote from George Box said. Post your data to dropbox.com – Tom Reilly Feb 27 '15 at 18:57
• John, You first need a good baseline. If you don't have historical predictions to build a regression model then you might as well make hit the forecasts by a % like you want to do, but that first you need a good baseline forecast. – Tom Reilly Feb 27 '15 at 19:06

"Our business growth is essentially slowing over the years --- but I'm not sure if a 0.0 beta trend is reasonable for a model --- a sudden up-shot would affect the level, but not the trend whatsoever."

"Like say I have my ETS with a September forecast, plus this sales "add-on" value. Come July, we have a HUGE sales spike with expected clients, the ETS model adapts upward, now the 'add-on' for September is --- well the growth is being counted twice."

To the above, I would respond using a quote from the book "Principles of Forecasting" by Armstrong (which I highly recommend) -

A trend is trend is a trend
But the question is, will it bend?
Will it alter its course
Through some unforeseen force
And come to a premature end
- Cairncross(1969)


Armstrong follows:

    Will the trend bend ? some statisticians believe
that the data can reveal this. In my judgement,
this can best be answered by domain knowledge.
Experts often have good knowledge of the series and what causes it to vary.


Trend forecasting is one of the hardest thing to accomplish in forecasting real world problems. I would take Armstrong's advice and use your expert judgment in forecasting trend instead of relying on extrapolation methods like ETS/dampened trend.

If I interpret it correctly, you have following questions:

What would be sales forecast when you get new clients ? what is "expected current client growth" and "Expected new client growth" ?

• This is not a statistical question, only market intelligence/field
sales/primary market research can address this question. You are bound to get large forecast errors if you use your intuition. Use a well structured judgmental forecasting to forecast these type of problems.

Once you have the above question answered - second question is how to incorporate this into time series model ?

• step 1 is to create a baseline model using statistical and/or judgmental methods. Extrapolation methodslike exponential smoothing can be one of the techniques. Once you have created a baseline model, you could use different scenarios from the above question and add-on to your baseline and communicate to decision makers the best and worst case scenarios using your judgement. Get organizational buy in in advance to reduce bias later on. This is called scenario forecasting. You basically add your judgmental probabilities/forecast to baseline model and communicate scenarios.

The third problem is integrating this "expected current client growth" and "Expected new client growth" with the Triple Exponential Smoothing model .... isn't this model ALREADY boosting the level and trends based on sales growth? Like ... simply slapping the "expected growth" on top of the ETS model can be "double-counting" it.

• No, ETS just extrapolates past trend, seasonal and other patterns. ETSdoes NOT know what you are going to get incrementally with adding new clients. If you have evidence that ETS already captures new client sales then I would say ETS is providing you overly optimistic baseline forecasts and you should probably dampen it or use another method.
• You also have existing data an 30 clients, why don't you use them as your analogue and forecast sales of new clients. This is called forecasting by analogy. This takes the guess work out of your forecasts.

Should I need to decompose the problem?

• Of course break down(decompose) complex problem and solve it at the lowest granular level and add them finally. Complexity is reduced when you break down the problem and solve it individually. Here is another nice article on forecasting by decomposition.
• Thanks, makes sense but what I'm saying is this. You have a ETS model that says 100 units will sell in Oct, Nov, and Dec. Then you have your sales domain knowledge that 50 additional units will sell Oct, Nov, Dec. Right? So that's 150 per month. Now, October passes and you were indeed on the money, 150 units sold. Now your ETS model, which alters the forecast based on the 150 unit historical data, says Nov and Dec, based on the Oct 150, are now projected to sell 130 each. If you don't readjust the sales forecast, which is +50, it will NOW say 180 are projected for Nov and Dec. – John Babson Feb 27 '15 at 22:53
• See what I mean? Even if you were right on the money, ETS doesn't really isolate the trend for you to add later. It captures and integrates all historical data. I guess one way around this is, preserve a "baseline" historical which is essentially the "actual" historical minus what you think or maybe actually was the sales domain adjustment --- is that right? – John Babson Feb 27 '15 at 22:56
• Yes, preserve baseline until you enough historical data to incorporate new customer sales in ETS model. – forecaster Feb 27 '15 at 23:20