Tanjima Hossain
If you are looking for different types of time series forecasting methods in Excel, then you will find this article useful. Forecasting methods are helpful to determine the future demands or sales or temperature etc. by analyzing the available data for a time series. So, let’s start our main article.
Download Workbook
3 Methods of Time Series Forecasting in Excel
Here, we have the following dataset containing the records of demands from Jan-21 to Sep-21, and using these values we will determine the demand for Oct-21 by using 3 different forecasting methods.
We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.
Method-1: Exponential Time Series Forecasting
We will discuss the exponential time series forecasting method here to do time series forecasting correctly to determine the demand for Oct-21. For this purpose, you can use the FORECAST.ETS function along with the Forecast Sheet option of Excel.
Steps:
Firstly, we will use a formula for the exponential forecasting method.
➤ Type the following formula in cell D13.
=FORECAST.ETS(C13,D4:D12,C4:C12,1,1)Here, C13 is the month on which we want to determine the demand, D4:D12 is the range of demands, C4:C12 is the range of months that have available demands, 1 is for detecting seasonality automatically, and the last 1 is for automatic completion of missing values with the help of linear interpolation.
➤ Press ENTER.
Then, you will get the forecasted demand for Oct-21.
Now, we will use the built-in Excel feature to determine the demand value for Oct-21 using the exponential forecasting method.
➤ Select the range of the time series and their corresponding demand values and then go to the Data Tab >> Forecast Group >> Forecast Sheet Option.
After that, the Create Forecast Worksheet dialog box will open up with the graph containing the forecasted demands.
You can customize this graph by clicking on Options.
➤ We have changed the date in the Forecast End box to the date 01-10-2021 here and then clicked on the Create option to represent the forecasted value in a table.
Finally, you will have the following table with the demand for Oct-21 and this value is similar to the value we got using the previous function.
Read More: FORECAST Function in Excel (with other Forecasting Functions)
Method-2: Linear Time Series Forecasting
In this section, we are going to use the linear time series forecasting method to determine the demand for Oct-21. To do this, we will use the FORECAST. LINEAR function here.
Steps:
➤ Type the following function in cell D13.
=FORECAST.LINEAR(C13,D4:D12,C4:C12)Here, C13 is the month on which we want to determine the demand, D4:D12 is the range of demands, and C4:C12 is the range of months that have available demands.
➤ Press ENTER.
Finally, you will get the forecasted demand for Oct-21.
Read More: Forecasting in Excel Using FORECAST Function & Auto Fill Handle Tool
Similar Readings
- How to Forecast Growth Rate in Excel (2 Methods)
- How to Forecast Sales Growth Rate in Excel (6 Methods)
Method-3: Weighted Moving Average Forecasting
Here, we will use the weighted moving average forecasting method to determine the demand in Oct-21. To do this we need some weighted factors which we have gathered as W1, W2, and W3 following the dataset.
The latest demand value has more significance than the oldest demand value. So, here we will multiply the latest value with a factor of 0.6 then the second latest value will be multiplied by 0.4 and the oldest value will be multiplied by 0.2 for its less significance in calculating this 3-point moving average.
Steps:
➤ Type the following formula in cell D13.
=D12*C17+D11*C16+D10*C15Here, D12 is the latest demand value in September 2021, C17 is the weight factor 0.6 to multiply with the value in D12, then D11 is the 2nd latest demand in August 2021, C16 is the weight factor 0.4 to multiply with the value in D11 and finally, D10 is the oldest demand in July 2021, C15 is the weight factor 0.2 to multiply with the value in D10.
➤ Press ENTER.
Eventually, we will be able to have the forecasted demand for Oct-21.
Read More: How to Do Budgeting and Forecasting in Excel (2 Suitable Ways)
Comparison Between Time Series Forecasting Methods with Graphical Representation
Here, we have shown the comparison between the forecasted results using the Exponential, Linear, and Weighted Moving Average methods along with a graphical presentation of this comparison. For Weighted Moving Average we are getting the highest demand than the other two methods.
Practice Section
For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.
Conclusion
In this article, we tried to cover the time series forecasting methods in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.
