Excel Statistics Assignment

Instructions:
Perform all computations in Excel. Submit the completed Excel file for grading. For
the normal probability questions, you can use the Z table, or you can use the NORM.DIST()
function. See Normal Probability Example.xls on the Lecture Slides and Spreadsheets page of
LM 2 for an example of how to use this function.
Scenario 1
You are staffing a security detail for a large manufacturing plant. Absenteeism is an ongoing
problem. (Don’t blame the staff. You don’t pay very well, and since you started your master’s
degree while still working full time, you have been somewhat irritable. ) The schedule has five
employees designated for duty today. Based on history, the probability that everyone will show
up is 65%. The probability that only 4 will show up is 22%, the probability that 3 will show up is
9%, and the probability that 2 will show up is 4%.
1. What is the probability that 3 or 4 employees will show up?
2. What is the probability that there will be only 1 employee on duty?
3. What is the probability that no more than four employees will show up?
Scenario 2
The armory has four AR15s, three Glock 9s, two shotguns, and three 45 caliber revolvers. Define
A as semi-automatic weapons, define B as handguns, and define C as long guns.
4. If you are randomly issued a weapon, what are the probabilities associated with drawing each
type of gun?
5. If you are randomly issued a weapon, what is the probability it will be a semi-automatic
weapon? Hint: P(A)?
6. If you are randomly issued a weapon, what is the probability that it not a semi-automatic
weapon?
7. If you are randomly issued a weapon, what is the probability it will be a semi-automatic
weapon or a long gun? Hint: P(AC)
Scenario 3:
You are in charge of cybersecurity at your firm. An analyst for your firm determined that the
number of daily attacks on your network are normally distributed with a mean of 345 and a
standard deviation of 68. Note: I put a nice normal table in the File Cabinet for this exercise.
Also, remember that you have to compute the Z score = (X – )/. Then, you use the Z score to
find the probability. If the probability is for less than, you simply read the probability off the table.
If the probability is for greater than, you take the complement of the table probability. Easy!!
8. What is the probability that you will have over 400 attacks today?
9. What is the probability that you will have less than 345 attacks tod