FORECASTING IN EXCEL 2019

 Hello reader;

Today I am writing about how to develop a forecast graph using Excel 2019. While I was looking up how to handle time series data in R I tried to forecast total fertility rate (number of births per woman) in Excel 2019 with the help of data that I got from world data bank website:

 https://data.worldbank.org/indicator/SP.DYN.TFRT.IN 

The inspiration is to learn about how to forecast or generate cool graphs like one's ECG gives and that too in a simpler way by using regular software like Microsoft Excel and utilizing all the functions that are overlooked. 

Reference image for time series graph.

Certain statistics and parameters in time series are described below,

"TIME SERIES WITH THIS TERM REMEMBER THREE RELATED TERMS: ERROR, TREND AND SEASONALITY"

• ALPHA:  controlling smoothening factor for level.
• GAMMA: seasonal component
• BETA : decay of influence of change in trend
• MAPE : Mean absolute percentage error
• SMAPE : symmetric mean absolute percentage error
• RMSE: root mean square error
• MSE: mean square error

Now there is concept called "exponential smoothening" which involves certain weights are given to observations. The more the recent observation the more the weight is given to that observation.

In this exponential smoothening concept there are certain factors: alpha, beta and gamma.

SMAPE 

F_t : Forecast value and A_t actual value 

whenever you create a forecast model the SMAPE term should be less than 5% it means below 5% of SMAPE the model is acceptable. 

Without delaying moving on practical part

•  https://data.worldbank.org/indicator/SP.DYN.TFRT.IN 
•  Get fertility data from above website

Raw data look!

Now open new sheet and create a new data I have selected few countries and created columns:

Year: 1960 to 2019 and rest of the columns were respective country name and fertility rates 

While pasting from raw data into new sheet make sure you select paste special and paste the values transposing them.

Working data look!

• DATA TAB > Forecast sheet

• Click on "Options"

• Timeline Range: Select all the cells under year column
• Values Range: Select the cells of fertility rate under a particular country 
• Aggregate Duplication Using: Change it to Median
• Forecast End: Select up to which year you want forecasted fertility rate
•  Forecast Start: From which year you want to consider the observations
• Confidence interval 
• Here, I kept the seasonality option to be detected automatically
• Check the "Include forecast statistics" to get parameter values as well as SMAPE, RMSE, MSE values.
• Click Create and a new sheet within same excel file will be opened.

• As shown above the new sheet will look like consist of timeline, values, forecast, upper and lower confidence bound with a additional table consisting of values of alpha, beta, gamma and several other parameters.
• A graph will appear consist of appropriate levels. As I have selected to get forecasted values up till 2034 year, the table will consist of forecasted fertility rate values.
• Below in this table, the forecasted fertility rate values of India are shown.
• 1.6983409 is the forecasted fertility rate value of India in 2034 year with respective lower and upper confidence values.
• The SMAPE of this model is 0.01 which is acceptable. 

In this way, one can easily perform forecasting in Microsoft Excel 2019. 

Further, you can also explore more about SMAPE and MAPE.

Keep Exploring😊