CHAPTER 5 Topic(s): Cost-Volume-Profit Excel Functions and Tools: Break-even Chart Bill Johnson, sales manager, and Diane Buswell, controller, at Current Designs are beginning to analyze the cost considerations for one of the composite models of the kayak division. They have provided the following production and operational costs necessary to produce one composite kayak. Kevlar $250 per kayak Resin and supplies $100 per kayak Finishing kit (seat, rudder, ropes, etc.) $170 per kayak Labor $420 per kayak Selling and administrative expenses--variable $400 per kayak Selling and administrative expenses-fixed $119,700 per year Manufacturing overhead-fixed $240,000 per year Bill and Diane have asked you to provide a cost-volume-profit analysis, to help them finalize the budget projections for the upcoming year. Bill has informed you that the selling price of the composite kayak will be $2,000. Required Use sheet tab Part 1 1. (a) Calculate variable costs per unit. (b) Determine the unit contribution margin. (c) Using the contribution margin per unit, determine the break-even point in units for this product line. (d) Assume that Current Designs plans to earn $270,600 on this product line. Using the contribution margin per unit, calculate the number of units that need to be sold to achieve this goal (e) Based on the most recent sales forecast, Current Designs plans to sell 1,000 units of this model. Using your results from part (C), calculate the margin of safety and the margin of safety ratio. (1) Create a break-even chart for Current Designs using Excele's chart tools. WHAT-IF? 2. Suppose Current Designs locates a supplier that is able to provide fast-dry resin. While the resin cost will increase by $20 per kayak for this fast-dry formula, the fixed manufacturing overhead costs will decline by $72,980 per year by eliminating the rental of a drying machine. Perform what-if analysis to determine how many units Current Designs will need to produce and sell if these changes occur. Illustrate this change with a break- even chart. Should Current Designs make the change