2. Complete the following problem. You will use goal seek as above, but you will need to figure out how to integrate the selling price into the equations. Stapleton Manufacturing intends to increase capacity through the addition of new equipment. Two vendors have presented proposals. The fixed cost for proposal A is $80,000, and for proposal B is $39,000. The variable cost for A is $10 and for B is $14. The selling price (revenue per unit) of each unit is $20.00.
   1. What is the crossover point in units for the two options?
   2. At the expected volume of 8,500 units, which alternative should be chosen?

Operations Management

. 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 the top example Click OK. When goal seek finds an answer, click OK again You will need to run goal seek separately for each volume you want to calculate The option with the lower fixed costs has the lower costs at any volume below the crossover volume Crossover chart Fixed costs 200000 300000 Software A Software B Crossover volume between Software A and B Variable costs Total costs 6 0 -C34D3*H$3) 25 -C4+(D4 HS3) -E3-E4 Difference Crossover volume between Software B and C Software B Software C 300000 25 40000010 -C12-(D12*H$12) -C13+(D13*H$12) -E12-E13 Difference