Time Series Forecasting Methods in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


Time Series Forecasting in Excel: 3 Methods

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: How to Do Budgeting and Forecasting in Excel


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: How to Forecast Sales in Excel


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


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


Download Workbook


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


<< Go Back to Forecasting in Excel |Excel for Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo