Question

A hardware company sells a lot of low-cost, high- volume products. For one such product, it is equally likely that annual unit sales will be low or high. If sales are low (60,000), the company can sell the product for $10 per unit. If sales are high (100,000), a competitor will enter and the company will be able to sell the product for only $8 per unit. The variable cost per unit has a 25% chance of being $6, a 50% chance of being $7.50, and a 25% chance of being $9. Annual fixed costs are $30,000.

1. Use simulation to estimate the company’s expected annual profit. To do this set up your spreadsheet so that all the calculations for a replication are in one row as shown below (for example):

Replication #	Sales	.....	Profit
1
2
:
:
100

Use rand() to generate values for all the uncertain quantities in this situation. Once you have done all 100 replications, freeze your spreadsheet by doing Copy/Paste (Values) on the cells with the uncertain quantities – otherwise any cell that depends on rand() will keep changing.

2. What is the average annual profit? What is the standard deviation of profit?
3. Construct a frequency histogram for profit. Comment on this histogram. Your chart should be clear to anyone who reads it.
4. Now suppose that annual unit sales, variable cost, and unit price are equal to their respective expected values—that is, there is no uncertainty. Determine the company’s annual profit for this scenario.
5. Can you conclude from the results in parts (b) and (d) that the expected profit from a simulation is equal to the profit from the scenario where each input assumes its expected value? Explain.

Answer 1