MG495 Business Policy: Final Case Study (FedEx)

Regression Analysis Tips

Simple regression can be a powerful tool for forecasting. For your ‘Live” case analysis, regression may be the easiest way to accurately forecast sales. You simply develop a spreadsheet with annual sales and time. Excel programs can perform regression analysis once data has been inputted into a spreadsheet. If you don’t remember how to use regression with excel, conduct a quick web search. There are many sites which “walk” you through the steps. If you recall, a regression line is a line that best fits the data. A good fit allows you to forecast.

How do you determine if a line is a good fit? Excel can calculate an R value which is called the correlation coefficient. When you square R you have the coefficient of determination. Both of these values give us important information. The R value can range from -1 to 1. We are most concerned with the sign (positive or negative) of the R value. If it is a positive number, then we know that there is a direct relationship between the variables. As X increases, Y increases. As X, decreases, Y decreases. Essentially, we have a line that rises to the right. If R is a negative number, we know the line falls to the right. As X increases, Y decreases. As X decreases, Y increases. In several industries you will find an inverse relationship. For example, we might expect a repair parts business to improve when the economy is poor.

In simple regression R squared (coefficient of determination) tells us the strength of the relationship between the two variables. R squared can range from 0 – 1. The closer it is to one the better the fit between the two variables. It is worth noting that R squared indicates the strength of the relationship between two variables. It does not show causality. Too often novices state that one variable causes another. There may be a cause and effect relationship between the variables but regression does not measure that. It only measures a mathematical relationship between variables.

Using regression to forecast sales for a company.

Step 1) Input sales (Y values) and years (X values) into two separated columns in an excel spreadsheet. You should have 5 – 10 years of data. You can use fewer years of data but

there should be a reason as to why you are doing that.

Step 2) Calculate a regression line. This will give you Y=MX + B (Y should be sales and X should be time) (M is the slope of the line and B is the Y intercept.)

Step 3) Calculate R (Excel will do this as well.) Once you have this value, square it to get R squared.

Step 4) If the R squared value is close to 1 you may use it to forecast sales for the following year by plugging in the year in the X value and determining Y.

If R squared is not close to 1, you will have to use another forecasting technique taught in a previous class. There are many techniques to forecast annual sales.

Sometimes, one can eliminate very old data from a regression analysis and improve the R squared value. The rationale for doing this may be that future sales can be better predicted by more recent events as there have been significant changes in the industry and/or company. Using old sales data is not indicative of today’s market.

Websites

How to Run Regression Analysis in Microsoft Excel

http://www.wikihow.com/Run-Regression-Analysis-in-Microsoft-Excel

Regression in Excel - Easy Excel Tutorial

http://www.excel-easy.com/examples/regression.html

How to Use Regression Analysis | eHow

http://www.ehow.com/how_5807198_use-regression-analysis.html

Simple Linear Regression Analysis

http://reliawiki.org/index.php/Simple_Linear_Regression_Analysis