This article is adapted from Microsoft Office Excel 2003 Inside Out by Craig Stinson and Mark Dodge. Click here for the full article.
The concept of regression might sound strange because the term is normally associated with movement backward, whereas in the world of statistics, regression is often used to predict the future. Simply put, regression is a statistical technique that finds a mathematical expression that best describes a set of data.
Often businesses try to predict the future using sales and percent-of-sales projections based on history. A simple percent-of-sales technique identifies assets and liabilities that vary along with sales, determines the proportion of each, and assigns them percentages. Although using percent-of-sales forecasting is often sufficient for slow or steady short-term growth, the technique loses accuracy as growth accelerates.
Regression analysis uses more sophisticated equations to analyze larger sets of data and translates them into coordinates on a line or curve. In the not-so-distant past, regression analysis was not widely used because of the large volume of calculations involved. Since spreadsheet applications, such as Excel, began offering built-in regression functions, the use of regression analysis has become more widespread.
Read on . .
Excel includes several array functions for performing linear regression—LINEST, TREND, FORECAST, SLOPE, and STEYX—and exponential regression—LOGEST and GROWTH. These functions are entered as array formulas and they produce array results.
You can use each of these functions with one or several independent variables. The following list provides a definition of the different types of regression:
- Linear regression produces the slope of a line that best fits a single set of data. Based on a year's worth of sales figures, for example, linear regression can tell you the projected sales for March of the following year by giving you the slope and y-intercept (that is, the point where the line crosses the y-axis) of the line that best fits the sales data. By following the line forward in time, you can estimate future sales, if you can safely assume that growth will remain linear.
- Exponential regression produces an exponential curve that best fits a set of data that you suspect does not change linearly with time. For example, a series of measurements of population growth will nearly always be better represented by an exponential curve than by a line.
- Multiple regression is the analysis of more than one set of data, which often produces a more realistic projection. You can perform both linear and exponential multiple regression analyses.
For example, suppose you want to project the appropriate price for a house in your area based on square footage, number of bathrooms, lot size, and age. Using a multiple regression formula, you can estimate a price, based on a database of information gathered from existing houses.
Click here for the full article from Microsoft on using Excel for Linear and Exponential regression.
Sample files You can download sample files that relate to excerpts from Microsoft Office Excel 2003 Inside Out from Downloads on Microsoft Office Online. This article uses the files Analysis.xls and Home Price Estimator.xls .
Recent Comments