CD6 EXCEL Tutorial

CURRENT DESIGNS

Current Designs manufactures two different types of kayak, rotomolded kayaks and composite kayaks.

The following information is available for each product line.

Rotomolded

Composite

Sales price/unit

$950

$2,000

Variable costs/unit

$570

$1,340

The company's fixed costs are $820,000. An analysis of the sales mix identifies that rotomolded kayaks

make up 80% of the total units sold.

Instructions

(a)

Determine the weighted-average unit contribution margin for Current Designs.

(b)

Determine the break-even points in units for Current Designs and identify how many units of each

type of kayak will be sold at the break-even point. (Round to the nearest whole number.)

(c )

Assume that the sales mix changes, and rotomolded kayaks now make up 70% of total units sold.

Calculate the total number of units that would need to be sold to earn a net income of $2,000,000

and identify how many units of each type of kayak will be sold at this level of income. (Round to the

nearest whole number.)

(d)

Assume that Current Designs will have sales of $3,000,000 with two-thirds of the sales dollars in

rotomolded kayaks and one-third of the sales dollars in composite kayaks. Assuming $660,000 of fixed

costs are allocated to the rotomolded kayaks and $160,000 to the composite kayaks, prepare a CVP

income statement for each product line.

(e )

Using the information in part (d), calculate the degree of operating leverage for each product line and

interpret your findings. (Round to two decimal places.)

(a)

Determine the weighted-average unit contribution margin for Current Designs.

Rotomolded Kayaks

Composite Kayaks

Sales price/unit

Value

Value

Variable costs/unit

Value

Value

Unit Contribution margin (UCM)

?

?

Product mix

Value

Value

Weighted Average UCM

?

+

?

?

(b)

Determine the break-even points in units for Current Designs and identify how many units of each

type of kayak will be sold at the break-even point. (Round to the nearest whole number.)

Fixed costs

Value

Weighted Average UCM

Value

Breakeven units

?

Rotomolded Kayaks

Composite Kayaks

Breakeven unit distribution

?

?

(c )

Assume that the sales mix changes, and rotomolded kayaks now make up 70% of total units sold.

Calculate the total number of units that would need to be sold to earn a net income of $2,000,000

and identify how many units of each type of kayak will be sold at this level of income. (Round to the

nearest whole number.)

Target net income in units:

Rotomolded Kayaks

Composite Kayaks

Sales price/unit

Value

Value

Variable costs/unit

Value

Value

Unit Contribution margin (UCM)

?

?

Product mix

Value

Value

Weighted Average UCM

?

+

?

?

Required sales in units:

Total fixed costs

Value

Value

Target net income

Value

Value

Total required sales (dollars)

?

?

Weighted Average UCM

?

?

Required sales in units

?

?

(d)

Assume that Current Designs will have sales of $3,000,000 with two-thirds of the sales dollars in

rotomolded kayaks and one-third of the sales dollars in composite kayaks. Assuming $660,000 of fixed

costs are allocated to the rotomolded kayaks and $160,000 to the composite kayaks, prepare a CVP

income statement for each product line.

Rotomolded Kayaks

Composite Kayaks

Sales

Value

Value

Variable Costs

Value

Value

Contribution Margin

?

?

Fixed Costs

Value

Value

Net Income

?

?

(e )

Using the information in part (d), calculate the degree of operating leverage for each product line and

interpret your findings. (Round to two decimal places.)

Rotomolded Kayaks

Composite Kayaks

Contribution Margin (a)

Value

Value

Net Income (b)

Value

Value

Degree of Operating Leverage (a b)

?

?

After you have completed CD6, consider the following additional question.

1.

Assume that variable cost per unit for the rotomolded kayak and composite kayak changed to $610 and

$1,400 respectively. Show impact of these changes on each of the scenarios provided.