Forecast the result for next few months or year using Forecast () - is it the right method

[Cheong Yin Yin]

Hi


I have been asked to use scientific method to predict/forecast for post implementation review result.

Post implementation review is a survey to seek end user’s feedback on the overall project delivery. The rating scale is a 5 point scale. Our goal is to achieve 50% for rating <=2.


Attached a set of data set. Management would like to predict what would be the result for the rest of months in FY17. I used excel and add the trendline but I don’t understand it. I surfed nets and there are lots of overwhelming information online. The more I read the more I confuse, like alpha, Std Dev, linear, regression, exponential, etc.

I read through and decided to use the simplest model and something I can understand is the Forecast () function to do the prediction. My colleague questioned me amongst so many models what is the reason you use the forecast () function. I don’t know how to answer them and I am not sure whether this is the right technique to use or not.


Please help.

Yearly data trend:
YearActualForecast
201354%56%
201457%54%
201552%52%
201650%51%
201749%


Monthly data trend:
MMM-YYRating<=2Trend
Apr-1643%48%
May-1650%48%
Jun-1643%48%
Jul-1643%49%
Aug-1675%49%
Sep-1645%49%
Oct-1657%49%
Nov-1660%49%
Dec-1633%49%
Jan-1729%49%
Feb-1750%49%
Mar-1758%50%
Apr-1757%50%
May-1763%50%
Jun-1731%50%
Jul-1717%50%
Aug-1767%50%
Sep-1767%50%
Oct-1751%
Nov-1751%
Dec-1751%
Jan-1851%
Feb-1851%
Mar-1851%

 

[David Evans]

Why don't you create a sample file with all your data and post it here with what you're looking to achieve? You will get more and better quality results.

 

[Cheong Yin Yin]

Thanks for the feedback. Attached the file.

If forecast () is not the right method, why?
What would be the recommended scientific method should use?

 

[David Evans]

Thanks for the feedback. Attached the file.

If forecast () is not the right method, why?
What would be the recommended scientific method should use?

What version of Excel do you have available? Your file is saved in an old Excel format, and more recent versions of Excel have improved forecasting tools. However, just because it's saved in an old format does not mean that is what you're using ...

 

[Cheong Yin Yin]

What version of Excel do you have available? Your file is saved in an old Excel format, and more recent versions of Excel have improved forecasting tools. However, just because it's saved in an old format does not mean that is what you're using ...

Hi David
Thank you for your feedback. FYI, the excel version is current version used in the company.

I am poor in statistics calculation as well as not good in excel. Can I use the forecast () function to calculate the forecast % for the result? If it is correct, what is the recommended and why? Please advise.

To clarify, what do you mean on this: However, just because it's saved in an old format does not mean that is what you're using ...

Once again, thank you.
Best regards, Yin2

 

[NARAYANK991]

Hi ,

Excel has changed its file extension from the .xls extension which was used in Excel versions '97 through 2003 ; from Excel 2007 onwards , the file extensions in use for workbooks are .xlsx , .xlsm and .xlsb

Thus , a file with any of the latter 3 extensions , cannot be opened if you have an Excel version prior to Excel 2007 viz. Excel 97 , Excel 2000 , Excel 2002 or Excel 2003.

However , as a means of backward compatibility , even if you are using Excel 2007 or any later version of Excel , you can always save your workbook in Excel '97-2003 format (.xls) so that users who have those earlier versions of Excel can still open your file. The only caveat is that if your workbook uses functions and features that were not available in the earlier versions of Excel , users can open the file , but will not be able to work with the file since those functions and features are not supported in the earlier versions. One example would be Slicers , which were introduced in Excel 2010.

So when David has asked you for your Excel version , please mention whether it is Excel 2007 , Excel 2010 , Excel 2013 or Excel 2016 or whether it is any earlier version Excel '97 through Excel 2003.

Narayan

 

[David Evans]

To clarify, what do you mean on this: However, just because it's saved in an old format does not mean that is what you're using ...

Once again, thank you.
Best regards, Yin2

Your file was saved in an Excel 97-2003 Worksheet - you can select this format if your trying to ensure compatibility with old versions of Excel, however, it won't be able to preserve the features of a newer version. It may be that your Default File Save has been set to this, if so, it is easily changes.

EDIT - @NARAYANK991 has beaten me to the answer, and as usual his is very thorough - He's a Ninja!

 

[David Evans]

Let's see if we can find out which version of Excel you are using by hook or by crook ...
Try opening the attached file - Let us know if you open all the sheets or whether you get compatibility errors.

 

[Hui]

Easiest way is to do the following

Start Excel
Goto the VBA Editor (press Alt+F11)
Double click on a Sheet or Workbook (Yellow)



paste the code below into the Right pane
Press F5

Sub Show_Version()
MsgBox "Welcome to Microsoft Excel version " & Application.Version & " running on " & Application.OperatingSystem & "!"
End Sub

Excel will respond with something like:


Now if You are running on a Mac, some/most versions don't support VBA so let us know here as you won't be able to follow the above steps

 

[Cheong Yin Yin]

Let's see if we can find out which version of Excel you are using by hook or by crook ...
Try opening the attached file - Let us know if you open all the sheets or whether you get compatibility errors.

Dear David

Thank for the quick answer. Yes, I am using Excel 2013. How do you derive the Lower Confidence Bound(Trend), Upper Confidence Bound(Trend) and Forecast(Trend).

By the way, what about the yearly data. What method and how to derive the forecast for 2017? I am worried my management will ask me to explain why i choose this method? I need to provide the explanation.

YearActual
201354%
201457%
201552%
201650%
2017

Thanks
Best regards, Yin2

 

[Cheong Yin Yin]

Hi ,

Excel has changed its file extension from the .xls extension which was used in Excel versions '97 through 2003 ; from Excel 2007 onwards , the file extensions in use for workbooks are .xlsx , .xlsm and .xlsb

Thus , a file with any of the latter 3 extensions , cannot be opened if you have an Excel version prior to Excel 2007 viz. Excel 97 , Excel 2000 , excel 2002 or Excel 2003.

However , as a means of backward compatibility , even if you are using Excel 2007 or any later version of Excel , you can always save your workbook in Excel '97-2003 format (.xls) so that users who have those earlier versions of Excel can still open your file. The only caveat is that if your workbook uses functions and features that were not available in the earlier versions of Excel , users can open the file , but will not be able to work with the file since those functions and features are not supported in the earlier versions. One example would be Slicers , which were introduced in Excel 2010.

So when David has asked you for your Excel version , please mention whether it is Excel 2007 , Excel 2010 , Excel 2013 or Excel 2016 or whether it is any earlier version Excel '97 through Excel 2003.

Narayan

Dear Narayan

Thanks you for the fast response.

Thanks
Best regards, Yin2

 

[Chihiro]

Forecasting isn't a simple thing. You will need to test out various models and validate result.

Looking at your monthly chart, there isn't any trend or pattern that stands out. And no seasonality looking at 12 month cycle.

I don't believe ETS (Exponential Smoothing) method demonstrated by David or Linear Regression is the right model for your data.

See if you can find trend using more granular data (such as weekly, daily data).

Another thing to consider. If there was special event/incident that caused data point to be out of norm (say hurricane damage etc), then those points should be normalized.

NOTE: FORECAST.ETS is only available from Excel 2016. If you want to do similar forecasting, you will need to install Data Mining Add-in, you can download it from link below for Excel 2013.
https://www.microsoft.com/en-us/dow...578&751be11f-ede8-5a0c-058c-2ee190a24fa6=True

If using Excel 2007 or 2010, you can use the one below.
https://www.microsoft.com/en-us/dow...294&751be11f-ede8-5a0c-058c-2ee190a24fa6=True

 

[Cheong Yin Yin]

Forecasting isn't a simple thing. You will need to test out various models and validate result.

Looking at your monthly chart, there isn't any trend or pattern that stands out. And no seasonality looking at 12 month cycle.

I don't believe ETS (Exponential Smoothing) method demonstrated by David or Linear Regression is the right model for your data.

See if you can find trend using more granular data (such as weekly, daily data).

Another thing to consider. If there was special event/incident that caused data point to be out of norm (say hurricane damage etc), then those points should be normalized.
NOTE: FORECAST.ETS is only available from Excel 2016. If you want to do similar forecasting, you will need to install Data Mining Add-in, you can download it from link below for Excel 2013.
https://www.microsoft.com/en-us/dow...578&751be11f-ede8-5a0c-058c-2ee190a24fa6=True

If using Excel 2007 or 2010, you can use the one below.
https://www.microsoft.com/en-us/dow...294&751be11f-ede8-5a0c-058c-2ee190a24fa6=True

Hi

Thanks for the input. I totally agreed forecast is not easy. That's why I seek help from all the experts for advices and solutions.

For the data, it is impossible for me to get daily or weekly data.
What about for the yearly data set? How to forecast the 2017 result?

Year Actual
2013 54%
2014 57%
2015 52%
2016 50%
2017 ?

Please advise

Thanks
Best regards, Yin2

 

[Chihiro]

Base it on your monthly data. You really don't have enough of data point to calculate solely based on yearly data.

 

[NARAYANK991]

Hi

Thanks for the input. I totally agreed forecast is not easy. That's why I seek help from all the experts for advices and solutions.

For the data, it is impossible for me to get daily or weekly data.
What about for the yearly data set? How to forecast the 2017 result?

Year Actual
2013 54%
2014 57%
2015 52%
2016 50%
2017 ?

Please advise

Thanks
Best regards, Yin2

Hi ,

I think the best thing to do would be to tell your boss that such forecasting is meaningless.

You can forecast the trend for the next 3 months if you have data for the past few years ; what I mean is that a forecast for the coming 3 months will be meaningful only if it is based on at least 12 months of data , preferably 24 or even 36.

To forecast based on 4 data samples is not forecasting , but the equivalent of tossing a coin ; you have only a 50% chance of being right. You also have a 50% chance of being wrong.

The data you have shows a rise in the second year , and a fall in two consecutive years ; without a knowledge of why it increased in the second year or why it fell two years running , based only on the data values , nothing can be predicted ; even if you can predict , the chances of your prediction being wrong are extremely high.

Ask for more data so that you can correlate the data with actual events ; did the yearly increase happen because some competitor closed down , or did it happen because of some innovation in your product ?

Did the fall happen because of changes in import duties or because foreign competitors dumped material in your markets or ... Unless the reasons are known for changes in the data , predicting based solely on data is never done.

Your monthly data shows too much of oscillation for the trend to be meaningful ; the trend is more an arithmetic average.

Narayan

 

[Hui]

The best way to start forecasting is to understand what the data you have presented means in the real world.

Is an 18 month trend applicable?
Does the company have more data maybe going back 4 or 5 years or more?
Is there seasonal trends to explain the High's and Low's in the data?

If you have enough data you should compare each month going back several years. Often there maybe closer trends on a month by month basis than a time line basis.

The data also appears to have become more volatile, bigger swings, in the past 12 months compared to the first bit of data, is there a reason for that?

If the largest and smallest values are exp0lainable, can those months be normalised, by adding/removing the causes?

It is only when you understand what is going on, can you start to apply 'Scientific" methods of analysis

 

[Cheong Yin Yin]

Dear all

Thanks for all the inputs and explanation. I don't have more data to go because we only started to collect the result in 2016.

I fully understand this forecast method is meaningless. Is that possible we just give a forecast no by sharing with them the forecast no may not be accurate because of not enough data set.

Can we use the linear model?
Can you share with me how to interpret the y=-0.017x +0.575 and R2 = 0.5402?

Thanks
Best regards, Yin2

 

[NARAYANK991]

Hi ,

Do you have enough time to go through this series of posts by Hui ?

If not , I can explain just the one part that you want an explanation for.

https://chandoo.org/wp/2011/01/24/trendlines-and-forecasting-in-excel/

Narayan

 

[Hui]

You have use text as the x Values for the forecast and so Excel treats them as integers starting at 1, ie: FY13 =1 FY14 = 2 etc
So in your case FY17 will be 5

When you use the Trendlines in Chart or the Function Excel returns the parameters for the Line of best fit
in your case it is y=-0.017x +0.575 and R2 = 0.5402?
What this says is the Y Value (What you are forecasting) is equal to -0.017 *
X (the date number) plus 0.575
The forecast value (Y) = -0.017 x 5 +0.575
= 0.49
= 49%

The R2 value is a measurement of how closely the data fits the line of best fit
A low value of 0.01 to 0.3 means there is little correspondance
A medium Value 0.3 to 0.7 means there is a medium level of correspondance
A high value > 0.7 means there is a Good correspondance

 

[Cheong Yin Yin]

Hi ,

Do you have enough time to go through this series of posts by Hui ?

If not , I can explain just the one part that you want an explanation for.

https://chandoo.org/wp/2011/01/24/trendlines-and-forecasting-in-excel/

Narayan

Hi

Sorry to inform you that I am still trying to digest the series of posts provided by Hui due to limited knowledge.

Thanks
Best regards, Yin2

 

[Cheong Yin Yin]

You have use text as the x Values for the forecast and so Excel treats them as integers starting at 1, ie: FY13 =1 FY14 = 2 etc
So in your case FY17 will be 5

When you use the Trendlines in Chart or the Function Excel returns the parameters for the Line of best fit
in your case it is y=-0.017x +0.575 and R2 = 0.5402?
What this says is the Y Value (What you are forecasting) is equal to -0.017 X
X is the date plus 0.575
The forecast value (Y) = -0.017 x 5 +0.575
= 0.49
= 49%

The R2 value is a measurement of how closely the data fits the line of best fit
A low value of 0.01 to 0.3 means there is little correspondance
A medium Value 0.3 to 0.7 means there is a medium level of correspondance
A high value > 0.7 means there is a Good correspondance

Dear Hui

Thank you for the explanation. Now I am understand how to interpret the equation.

Thanks
Best regards, Yin2