Group Graded Assignment 5

Group Graded Assignment 5 Instructions

Complete the Assignment, name it as GA or GAG GroupXX_Assign5.xlsx (where XX is your Group Number), and upload and submit to the instructor through D2L, using the link named “Group Graded Assignment 5.” 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 GA (Graded Assignment) 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, FORMATTING, OR FUNCTIONALITY OF THE SPREADSHEET UNLESS INSTRUCTED TO DO SO.

Question 1

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.

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

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

3. 9 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.

4. 10 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.
   • 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.

5. 5 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:
6. 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.

6. 14 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 . To calculate the variance you will first be required to follow these steps.

1. 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. The formulas should make absolute reference to the expected value in cell K9, and relative reference to the values in cells G2 through G8.
2. Provide formulas in cells M2 through M8 that square the differences found in cells L2 through L8, that is, formulas for.
3. 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 .
4. Calculate the Variance and place the value in cell N9.
5. In cell N10, calculate the standard deviation. Recall that the formula for the standard deviation is.

7. 2 points: In cell L13, calculate the probability that Office Support will have two or more service calls per day. That is, find.
8. 2 points: In cell L14, calculate the probability that Office Support will have less than two service calls per day. That is, find.

Question 2

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.

1. 3 points: Starting in cell A3, moving down, list all possible values for the number of defective microprocessors (out of 5 sampled).

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

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

4. 4 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).

5. 4 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.

Question 3

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.

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

2. 4 points: Notice that the sample mean you calculated in the previous question was just one of many that could have been obtained. That is, many samples of size 100 could have been taken, and each sample would result in a different (but close) sample mean. As a result, the sample means tend to vary around the grand population mean, as do individual values, but the variance of sample means is smaller. The standard deviation measures variation of individual data values around the population mean, while the standard error measures variation of sample means around the population mean. In cell E13, write a formula that calculates the standard error (SE) based on an assumed process mean of 400 mm, standard deviation of 2 mm, and sample of size 100.

3. 6 points: The population of possible sample means is assumed normally distributed since the underlying sample values were normally distributed. Since this is the case, we can use the Empirical Rule to determine the lower and upper limit (interval) of expected sample means based on samples of size 100. That is, we can use the interval to predict the range of possible values of sample means when the process is working correctly. Use the Empirical Rule to determine the two values between which we expect to see almost all sample means (of size 100) when the process is working. You must write a formula that references the assumed parameters. When we say almost all, we mean 99.7% of data, which is contained between  3 SE of the population mean. Write the formula for the lower limit in cell E19, and for the upper limit in F19. See equation 7.2 on page 320 of our text for guidance.

4. 2 points: Based on comparing the sample mean of the process with your answer to part c, does it appear that the process is working properly? Write the word Yes or No in cell E21.

5. 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.

6. 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.

The post Group Graded Assignment 5 appeared first on My Assignment Online.

Expert Help With This Assignment — On Your Terms