The Excel file Outsourcing Decision Model is a spreadsheet implementation of the outsourcing decision model. The model calculates the total cost for manufacturing and outsourcing. The key outputs in the model are the difference in these costs and the decision that results in the lowest cost. Note how the IF function is used in cell B20 to identify the best decision. Assume that the production volume is uncertain. Suppose that the manufacturer has enough data to estimate that he production volume will be normally distributed with a mean of 1,100 and a standard deviation of 100.

a. Use a 100-trial Monte Carlo simulation. Hint: Create a data table that shows both the cost difference and decision for each trial. b. Calculate the average cost difference and percent of trials that result in manufacturing or outsourcing as the best decision.

Outsourcing Decision Model Data Manufactured in-house Fixed cost Unit variable cost $50,000.00 $125.00 Purchased from supplier Unit cost $175.00 Production volume 1500 Model Total manufacturing cost Total purchased cost $237,500.00 $262,500.00 st difference (Manufacture - Purchase) Best Decision -$25,000.00 Manufacture Outsourcing Decision Model Data Manufactured in-house Fixed cost Unit variable cost $50,000.00 $125.00 Purchased from supplier Unit cost $175.00 Production volume 1500 Model Total manufacturing cost Total purchased cost $237,500.00 $262,500.00 st difference (Manufacture - Purchase) Best Decision -$25,000.00 Manufacture