Forecasting Using Linear Regression

Forecasting using linear regression takes driver-based forecasting a step forward using statistical analysis. Let’s walk through this powerful tool to take historical data points and turn them into a forecast.

What is Linear Regression?

Linear regression explains the relationship between two variables by creating the best fit line. The tighter the relationship between the variables (correlation) the better the line fits. As an example, you could run a linear regression on the number of visitors to a store and the sales revenue each day. You would expect these two items to have a high correlation. You could do the same for the number of website hits and the corresponding sales revenue each day.

For Finance, we can use the best fit line for forecasting. Taking the example above, if we know historical website hits and sales revenue, we can generate a best fit line and forecast our revenue given a certain number of website hits.

When does Linear Regression Work?

You can use linear regression when you have a driver that highly correlated to your financials and when you can forecast that variable independently. Linear regression cannot generate an entire forecast, you will need inputs even if the input comes from economic or market data.

Step 1: Create a Data Table

To start forecasting using linear regression, we need to create a data table. This data table can be either horizontal or vertical, but the data must be paired together. In this example, we are going to use website hits (the independent variable) and sales revenue (the dependent variable, as sales require visitors) for given days.

Creating a data table for linear regression

Step 2: Create a Scatter Plot Chart

Next, create a scatter plot chart in Excel using the two columns of data. While there are many add-ins, tools, and formulas to perform linear regression, it is built right into the scatter plot. You can customize the chart and ranges as you see fit, although it is a best practice to keep a zero base.

Creating a scatter plot as part of linear regression forecasting

Step 3: Customize the Trendline

Next, add a trendline to the scatter plot and customize it for linear regression. Select a linear trendline and allow it to forecast forwards and backwards. Also make sure to display the formula as this will be important in the next step.

Customizing the linear regression trendline

Step 4: Generate the Forecast

Last, we will generate the forecast. Take the linear regression formula which is below as y=2.0379x=1693.8. Populate the independent variable (x=website hits) for the dates you wish to forecast. Then run the formula to generate the dependent variable (y=sales revenue). This can be reiterated for as many time periods as you need.

Looking at the result, the model predicts $126,693 in sales revenue for 63,000 website hits. Benchmarking against two related data points of 62,000 and 64,000, this lands within the range of $122,760-$129,280 which is a valid result.

generating a forecast using linear regression

Using Budgeting And Forecasting Software

Budgeting and forecasting software has different forecasting methods built in, and forecasting using linear regression is one of the most common. Budgeting and forecasting software can also help you monitor trends in your industry so you can make informed decisions about where to allocate resources.

Have any questions on forecasting using linear regression? Are there other topics you would like us to cover? Leave a comment below and let us know! Make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.