Complete Excel Statistics Assignment

CompleteThis-ExcelSkillsDemonstration.xlsx

CoverSheet

Student Excel Skills Demonstration
Please read and follow these instructions carefully:
1	Enter your name here:
2	Go to Each Sheet
3	Enter your Excel formulas to display the results, where appropriate
4	Skip the problems you can't complete and come back to them later.
5	When complete save the workbook locally

1	Compute the Average of the following numbers					1	567	3874	2081	74	-88
2	Name the following numbers as a range "Test1"					84	88	188	881	38	60	12	-38	659
3	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
4	Insert a line Graph in the indicated location
	X Axis (horizontal)		Jan	Feb	Mar	Apr	Jun	Jul	Aug	Sept	Oct	Nov	Dec
	Y Axis (Vertical)		600	650	675	790	821	888	901	1028	1150	1200	1333
5	Create and display the Data Analysis Descriptive Statisics for the following numbers:
	What is the difference in the Standard Deviation of the two columns?
	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
6	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.
7	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

Answer

1	Ten percent of the meals served in Brent's Deli are incorrectly priced.
	What is the probability (using the Binomial calcuations) that out of a sample of 10 meals
		a.	Exactly one is priced wrong?
		b.	Exactly 5 are priced wrong?
2	Mortorcycle 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

Answer

1	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?
2	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

Answer

1	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
2	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.

Answer

1	List the 5 steps in Hypothesis testing
2	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
3	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

Answer