1. Tutorials
  2. Simple linear regression tutorial

Simple Linear Regression in Excel: Worked ExampleLearn how to fit a simple regression model, check the assumptions of the ordinary least squares linear regression method, and make predictions using the fitted model.

On March 1, 1984 the Wall Street Journal published data on the advertising spend and yield for a number of commercial TV adverts. The advertisements were selected by an annual survey conducted by Video Board Tests, Inc., a New York ad-testing company, based on interviews with 20,000 adults who were asked to name the most outstanding TV commercial they had seen, noticed, and liked. The retained impressions were based on a survey of 4,000 adults, in which regular product users were asked to cite a commercial they had seen for that product category in the past week. Of interest is fitting a model to describe the relationship, and making predictions using the model. For more information see DASL Story: TV Advertising Yields.

In this tutorial you will perform the following tasks:

  1. Plotting bivariate data
    Create a scatter plot of the relationship between the variables.
  2. Fitting a simple regression model
    Fit a polynomial regression model to describe the relationship.
  3. Checking the assumptions of the regression model
    Check the assumptions and robustness of the model.
  4. Changing the regression fit
    Change the model to a logarithmic regression to better describe the relationship.
  5. Making predictions
    Estimate the average retained impressions given a $100 million spend.

Plotting bivariate data

The scatter plot is a fundamental tool for looking at bivariate data. It shows the important characteristics of the data and can be used to decide what model may describe the relationship between the variables.

  1. Open the file tutorials\TV Ad Yield.xlsx.
  2. Click a cell in the dataset.
  3. On the Analyse-it ribbon tab, in the Dataset group, click Dataset.

    The dataset task pane is displayed.

  4. Select Labels in first column.
  5. Click Apply.

    The dataset task pane is closed.

  6. On the Analyse-it ribbon tab, in the Statistical Analyses group, click Fit Model drop-down list, and then click Scatter.
    The analysis task pane opens.A scatter plot is added to the analysis task pane.
  7. In the Y variable list, select Retained impressions per week.
  8. In the X variable list, select Budget.
  9. Click Calculate.
    The results are calculated and the analysis report opens.

The scatter plot suggests the relationship between advertising yield and spend is not a straight line. It appears the yield increases sharply at lower spend and levels out as spending increases.

scatter plot

Fitting a simple regression model

After inspecting the scatter plot the next step is to fit a model to describe the relationship.

  1. On the Analyse-it ribbon tab, in the Fit group, click Fit > Polynomial 2 - Quadratic.

    A 2nd order polynomial fit task is added to the analysis task pane.

  2. On the Analyse-it ribbon tab, in the Model group, click Test Effect of Model.

    The Effect of Model task is added to the analysis task pane.

  3. Click Recalculate.
    The results are calculated and the analysis report opens.

The regression line on the scatter plot shows the relationship described by the model.

quadratic regression

The effect of model table p-value of 0.0011 tells you that there is a statistically significant relationship between spend and yield.

effect of model table for regression fit

Checking the assumptions of the regression model

Most statistical methods have assumptions that should be true for the results to be valid. In ordinary least squares linear regression the following assumptions must be true:
  • There is a linear relationship between the predictor and response variables.
  • The error term has the same variance in each observation.
  • The errors are uncorrelated between observations.
  • The errors are normally distributed.
It is also important to consider influential observations that if removed would substantially change the model fit.
  1. On the Analyse-it ribbon tab, in the Diagnostics group, click Residuals > 3-up Plot.

    A residual plot with histogram and normal probability plot of the residuals are added to the analysis task pane.

  2. On the Analyse-it ribbon tab, in the Diagnostics group, click Outliers and Influence.

    An influence plot is added to the analysis task pane.

  3. Click Recalculate.
    The results are calculated and the analysis report opens.

The residual plot and normality plot show that the assumptions do not seem to be seriously violated.

residual plot

However the influence plot shows that McDonald's has a large influence on the fit.

fit model regression influence plot

Looking again at the scatter plot and fit shows there is a downturn in the fitted line, compared to the data, as the spend increases. It might be worth considering alternative models to better describe the relationship.

Changing the regression fit

After fitting a model and looking at the diagnostics you might reconsider some decisions. Often you may want to reformulate the model by changing the fit, or by adding, removing, or applying transformations to the variables.

  1. On the Analyse-it ribbon tab, in the Fit group, click Fit > Logarithmic.
  2. Click Recalculate.
    The results are calculated and the analysis report opens.

The scatter plot suggests the logarithmic model fits quite well and better describes the relationship than a quadratic model. The influence plot shows there are no outliers or highly influential plots.

logarithmic regression

Making predictions

In many cases the purpose of model fitting is to make predictions about the response given a value of the predictor.

It is important to remember that making predictions outside the observed range of the data (known as extrapolation) is risky. It is impossible to be sure that the fitted relationship extends beyond the observed range.

  1. On the Analyse-it ribbon tab, in the Predict group, click Predict .

    The Predict Y panel is added to the analysis task pane.

  2. In the Predict Y grid, enter 100 in the #1 edit box.
  3. Select the Mean check box, and in the Confidence interval edit box, enter 95%.
  4. Select the Individual check box, and in the Prediction interval edit box, enter 95%.
  5. Click Recalculate.
    The results are calculated and the analysis report opens.

For a Budget of 100 million $ the average retained impressions per week is 64.9 million. The 95% confidence interval (CI) 50.2 to 79.5 million is the uncertainty in the mean estimate. The 95% prediction interval (PI) 18.2 to 111.6 is the interval we would expect a single future observations to lie within.

predict Y from regression fitted model

Tutorials v6.20.0

Administrator's Guide

User's Guide

Statistical Reference Guide