Consider the screenshot of the spreadsheet for the Special Products break even analysis (shown below).

The production manager has decided to order a production run of Q=35,000. Use simulation in excel to find the distribution of profit given the following random inputs:

The fixed cost varies uniformly between 9 and 11 million dollars. The marginal cost is normally distributed with a mean of $1,000 and a standard deviation of $200. The Sales Forecast is triangularly distributed with a minimum of 20,000, a most likely value of 30,000 and an upper limit of 40,000. All variables are continuous. Don't round to integer values.

Use 1,000 iterations and find the mean and the 90^th percentile of the profit distribution and the probability of a loss. Include the histogram of profit in your answer. Please show all excel formulas and necessary simulation parameters and simulation window screenshots.

1 Special Products Co. Break-Even Analysis Results Range Name BreakEvenPoint FixedCost MarginalCost ProductionQuantity C9 Profit SalesForecast TotalFixedCost TotalRevenue TotalVariableCost F6 UnitRevenue Data Unit Revenue $2,000 Cell F9 Total Revenue $60,000,000 Total Fixed Cost$10,000,000 Total Variable Cost$30,000,000 Profit (Loss) $20,000,000 4 Fixed Cost $10,000,000 Marginal Cost $1,000 Sales Forecast 30000 C6 F7 C7 F5 Production Quantity30000 Break-Even Point 10,000 10 12 13 C4 15 16 17 18 19 20 21 Total Revenue= UnitRevenue*MIN(SalesForecast,ProductionQuantity) Total Fixed Cost I(ProductionQuantity>0,FixedCost,0) Total Variable Cost MarginalCost ProductionQuantity Profit (Loss) = Total Revenue-(Tota FixedCost+TotalVariableCost) Break-Even Point-FixedCost/(UnitRevenue-MarginalCost) 23 24 25 28