CD5 - EXCEL Tutorial
CURRENT DESIGNS
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,000 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.
Instructions
(a)	Calculate variable cost per unit.
(b)	Determine the unit contribution margin.
(c )	Using the unit contribution margin, determine the break-even point in units for this product line.
(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.
(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.
NOTE: Enter a number in cells requesting a value; enter either a number or a formula in cells with a "?" .
(a)	Calculate variable cost per unit.
	Kevlar					$250
	Resin and supplies					100
	Finishing kit (seat, rudder, ropes, etc.)					170
	Labor					420
	Selling and administrative expenses - variable					400
	Total variable costs per unit					$1,340
(b)	Determine the unit contribution margin.
	Unit selling price					$2,000
	Unit variable cost					1,340
	Unit contribution margin					$660
(c )	Using the unit contribution margin, determine the break-even point in units for this product line.
	Selling and administrative expenses - fixed					119000
	Manufacturing overhead - fixed					240000
	Total fixed costs (a)					$359,700
	Unit contribution margin (b)					$660
	Break-even points (units) (a b)					545
(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 fixed costs					359700
	Target net income					270000
	Total fixed costs + target net income (a)					$629,700
	Unit contribution margin (b)					$660
	Units need to be sold (a b)					955
(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.
	Margin of safety
	Actual (expected) sales			$2,000,000
	Break-even sales			$1,090,000
	Margin of safety (dollars)			$910,000
	Margin of safety ratio
	Margin of safety (dollars) (a)			$910,000
	Actual (expected) sales (b)			$2,000,000
	Margin of safety ratio (a b)			45.5%
After you have completed CD-5, consider the following additional question
1.	Assume that the unit selling price per kayak changed to $2,200 each, and fixed manufacturing overhead
	increased to $360,000. Show impact of these changes on calculations.
	Just need help with this part ^^^^^ Thank you!