Reference no: EM132317272

Data Analysis for Decision Making Assignment -

This assignment will demonstrate that the student is capable of using Excel to perform a variety of statistical, probabilistic and graphic functions.

Part A - Basics

Q1. Compute the Average of the following numbers 1 567 3874 2081 74 -88

Q2. Name the following numbers as a range "Test1" 84 88 188 881 38 60 12 -38 659

Q3. Place the Square Root of each of these numbers below it 12 89 36 89 10 38 72 690 200.9. User the SQRT() function Answer.

Q4. Insert a line Graph in the indicated location

X Axis (horizontal)

Y Axis (Vertical)

Jan	Feb	Mar	Apr	Jun	Jul	Aug	Sept	Oct	Nov	Dec
600	650	675	790	821	888	901	1028	1150	1200	1333

Q5 Create and display the Data Analysis Descriptive Statisics for the following numbers:

Col 1	Col 2
78	750
79	788
82	901
86	1600
88	1700
89	1800
90	1900
94	2560
98	2800
103	2900
111	3000
128	3050
139	3051
160	3052
190	3053

What is the difference in the Standard Deviation of the two columns?

Q6. In the Answer cell, calculate the value "True" if column 1 has a higher standard deviation, and "False" if column 2 has higher standard deviation. Use the IF function.

Q7. Create a Pie chart for the following data with the segments labelled correctly

Red	40
Blue	21
Green	12
Yellow	9
White	9
Orange	9

Part B - Discrete

Q1. Ten percent of the meals served in Brent's Deli are incorrectly priced. What is the probability (using the Binomial calculations) that out of a sample of 10 meals

a. Exactly one is priced wrong?

b. Exactly 5 are priced wrong?

Q2. Mortor-cycle pass an intersection at a rate of one every ten minutes. According to Poisson calculations, what is the probability I see

a. Exactly 8 motorcycles in one hour.

b. At least 6 motorcycles in one hour.

Part C - Normal

Q1. According to the IRS the mean tax refund for the year 2009 was $3333. The standard deviation of the population of returns is $551, and the amounts follow the normal distribution Using Norm.Dist

a. What percent of the returns are more than $2000?

b. What percent of the returns are more than $5000?

c. What percent of the returns are between $3000 and $5000?

Q2. Create an approximate line graph of this normal distribution with $3333 as the mean. You can make a table of values from which to create the graph.

Part D - Confidence

The daily sales of Burger Baby restaurants follow the normal distribution with a standard deviation of $4000.

a. A sample is taken of 50 restaurants and the average sales were $40,000/day. What is the 99% confidence interval for the average restaurant sales?

b. What is the probability that the average sales are really $36,000 or less per day? Assume the actual population average is $40,000 per day.

Q2. A sample of the sales at 50 Haute Dog restaurants is taken and the sample mean is $30,000 per day with a sample standard deviation of $2000. The population standard deviation is not known.

a. What is the 90% confidence interval for the daily sales at all restaurants?

b. What is the probability that the average sales are really $32,000 or more per day? Assume that the population average is $30,000 per day and the std. Deviation is $2k.

Part E - Hypothesis Testing

Q1. List the 5 steps in Hypothesis testing.

Q2. A tire company makes 5000 tires an hour on average with a known standard deviation of 50.

A sample is taken last hour and 5100 tires were made.

Spell out the null and alternate hypotheses that the mean is different than 5000.

Evaluate the hypothesis (Accept or Reject) at the following significance levels:

a. 0.01

b. 0.05

Q3. Samples of a car company's output is taken and 490 cars are made in a day with a known standard deviation of 16 cars. Spell out the null and alternate hypotheses that the average production of the car company is less than 475 cars. Evaluate the hypothesis (Accept or Reject) at the following significance levels

a. 0.001

b. 0.1

Attachment:- Assignment File.rar