Monte Carlo Simulation Modeling

Problem 1 The management of Brinkley Corporation is interested in using simulation to estimate the profit per unit for a new product. The selling price for the product will be $45 per unit. Probability distributions for the purchase cost, the labor cost, and the transportation cost are estimated as follows: Procurement Cost ($) Probability Labor Cost ($) Probability Transportation Cost ($) Probability 10 0.25 20 0.10 3 0.75 11 0.45 22 0.25 5 0.25 12 0.30 24 0.35 25 0.30 (a) Construct a simulation model to estimate the average profit per unit. What is a 95% confidence interval around this average? Round your answers to two decimal places. Lower Bound: $  Upper Bound: $  (b) Management believes that the project may not be sustainable if the profit per unit is less than $5. Use simulation to estimate the probability that the profit per unit will be less than $5. What is a 95% confidence interval around this proportion? Round your answers to one decimal of a percentage. Lower Bound:  % Upper Bound:  % Problem 2 All answers were generated using 1,000 trials and native Excel functionality.) The Iowa Wolves is scheduled to play against the Maine Red Claws in an upcoming game in the National Basketball Association (NBA) G League. Because a player in the NBA G League is still developing his skills, the number of points he scores in a game can vary substantially. Assume that each player's point production can be represented as an integer uniform random variable with the ranges provided in the following table: Player lowa Wolves Maine Red Claws 1  [5,20]  [7,12] 2  [7,20]  [15,20] 3  [5,10]  [10,20] 4  [10,40]  [15,30] 5  [6,20]  [5,10] 6  [3,10]  [1,20] 7  [2,5]  [1,4] 8  [2,4]  [2,4] (a) Develop a spreadsheet model that simulates the points scored by each team and the difference in their point totals. What are the average and standard deviation of points scored by the Iowa Wolves? Round your answers to one decimal place. Standard Deviation:   (b) What are the average and standard deviation of points scored by the Maine Red Claws? Round your answers to one decimal place.   Standard Deviation:   (c) Let Point Differential = Iowa Wolves points – Maine Red Claws points. What is the average Point Differential between the Iowa Wolves and Maine Red Claws? What is the standard deviation of the Point Differential? If your answer is negative, enter a minus sign in the input box. Round your answers to one decimal place. Standard Deviation:   (d) What is the probability that the Iowa Wolves scores more points than the Maine Red Claws? Round your answer to the nearest whole number.  % (e) The coach of the Iowa Wolves feels that they are the underdog and is considering a riskier game strategy. The effect of this strategy is that the range of each Wolves player's point production increases symmetrically so that the new range is [0, original upper bound + original lower bound]. For example, Wolves player 1's range with the risky strategy is [0, 25]. How does the new strategy affect the average and standard deviation of the Wolves point total? How does that affect the probability of the Iowa Wolves scoring more points than the Maine Red Claws? Round first two numerical answers to one decimal place and the last answer to a whole percentage.  the standard deviation of the Iowa Wolves point total will be   points. The probability of the Iowa Wolves scoring more points than the Maine Red Claws will become  %. Problem 3 (All answers were generated using 1,000 trials and native Excel functionality.) In preparing for the upcoming holiday season, Fresh Toy Company (FTC) designed a new doll called The Dougie that teaches children how to dance. The fixed cost to produce the doll is $100,000. The variable cost, which includes material, labor, and shipping costs, is $34 per doll. During the holiday selling season, FTC will sell the dolls for $42 each. If FTC overproduces the dolls, the excess dolls will be sold in January through a distributor who has agreed to pay FTC $10 per doll. Demand for new toys during the holiday selling season is uncertain. The normal probability distribution with an average of 60,000 dolls and a standard deviation of 15,000 is assumed to be a good description of the demand. FTC has tentatively decided to produce 60,000 units (the same as average demand), but it wants to conduct an analysis regarding this production quantity before finalizing the decision. (a) Create a what-if spreadsheet model using formulas that relate the values of production quantity, demand, sales, revenue from sales, amount of surplus, revenue from sales of surplus, total cost, and net profit. What is the profit when demand is equal to its average (60,000 units)? $      (b) Modeling demand as a normal random variable with a mean of 60,000 and a standard deviation of 15,000, simulate the sales of The Dougie doll using a production quantity of 60,000 units. What is the estimate of the average profit associated with the production quantity of 60,000 dolls? Round your answer to the nearest dollar. $  How does this compare to the profit corresponding to the average demand (as computed in part (a))? The average profit from the simulation is   the profit computed in part (a).     (c) Before making a final decision on the production quantity, management wants an analysis of a more aggressive 70,000-unit production quantity and a more conservative 50,000-unit production quantity. Run your simulation with these two production quantities. What is the average profit associated with each? Round your answers to the nearest dollar. When ordering 50,000 units, the average profit is approximately $ . When ordering 70,000 units, the average profit is approximately $ .     (d) Besides average profit, what other factors should FTC consider in determining a production quantity? Compare the four production quantities (40,000; 50,000; 60,000; and 70,000) using all these factors. If required, round Probability of a Loss to three decimal places and Probability of a Shortage to two decimal places. Round the other answers to the nearest dollar. Production Quantity Average Net Profit Profit Standard Deviation Maximum Net Profit Probability of a Loss Probability of a Shortage 40,000 $  $  $      50,000 $  $  $      60,000 $  $ Problem 4 (All answers were generated using 1,000 trials and native Excel functionality.) At a local university, the Student Commission on Programming and Entertainment (SCOPE) is preparing to host its first music concert of the school year. To successfully produce this music concert, SCOPE has to complete several activities. The following table lists information regarding each activity. An activity’s immediate predecessors are the activities that must be completed before the considered activity can begin. The table also lists duration estimates (in days) for each activity. Activity Immediate Predecessors Minimum Time Likely Time Maximum Time A: Negotiate contract with selected musicians — 5 6 9 B: Reserve site — 8 12 15 C: Logistical arrangements for music group A 5 6 7 D: Screen and hire security personnel B 3 3 3 E: Advertising and ticketing B, C 1 5 9 F: Hire parking staff D 4 7 10 G: Arrange concession sales E 3 8 10 The following network illustrates the precedence relationships in the SCOPE project. The project begins with activities A and B, which can start immediately (time 0) because they have no predecessors. On the other hand, activity E cannot be started until activities B and C are both completed. The project is not complete until all activities are completed. (a) Using the triangular distribution to represent the duration of each activity, construct a simulation model to estimate the average amount of time to complete the concert preparations. Round your answers to one decimal place. Project Duration Average   days Standard Deviation   days     Problem 5 (All answers were generated using 1,000 trials and native Excel functionality.) Orange Tech (OT) is a software company that provides a suite of programs that are essential to everyday business computing. OT has just enhanced its software and released a new version of its programs. For financial planning purposes, OT needs to forecast its revenue over the next few years. To begin this analysis, OT is considering one of its largest customers. Over the planning horizon, assume that this customer will upgrade at most once to the newest software version, but the number of years that pass before the customer purchases an upgrade varies. Up to the year that the customer actually upgrades, assume there is a 0.50 probability that the customer upgrades in any particular year. In other words, the upgrade year of the customer is a random variable. Furthermore, the revenue that OT earns from the customer's upgrade also varies (depending on the number of programs the customer decides to upgrade). Assume that the revenue from an upgrade obeys a normal distribution with a mean of $100,000 and a standard deviation of $25,000. Using the template in the file OrangeTech, complete a simulation model that analyzes the net present value of the revenue from the customer upgrade. Use an annual discount rate of 10%. Attached is the data file. (a) What is the average net present value that OT earns from this customer? Round your answer to the nearest whole number. Do not round your intermediate calculation. $      (b) What is the standard deviation of net present value? Round your answer to the nearest whole number. Do not round your intermediate calculation. $  7. less than Model Orange Tech Parameters Annual Upgrade Probability 0.5 Upgrade Year Discount Rate 10% Upgrade Revenue Upgrade Revenue (normal) mean $100,000 standard deviation $25,000 Model Year 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25