Multiple Regression Project
The is the only deliverable in Week Four. It is the case study titled “Locating New Pam and Susan’s Stores,” described at the end of Chapter 12 of your textbook.
The case involves the decision to locate a new store at one of two candidate sites. The decision will be based on estimates of sales potential, and for this purpose, you will need to develop a multiple regression model to predict sales. Specific case questions are given in the textbook, and the necessary data is in the file named pamsue.xls.
Assuming that you are reasonably comfortable with using Excel and its Analysis ToolPak add-in, you should expect to spend approximately 2-3 hours on computer work, and another 3-4 hours on writing the
To make them easy to read, you may want to format the cells to show numbers with 2 or 3 decimal places.
5. Write down the names of 10 quantitative X variables having the highest correlations with sales. From the correlations worksheet, move to the data worksheet. Select the following columns: sales, plus the 10 quantitative X variables you wrote down, plus comtype1, comptype2, comptype7 (here, you could include up to three more dummy variables, but they are likely to be statistically not significant, so you can save some work - see 2. above). Copy these onto a blank worksheet. Make sure there are no blank columns in within the data range in the new worksheet. Note: To prevent unexpected changes in copying data when formulas are involved, use Paste Special with Values selected when pasting data into a new worksheet.
6. Use Regression under Data Analysis to obtain the regression output table for sales using the variables in the columns you had selected, making sure that Labels and New Worksheet Ply checkboxes are checked, and leave the other boxes unchecked. On the name tab of the output sheet (at the bottom), change the name of the worksheet to Model1.
7. Using appropriate statistics in the regression output table, see if any of the X variables is statistically not significant. If there is at least one insignificant X variable, write down the most insignificant variable, move to the data sheet and delete that column,