JPL Inc has provided its sales and expense data for the most

JPL, Inc. has provided its sales and expense data for the most recent period. The Controller has asked you prepare a spreadsheet that shows the related CVP Analysis computations. Use the information included in the Excel Simulation and the Excel functions described below to complete the task. Cell Reference: was entered, the formula would output the result from cell B5, or 75 in this example. Allows you to refer to data from another cell in the worksheet. From the Excel Simulation . Basic Math functions: Allows you to use the basic math symbols to perform mathema functions. You can use the following keys:+ (plus sign to add), in a blank cell -85-86\" was entered, the formula would add the values from those cells and output the result, or 120 in this example. using the other math symbols the result would output an appropriate answer for its function.

Solution

(1) CM Ratio and Variable Expense Ratio

Selling Price per unit

$75 per unit

Variable Expense per unit

$45 per unit

Contribution Margin per unit

$30 per unit

CM Ratio [$30/$75] x 100

40%

Variable Expense Ratio [$45/$75] x 100

60%

(2) Break Even Point

Break Even in unit sales = Fixed Costs / Contribution margin per unit

Break Even in Dollar sales = Fixed Costs / CM Ratio

Break Even in unit sales [$210,000 / $30]

7,000 Units

Break Even in Dollar sales [$210,000 / 0.40]

$525,000

(3) Margin of Safety

Margin of Safety = Actual Sales - Break Even Sales

Margin of Safety in Dollars (11,200 Units x $75) - $525,000

$315,000

Margin of Safety in Percentage [$315,000/840,000] x 100

37.50%

(4) Degree of Operating leverage

Sales

$840,000

Variable Costs

504,000

Contribution Margin

336,000

Fixed Costs

210,000

Net Operating income

126,000

Degree of Operating leverage

= Contribution Margin / Net Operating Income

= $336,000 / 126,000

= 2.67

Degree of Operating leverage = 2.67

Selling Price per unit	$75 per unit
Variable Expense per unit	$45 per unit
Contribution Margin per unit	$30 per unit
CM Ratio [$30/$75] x 100	40%
Variable Expense Ratio [$45/$75] x 100	60%