Tips for Pam and Sue

Multiple Regression forge The is the and deliverable in Week Four. It is the lineament study titled hole New Pam and Susans Stores, lined at the end of Chapter 12 of your textbook. The case involves the decision to locate a radical store at genius of both candidate sites. The decision willing be based on estimates of sales potential, and for this purpose, you will need to develop a multiple relapse determine to predict sales. Specific case questions be given in the textbook, and the necessary in goation is in the shoot down named pamsue. ls. Assuming that you are clean comfortable with using Excel and its Analysis ToolPak add-in, you should expect to spend somewhat 2-3 hours on ready reckoner mold, and an other(a) 3-4 hours on writing the account. It is a sincere idea not to wait until the last day to do the constitutional project and import the report. subject matter of the report consists of your answers to the case questions, plus computer proceeds(s) to support your answers. disport keep the correct report including computer outputs low 8 printed pages.Thus, your write up should be concise, and you need to be discriminating in deciding which computer outputs to embroil. You can subroutine your discretion in formatting your write up, save workout good writing practices and strain to make it look professional (more on the report format below). Project Hints and Guidelines It is assumed that you have access to 1. Microsoft Excel with Analysis ToolPak (do NOT use tone of voicewise regression for this project even if it runs on your computer). 2. selective information file named pamsue. xls in the DataSets. zip folder.Basic Excel skills you need are the ability to constrain histograms and scatterplots, to create dummy variables, copying or moving editorials of data in a spreadsheet, and the ability to use the Correlation and Regression facilities under Data Analysis (available when Analysis ToolPak has been added in). Rem ember that Analysis ToolPak requires contiguous set offs of data for coefficient of correlation or regression. 1. Open the file pamsue. xls. First, move the column for sales so that it is the rightmost column (it is now to the right of comtype).If the old sales column remains but appears empty, delete that column. 2. Obtain a scatterplot of the sales on the vertical axis against comtype on the horizontal axis. This will give you a good idea of whether different categories of comtype appear to differ in sales. In the scatterplot, you should advert that sales in the middle categories 3 6 are in similar ranges on the vertical axis, but 1 and 2 have somewhat higher sales, and category 7 appears to have somewhat lower sales.This implies that, when you create dummy variables for comtype, dummy variables for categories 1, 2, 7 are credibly to be statistic aloney significant in the multiple regression model (and dummy variables for categories 3 6 are likely to be not significant). Alt hough it would be desirable to also contain the scatterplot of sales against every other X variable, you can omit these if you do not have time, and use the correlation coefficients instead (see step 4 below). 3. Insert seven new columns immediately to the left of comtype, and in these columns, create seven dummy variables to tally the seven categories of site types.Name them comtype1, comtype2, , comtype7. At this point, you have 40 columns of data in the spreadsheet with comtype and sales in the last two columns. 4. lend oneself the Correlation speediness under Data Analysis to obtain the correlation coefficients between sales and all of the other variables except store and comtype (why exclude comtype? ). This will pay back a matrix of correlation coefficients between sales and every X variable, as well as between every pair of X variables. To make them easy to read, you may want to format the cells to show total with 2 or 3 decimal places. . Write down the label of 10 qu antitative X variables having the highest correlations with sales. From the correlations worksheet, move to the data worksheet. Select the pursuit columns sales, plus the 10 quantitative X variables you wrote down, plus comtype1, comptype2, comptype7 (here, you could imply 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 on that point 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, make 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), chang e the name of the worksheet to Model1. 7. exploitation 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, and re-run Regression without that variable. Repeat until there are no insignificant X variables. Name each output sheet Model2, Model3, and so on for easy identification. 8. When you get to a model in which all remaining X variables are statistically significant, you will have launch the final regression equation for predicting sales. Re-run the last model, but this time checking the Residuals checkbox.This will reproduce the last regression table, but below it, you will see columns for Predicted sales and Residuals. Obtain a scatterplot of Residuals against Predicted sales. Also obtain a histogram of Residuals. 9. Use the final regression equation you found in the last step to predict sales at the two sites under consideration. You have dear completed all necessary computer work for your project report. straight you have to write a report to present your answers to the case questions (see pages 388-389 of your textbook), and the reasons for those answers.In name of physical organization, a reasonable format for the report is described below. Content and Format of the Project Report Cover page Include the report title, your name, course, section, facilitator, and date. Go to a new page, and use the following subsection headings for the report. insane asylum maven paragraph (two at most) describing the subject and context of the project. Data One or two paragraphs describing the data in plain English ( be of variables, number of observations, units for data values, etc. ) Results and Discussion This is the main body of the report.It is where you will describe what you have done, what you found, and answer the case questions with the reasons fo r your answers. These reasons should be based on the uninflected work you have done using Excel. Depending on how concisely you write and how many tables and graphs you include, this page could be 3-4 pages long. Conclusion One or two paragraphs discussing any remaining issues (e. g. shortcomings and possible improvements of the analyses in the report). In the Results and Discussion section, you should include a few informative tables or graphs derived from your computer analyses.DO NOT include anything that is not absolutely necessary. DO NOT include entire worksheets form Excel, but only the parts you need. For example, do not include the entire correlation matrix found in step 4 above, but you can make a small table to show the 10 variables having the highest correlations with sales. You should include the scatterplot of sales against comtype, relevant portion of the final regression output table, the final regression equation, and the two residual graphs you obtained in step 8. Please keep the total length of the report under 8 printed pages (5 to 6 pages should be sufficient in most cases).