Great Lakes Automotive (GLA) is considering producing, in-house, a gear assembly that it currently purchases from Delta Supply for $6 per unit. GLA estimates if it chooses to manufacture the gear assembly, it will cost $23,000 to set up the process and then a marginal cost of $3.82 per unit for labour and materials.

At what sales volume would these options cost GLA the same amount of money?

Assume now that GLA will be able to sell the new gear for the same price it buys it from Delta supply (i.e. $6/unit). Let S = sales forecast of the number of gears that can be sold and X = the number of gears produced. Write an (algebraic/mathematical) expression for the profit in terms of these two variables (or parameters) X and S. Hint: The actual sales will be the smaller of X and S.

Formulate a spreadsheet model that will give the profit in part b for any values of the two parameters, X and S. Use the Excel goal seek command (Data > What-If-Analysis > Goal Seek) to calculate the break-even point found in part a. How many gears should GLA make when S = 9,000 gears and when S = 12,000 gears? What are the corresponding profits? (Hint: Your spreadsheet needs to incorporate the following logic: X = 0 if S < BEP and X = S if S > BEP, where BEP = break-even-point.)

Assume S = 12,000 gears. Use the data table tool in Excel (Data > What-If-Analysis > Data Table) to calculate the profit realized when X varies from 4,000 to 18,000 in increments of 2,000, i.e. X = 4,000, 6,000, 8,000 18,000 assuming that every unit that is produced is sold. Plot an X-Y (i.e. scatter plot) graph of profit versus X.

A reliable sales forecast has been obtained indicating that GLA would be able to sell 12,000 gears. However, management is concerned that this conclusion might change if more accurate estimates were available for the cost of setting up the production process, the marginal production cost, and the unit revenue. Therefore, before a final decision is made, management wants sensitivity analysis done on these estimates. Use the spreadsheet you developed in part c and use the goal seek command and/or data table features in Excel to perform the following sensitivity analysis. Note: Parts (e), (f), and (g) are independent and each part refers to the original data.

How large can the cost of setting up the production process before the in-house gear assembly cease to be profitable?

How large can the marginal production cost be before the in-house gear assembly cease to be profitable?

How small can the unit revenue be before the in-house gear assembly cease to be profitable?