The purpose of the assignment is to gather the information needed to set up the problem, run the simulation, and interpret the results to solve the problem.Complete Chapter 12 Problems 2, 3a, 4, 6a, 6

The purpose of the assignment is to gather the information needed to set up the problem, run the simulation, and interpret the results to solve the problem.

Complete Chapter 12 Problems 2, 3a, 4, 6a, 6c, 10, 27a, 27c, and 27d from the textbook, using the student data files provided in the Course Materials, as directed.

Complete the assignment using the Analytic Solver Platform. Unless otherwise stated, run simulations with 1,000 trials.

Submit the Excel outputs required for each problem. Note that Excel files are to include all functions and formulas used to generate the problem solutions.

Submit a narrative summary in a Word document of the results of running each simulation.

2 The graph of the probability distribution of a normally distributed random variable with a mean of 20 and standard deviation of 1.5 is shown in Figure 12.5. The Excel function: =NORMINV(Rand( ),20,1.5)also returns randomly generated observations from this distribution.

Mean = 20

Standard deviation = 1.5

a. Use Excel’s NORMINV( ) function to generate 100 sample values from this distribution.

b. Produce a histogram of the 100 sample values you generated. Does your histogram look like the graph for this distribution in Figure 12.5?

c. Repeat this experiment, with 1,000 sample values.

d. Produce a histogram for the 1,000 sample values you generated. Does the histogram now more closely resemble the graph in Figure 12.5 for this distribution?

e. Why does your second histogram look more “normal” than the first one

3. Refer to the Hungry Dawg Restaurant example presented in this chapter. Health claim costs actually tend to be seasonal, with higher levels of claims occurring during the summer months (when kids are out of school and more likely to injure themselves) and during December (when people schedule elective procedures before the next year’s deductible must be paid). The following table summarizes the seasonal adjustment factors that apply to RNGs for average claims in the Hungry Dawg problem. For instance, the average claim for month 6 should be multiplied by 115%, and claims for month 1 should be multiplied by 80%.

Month 1 2 3 4 5 6 7 8 9 10 11 12

Seasonal Factor 0.80 0.85 0.87 0.92 0.93 1.15 1.20 1.18 1.03 0.95 0.98 1.14

Suppose the company maintains an account from which it pays health insurance claims. Assume there is $2.5 million in the account at the beginning of month 1. Each month, employee contributions are deposited into this account and claims are paid from the account.

1. Modify the spreadsheet shown in Figure 12.9 to include the cash flows in this account. If the company deposits $3 million in this account every month, what is the probability that the account will have insufficient funds to pay claims at some point during the year? (Hint: You can use the COUNTIF( ) function to count the number of months in a year in which the ending balance in the ac-count is below 0.)

4. One of the examples in this chapter dealt with determining the optimal reorder point for a computer monitor sold by Millennium Computer Corp. Suppose that it costs MCC $0.30 per day in holding costs for each monitor in beginning inventory, and it costs $20 to place an order. Each monitor sold generates a profit of $45, and each lost sale results in an opportunity cost of $65 (including the lost profit of $45 and $20 in lost goodwill). Modify the spreadsheet shown in Figure 12.23 to determine the reorder point and order quantity that maximize the average monthly profit associated with this monitor.

6.Suppose a product must go through an assembly line made up of five sequential operations. The time it takes to complete each operation is normally distributed with a mean of 180 seconds and standard deviation of 5 seconds. Let X denote the cycle time for the line, so that after X seconds, each operation is supposed to be finished and ready to pass the product to the next operation in the assembly line.

a. If the cycle time X 5 180 seconds, what is the probability that all five operations will be completed?

c. Suppose that the company wants all operations to be completed within 190 seconds 98% of the time. Further suppose that the standard deviation of an operation can be reduced at a cost of $5,000 per second of reduction (from 5), and any or all operations may be reduced as desired by up to 2.5 seconds. By how much should the standard deviations be reduced to achieve the desired performance level, and how much would that cost?

10. WVTU is a television station that has 20 thirty-second advertising slots during their regularly scheduled programming each evening. The station is now selling advertising for the first few days in November. They could sell all the slots immediately for$4,500 each, but because November 7 will be an election day, the station manager knows she may be able to sell slots at the last minute to political candidates in tight races for a price of $8,000 each. The demand for these last-minute slots is estimated as follows:

Demand

8 9 10 11 12 13 14 15 16 17 18 19

Probability 0.03 0.05 0.10 0.15 0.20 0.15 0.10 0.05 0.05 0.05 0.05 0.02

Slots not sold in advance and not sold to political candidates at the last minute can be sold to local advertisers at a price of $2,000.

1. If the station manager sells all the advertising slots in advance, how much revenue will the station receive?

2. How many advertising slots should be sold in advance if the station manager wants to maximize expected revenue?

3. If the station manager sells in advance the number of slots identified in the prevous question, what is the probability that the total revenue received will exceed the amount identified in part a where all slots are sold in advance

27. Vinton Auto Insurance is trying to decide how much money to keep in liquid assets to cover insurance claims. In the past, the company held some of the premiums it received in interest-bearing checking accounts and put the rest into investments that are not quite as liquid but tend to generate a higher investment return. The company wants to study cash flows to determine how much money it should keep in liquid assets to pay claims. After reviewing historical data, the company determined that the average repair bill per claim is normally distributed with a mean of $1,700 and standard deviation of $400. It also determined that the number of repair claims filed each week is a random variable that follows the probability distribution shown in the following table:

Number of Claims 1 2 3 4 5 6 7 8 9

Probability 0.05 0.06 0.10 0.17 0.28 0.14 0.08 0.07 0.05

In addition to repair claims, the company also receives claims for cars that have been “totaled” and cannot be repaired. A 20% chance of receiving this type of claim exists in any week. These claims for totaled cars typically cost anywhere from $2,000 to $35,000, with $13,000 being the most common cost.

a. Create a spreadsheet model of the total claims cost incurred by the company in any week.

c. What is the average cost the company should expect to pay each week?

d. Suppose that the company decides to keep $20,000 cash on hand to pay claims. What is the probability that this amount would not be adequate to cover claims in any week?