Assignment 7
Chapter 12 – Simulation Homework
Problem 1:
Fuji Tire Company has produced a new tire with an estimated mean lifetime mileage of 40,000 miles. Management also believes that the standard deviation is 3900 miles and that tire mileage is normally distributed. Use an Excel worksheet to simulate the miles obtained for a sample of 500 tires.
a. Use the Excel COUNTIF function (see Appendix A for a description of the Excel COUNTIF function) to determine the number of tires that last longer than 40,000 miles. What is your estimate of the percentage of tires that will exceed 40,000 miles?
b. Use COUNTIF to find the number and percentage of tires that obtain mileage less than 35,000 miles. Then find the number and percentage of those with less than 32,000 miles and of those with less than 30,000 miles.
***NOTE: the exact numbers will change every time you change the spreadsheet. The assignment will be graded on the integrity of the model.
Problem 2:
The price of a share of Northwest Industrial Products listed on the New York Stock Exchange is currently $103. The following probability distribution shows how the price per share is expected to change over a three-month period:
|
Stock Price Change ($) |
Probability |
|
-2 |
0.05 |
|
-1 |
0.10 |
|
0 |
0.25 |
|
+1 |
0.20 |
|
+2 |
0.20 |
|
+3 |
0.10 |
|
+4 |
0.10 |
Do the following on an Excel spreadsheet:
a. Set up intervals of random numbers that can be used to generate the change in stock price over a three-month period.
b. With the current price of $103 per share and the random numbers 0.7895, 0.4522, 0.1801, 0.0039, and 0.8531, simulate the price per share for the next five 3-month periods. What is the ending simulated price per share?
Problem 3:
Develop an Excel worksheet simulation for the following problem. The management of Paragon Household Products is considering the introduction of a new product. The fixed cost to begin the production of the product is $25,388. The variable cost for the product is uniformly distributed between $15 and $20 per unit. The product will sell for $42 per unit. Demand for the product is best described by a normal probability distribution with a mean of 1200 units and a standard deviation of 300 units. Develop a spreadsheet simulation that uses 500 simulation trials to answer the following questions:
a. What is the mean profit for the simulation?
b. What is the probability that the project will result in a loss?
C. What is your recommendation concerning the introduction of the product?
***NOTE: the exact numbers will change every time you change the spreadsheet. The assignment will be graded on the integrity of the model.
Problem 4:
The Wreck-It-Ralph Auto Insurance Company developed the following probability distribution for automobile collision claims paid during the past year:
|
Payment ($) |
Probability |
|
0 |
0.83 |
|
500 |
0.06 |
|
1,000 |
0.05 |
|
2,000 |
0.02 |
|
5,000 |
0.02 |
|
8,000 |
0.01 |
|
10,000 |
0.01 |
a. Set up intervals of random numbers that can be used to generate automobile collision claim payments.
b. Using the following 20 random numbers, simulate the payments for 20 policyholders. How many claims are paid and what is the total amount paid to the policyholders?
Random numbers:
|
.3389 |
.7753 |
.8769 |
.8497 |
.2346 |
|
.4449 |
.4998 |
.1386 |
.532 |
.9955 |
|
.1679 |
.2225 |
.5847 |
.0112 |
.7330 |
|
.8191 |
.8454 |
.6899 |
.6246 |
.3572 |
Problem 5:
See excel workbook