Linear Forecasting with Excel Functions

Linear forecasting in Excel helps predict future values based on past data. This technique assumes that the relationship between the past data points is linear, and the future values will follow the same trend. In this tutorial, we will explore how to use Excel functions such as the FORECAST function and TREND function for linear forecasting with real-time examples.

Step-by-Step Tutorial

Step 1: Set Up Your Data

To forecast future values, you need historical data. For example, let's say we have monthly sales data for the first six months of the year, and we want to forecast the sales for the next month.

        | Month | Sales |
        |-------|-------|
        | Jan   | 150   |
        | Feb   | 180   |
        | Mar   | 200   |
        | Apr   | 220   |
        | May   | 250   |
        | Jun   | 270   |

This data shows sales from January to June, and we want to forecast sales for July.

Step 2: Use the FORECAST Function

The FORECAST function predicts a future value along a linear trend. It uses the formula: FORECAST(x, known_y’s, known_x’s), where:

  • x: The data point for which you want to forecast a value (e.g., July).
  • known_y’s: The range of historical data (e.g., Sales data).
  • known_x’s: The range of data corresponding to the independent variable (e.g., Months).

Steps to Use the FORECAST Function:

  1. Click on the cell where you want to display the forecasted value (e.g., for July in cell B8).
  2. Enter the formula: =FORECAST(7, B2:B7, A2:A7).
  3. Press "Enter".

Explanation:

  • 7 is the value representing July (the 7th month).
  • B2:B7 contains the known sales data (known_y’s).
  • A2:A7 contains the known months (known_x’s).

After entering the formula, Excel will return the forecasted sales for July based on the linear trend of previous months.

Step 3: Use the TREND Function for Forecasting

The TREND function returns values along a linear trend. Unlike FORECAST, it can be used to return multiple forecasted values for future data points.

The formula for the TREND function is: TREND(known_y’s, known_x’s, new_x’s), where:

  • known_y’s: The range of historical dependent values (e.g., Sales data).
  • known_x’s: The range of historical independent values (e.g., Months).
  • new_x’s: The new data points (future months) for which you want to forecast values.

Steps to Use the TREND Function:

  1. Select the range where you want to display the forecasted values (e.g., B8:B9 for July and August).
  2. Enter the formula: =TREND(B2:B7, A2:A7, A8:A9).
  3. Press "Ctrl + Shift + Enter" to enter it as an array formula.

Explanation:

  • B2:B7 contains the historical sales data (known_y’s).
  • A2:A7 contains the historical months (known_x’s).
  • A8:A9 contains the future months (July and August) for which you want to forecast sales.

After entering the formula, Excel will return the forecasted sales for July and August.

Real-Time Examples

Example 1: Forecasting Sales Using the FORECAST Function

Scenario: You have sales data for January to June and want to forecast the sales for July. Using the FORECAST function, the result is as follows:

        | Month | Sales | Forecasted Sales for July |
        |-------|-------|---------------------------|
        | Jan   | 150   |                           |
        | Feb   | 180   |                           |
        | Mar   | 200   |                           |
        | Apr   | 220   |                           |
        | May   | 250   |                           |
        | Jun   | 270   |                           |
        | Jul   |       | 300                       |

By using the formula =FORECAST(7, B2:B7, A2:A7), Excel forecasts the sales for July to be 300.

Example 2: Forecasting Multiple Months Using the TREND Function

Scenario: You want to forecast sales for the next two months (July and August). Using the TREND function, the result is as follows:

        | Month | Sales | Forecasted Sales for July | Forecasted Sales for August |
        |-------|-------|---------------------------|-----------------------------|
        | Jan   | 150   |                           |                             |
        | Feb   | 180   |                           |                             |
        | Mar   | 200   |                           |                             |
        | Apr   | 220   |                           |                             |
        | May   | 250   |                           |                             |
        | Jun   | 270   |                           |                             |
        | Jul   |       | 300                       |                             |
        | Aug   |       |                           | 330                         |

By using the formula =TREND(B2:B7, A2:A7, A8:A9), Excel forecasts the sales for July as 300 and for August as 330.

Tips for Linear Forecasting in Excel

  • Always ensure that your data follows a linear trend before using linear forecasting techniques.
  • Use a scatter plot to visualize the trend of your data before performing forecasting.
  • For best results, use a larger dataset to reduce the impact of outliers on the forecast.
  • For non-linear data, consider using advanced forecasting methods or models.

Conclusion

Linear forecasting in Excel is a powerful tool for predicting future values based on historical data. By using the FORECAST and TREND functions, you can easily forecast future trends, helping you make data-driven decisions. Whether you're forecasting sales, revenue, or any other variable, these functions offer a straightforward way to apply linear regression in Excel.




Advertisement