Formula Help

[austenr]

I usually try to do this kind of thing in Tableau or Power BI but am forced to try to do it in Excel 2013.

Trying to do a forecast for calls for month of March. Using the following formula produces results that I think are unrealistic:

In E59

=forecast(A59,$C$2:$C$58,$A$2:$A$58)
Returns 32.67881
Copying that formula down the result varies from 32 to 33 with fractions.
Was expecting some more variances based on the data.

Anyone have any ideas?

Workbook attached.

[Paul_Hossler]

FWIW, your data is pretty erratic and seems to vary by DOW

21838

You could

1. Use a 30 a moving average (see attachment)

or

2. Do 7 different forecasts (one for each DOW) separating inputs by day of week

[SamT]

Day of Week affects calls.

Month of year also affects calls, as do holidays and general public attitudes. For example all calls and sales are up this year compared to last march due to the so called 'Trump Effect' on Society's attitude.

[Paul_Hossler]

I separated the input data by DOW, and just the input DOW data to FORECAST the 'output'

21841

Still don't know how statistically valid any of this is

[austenr]

Thanks guys. Yea the data is totally bogus just generated some random value between 1 and whatever for something to play with. What they want is looking at how calls are trending compared in a bar chart with a poly trend.

This would be so much easier to do with excel 2016. All or a lot of this is built in.