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.
a. At what sales volume would these options cost GLA the same amount of money?
b. 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.
c. 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.)