Enable Editing (0) PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected view K19 DK 1 CD5 - EXCEL Tutorial 3 CURRENT DESIGNS 4 Bill Johnson, sales manager, and Diane Buswell, controller at Current Designs are beginning to analyze the cost 5 considerations for one of the composite models of the kayak division. They have provided the following production 6 and operational costs necessary to produce one composite kayak. Kevlar Resin and supplies Finishing kit (seat, rudder, ropes, etc.) Labor Selling and administrative expenses - variable Selling and administrative expenses - fixed Manufacturing overhead - fixed $250 per kayak $100 per kayak $170 per kayak $420 per kayak $400 per kayak $119,000 per year $240,000 per year 17 18 Bill and Diane have asked you to provide a cost-volume-proft analysis, to help them finalize the budget prolections for 19 the upcoming year. Bil has informed you that the seling price of the composite kayak will be $2,000. 21 Instructions 22 (a) Calculate variable cost per unit. 23 (b) Determine the unit contribution margin. 24 ) Using the unit contribution margin, determine the break-even point in units for this product line. 25(d) Assume that Current Designs plans to earn $270,000 on this product line. Using the unit contribution 26 margin, calculate the number of units that need to be sold to achieve this goal. 27(e) Based on the most recent sales forecast, Current Design plans to sell 1,000 units of this model 28 Using your results from part (c), calculate the margin of safety and the margin of safety ratio. 29 NOTE: Enter a number in cells requesting a value; enter either a number or a formula in cells with a "?". 165-9 P5-2AC05 11 Type here to search D 11 M e w Er File Home Insert Page Layout Formulas Data Review ViewHelp DYMO Label QuickBooks PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View. K19 x f| A B C D E F G H 31 32 (a) Calculate variable cost per unit. Kevlar Resin and supplies Finishing kit (seat, rudder, ropes, etc.) Labor Selling and administrative expees - variable Total variable costs per unit Value Value Value Value Value 42 (b) Determine the unit contribution margin. Value Unit selling price Unit variable cost Unit contribution margin (c) Using the unit contribution margin, determine the break-even point in units for this product line. Value Value Selling and administrative expenses - fixed Manufacturing overhead - fixed Total fixed costs (a) Unit contribution margin (b) Break-even points (units) (a+b) Value 58 (d) Assume that Current Designs plans to earn $270,000 on this product line. Using the unit contribution margin, calculate the number of units that need to be sold to achieve this goal. Total fived sosten Value # O Type here to search On M e 3 w PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View Enable Editing K19 4ABCD 58 (d) Assume that Current Designs plans to earn $270,000 on this product line. Using the unit contribution margin, calculate the number of units that need to be sold to achieve this goal. Value Value Total fixed costs Target net income Total fixed costs + target net income (a) Unit contribution margin (b) Units need to be sold (a + b) Value 68 (e) Based on the most recent sales forecast, Current Design 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. 69 Margin of safety Actual (expected sales Break-even sales Margin of safety (dollars) Value Value Margin of safely ratio Margin of safety (dollars) (a) Actual (expected) sales (b) Margin of safety ratio (a+b) Value Value 2 83. After you have completed CD-5, consider the following additional question 841. Assume that the unit selling price per kayak changed to $2,200 each, and fxed manufacturing overhead 85 increased to $360,000. Show impact of these changes on calculations. 1 E5-9 P5-2A CD-5 a Type here to search 0 e W
