MAR 5620 In-Class Project 1 – Spring 2005Step 1: Download the U.S. SMSA Per Capita Retail Sales 1992 dataset as a text (.txt) file to your computerStep 2: Import the file to EXCEL- File Open - Filename (text file) Dialog box opens- Original data type: FIXED WIDTH Click Next- Click and drag first vertical arrow to position 35 - Click Next- Click Finish Dataset is in EXCEL formatStep 3: Delete unneeded variables- Highlight cells E1 and F1 - Click Edit Delete Entire Column OK- Repeat for cell C1Step 4: Add variable names- Click Insert - Rows - Give names (e.g. county, retail, income)Step 5: Remove counties with “missing” income- Place the cursor in any cell in columns A-C- Click Data Sort Sort by Income Header Row OK- Highlight cells A2 through A17 (Virginia Counties with “0” income)- Click Edit - Delete Entire Row OK- Re-sort data by County (not necessary, but helpful)Step 6: Obtain a Histogram- In cell F1, type the label Bins- In cells F2-F7, type 2,4,6,8,10,12, respectively- Place cursor in any cell in columns A-C (this needs to be done for most data analysis options)- Click Tools Data Analysis Histogram- Input Range: B1:B830- Bin Range: F1:F7- Click on Labels and Chart Output then OK- Histogram appears on new worksheet, you can adjust size and experiment with graphics optionsStep 7: Obtain a scatterplot of retail sales versus income- EXCEL wants the X (horizontal) variable to the right of the Y (vertical variable)- Highlight Cells B1:B830- Click Edit Copy Cells will have lines flashing around them- Place Cursor in cell D1, click Edit Paste- Highlight cells C1:D830- Click on the Chart Wizard (looks like a bar chart)- Select XY (Scatter) Next (Several times) Finish- You can adjust size and experiment with graphics optionsStep 8: Obtain Descriptive Statistics- Click Tools Data Analysis Descriptive Statistics- Input Range: B1:B830- Click on Labels in first row and Summary Statistics then OKStep 9: Obtain the correlation between Retail Sales and Income- Click Tools Data Analysis Correlation- Input Range: B1:C830- Click on Labels in first row then OKStep 10: Fit a regression equation relating retail sales to income- Click Tools Data Analysis Regression- Input Y Range: B1:B830- Input X Range: C1:C830- Click on Labels, then
View Full Document