Probs and stat using excel functions and formula

1
M2 Group Assignment Instructions Complete the Assignment, name it as GroupXX_Assign2.xls (where XX is your Group Name), and upload and submit to the instructor through Dropbox. Do not enter anything in the
spreadsheet cells that are black, labeled “Grader”. You must complete this assignment without the assistance of persons other than the members of your Group. You may use any other resources you deem necessary. Answer the questions below by placing the appropriate graph and/or answers in the designated cells of the spreadsheet.
DO NOT CHANGE THE APPEARANCE OR FUNCTIONALITY OF THE
SPREADSHEET UNLESS INSTRUCTED TO DO SO.
Question 1 (35 points)
Office Support, Inc. provides on-site repair for most large photocopy machines. It currently has five trained repair teams that it sends out on an on-call basis. Since the company advertises one-day service, it will not accept more than five requests for service per day. Two months ago, the vice president started considering expanding the workforce. At that time he asked the call desk to record the actual calls for each of the next 40 days. The data to respond to the questions below are provided in the Office
worksheet. Define the random variable x as the number of service calls per day. Clearly x is a discrete
random variable.
a. 3 points: Use built-in Excel functions to find the minimum and maximum values of x. That is,
find the minimum number and maximum number of service calls per day over the 40 day period.
• Place the minimum in cell E2.
• Place the maximum in cell E3.
b. 3 points: Based on the minimum and maximum number of service calls per day in the sample of
40 days, specify the complete range of x. That is, make a list of all possible outcomes of x under
the column labeled x starting in cell G2.
c. 5 points: Using the built-in Excel function named COUNTIF, calculate the count (frequency) of
each outcome (x) in the sample. In general, your function with its arguments will appear as
“=COUNTIF(argument 1, argument 2),” where argument 1 is the data range and argument 2 is a
cell reference containing a specific outcome value. Start by finding the count for x = 0, then
finding the count for all other outcomes. The values will be under the column labeled “Count.”
• In the first unused cell following the last count value (from above), use Excel’s built-in
SUM function to calculate the total count (frequency). For example, if the count cells
went from H2:H7, enter the sum in cell H8. Format the sum cell (box, color, etc.) to
highlight that it contains the sum of the values above it.
d. 6 points: Beginning in cell I2, write a formula to calculate the probability of each outcome, based
on the concept of relative frequency. Reference the cell containing the sum of counts (from
above) as an absolute reference in your formula, but reference the cell containing the count as a
relative reference.
• In the first unused cell following the last probability value (from above), use Excel’s
built-in SUM function to calculate the total probability. For example, if the probability
cells went from I2:I7, enter the sum in cell I8. 2
• Format all the probability values (including the sum of probabilities) in column I using 3
decimal places. Format the sum cell (box, color, etc.) to highlight that it contains the
sum of the values above it.
e. 4 points: In cell K9, calculate the expected value, that is, find the average number of service calls
per day. The formula for Expected Value is: E x x P x ( ) ( ) = ⋅ ∑
• To calculate the expected value you should first write a formula in cell K2 and drag it to K8.
The formula in cell K2 should make relative reference to the values in cell G2 and cell I2.
f. 10 points: In cell N9, calculate the variance, that is, find the variance for the random variable
number of service calls per day. The formula for Variance is Var x ( ) = ∑[x E x − ( )]2 ⋅ P x ( ) . To
calculate the variance you will first be required to follow these steps.
• Provide formulas in cells L2 through L8 that find the difference in each value of x and the
expected value, that is, a formula for[x E x − ( )] . The formulas should make absolute
reference to the expected value in cell K9, and relative reference to the values in cells G2
through G8.
• Provide formulas in cells M2 through M8 that square the differences found in cells L2
through L8, that is, formulas for[x E x − ( )]2 .
• Provide formulas in cells N2 through N8 that multiply the squared differences found in cells
M2 through M8 by the probability values calculated in column I, that is, formulas for
[x E x − ( )]2 ⋅ P x ( ) .
• Calculate the Variance and place the value in cell N9.
• In cell N10, calculate the standard deviation. Recall that the formula for the standard
deviation is StdDev Var x = ( ) .
g. 2 points: In cell L13, calculate the probability that Office Support will have two or more service
calls per day. That is, find P X ( 2) ≥ .
h. 2 points: In cell L14, calculate the probability that Office Support will have less than two service
calls per day. That is, find P X ( 1) ≤ .
Question 2 (15 points)
Asterex Inc. produces silicon gaskets that are used to connect piping materials for the petroleum
industry. The gaskets are ring shaped, and look like a thin donut with a big hole in the center. It is
important that the gaskets have the proper inside diameter (ID), outside diameter (OD), and wall
thickness. The quality control department samples and tests gaskets every 15 minutes to ensure
conformance to quality characteristics and engineering specifications for the three quality dimensions.
Recently, there has been some concern about the OD of the gaskets. A sample of 100 gasket OD
measures was taken and the data is in column B. If the gasket production machine is working
properly, the population of gasket OD measures can be reasonably modeled by a Normal distribution
with mean OD = 400 mm and standard deviation OD = 2 mm. Use the spreadsheet named Asterex.
a. 5 points: Find the values for the sample statistics indicated in column D. Use a built-in Excel
function or formula when appropriate. Place the appropriate function or formula for each
statistics in the indicated cell in column E. 3
b. 5 points: The engineering specifications provide that a gasket should be between 395 mm and
405 mm, otherwise a gasket is defective. Assuming the process is working correctly; find the
probability that a randomly selected gasket is not defective. Use Excel’s built-in function for the
Normal distribution to answer the question, and place the value in cell J2.
c. 5 points: The engineering specifications provide that a gasket should be between 395 mm and
405 mm, otherwise a gasket is defective. Assuming the process is working correctly; find the
probability that a randomly selected gasket is defective. Use Excel’s built-in function for the
Normal distribution to answer the question, and place the value in cell J4.
Question 3 (35 points)
For the coming season, Savannah Bee Company plans to introduce a new product called Orange Blossom
Honey. Savannah Bee faces the decision of how many units of Orange Blossom Honey to produce for the
coming holiday season.
Members of the management team recommended production quantities of 1,500, 1,800, 2,400, and 2,800.
The different production quantities reflect considerable disagreement regarding the market potential of the
new product. The product management team has contracted you for an analysis of the stock out
probabilities for various production quantities, an estimate of the profit potential, and to help make a
production quantity recommendation.
Savannah Bee expects to sell Orange Blossom Honey for $20, and the cost is $11 per unit. If inventory
remains after the holiday season, Savannah Bee will sell all surplus inventory for $10 per unit. After
reviewing the sales history of similar products, Savannah Bee’s senior sales forecaster predicted an
expected demand of 2,000 units with a 0.9 probability that demand would be between 1,000 units and
3,000 units.
a. 6 points: Please use sales forecaster’s prediction to describe a normal probability distribution that
can be used to approximate the demand distribution. Compute the normal distribution’s standard
deviation. (Place the answer in Cell C2 and the formula(s) in Cells G2:K2)
b. 6 points: Once you have approximated the demand using a normal distribution, please compute
the probabilities of a stock out for the production quantities suggested by members of the
management team. (Place the answers in Cells C7:C10)
c. 16 points: Assuming three cases scenarios (i.e., worse case with a sales quantity of 1,000 units;
most likely case with a sales quantity of 2,000 units; and best case with a sales quantity of 3,000
units), please figure out the projected profit for the production quantities suggested by the
management team. Complete the tables in Cells B13: J39 by writing formulas and using Excel
functions when necessary.
1,000 2,000 3,000 4
d. 7 points: One of the managers felt that the profit potential was so great that the production
quantity should have a 90% chance of meeting demand and only a 10% chance of any stock-out.
What quantity would be produced under this policy? (Place your answer in Cell C44)
Question 4 (15 points)
Gateway 2000 Inc. receives large shipments of microprocessors from Intel Corp. It must try to ensure
that the proportion of microprocessors that are defective is small. Suppose Gateway samples and tests 5
microprocessors out of a shipment of thousands of these microprocessors. Suppose also that if at least 1
of the microprocessors is defective, the shipment is returned. This sampling and inspection scheme can
be modeled as a Binomial process with parameters n and p. Define x = the number of defective
microprocessors out of 5 sampled and inspected. Use the spreadsheet named Gateway.
a. 2 points: Starting in cell A3, moving down, list all possible values for the number of defective
microprocessors (out of 5 sampled).
b. 6 points: Suppose that Intel Corp.’s shipment contains 10% defective microprocessors. Use
Excel’s built-in function for the Binomial distribution to calculate the probability for each
outcome you listed in column A. Start the probability calculations in cell B3 and move down.
Also, show that you ensured that the sum of the probabilities of all possible outcomes is 1.
c. 1 points: Again, suppose that Intel Corp.’s shipment contains 10% defective microprocessors. In
cell C11, find the average number of defectives we expect in a sample of 5 microprocessors.
d. 3 points: Again, suppose that Intel Corp.’s shipment contains 10% defective microprocessors. In
cell C13, provide the probability that the entire shipment will be returned (assuming 10% defect
rate and 5 microprocessors sampled).
e. 3 points: In cell C15, calculate the probability that the entire shipment will be kept by Gateway
even though the shipment has 10% defective microprocessors assuming 5 microprocessors are
sampled.