Operations Management

Chapter 7 homework Complete the two problems using Excel and the GOAL SEEK INSTRUCTIONS below. 1. PROBLEM: Borges Machine Shop Inc is considering three options to add production capacity shown in the chart below. Annual Fixed Costs Annual Variable Costs General Purpose Equipment $175,000 $16.00 Flexible Manufacturing System $240,000 $13.50 Dedicated Machine $660,000 $11.00 a. Find the crossover volume in units between each of the three options (2 crossover volumes). b. If production is expected to be 100,000 units per year, which option should Borges select? GOAL SEEK INSTRUCTIONS Using Goal Seek for Crossover Analysis using example 1 on page 286-7 First create the spreadsheet as shown below. Notice that the formula for software B must point to two different volumes depending on whether we looking at the crossover point with software A or C. Click on Data, what-if analysis, goal seek Set cell = THE CELL WHERE YOU CALCULATE THE DIFFERENCE BETWEEN THE TOTAL COSTS, E5 in the top example To value = 0, because we want to find the volume where the total costs between the two software programs are equal By changing = THE PLACE WHERE YOU WOULD LIKE THE RESULTING NUMBER TO APPEAR, H3 in