Question

The management of Madeira Manufacturing Company is considering the introduction of a new product. The fixed cost to begin the production of the product is $35,000. The variable cost for the product is uniformly distributed between $17 and $23 per unit. The product will sell for $55 per unit. Demand for the product is best described by a normal probability distribution with a mean of 1,300 units and a standard deviation of 300 units. Develop an Excel worksheet simulation for this problem. Use 500 simulation trials to answer the following questions: a. What the mean profit for the simulation? Round your answer to the nearest dollar. Mean profit = $ X b. What is the probability that the project will result in a loss? Recalculate the numerical value of probability in percent and then round your answer to the nearest whole number. 19 X % Probability of Loss =

Answer 1

Profit Function=(Selling-Variable)Demand-Fixed Cost

Fixed Cost 35000 Selling Price 55 Demand (Normal Dist) Mean 1300 SD 300 Variable Cost(Uniform Distribution) Min 17 Max 23

Simulation Table

A F 1 2 Fixed Cost 35000 3 Selling Price 55 4 Demand (Normal Dist) Mean 1300 6 SD 300 7 Variable Cost(Uniform Distribution) 8 Min 17 Max 23 10 Simulation Trial 11 12 Trial Demand VC Profit Instances of loss 1 635.9197 17.08057 B13*($C$3-C13)-$C$2 IF(D13<0,1,0) 13 nLO

For Random No Go to Data analysis and then Random Number

Demand Random-

Random Number Generation X 7 Number of Variables: OK 1 Number of Random Numbers: Cancel 500 Distribution: Help Normal Parameters Mean 1300 Standard deviation = 300 Random Seed 123 Output options $B$13] Qutput Range New Worksheet Ply: O New Workbook HA

Variable Cost Random-

X Random Number Generation Number of Variables: OK Number of Random Numbers: Cancel 500 Distribution Help Uniform Parameters and 23 Between 17 Random Seed: 123 Output options Qutput Range: New Worksheet Ply $C$13 New Workbook

Final table

Simulation Trial 11 12 Trial Demand VC Profit Instances of loss 13 1 635.9197 17.08057 -10886.3 1 14 2 1361.696 20.48881 11993.75 0 15 3 1460.968 21.22529 14343.78 0 16 4 1223.93 19.39949 8572.519 0 17 5 1974.012 22.92602 28314.41 0 18 6 914.9236 17.59786 -779.898 1 19 7 1762.478 22.63048 22050.57 0 20 8 2134.065 22.9837 33324.85 0 21 9 1140.831 18.78716 6312.731 0 22 10 1619.38 22.13883 18214.72 23 11 1394.376 20.74078 12770.25 24 12 700.7703 17.13733 -8466.97 1 25 13 971.4516 17.82034 1118.244 26 14 944.3244 17.70736 216.3503 27 15 1739.738 22.57189 21416.41| 0 28 16 1350.788 20.4033 11732.8 0 29 17 1763.156 22.63213 22069.61 0 30 18 1840.619 22.78539 24294.82 0 31 19 1837.391 22.78027 24200.26 0 32 20 1503.927 21.51003 15366.47 0 33 21 1292.552 19.94058 10316.12 0

A) Mean Profit=Average(D13:D512)=10377.35

B)Probability of loss= total Instance of loss/500=55/500=0.11 or 11%