Assignment:
Discuss the folloiwng:
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.
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
You will use the Cereal worksheet.The file is given in attachment Using only the fiber variable data from the project cereal data, create a bar chart and a pie chart to represent the data.
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 given in the attachment 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.
Attachment:- Milk Production.rar