Time Series Forecasting with FORECAST() and TREND() in Excel
Time series forecasting in Excel helps predict future values based on historical data trends. Two important functions for time series forecasting are FORECAST() and TREND(). These functions are used to make predictions for future data points based on past data. In this tutorial, we will explore how to use these functions with real-time examples.
Step-by-Step Tutorial
Step 1: Prepare Your Data
For time series forecasting, you need a series of historical data points. Let’s consider monthly sales data for a year, from January to December, and we want to forecast sales for the next month, January of the next year.
| Month | Sales |
|-------|-------|
| Jan | 150 |
| Feb | 180 |
| Mar | 200 |
| Apr | 220 |
| May | 250 |
| Jun | 270 |
| Jul | 300 |
| Aug | 320 |
| Sep | 340 |
| Oct | 360 |
| Nov | 380 |
| Dec | 400 |
This data shows sales from January to December, and we want to forecast the sales for the next month, January of the following year.
Step 2: Using the FORECAST() Function
The FORECAST() function in Excel is used to predict a future value based on linear regression. The syntax for the FORECAST function is:
FORECAST(x, known_y's, known_x's)
Where:
- x is the new data point for which you want to forecast a value (e.g., January of next year, which would be represented by 13 as it is the 13th month).
- known_y's is the range of historical dependent values (e.g., Sales data).
- known_x's is the range of historical independent values (e.g., Months, represented numerically).
Steps to Use the FORECAST() Function:
- Click on the cell where you want to display the forecasted value (e.g., in cell B14 for January of the next year).
- Enter the formula:
=FORECAST(13, B2:B13, A2:A13). - Press "Enter".
Explanation:
- 13 represents the 13th month (next January).
- B2:B13 contains the sales data (known_y's).
- A2:A13 contains the months (known_x's), represented numerically as 1 for January, 2 for February, and so on.
After entering the formula, Excel will forecast the sales for January of the next year based on the linear trend of the sales data.
Step 3: Using the TREND() Function
The TREND() function in Excel is used to return values along a linear trend. Unlike the FORECAST() function, which returns a single predicted value, TREND() can be used to return multiple forecasted values for future data points. The syntax for the TREND function is:
TREND(known_y's, known_x's, new_x's)
Where:
- known_y's is the range of historical dependent values (e.g., Sales data).
- known_x's is the range of historical independent values (e.g., Months).
- new_x's is the range of future data points (e.g., the next months for which you want to forecast sales).
Steps to Use the TREND() Function:
- Select the range where you want to display the forecasted values (e.g., cells B14:B15 for next January and February).
- Enter the formula:
=TREND(B2:B13, A2:A13, A14:A15). - Press "Ctrl + Shift + Enter" to enter it as an array formula.
Explanation:
- B2:B13 contains the sales data (known_y's).
- A2:A13 contains the months (known_x's).
- A14:A15 contains the future months (next January and February).
After entering the formula, Excel will return the forecasted sales for the next two months (January and February of next year).
Real-Time Examples
Example 1: Forecasting Sales Using the FORECAST() Function
Scenario: You have sales data for the months of January to December, and you want to forecast the sales for January of the next year. Using the FORECAST function, the result is as follows:
| Month | Sales | Forecasted Sales for January (Next Year) |
|-----------------|-------|--------------------------------------------|
| Jan | 150 | |
| Feb | 180 | |
| Mar | 200 | |
| Apr | 220 | |
| May | 250 | |
| Jun | 270 | |
| Jul | 300 | |
| Aug | 320 | |
| Sep | 340 | |
| Oct | 360 | |
| Nov | 380 | |
| Dec | 400 | |
| Jan (Next Year) | | 420 |
By using the formula =FORECAST(13, B2:B13, A2:A13), Excel forecasts the sales for January of the next year to be 420.
Example 2: Forecasting Multiple Months Using the TREND() Function
Scenario: You want to forecast sales for the next two months (January and February of next year). Using the TREND function, the result is as follows:
| Month | Sales | Forecasted Sales for January | Forecasted Sales for February |
|-----------------|-------|------------------------------|--------------------------------|
| Jan | 150 | | |
| Feb | 180 | | |
| Mar | 200 | | |
| Apr | 220 | | |
| May | 250 | | |
| Jun | 270 | | |
| Jul | 300 | | |
| Aug | 320 | | |
| Sep | 340 | | |
| Oct | 360 | | |
| Nov | 380 | | |
| Dec | 400 | | |
| Jan (Next Year) | | 420 | |
| Feb (Next Year) | | | 440 |
By using the formula =TREND(B2:B13, A2:A13, A14:A15), Excel forecasts the sales for January to be 420 and for February to be 440.
Tips for Time Series Forecasting in Excel
- Ensure your data has a clear linear trend before using the FORECAST() or TREND() functions.
- If your data has seasonal patterns or non-linear trends, consider using more advanced forecasting methods.
- Use a larger dataset for better accuracy in your forecasts.
- Visualize the trend using charts like scatter plots to ensure the trend is linear.
Conclusion
Time series forecasting in Excel using the FORECAST() and TREND() functions is a powerful tool for predicting future data points. These functions are particularly useful for making predictions based on historical data with linear relationships. Whether you're forecasting sales, revenues, or any other time-dependent variable, these functions can help guide your decisions and plans for the future.