1. 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 $20 per unit, with a variable cost of $18 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.
(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 or that variable costs can be reduced to 90 percent of their current level. Assuming all other costs stay at the base-case levels, use the model to determine which alternative would lead to a higher profit contribution.
(e) Suppose that the two alternatives in (d) are being considered, but that to increase sales by 35 percent, fixed costs would rise by $5000 annually to cover additional promotional expenses. Similarly, to reduce variable costs to 90 percent of their base-case level would require $10,000 annually to cover costs of new equipment. Make any changes needed ...