Question

Given the following data, what formulas would one use to complete a spreadsheet that shows the related CVP Analysis computations? Thanks!

Course Documents-MA \ D Study Guide for Test-2-, ?D Chapter 5 CVP Excel x \. x ox?0.32041458693813785 1527375348486 Caibri Blu- .2.?. Alignment Number Cordboral Formetas. Cell Cells Editing Formatting TableStyles Styies - A1 ¥ | i | X ? | Given the following information complete a CVP analysis 1 Given the following information complete a CVlanalysis 2 for JPL, Inc 4 Unit sales 11,200 units Selling price per unit 6 Variable expenses per unit $75 per unit 45 per unit Fixed expenses $210,000 9 Use the data to answer the following. 11 1. Compute the CM ratio and variable expense ratio 2 Selling price per unit 1 Variable expenses per unit 14 Contribution margin per unit per unit per unit per unit 16 CM ratio 17 Varlable expense ratio 19 2. Compute the break-even paint 20 Break-even in unit sales 21 Break-even in dollar sales units 23 3.Compute the margin of s n doll Sheeti eBook & Resources

Answer 1

Answer

• All working forms part of the answer
• Please follow the ‘working equation column’ to understand computation of figures

A	Unit Sales	11200	units
B	Selling Price per unit	$                                   75.00	per unit
C	Variable expenses per unit	$                                   45.00	per unit
D	Fixed Expenses	$                       2,10,000.00
B	Selling Price per unit	$                                   75.00	per unit
C	Variable expenses per unit	$                                   45.00	per unit
E= B - C	Contribution margin per unit	$                                   30.00	per unit
F = E/B	CM ratio	40%
G = C/B	Variable Expense ratio	60%
H = D/E	Break Even in Unit Sales	7000
I = D/F	Break Even in Dollar Sales	$                       5,25,000.00
J = 11200 x $75	Total Sales	$                       8,40,000.00
K = I	Break Even in Dollar Sales	$                       5,25,000.00
L = J - K	Margin of Safety in dollars	$                       3,15,000.00
M = J	Sales	$                       8,40,000.00
N = 11200 x $45	Variable expenses	$                       5,04,000.00
O = M - N	Contribution margin	$                       3,36,000.00
P = D	Fixed Expenses	$                       2,10,000.00
Q = O - P	Net Operating Income	$                       1,26,000.00
R = O/Q	Degree of operating Leverage	2.67