Name:

Stat 351 Project #2

In this project, you are given three problems, all involving Excel calculations. Please use this document as a shell for your answers. This project must be done individually. Turn in a printed copy of your answer sheet during discussion session the week of November 1st. Late projects will be deducted points.

Problem 1 (18 points)

Suppose that 20% of all copies of a particular textbook fail a certain binding strength test. Let X denote the number among 15 randomly selected copies that fail the test.

  1. Does this experiment classify as a binomial experiment? Check the 4 requirements. (2 points)

Yes, it does. The trials are independent, the probability remains constant, there are a set number of trials, and there are only two possibilities (fail/pass).

  1. Provide a table with the probability distribution of X. It should show the possible values and corresponding probabilities. (8 points)

X / P(X) / Excel Formula Used
0 / 0.035184372 / =BINOMDIST(0,15,0.2,FALSE)
1 / 0.131941395 / =BINOMDIST(1,15,0.2,FALSE)
2 / 0.230897442 / =BINOMDIST(2,15,0.2,FALSE)
3 / 0.250138895 / =BINOMDIST(3,15,0.2,FALSE)
4 / 0.187604171 / =BINOMDIST(4,15,0.2,FALSE)
5 / 0.103182294 / =BINOMDIST(5,15,0.2,FALSE)
6 / 0.042992623 / =BINOMDIST(6,15,0.2,FALSE)
7 / 0.013819057 / =BINOMDIST(7,15,0.2,FALSE)
8 / 0.003454764 / =BINOMDIST(8,15,0.2,FALSE)
9 / 0.00067176 / =BINOMDIST(9,15,0.2,FALSE)
10 / 0.000100764 / =BINOMDIST(10,15,0.2,FALSE)
11 / 1.14504E-05 / =BINOMDIST(11,15,0.2,FALSE)
12 / 9.54204E-07 / =BINOMDIST(12,15,0.2,FALSE)
13 / 5.50502E-08 / =BINOMDIST(13,15,0.2,FALSE)
14 / 1.96608E-09 / =BINOMDIST(14,15,0.2,FALSE)
15 / 3.2768E-11 / =BINOMDIST(15,15,0.2,FALSE)
  1. What is the probability that at most 8 fail the test? (2 points)

Answer
P(at most 8 fail) / 0.99922
  1. What is the probability that exactly 8 fail? (2 points)

Answer
P(exactly 8 fail) / 0.00345
  1. What is the probability that at least 8 fail? (2 points)

Answer
P(at least 8 fail) / 0.00424
  1. What is the expected value of X? (2 points)

Answer
E(X) / 3

Problem 2 (12 points)

Calls come into a call center at an average rate of 15 calls every 10 minutes.

  1. What is the probability that, during a 10 minute interval, the call center will receive:
  2. exactly 12 calls? (2 points)

Excel Formula Used / Answer
=POISSON(12,15,FALSE) / 0.0829
  1. fewer than 6 calls? (2 points)

Excel Formula Used / Answer
=POISSON(5,15,TRUE) / 0.0028
  1. more than 10 calls? (2 points)

Excel Formula Used / Answer
=1-POISSON(10,15,TRUE) / 0.8815
  1. What is the probability that 3 minutes will pass with no incoming calls? (4 points)

Excel Formula Used / Answer
=POISSON(0,15*3/10,FALSE) / 0.0111
  1. Let X equal the number of calls coming into the call center in a 10 minute interval. What is the variance of X? (2 points)

Answer
Var(X) / X

Problem 3(20 points)

In a call center, a team of associates forecast the number of calls that each department (Sales, Service, and Claims) will receive on a monthly basis. At the end of the year, the head of the department rates the team on the accuracy of their forecast (1 being the least accurate and 5 being the most accurate). Shown in the following table are the probabilities for each score in each department.

Score / Accuracy / Sales / Service / Claims
1 / 6% / 0.02 / 0.02 / 0.01
2 / 5 – 6% / 0.06 / 0.04 / 0.02
3 / 4 – 5% / 0.18 / 0.06 / 0.06
4 / 3 – 4% / 0.58 / 0.73 / 0.30
5 / < 3% / 0.16 / 0.15 / 0.61
  1. Are the probability distributions for each of the departments valid? Explain. (3 points)

Yes, they are valid, since they each add up to 1. There are no negative values, and no values greater than 1.

  1. For Sales, what is the probability that they score at least 4? (2 points)

Answer
P(score is at least 4) / 0.74
  1. For Claims, what is the probability that they score less than 3? (2 points)

Answer
P(score is less than 3) / 0.03
  1. What is the expected score for Sales? Service? Claims? (3 points)

Answer
E(Score for Sales) / 3.8
E(Score for Service) / 3.95
E(Score for Claims) / 4.48
  1. What is the variance of the scores for Sales? Service? Claims? (10 points)

SALES
x / p(x) / x-µ / (x- µ)2 / (x- µ)2p(x)
1 / 0.02 / -2.8 / 7.84 / 0.1568
2 / 0.06 / -1.8 / 3.24 / 0.1944
3 / 0.18 / -0.8 / 0.64 / 0.1152
4 / 0.58 / 0.2 / 0.04 / 0.0232
5 / 0.16 / 1.2 / 1.44 / 0.2304
SERVICE
x / p(x) / x-µ / (x- µ)2 / (x- µ)2p(x)
1 / 0.02 / -2.95 / 8.7025 / 0.17405
2 / 0.04 / -1.95 / 3.8025 / 0.1521
3 / 0.06 / -0.95 / 0.9025 / 0.05415
4 / 0.73 / 0.05 / 0.0025 / 0.001825
5 / 0.15 / 1.05 / 1.1025 / 0.165375
CLAIMS
x / p(x) / x-µ / (x- µ)2 / (x- µ)2p(x)
1 / 0.01 / -3.48 / 12.1104 / 0.121104
2 / 0.02 / -2.48 / 6.1504 / 0.123008
3 / 0.06 / -1.48 / 2.1904 / 0.131424
4 / 0.30 / -0.48 / 0.2304 / 0.06912
5 / 0.61 / 0.52 / 0.2704 / 0.164944
Answer
Var(Score for Sales) / 0.72
Var(Score for Service) / 0.5475
Var(Score for Claims) / 0.6096