Petty Components makes a variety of parts for the Auto Industry. One of these parts (TR 17) does not seem to be contributing to profits. The selling price of the part is $22.75, the variable cost is $17.50 and the Fixed Cost for this product is $105,000 per year. In the past fiscal year, 18,000 of this product was produced and sold.
A. Construct a correct, flexible and documented spreadsheet model that will allow varying inputs and resulting revenue, costs and profit. Check the base case above to see if the company is losing on this part.
B. Using the model and Goal Seek, find the break even quantity.
C. Construct a data table and chart showing profit vs. quantity.
D. Mr. Petty is trying to determine where to put his efforts in attempting to increase the profitability for TR 17. Should we increase sales volume by 33% or reduce the variable cost by 12%. Using the model, determine which method would increase profitability more.