Excel Forecasting using Linear Regression.

 Excel Forecasting using Linear Regression.

Excel allows us to predict missing or future data based on a given set of data. Excel follows a linear trend to forecast data using the FORECAST function. The new FORECAST.LINEAR function is a new Excel function for forecasting and the function FORECAST.LINEAR can predict a future value in a linear fashion.

The FORECAST.LINEAR function is categorized under Excel Statistical functions. It will calculate or predict a future value using linear regression.

In financial modeling, the FORECAST.LINEAR function can be useful in calculating the statistical value of a forecast made. For example, if we know the past earnings and expenses that are a certain percentage of sales, we can forecast the future amounts using the function.

Let us have an example:

Objective: Forecast the demand on day 8 to 10 using linear regression.

Step 1. Go to cell C8 and type =FORECAST.LINEAR(A8,B2:B7,A2:A7)

Step 2. By dragging the FORECAST.LINEAR function downwards, the absolute references ($B$2:$B$7 and $A$2:$A$7) remain fixed. However, the relative reference cell (A8) changes to A9 and A10.

Step 3. Enter 1350 into cell C7. Select the cells A1:C11. Create a scatter plot with straight lines and markers.

Step 4. Select Scatter Plots with Straight Lines and Markers.

When a trendline is added to a chart, the equation can be displayed. The equation can extrapolate future values based on the given data.

Step 5. Change Chart Style that fits your intended audience.

Final result of the Forecast using Linear Regression.