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

Value

Resin and supplies

Value

Finishing kit (seat, rudder, ropes, etc.)

Value

Labor

Value

Selling and administrative expenses - variable

Value

Total variable costs per unit

?

(b)

Determine the unit contribution margin.

Unit selling price

Value

Unit variable cost

?

Unit contribution margin

?

(c )

Using the unit contribution margin, determine the break-even point in units for this product line.

Selling and administrative expenses - fixed

Value

Manufacturing overhead - fixed

Value

Total fixed costs (a)

?

Unit contribution margin (b)

Value

Break-even points (units) (a b)

?

(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

Value

Target net income

Value

Total fixed costs + target net income (a)

?

Unit contribution margin (b)

Value

Units need to be sold (a b)

?

(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

Value

Break-even sales

Value

Margin of safety (dollars)

?

Margin of safety ratio

Margin of safety (dollars) (a)

Value

Actual (expected) sales (b)

Value

Margin of safety ratio (a b)

?

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.