Statistics: Lab #1

Excel Graphs and Charts, Descriptive Statistics

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

Today�s Lab will allow you to practice (and learn) some techniques in Excel that will allow you to create some descriptive statistics, both graphical and numerical.

Part A: Graphs and Charts

Excel contains a feature called the Chart Wizard that walks a user through the process of creating charts and graphs from a data set.

� To retrieve your Data.

1.� In a taste test, 250 people were asked which of 5 light beers they preferred.� The beers were labeled 1,2,3,4, and 5.� The data are stored at TasteTest in the range A4:A253.� To download this (or any) file, right-click on the file name and select Save Target As (Internet Explorer) or Save Link As (Netscape Navigator).

Or, by clicking on the above link, you will open an Excel file that you can now save to your floppy disk.

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 today�s date in cell A2.

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

� Creating a bar/column/pie chart. (Similar steps are found at the end of Ch. 2 of your text and following their examples will help.)

1.      You need to count how many people responded to each brand of light beer.� Somewhere at the top of the worksheet create a label called �Brand� and in the very next column to the right, �Frequency�.

2.      In the five rows below the �Brand� label, put the numbers 1,2,3,4,5.

3.      To count the # of times consumers responded that they preferred brand 1 (for example), you need to use the COUNTIF command.� In the cell immediately below �Frequency�, you�ll put your first COUNTIF command.� Type =countif(the range of the data,1).� This will look through all 250 responses and count up every response for brand #1.�

4.      Construct similar commands in the 4 cells below to count the number of responses for brand 2,3, 4, and 5.

5.      In the next column to the right, labeled �Relative Frequency�, construct formulas to calculate the relative frequency of each brand.�

6.      Highlight just the �Frequency� column of the table, including the column label "Frequency". Now enter the Chart Wizard.

7.      Step 1: Select the Column chart and the first sub-type, the Clustered Column. Click Next.

8.      Step 2: You should see that your data has been sorted by type of brand.

9.      Step 3: Go to the "Data Labels" tab and select "Show value" option. See how this modifies your column chart. Give the entire chart and axes reasonable titles.

10.  Step 4: Select the option of "As object in Sheet 1" so your finished chart appears right next to your column of data. Click Finish.

  • Now do the same with a pie chart.
  1. Modify a pie chart so that it appears as a 3-dimensional pie chart with labels on each slice of the pie, showing the % of responses accounted for by each brand, as well as the brand number.
  2. You may need to move one or both charts so that they do not overlap each other.
  3. Save your files.

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.

1.      Light Beer Taste Test:� As the chief marketing analyst in charge of conducting this taste test, what can you tell the manufacturers of these light beers?� How should the grocery stores allocate shelf space?� Be sure to make at least two specific statistical inferences.� (6 points)

� Analyzing two variables.

1. The economics department of a national investment banking firm is conducting a study to determine how housing sales are related to mortgage rate levels.� The number of houses started and sold and the average monthly mortgage rate for 36 months are stored in the file Housing.� Save your file as �Lab 1 Housing, yourname�.

2. Select the data, including the column labels, and enter the Chart Wizard.

3. Choose a scatter plot in step 1.

4. Modify the chart title and axis labels to describe your chart. Your interest rate should be on the X-axis.

5. Insert this chart into a separate chart sheet and modify the chart so that its appearance is coherent.

6. Save your files.

.

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.

What would you tell the investment bank about how the mortgage rate affects housing sales?� Does this make economic sense?� Explain.� (5 points)

Double-check all 3 of your charts and graphs so that you have labeled everything, charts are not overlapping and the appearance is professional looking.�� Maximum 3 points for each of the above 3 charts = 9 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

In this section you will be using Microsoft Excel to analyze the monthly bills of new subscribers to a long distance telephone company.� The data represent the amount of money spent in the first month after signing on with the company.� A sample of 200 new residential customers was taken.� �

� To retrieve your Data.

1. The following file "Long Distance� includes data for use in this lab assignment. To download this (or any) file, right-click on the file name and select Save Target As (Internet Explorer) or Save Link As (Netscape Navigator).

Or, by clicking on the above link, you will open an Excel file that you can now save to your disk.

2. Once you have opened the data, type your name in cell A1 and today�s date in A2.

3. Save your Excel file as "Lab1 Phone, yourname".

� Obtaining Numerical Descriptive Statistics in Excel. (Similar steps are found at the end of Chapter 3 of your text)

1. Select the Tools pull-down menu.

2. Select Data Analysis option. The lack of this option means a tool pack hasn�t been loaded but it�s fairly easy to load.� Go to the Tools menu, choose Add-Ins and select the box that says Analysis ToolPak and then click OK.� If you are working in a campus lab, and this option is not available to you, you need to contact Neil Reeves (6835) in Academic Computing and he will install it.� If you are working on your own computer, you may need to reinstall Excel with your original CD.�

a. Your input range is the column of data. Your data is in the range A3:A203.

b. Make sure the option of "Columns" is selected.

c. Make sure the option of "Labels in first row" is selected.

d. Select Output range to tell Excel where to print your results. Enter D1.

e. Click the box that says "Summary Statistics"

f. Finally, click OK to perform the analysis.

3. Your D and E columns may be too narrow to read the entire text of the output. To widen the columns, highlight the entire box of output with your mouse. Then go to the Format pull-down menu, highlight Columns, then Auto Fit selection.� Accurately producing the descriptive statistics is worth 2 points.

� Save your file.

� Frequency Distribution and Histogram

1. You need to decide how many classes you will use, how wide each class will be, and what will be the lower and upper limits.

2. In cell F1 type the word "Bin". This is Excel's way of identifying the upper limit of each class.

3. Now choose your parameters. For example, if you were going to choose the first class to range from $0 to $5 you would enter 5 in cell F2, 10 in F3 and so on. Continue entering bin values until you have all of your upper limits for all classes of data.

4. Select the Tools pull down menu.

a. Choose the Data Analysis option

b. Choose Histogram

c. Your input range is your data. �Again don�t forget to check the box that says "Labels".

d. Your bin range is what you did in the F column, including the cell that says "Bin".

e. Choose G2 as your output range. Check the box that says "Chart Output".

f. Click OK.� Feel free to revise the histogram by making different choices in step 1.

Creation of a histogram with a reasonable number of classes and range sizes, and given those choices the output is accurate, is worth a maximum of 2 points.

Finally, use your statistical output to write a brief report that addresses the following:

  • What can you say about the distribution of long distance spending for the new customers? �Does it appear to be bell-shaped?� Explain.� (2 points)
  • Make three specific statistical inferences to discuss this distribution to your audience. (3 points)
  • Focusing on those that we have covered in class, discuss the measures of location and dispersion. With regards to this data, what do they each mean? (6 points)
  • For the moment we can assume that, for the population, long distance spending in the first month is normally distributed.� Explain how you would use the Empirical Rule to look for outliers. Using the sample data, are there any outliers?� How high would the long distance bill need to be to be considered a statistical outlier?� (5 points)

OK, you�ve carefully completed both parts A and B to this lab. You�ve double-checked that you have your typed responses to questions. Bring the typed responses to class on the due date. Your emailed Excel files must also be sent by the beginning of class on the due date. When you email your file, make sure your name is in the filename. Late assignments or those sent incorrectly will be penalized. Check the syllabus or ask for help if you�re uncertain of how to do any of this.