Statistics: Lab #1,Winter 2008

Quantitative Methods: Lab #1

Excel Graphs and Charts, Descriptive Statistics

You will have two sections for this lab, they are separated below as parts A and B.

This lab will allow you to practice (and learn) some techniques in Excel that allow you to create some descriptive statistics, both graphical and numerical.

Part A: Graphs and Charts

To retrieve your Data.

We will be using the data that accompanies the “Pelican Stores” Case Problem 1 on page 84 of the textbook. Follow the instructions in this lab, but you should read the background of this case in the text.

1. Open an Excel file that you can now save and begin working on.

2. Once you have opened the data, type your name (and any other people in your group) in cell A1 of the first sheet and the date in cell A2.

3. Save your Excel file as "Lab1 Pelican, yourname". If you have more than one person in the group, save it as "Lab 1 Accident, allnames". Save early and often!!!

I. Shopping patterns based on marital status

Creating a frequency distribution. (Similar steps are found at the end of Ch. 2 of your text and following their examples will help.)

1. The first thing we’re going to do is create a PivotTable. Drag and select all of the observations in columns C through I.

2. Select the Insert menu at the top of the page and then select PivotTable. Choose the option to locate the PivotTable in a new worksheet. You should now have a new worksheet open with a new window of options available to you, likely on the right side of the sheet.

3. In the section titled “Field List”, check the boxes for Marital Status and Method of Payment.

4. Left-click on the Marital Status field and drag down to the smaller window titled “Column Labels”.

5. Left-click on the Method of Payment field and drag down to the smaller window titled “Row Labels”.

6. Left-click on the Marital Status field and drag down to the smaller window titled “∑ Values”. You should now have a table in this worksheet that has the look of a frequency distribution of the method of payment for both married and single customers. (worth a maximum of 1 point)

Creating a column chart.

1. From your PivotTable, select Options under the PivotTable Tools menu at the top of the worksheet. Select PivotChart and you get a window that pops up with many different chart options. Select the first column chart and a nice chart should be created, along with a small window called the PivotChart Filter Pane. Make sure under the Legend Fields (Series) that both Married and Single are selected.

2. At the top of the worksheet click on Move Chart and select New Sheet. The column chart should now appear in its own chart sheet.

3. Select the Layout menu at the top of the worksheet. Add an appropriate title for the chart and one for the vertical axis.

4. Right click on a column and select “Add Data Labels” for both groups of customers. (worth a maximum of 2 points)

Now for a pie chart.

1. Follow step 1 from the previous section except now select a pie chart. In the legend fields, make sure only the Married field is selected.

2. Same as previous section.

3. Adjust the title of the chart.

4. Same as previous section, except when you see the data labels, right click on one of them and select “Format Data Labels”. Under “Label Options”, choose the boxes that say “category name” and “percentages” and “show leader lines”.

5. Repeat for the single customers. (2 pie charts are worth a maximum of 4 points)

Save your files.

Analyzing two variables.

1. You have been asked by the President to analyze the relationship between the recent unemployment rate (in July 2015) in a state and educational attainment (the percentage of residents in 2010 with a Bachelor’s degree or higher) in that state. You have data for all states and the District of Columbia. The data can be found stored in this file.

Save your file as Lab 1 States, yourname.

2. Select the data (not the state names), including the column labels, and create a scatter plot. Your unemployment rate should be on the y-axis.

3. Modify the chart title and axis labels to describe your chart.

4. Right click on the graph and select Move Chart, then select New Sheet. Modify the chart so that its appearance is clear and coherent.

5. Right click on any one of the dots in the scatter plot. Select “Add Trendline” and add a linear trend line. Close the window and a trendline will appear. (scatter plot is worth 2 points)

6. Save your files.

Double-check all of your charts and graphs so that you have labeled everything, charts are not overlapping and the appearance is professional looking.

Questions: Please type your responses and bring to class on the due date. Note: I expect a short paragraph or two for analytical aspects of lab assignments.

· Question: As the analyst for Pelican, does it appear that married and single customers have similar payment methods? Be sure to make at least three specific statistical inferences. (8 points)

· What would you tell the President about how the unemployment rate and educational attainment might be related? Does this make economic sense? Explain. (8 points)

NOTE: You do not need to print your graphs, just responses to above questions. More thoughtful and thorough responses are preferred over the opposite.

Part B: Descriptive Statistics