Question

Suppose that a firm can produce a part it uses for $500 per unit, with a fixed cost of $12,000. The company has been offered a contract from a supplier that allows it to purchase the part at a cost of $510 per unit, which includes transportation. The key outputs in the model are the difference in these costs and the decision that results in the lower cost. Assume that the production volume is uncertain. Suppose the manufacturer has enough data and information to estimate that the production volume will be normally distributed with a mean of 1,200 and a standard deviation of 85. Use a 100-trial Monte Carlo simulation to find the average cost difference and percent of trials that result in manufacturing or outsourcing as the best decision. (Your data table should show both the cost difference and decision for each trial.)

Note: Please include Excel worksheet with all the details with your answer.

Answer 1

Excel model:

For the data-table, follows the following steps after the above setup is made:

1. Select the array E2:F102

2. Go to Data --> What-if analysis --> Data table

3. Enter the details as above in the 'Data Table' window that opens.

4. Click on OK.

Result:

-------------------