Forecasting

[austenr]

Not sure if anyone can help with the attached spreadsheet. Was given this call data and need to forecast the predictive calls by brand for the next 4 months. I tried using the FORECAST formula but couldn’t get it to work properly.

If anyone has any insight into this could you either edit this WB or give a source for more information. Thanks.

[SamT]

Since tha data for the next 10 months is already in your system, you don't need to forecast, you can already see the answer.

[Paul_Hossler]

Confused

The data goes from 1/1/2017 to 12/31/2018 and the 'Calls' and the 'Brand' are random numbers

Any forecast on this data would seem to be just as random

[austenr]

Confused

The data goes from 1/1/2017 to 12/31/2018 and the 'Calls' and the 'Brand' are random numbers

Any forecast on this data would seem to be just as random

Sorry guys just posted the wrong WB.


New one w/o formulas.

[Paul_Hossler]

My statistics is a little rusty, but it seems like the Poisson distribution is what you want

The mean is based on the last 12 months of data, but you could 'look back' a different period to estimate on the (say) last 4 months of data

[p45cal]

I think the data in your latest file are a little too random/desensitised; take the data for April and May 2017 for brand 1 as an example:
Capture3.PNG
There are only 2 data for May, and 7 for April. What do these data in the column Cals represent? Cals on that date? Cals including days from the previous date?
In April there are 16k cals, in May only 4.5k.

I suspect we'll need more realistic data.

Meanwhile, you might get a reasonable visual, this is a plot using a pivot table where the data are grouped by month and year, here we're only showing brand 4, and I've added a dotted blue linear trendline, forecast 4 months at the right. This is chart built-in stuff:
Capture3.jpg

[Paul_Hossler]

Sometimes I have TOO much time on my hands

I didn't understand the Calls and Brands concept exactly, so as a first shot I assumed that there were varying number of 5 types of brand Calls each day -- really just my guess


This is 1) just a guess and 2) only a first step


I think the Poisson is the way to go, but your random data was just linearly random, so I dug out an old function that generates random data based on the Poisson distribution (given a mean) and used that to generate some test data (GREEN)

The worksheet function POISSON.DIST() generates the probability of at being between 0 and N inclusive (ORANGE) and the probability of at exactly N (YELLOW)

So I'm figuring if you had real data in the GREEN instead of my random data, you could tell the probability of getting X calls

Capture.jpg

Like I said -- my statistics is very rusty, so this may be off base

[SamT]

You can't forecast a market based on one years data, you have to compare monthly sales across many years. All markets are both cyclical and seasonal. Then throw in the Oil cost effect, the Trump effect, the La Nina/El Nino effect, etc ad nauseum.

After feeding in all of one years data and all the Etc effects, my Ouija Board says "Add 3.428941% to the previous year's months, turn it in and worry about what your plans are for the weekend." That is called WAG.

Another alternative is to look at the extended weather forecast, compare the time in question to the annual averages, and use that as your Market Forecast. Be sure and use the Kelvin scale when calculating % of temperature variances. That is called a SWAG.

Finally, just call your stock broker and ask him what she thinks the market will do. That's called RESEARCH.