2. Simulation The spreadsheet of Outsourcing Decision Model is provided. The model calculates the total cost for manufacturing and outsourcing. The key output in the model is the decision that results in the lowest cost (Manufacture or Outsource). Assume that the fixed cost and production volume are 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. Fixed cost follows a uniform distribution with a minimum of $40,000 and a maximum of $60,000. Use a 500-trial Monte Carlo simulation to find percent of trials that result in manufacturing or outsourcing as the be decision. Outsourcing Decision Model Manufactured in-house Fixed Cost: Uniform Distribution Min (a) Max (b) 40.000 60,000 Fixed cost Unit variable cost $125 Purchased from supplier Unit cost $175 Production Volume: Normal Distribution Mean Standard deviation 1100 100 Production volume Model Total manufacturing cost Total purchased cost 137 Cost difference (Manufacture - Purchase) Best Decision Manufacture Your answer: percent of trials that result in manufacturing: percent of trials that result in outsourcing: III IIIIIIIIIIII T Manufactured in-house Fixed Cost: Uniform Distribution Min (a) Max (6) 40,000 50.000 Fixed cost Unit variable cost $125 Purchased from supplier Unit cost $175 Production Volume: Normal Distributio standard deviation 11001 Mean Production volume 100 Model Total manufacturing cost Total purchased cost Cost difference (Manufacture - Purchase) Best Decision Manufacture Your answer: percent of trials that result in manufacturing percent of trials that result in outsourcing: ERRASSENARE 88 89 996999 2. Simulation The spreadsheet of Outsourcing Decision Model is provided. The model calculates the total cost for manufacturing and outsourcing. The key output in the model is the decision that results in the lowest cost (Manufacture or Outsource). Assume that the fixed cost and production volume are 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. Fixed cost follows a uniform distribution with a minimum of $40,000 and a maximum of $60,000. Use a 500-trial Monte Carlo simulation to find percent of trials that result in manufacturing or outsourcing as the be decision. Outsourcing Decision Model Manufactured in-house Fixed Cost: Uniform Distribution Min (a) Max (b) 40.000 60,000 Fixed cost Unit variable cost $125 Purchased from supplier Unit cost $175 Production Volume: Normal Distribution Mean Standard deviation 1100 100 Production volume Model Total manufacturing cost Total purchased cost 137 Cost difference (Manufacture - Purchase) Best Decision Manufacture Your answer: percent of trials that result in manufacturing: percent of trials that result in outsourcing: III IIIIIIIIIIII T Manufactured in-house Fixed Cost: Uniform Distribution Min (a) Max (6) 40,000 50.000 Fixed cost Unit variable cost $125 Purchased from supplier Unit cost $175 Production Volume: Normal Distributio standard deviation 11001 Mean Production volume 100 Model Total manufacturing cost Total purchased cost Cost difference (Manufacture - Purchase) Best Decision Manufacture Your answer: percent of trials that result in manufacturing percent of trials that result in outsourcing: ERRASSENARE 88 89 996999