Question

Problem 12-14 (Algorithmic)

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 $36,000. The variable cost for the product is uniformly distributed between $20 and $28 per unit. The product will sell for $56 per unit. Demand for the product is best described by a normal probability distribution with a mean of 1,200 units and a standard deviation of 100 units. Develop an Excel worksheet simulation for this problem. Use 500 simulation trials to answer the following questions:

1. What is the mean profit for the simulation? Round your answer to the nearest dollar.
   
   Mean profit = $ ___
2. 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.
   
   Probability of Loss =____ %

Answer 1

To simulate the variable cost from uniform distribution we use 20+ (28-20)*RAND()

To simulate demand from normal distribution we use NORM.INV(RAND(),1200,100)

The revenue is demand*56

Total cost is 36000+demand*(variable cost) (assuming that we produce the demand quantity)

Profit = revenue - total cost

mean profit is the average of profit for 500 simulations

Probability of Loss = (number of simulations with profit <0)/500

Prepare the following sheet

copy the rows to make 500 trials. Paste as values to avoid changes

Get this

ans:

Mean profit = $2,147

Probability of Loss =34%