Kaizer Plastics produces a variety of plastic items for packaging and distribution. One item, container #145, has had a low contribution to profits. Last year, 20,000 units of container #145 were produced and sold. The selling price of the container was $25 per unit, with a variable cost of $16 per unit and a fixed cost of $70,000 per year.
(a) Construct a correct, flexible, and documented base-case spreadsheet model that allows the user to easily vary the inputs to the problem and see the resulting revenue, costs, and profit. What is the profit level for the base case?
(b) Using the model and Goal Seek, find the break-even quantity.
(c) Construct a Data Table and chart showing profit versus quantity. Vary the quantity from 0 to 70000 in increments of 5000.
(d) The company is considering ways to improve profitability by either stimulating sales volume or reducing variable costs. Management believes sales can be increased by 35 percent of their current levels (alternative 1) or that variable costs can be reduced to 90 percent of their current level (alternative 2). Assuming all other costs stay at the base-case levels (values established in part (a)), use the model to determine which alternative would lead to a higher profit contribution.
Your submission must be an EXCEL file; put your solution to each part on a separate tab of the EXCEL workbook.
For part (a), make sure that you include a textbox containing at least a one-sentence response to the question asked.
For part (b), provide a screenshot (or Snipping Tool capture) of the Goal Seek Dialog Box. Run Goal Seek before taking the screenshot.
For part (c), place the Chart of the relevant data to the right of the Data Table results.
For part (d), provide a side by side set of computations for the two alternatives. Also, provide a written interpretation of the result for each alternative, and a written recommendation of your recommendation between the two alternatives.