Question

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

Answer 1

	Part (a)	Calculate Variable Cost per unit
		Kevlar		$250
		Resin and Supplies		$100
		Finishing kit		$170
		Labor		$420
		S & Adm Expenses		$400
		Total Variable Cost per Unit		$1,340
	Part (b)	Determine Unit Contribution margin
		Unit selling Price		$2,000
		Unit Variable Cost		$1,340
		Unit Contribution Margin		$660
	Part ©	Using Unit Contribution margin, determine the break even point in units for the product line
		S & Adm Expenses		119000
		Manufacturing Overhead Fixed		240000
		Total Fixed Cost (a)		359000
		Unit Contribution Margin		$660
		Break even Points (Units)(b)		$544
	Part (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 Cost		359000
		Traget Net Income		270000
		Total Fixed Cost + Target Net Income (a)		629000
		Unit Contribution Margin (b)		$660
		Units needs to be Sold (a/b)		$953
	Part (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		2000000
		Break even sales		1090000
		Margin of safety (dollars)		910000
		Margin of safety ratio
		Margin of safety (dollars) (a)		910000
		Actual (expected) sales (b)		2000000
		Margin of safety (dollars) (a/b)		45.5%

When Selling Price is $2200 then answer will be-

	Part (a)	Calculate Variable Cost per unit
		Kevlar		$250
		Resin and Supplies		$100
		Finishing kit		$170
		Labor		$420
		S & Adm Expenses		$400
		Total Variable Cost per Unit		$1,340
	Part (b)	Determine Unit Contribution margin
		Unit selling Price		$2,200
		Unit Variable Cost		$1,340
		Unit Contribution Margin		$860
	Part ©	Using Unit Contribution margin, determine the break even point in units for the product line
		S & Adm Expenses		119000
		Manufacturing Overhead Fixed		360000
		Total Fixed Cost (a)		479000
		Unit Contribution Margin		$860
		Break even Points (Units)(b)		$557
	Part (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 Cost		479000
		Traget Net Income		270000
		Total Fixed Cost + Target Net Income (a)		749000
		Unit Contribution Margin (b)		$860
		Units needs to be Sold (a/b)		$871
	Part (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		2200000
		Break even sales		1090000
		Margin of safety (dollars)		1110000
		Margin of safety ratio
		Margin of safety (dollars) (a)		1110000
		Actual (expected) sales (b)		2200000
		Margin of safety (dollars) (a/b)		50.5%