Define the=COUNTING 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 =COUNTINGthe minimum in cell EH. Place the maximum in cell EH. 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 GO. C. Mints: Using the built-in Excel function named COUNT IF, calculate the count (frequency) of each outcome (x) in the sample. In general, your function with its arguments will appear as “=COUNTING(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 findi=COUNTINGng 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 Excels built-in SUM function to calculate the total count (frequency).=COUNTING
For example, if the count cells went from H2O:HA, enter the sum in cell HA. 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 12, write a formula to calculate the probability of each outcome, based on the concept of relative frequency. Reference the =COUNTINGcell 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 c=COUNTINGell following the last probability value (from above), use Excels built-in SUM function to calculate the total probability.
=COUNTINGFor example, if the probability cells went from 12:17, enter the su=COUNTINGm in cell 18. Format all the probability values (including the sum of probabilities) in column 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 K, calculate the expected value, that is, find the average number of service calls x) E x P( x) per day. The formula for Expected Value is: E (= To calculate the expected value you should first write a formula in cell K and rag it to K. The formula in cell K should make relative reference to the values in =COUNTINGcell GO and cell 12. F. 0 points: In cell NO, calculate the variance, that is, find the variance for the random variable number of service calls per day. The formula for Variance calculate the variance you will first be required to follow these steps. Provide for=COUNTINGmulas in cells through 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 2 reference to the expected value in cell K, and relative reference to the values in ells GO through GO. Provide formulas in cells MM through MM that square the differences found in cells LO through LO, that is, formulas for [x- E ( x) ] .=COUNTING Provide formulas in cells NO t=COUNTINGhrough NO that multiply the squared differences found in cells MM through MM by the probability values calculated in column l, that is, formulas for [x-E(x)] Calculate the Variance and place the value in cell NO. In cell ONION, calculate the standard deviation. Recall that the formula for the standard deviation is Stated = Vary (x) . G. 2 points: In cell L 13, calculate the probability that Office Support will have two r m=COUNTINGore service calls per day. That is, find P ( X 2 2) . H. 2 points: In cell L 14, calculate the probability that Office Support will have less than two service calls per day.
That is, find P ( X s 1) . Question 2 (15 points) Aster Inc.=COUNTING Produces silicon gaskets that are used to connect piping materials for the petroleum industry. The gaskets are ring shaped, and look like a thin donuts with a big hole in the center. It is important that the gaskets have the proper inside diameter (ID), outside diameter (DO), 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.
=COUNTINGRecently, there has been some concern about the DO of the gaskets. A sample of 100 gasket DO measures was taken and the data is in column B. If the gasket production machine is working properly, the population of gasket DO measures can be reasonably modeled by a Normal distribution with mean DO = 400 mm and standard deviation DO = 2 mm. Use the spreadsheet named Aster. 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=COUNTING indicated cell in column E. . 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 Excels built-in function for the Normal distribution to answer the question, and place the value in cell J. 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 corr=COUNTINGectly; find the probability that a randomly selected gasket is defective.
U=COUNTINGse Excels buil=COUNTINGt-in function for the Normal distribution to answer the question, and place the value in cell J. 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 managem=COUNTINGent team recommended production quantities f 1,500, 1,800=COUNTING, 2,400, and 2,800. The different production quantities reflect considerable disagreement regarding the market potential of the new product.