1.) Use Microsoft Excel’s Analytic Solver Platform (monte carol simulation) to solve the question below:

A consumer electronics firm produces a line of battery chargers for cell phones. The distributions below apply:

Unit Price: triangular with a minimum of $18.95, most likely value of $24.95, and maximum of $26.95

Unit Cost: uniform with a minimum of $12.00 and a maximum of $15.00

Quantity Sold: 10,000 – 250 Unit price, plus a random term given by a normal distribution with a mean of 0 and a standard deviation of 10

Fixed Costs: normal with a mean of $30,000 and a standard deviation of $5,000

1.) What is the expected profit?

2.) What is the probability of a loss?

3.) What is the maximum loss?

## Expert Answer

1) Expected profit = $ 10746.57

2) In the results of Monte Carlo Simulation, we see that the shape of profit curve resembles normal distribution with following parameters

Average profit = $ 10,743

Standard deviation of profit = $ 6842

Therefore probability of loss is computed by determining the cumulative probability distribution under the profit curve, with profit less than or equal to 0.

Corresponding Z value = (0-10743)/6842 = -1.57

Therefore probability of loss = NORMSDIST(-1.57) = 0.0582 or 5.82%

3) Refer Min value of expected profit in the Monte Carlo results sheet.

Maximum loss is the Min value of profit = -19307.94