Statistics: Random Sampling, Bar Charts and Pie Charts, Descriptive Statistics, and Linear Regression

Part 1: Random Sampling

You will need to do a little problem solving and use Excel to generate random numbers to answer the exercises.

You will need to randomly select 20 accounts. Use the guidelines provided below:

You are generating a random number list to impartially (randomly) audit 20 of 74 different records.
Describe how you did this using Excel and why it is important for an auditor to randomly select files to be audited.
Sort the random number output in ascending order and paste your results in the Week 2 Lab Template.

In a separate document linked below, instructions are provided for generating random samples using Excel. For your report, paste your Excel output into the Week 2 Lab Template. Avoid including raw data that is not part of your answer to the questions posed in the Lab.

Random Sampling Instructions (click to open or right-click and download)
Random Sampling Display -- Sample results of random sampling (right-click to download)

Note: There are multiple ways to generate random samples in Excel. This is only one of the methods. After reading the tutorial instructions and using the Data Analysis ToolPak in Excel, you might find that another function in the Toolpak is the Random Number Generator. Feel free to try this method, but the approach shown in the tutorial will allow you to select from a group of records that you have in a table.

Part 2: Bar Charts and Pie Charts

For this part of the lab, you will use the Cereal worksheet. This is located inside the Week 2 Lab Excel file. (Download this file above, or click here.) Using only the fiber variable data from the project cereal data, create a bar chart and a pie chart to represent the data.

Remember that you'll need to make a table from the data first. (See the Week 2 Lab Excel file for such a table.) One table should work for both graphs. Be sure to copy-and-paste your graphs into the Week 2 Lab Template. For examples, refer to the worksheets labeled "bar chart" and "pie chart" in the Exercise Video Sample file.

Exercise Video Sample File -- this file contains sample graphs and tables to provide you with examples of the various graphs that need to be built for this lab. "Right-click" and "save target as ..." to download a copy of the file. Note: This file gives you an idea of how graphs look when created in Excel and also how to set up organized tables of data (frequency distributions and tables of counts).

Part 3: Descriptive Statistics

Read Milk Production of Holstein dairy cows at the end of Chapter 2. Use the data contained in the Milk Production worksheet (from the Week 2 Lab Excel file, linked above or click here to download) to answer the questions below.

Answer the following questions:

Find the sample mean.
Find the sample standard deviation.
Make a frequency distribution for the data. Distribution is started at right.
Create a histogram for the data. Does the data appear bell-shaped?
What true percent of the data lies within one standard deviations of the mean? Within two standard deviations of the mean? How do these results agree with the Empirical Rule?
Find the median of the milk production.
Find the maximum and the minimum value.
What is the range?
What is the first Quartile?
What is the second Quartile?
What is the third Quartile?
What is the 80th Percentile?

The "Descriptive Statistics" worksheet (from the Week 2 Lab Excel file) contains examples of the required Excel functions. Use these examples as a guide to help answer the above questions.

Part 4: Linear Regression

Use the Cereal data (used in Part 2 above) to analyze the relationship between

sugar (x) and calories (y)
sugar (x) and cost (y)
weight (x) and cost (y)
For each of the ordered pairs,

Make a scatter plot.
Insert the 'line of best fit' (regression line) into your scatter plot.
Display the corresponding regression equation on your chart.
Calculate the correlation coefficient using the CORREL function and show this on your scatter plot by inserting this in the text box with the regression equation.
Analyze and interpret the relationship of x and y. for each of the scatter plots.