• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Forecast formula in excel 2007

Sandip Roy

New Member
Dear Friends,

I have two files, one that I got from microsoft's portal & downloaded, other one is my own workbook (demo) where am working.

Issue is the downloaded file is carrying "Forecasting chart" along with its sheet which is generated using excel 2016.

What I want that I need the same formulas which are used in C61, D61, & E61 cells of 'Forecast.ets sample' sheet in "Forecast_ets_example" workbook to be used in my workbook. I tried but failed, as am not aware how to use those formulas.

Please note that I used normal Forecast formula in my workbook, but result is not satisfactory to me, instead I think "Forecast_ets_example" workbook's formulas are much more accurate.

It's unfortunate that I don't hv excel 2016.

Thanks in advance.

Regards
Sanndip
 

Attachments

  • Forecast_ets_example.xlsx
    41.4 KB · Views: 21
  • demo.xlsx
    15.5 KB · Views: 15
You really don't have enough data point to do proper forecasting.
With just 4 data points for each month... no forecasting method will give you seasonal pattern or accurate forecast...

To find more details about ETS (Exponential Triple Smoothing)...
Read links below.

http://www.itl.nist.gov/div898/handbook/pmc/section4/pmc431.htm
http://www.real-statistics.com/time...-time-series-forecasting/holt-winters-method/

However... in order to ensure accurate forecast model is used for your report. You will need to test multiple forecasting models and evaluate it's accuracy before accepting one model over another as valid for your business application/model.
 
Yes, you are right from amount of data point of view, actually I hv 15 months data, but here I put as demo to know how the formula can be adopted in my workbook.
But if you see the downloaded file, there you will see that if I click any cell carrying the formula, & push enter button wirhout pushing esc button, immeditaely it is showing error. Is it due to I am using excel 2007.
However, if possible, pls help me to modify my woekbook.
am studying your links.
Thnx for tour reply.
Regards
Sanndip
 
...actually I hv 15 months data,...

But, if the seasonality is in 12 month cycle... you'll need more than that.

See what happens to forecast when there's only 21 months of data.
upload_2017-8-10_8-22-46.png

In order for ETS method to detect 12 months pattern, you need 2 full set + 1/2 set (i.e. 30 months) at minimum.

upload_2017-8-10_8-31-35.png
 
Ohh!!!

This is GR8.

Hv you done this using Excel 2016 ? Am trying to get 30 months data. in that case how can I use ETS method in Excel 2007 ? I think not possible. :)

However, thanks again for your help.
 
It's possible. Though to exactly match ETS formula's result would be difficult.
As we can't be sure of exact algorithm used (we know it's AAA method, but underlying algorithm is a mystery).

To my knowledge, Data Mining Add-in for Excel (2007 & 2010) used slightly different algorithm (I think ARIMA algorithm), but will produce similar result.

See link for detail on the add-in.
https://support.office.com/en-us/article/Data-Mining-Add-ins-cbbce629-df1a-4b15-b40e-c494fec4f022

FYI - I recently started learning R-Script and it's very powerful for this sort of forecasting.

http://www.datamic.net/blog/dynamic-forecasting-with-power-bi-and-r
 
I forgot to mention how I created the examples.

If you can get your hands on Excel 2016... there's handy little tool in Data tab, called Forecast. Based on data selected, it will create new sheet that pretty much mirrors "Forecast.ets sample" along with chart.

https://support.office.com/en-us/ar...-Windows-22c500da-6da7-45e5-bfdc-60a7062329fd

Also, you can set seasonality manually... which allows for little less data points, I'd say about 19 data point needed for 12 months seasonality. However, could be dangerous, if you pick seasonality which actually may not be accurate.

upload_2017-8-11_12-17-38.png
 
Alas Friend !!! I don't have Excel 2016, it's very expensive & unfortunate for me. That's why I have been trying to use in alternate way & asked help in this forum.

However, am grateful to you for such help.
 
Back
Top