Excel Forecast Formula Returns Negative Values

[gimmic]

Hi everyone,
I have no idea about the usage of forecast formula. Tried to solve it from internet but i lost myself in heavy statistical information. I will be appreciated if anyone can help me on this.

In attached file i have actual data for first 9 days of June.(C3:J3)
For the remaining days of june i need forecasted quantities. In my formula all forecasted values are negetive. How should i write the formula?

 

[Chihiro]

You don't have enough data points to do proper forecasting.

And there's way too much variance in data to produce meaningful forecast using other methods in this case.

In general, you'd want 2 full set of pattern for seasonality + 1/2 of season.
Ex: If monthly data with 12 month seasonal cycle, you'd want about 30 months worth of data. This can change based on seasonality length.

In general, more historical data points you have, the better. However, if there has been some business rule / assumption change, that could impact accuracy of forecast.

If you set seasonality manually, depending on the data set, you can get away with little less data. But if that's going to produce dependable forecast or not will be up for debate.

Have a look at below link.
https://chandoo.org/forum/threads/forecast-formula-in-excel-2007.35393/#post-211880

 

[Chihiro]

Here's sample forecast model using 7 day (i.e. weekly) seasonality, with 95% confidence level for upper and lower bound.
Data is desensitized, but uses actual business data.

Data in B106 to 131. Was added after the fact to validate forecast model.
Variance between forecast and actual came within 5% for the forecast period. Which was adequate for this forecast's purpose.

Though you can build more accurate model by using different algorithm that takes into account spike for end and beginning of the month, along with statutory holidays... ROI wasn't great for amount of time that's required build such a model.

 

[gimmic]

Here's sample forecast model using 7 day (i.e. weekly) seasonality, with 95% confidence level for upper and lower bound.
Data is desensitized, but uses actual business data.

Data in B106 to 131. Was added after the fact to validate forecast model.
Variance between forecast and actual came within 5% for the forecast period. Which was adequate for this forecast's purpose.

Though you can build more accurate model by using different algorithm that takes into account spike for end and beginning of the month, along with statutory holidays... ROI wasn't great for amount of time that's required build such a model.

thank you very much for your response. I will check again and try. Hope if i can fix it.