'Tips for Pam and Sue\r'

'Multiple fixation upchuck The is the provided deliverable in week Four. It is the shell study titled â€Å" mend bleak Pam and Susan’s Stores,” draw at the end of Chapter 12 of your textbook. The courtship involves the decision to locate a sassy p atomic number 18ntage at one of dickens vista sites. The decision testament be establish on estimates of gross gross revenue potential, and for this purpose, you will study to develop a multiple turnaround get to predict sales. Specific case questions be given in the textbook, and the infallible data is in the file named pamsue. ls. expect that you are reasonably comfortable with victimisation Excel and its psychoanalysis ToolPak add-in, you should expect to run approximately 2-3 hours on computing device tend, and other(a) 3-4 hours on writing the herald. It is a honorable idea non to wait until the goal day to do the entire task and create verbally the report. Content of the report consi sts of your answers to the case questions, positive computer create(s) to support your answers. cheer keep the entire report †including computer turnouts †at a lower place 8 printed pages.Thus, your write up should be concise, and you need to be selective in deciding which computer outputs to intromit. You fuck occasion your discretion in data formatting your write up, besides employment salutary writing practices and try to make it reckon professional ( more(prenominal) on the report format below). Project Hints and Guidelines It is assumed that you start out doorway to 1. Microsoft Excel with Analysis ToolPak (do NOT usage smellwise retroversion for this project unconstipated if it runs on your computer). 2. information file named pamsue. xls in the infoSets. zip folder.Basic Excel skills you need are the ability to construct histograms and scatterplots, to create dope variables, copy or moving towboats of data in a spreadsheet, and the ability t o use the Correlation and Regression facilities under Data Analysis (available when Analysis ToolPak has been added in). Remember that Analysis ToolPak requires contiguous ranges of data for correlation or arrested development toward the mean. 1. Open the file pamsue. xls. First, drive the chromatography column for sales so that it is the right virtually column (it is now to the right of comtype).If the old sales column remains solely appears empty, offset that column. 2. Obtain a scatterplot of the sales on the vertical axis against comtype on the swimming axis. This will give you a proficient idea of whether disagreeent categories of comtype appear to differ in sales. In the scatterplot, you should see that sales in the middle categories 3 †6 are in similar ranges on the vertical axis, but 1 and 2 excite somewhat higher sales, and social class 7 appears to mother somewhat glare sales.This implies that, when you create dummy variables for comtype, dummy variables f or categories 1, 2, 7 are promising to be statistically significant in the multiple lapse model (and dummy variables for categories 3 †6 are likely to be not significant). Although it would be desirable to also attain the scatterplot of sales against all(prenominal) other X variable, you can omit these if you do not have time, and use the correlation coefficients kinda (see step 4 below). 3. Insert heptad new columns immediately to the left of comtype, and in these columns, create seven dummy variables to interpret 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 fail two columns. 4. give the Correlation facility under Data Analysis to hold in the correlation coefficients among sales and all of the other variables besides store and comtype (why exclude comtype? ). This will call forth a matrix of correlation coefficients amid sales and ev ery X variable, as well as between every pair of X variables. To make them gentle to read, you may want to format the cells to march numbers with 2 or 3 decimal places. . Write down the name calling of 10 quantitative X variables having the highest correlations with sales. From the correlations movementsheet, discover to the data worksheet. Select the following columns: sales, positively charged the 10 quantitative X variables you wrote down, plus comtype1, comptype2, comptype7 (here, you could embarrass 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 caisson worksheet. Make sure there are no blank columns in indoors the data range in the new worksheet.Note: To prevent unexpected changes in copying data when formulas are involved, use bed cover Special with Values selected when pasting data into a new worksheet. 6. Use Regression under Data Analysis to obtain the regr ession output table for sales apply 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. victimization 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(prenominal) 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 bring the final exam regression compare for predicting sales. Re-run the last model, but this time checking the Residuals checkbox.This wi ll 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 only completed all necessary computer work for your project report. Now you have to write a report to infix your answers to the case questions (see pages 388-389 of your textbook), and the reasons for those answers.In terms of physical organization, a reasonable format for the report is cited below. Content and Format of the Project give notice (of) 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. Introduction unrivaled paragraph (two at most) describing the subject and mise en scene of the project. Data One or two paragraphs describin g the data in plain side (number of variables, number of observations, units for data values, etc. ) Results and sermon 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 for your answers. These reasons should be based on the analytical work you have done using Excel. Depending on how concisely you write and how many tables and graphs you entangle, this page could be 3-4 pages long. Conclusion One or two paragraphs discussing any remaining issues (e. g. shortcomings and assertable 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 bant am 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 duration of the report under 8 printed pages (5 to 6 pages should be sufficient in most cases).\r\n'