Refer back to the outsourcing decision model in Example 1.4 of Chapter 1. The Excel file Outsourcing Decision Model is a spreadsheet implementation of the 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 the manufacturer has enough data and information to estimate that the production volume will be normally distributed with a mean of 1,100 and a standard deviation of 100. 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.

Data from Example 1.4

Suppose that a manufacturer can produce a part for $125/unit with a fixed cost of $50,000. The alternative is to outsource production to a supplier at a unit cost of $175. The total manufacturing and outsourcing costs can be expressed by simple mathematical formulas, where Q is the production volume: 

TC (manufacturing) = $50,000 + $125 x Q (1.2) 

TC (outsourcing) = $175 x Q (1.3) 

These formulas comprise the decision model, which simply describes what the costs of manufacturing and outsourcing are for any level of production volume. Thus, if the anticipated production volume is 1,500 units, the cost of manufacturing will be $50,000 + $125 x 1,500 = $237,500, and the cost of outsourcing would be $175 x 1,500 = $262,500; therefore, manufacturing would be the best decision. On the other hand, if the anticipated production volume is only 800 units, the cost of manufacturing will be $50,000 + $125 x 800 = $150,000 and the cost of outsourcing would be $175 x 800 = $140,000, and the best decision would be to outsource. If we graph the two total cost formulas, we can easily see how the costs compare for different values of Q. This is shown graphically in Figure 1.4. The point at which the total costs of manufacturing and outsourcing are equal is called the break-even volume. This can easily be found by setting TC (manufacturing) = TC (outsourcing) and solving for Q:  

Transcribed Image Text:

$50,000+ $125 x Q = $175 x Q $50,000 = 50 x Q Q = 1,000