Time Series Forecasting Methods in Excel

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.

time series forecasting methods in Excel

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.

time series forecasting methods in 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.

Exponential

➤ Press ENTER.
Then, you will get the forecasted demand for Oct-21.

time series forecasting methods in Excel

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.

Exponential

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.

Exponential

➤ 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.

Exponential

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.

time series forecasting methods in Excel

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.

time series forecasting methods in Excel

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.

Linear

➤ Press ENTER.
Finally, you will get the forecasted demand for Oct-21.

Linear

Read More: Forecasting in Excel Using FORECAST Function & Auto Fill Handle Tool


Similar Readings


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.

time series forecasting methods in Excel

Steps:
➤ Type the following formula in cell D13.

=D12*C17+D11*C16+D10*C15

Here, 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.

Weighted moving average

➤ Press ENTER.
Eventually, we will be able to have the forecasted demand for Oct-21.

Weighted moving average

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.

comparison


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.

Practice


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.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo