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.