CVP Analysis using Excel\'s Basic Math Functions JPL. Inc has provided ts sales
ID: 2397598 • Letter: C
Question
CVP Analysis using Excel's Basic Math Functions JPL. Inc has provided ts sales and expense data for the most recent penod. The Controller has asked you prepare a spreadsheet that shows the related CVP Analyss computations Use the Information Indluded in the Excel Simulation and the Excel functions desanbed below to complose the task Cell Reference Allows you to refor to data from another cel in the worksheet From the Excel Stmulation below, If in a blank co, -B5" was entered, the formula would output the result from cell B5, or 75 in this example Basic Math functions Allows you to use the basic math symbols to perform mathematical functions. You can use the following keys: tplus sign to add-minus sign to subtract (asteisk sign to mubply), and / (forward slash to divide). From the Excel Simulation below, if in a blank cell-85-86 was entered, the formula would add the values from those colls and output the result or 120 in this the other math symbols the result would output an appropriabe answer for ts function. example using CVP Analysis E el dting Given the following informatign complete a CVP analysis 11,200 S75 per unit as pet unit $210,000 s use The dala to answer the foilowing of 2 Next > search DOLL F6 FB F9 F10 5 6 7 8Explanation / Answer
Selling Price per Unit = B5 = 75
Variable Expenses per Unit = B6 = 45
Contribution margin per unit = B12-B3 = 30 per unit
CM Ratio = B14/B12 = 0.4
Variable Expense ratio = B13/B12 = 0.6
Break Even Point
Break even point in unit sales = Fixed Expenses/Contribution per Unit
= B7/B14 = 7000 units
Break even in Dollar Sales = Fixed Expenses/CM Ratio
= B7/B16 = 525,000
3 Margin of Safety
In Dollars = Sales – Break Even Sales in Dollars
=B4*B12 – B21 = 315,000
% = B24*100/B4*B12 = 37.5%
4 Degree of Operating leverage
Sales = B4*B12 = 840,000
Variable Expenses = B4*B6 = 504,000
Contribution Margin = B28-B29 = 336,000
Fixed Expenses = B7 = 210,000
Net Operating Income = B30-B31 = 126,000
Degree of Operating Leverage = Contribution/Net Operating Income
=B30/B32 = 2.67