A:

A linear regression is a data plot that graphs the linear relationship between an independent and a dependent variable. It is typically used to visually show the strength of the relationship, and the dispersion of results – all for the purpose of explaining the behavior of the dependent variable.

Say, as a simple example, we wanted to see test the strength of the relationship between amount of ice cream eaten and obesity. We would take the independent variable, the amount of ice cream, and relate it to the dependent variable, obesity, to see if there was a relationship. Given a regression is a graphical display of this relationship, the lower the variability in the data, the stronger the relationship, and the tighter the fit to the regression line.   

Important Considerations

There are a few critical assumptions about your data set that must be true in order to proceed with a regression analysis:

  1. The variables must be truly independent (using a Chi-square test).
  2. The data must not have different error variances (this is called heteroskedasticity).
  3. The error terms of each variable must be uncorrelated. If not, it means the variables are serially correlated.

If those three things sound complicated, well – they are. But the effect of one of all of those considerations not being true is a biased estimate. Essentially, you'd be misstating the relationship you are trying to measure.

Outputting a Regression in Excel

The first step in running regression analysis in Excel is to double-check that the free Excel plugin Data Analysis ToolPak is installed. This plugin makes calculating a range of statistics very easy. It is not required to chart a linear regression line, but it makes creating statistics tables simpler.   

Using the Data Analysis ToolPak, creating a regression output is just a few clicks. Remember that the independent variable goes in the X range.

Say we want to know, given the S&P 500 returns, if we can estimate the strength and relationship of Visa (V) stock returns.

Go to the Data Tab —> Data Analysis, and run the result:

[Note: If the table seems small, right-click image and open in new tab for higher resolution.]

Interpret the Results

Using that data (the same from our R-squared article), we get the following table:

Charting a Regression in Excel

We can chart a regression in Excel by highlighting the data and charting it as a scatter plot. Apply some formatting, and the visual result sums up the strength of the relationship, albeit at the expense of not providing as much detail as the table above. 

RELATED FAQS
  1. What's the difference between R-squared and adjusted R-squared?

    Learn how R-squared and adjusted R-squared values differ, how they are calculated, and the relationship between them. Read Answer >>
  2. How do I perform a financial analysis using Excel?

    Find out how to perform financial analysis through Microsoft Excel, which is probably the most widely used software among ... Read Answer >>
  3. Does a negative correlation between two stocks mean anything?

    Learn what the concept of negative correlation means, understand how it is generally calculated and see how it is used in ... Read Answer >>
  4. Is there an easy way to do financial forecasting in Excel?

    Find out how to use Excel to conduct financial forecasting. Learn how to build a complex financial model, and discover functions ... Read Answer >>
  5. What are the differences between regressive, proportional, and progressive taxes?

    Learn the three basic types of tax systems--regressive, proportional, and progressive--used in the U.S., and how they affect ... Read Answer >>
Related Articles
  1. Trading

    The linear regression of time and price

    This investment strategy can help investors be successful by identifying price trends while eliminating human bias.
  2. Trading

    When Is A Bull Market Not A Bull Market?

    During some bull or bear moves in the stock markets, investors will be going with the trend, but day traders may find they cannot.
  3. Investing

    What's a Sensitivity Analysis?

    Sensitivity analysis is used in financial modeling to determine how one variable (the target variable) may be affected by changes in another variable (the input variable).
  4. Investing

    Guide To Excel For Finance

    Formulas, functions and features you need to know when using Excel for financial analysis.
  5. Tech

    Advisor Tools: To ‘Excel’ or Not Excel?

    Microsoft Excel has been a staple of the financial services industry for decades but specialized advisor software solutions have sprung up in recent years.
  6. Financial Advisor

    How Do Companies Forecast Oil Prices?

    Read about the different forecasting methods that businesses use to predict future crude oil prices, and why it's so difficult to guess correctly.
  7. Financial Advisor

    Calculating Beta: Portfolio Math For The Average Investor

    Beta is a useful tool for calculating risk, but the formulas provided online aren't specific to you. Learn how to make your own.
  8. Small Business

    Calculating (Small) Company Credit Risk

    Determining creditworthiness of smaller and medium-sized corporations isn't as easy as for larger companies, but these tips can help.
RELATED TERMS
  1. Multiple Linear Regression - MLR

    Multiple linear regression (MLR) is a statistical technique that ...
  2. Stepwise Regression

    Stepwise regression is the step-by-step iterative construction ...
  3. Nonlinear Regression

    Nonlinear regression is a form of regression analysis in which ...
  4. Regressive Tax

    A regressive tax is a tax that is applied uniformly, resulting ...
  5. Line Of Best Fit

    A straight line drawn through the center of a group of data points ...
  6. Residual Sum Of Squares - RSS

    A residual sum of squares is a statistical technique used to ...
Hot Definitions
  1. Inflation

    Inflation is the rate at which prices for goods and services is rising and the worth of currency is dropping.
  2. Discount Rate

    Discount rate is the interest rate charged to commercial banks and other depository institutions for loans received from ...
  3. Economies of Scale

    Economies of scale refer to reduced costs per unit that arise from increased total output of a product. For example, a larger ...
  4. Quick Ratio

    The quick ratio measures a company’s ability to meet its short-term obligations with its most liquid assets.
  5. Leverage

    Leverage results from using borrowed capital as a source of funding when investing to expand the firm's asset base and generate ...
  6. Financial Risk

    Financial risk is the possibility that shareholders will lose money when investing in a company if its cash flow fails to ...
Trading Center