Forecasting

Hi

i am using the FORECAST.ETS (target_date,value,timeline) but i get all weird number for my forecast.

this forecast it's for a call center and i entered the historical data for June 27th 2016 and i would like to know the forecast for June 28th. But i keep getting results in the minus, CAn somebody help me please?

I don't have Excel 2016, and I haven't really played with FORECAST before, but I think one of your issues might be related to the inclusion of the date. If you're really just looking for peaks and troughs throughout the 24-hr day, it might work better if you severed the time from the date. I used the following in H5 to identify the time:

=TIME(HOUR(A5),MINUTE(A5),SECOND(A5))

Then the following in I29 to identify the FORECAST at the start of the 28th (then fill down).

=FORECAST($H29,$I$5:$I28,$H$5:$H28)

I don't know if Excel 2016 offers a better version of FORECAST, or even if I'm using this version optimally, but it appears to capture a peak and drop right before lunch. With inconsistent data, and 'best fit' prediction formula is unlikely to be very reliable, but it should at least help to separate the time from the date so that Excel views a cycle of times rather than an ever-increasing time/date value.

Hi,

your way seems to be good just a quick question, do you know is the calculation is done? Does it take an average of the data? For example on I14 the data says 21 but the forecast on I38 says 6.5. So should not the forecast be higher? Or should i add more data?

The helpfile on FORECAST is worth a read - it suggests that the function calculates a best fit linear regression curve, so the calculation is average based, which would drag the peaks nearer to the mean. More data would definitely help you get more accurate predictions, I would think.

Hi malidina,

Welcome to science and art of forecasting. Unfortunately, I too am not familiar with FORCAST.ETS. Must be new in 2016 since I do not see it in 2013. What you need to know about forecasting is what I tell all my college students, different methods will produce different results (the science of it) and then it comes down to how you interpret the results (the art of it).

FORECAST uses regression analysis to forecast to future by (non-technical) drawing a line in between all the past data points in question, projects the line into the future, and then future data points are plotted off the regression line based on the distances of the past data points off of the regression line. I typically see the forecast as flatter to much flatter the farther into the future one forecasts.

In the workbook I have sent back to you “Calls Forecast” sheet shows chart “Calls by Day with Trend Line” using regression analysis called a trend line with a formula for the slope of the line, y=. The important part of the knowing the formula is what comes after y=, + or -. + or no sign indicates a positive trend (going up), - sign indicated a negative trend (going down). Is it good or bad, it depends. If you are a sales department taking calls for orders, a positive trend is good; however, if you are a complaints department, a positive trend is not so good. It means more people are calling to complain about the products.

The second item to explain is the R squared. It measures the goodness of the fit of the line in the regression analysis. In most instances it is measured on a 0-1 scale, 0 being the worst fit and 1 being the best fit (all data in a straight line). With the current data points (I made up) in question, the fit is .0003. That is by all accounts not good, but it really does not matter since we are not forecasting based on regression. To be complete, I always provide it to my clients and teach it to my students. Again, the real takeaway is the slope of the trend line (+/-).

I prefer to forecast being able to see my data by using a table and build my formula. I am sure some who read this will label me as old school, but one just cannot beat seeing your data and building a mathematical model (and yes I have done both and still prefer building the model). The forecasting I subscribe to is “forecasting with seasonality” if you want to google it. I like it because it is a better predictor of the highs and the lows. Look at chart “Time Comparison Daily Calls Forecast”, the forecast much more closely overlays to the four prior days being used to predict the forecast. Sometimes it is just nice to “see” if a forecast makes sense.

To use this sheet as a tool for you, all you need to do is:

1 Enter the dates in question in to cells B4 oldest, C4, D4, and E4 newest ( you want the trend correct).

2 Enter the number of calls into the corresponding column below each date.

Touch nothing else on the sheet because everything else is linked. The forecast and the charts will both update.

Other issues I foresee:

If you are trying to forecast person hours needed based on call volume because the operation is open 7 day a week, heaver call volume on Saturday/Sunday will skew the Monday/Tuesday forecast higher, resulting in you adding staff while lower call volume on Thursday/Friday may skew the forecast lower for the weekend when you would really need more staff to handle the greater call volume.

Should you notice a pattern such as this develop, I would suggest running this forecasting model using a Saturday, Sunday, Saturday, Sunday to forecast Saturday and then continue to update each new weekend day and move on from there. In essence you are now running two forecasting models one using only weekdays and the other using only weekends. This may prove to be more accurate if your goal is to minimize payroll. Really you will just have to try it out and see.

I hope this works for you.

Hi,

I have tried to forecast by block of hours (AM, PM, EVE) but still numbers seems to be low. AS im forecasting for a call center and year of year business has been growing which means more calls, so how can we have a lower forecast of calls than the source? can you help me on this please

Hello Again,

I have reviewed your workbook and your forecast seems low because, as I have stated earlier, FORECAST uses regression analysis. I added a chart with trend line and plotted 0:00 to 5:00, the same first AM group you used to forecast. The purpose of the chart and trend line is to visually show why your data looks off. The trend line forecasted the same number of calls as the forecast function you used, even the -1 call. It is because a trend line also uses regression analysis.

I think one fundamental concept of forecasting I am uncertain if you understand is forecasting happens by studying the same time over periods of time, not the studying a unit of time to itself. For example, we can forecast next Mondays sales by looking as the last four Mondays sales.

So if send me a new workbook with 5 days of call counts, I will be more than happy to setup the forecast.

Hi malidina,

I sent you a message a week ago. Did you receive it?

Hi, maybe i missed your message and i am sorry about that. The last i message i got from you was on June 30th when you asked me to send you data for 5 days?