Question

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: Allows you to refer to data from another cell in the worksheet. From the Excel Simulation below. If in a blank cell,"=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: - plus sign to add).- (minus sign to subtract). (asterisk sign to multiply), and / (forward slash to divide). From the Excel Simulation below. If in a blank cell"=B5-B6" was entered, the formula would add the values from those cells and output the result, or 120 In this example. If using the other math symbols the result would output an appropriate answer for its function. 3 ? X XB FILE - Sign In AX . HOME Calibri B I be CVP Analysis - Excel PAGE LAYOUT FORMULAS DATA REVIEW VIEW A A $.. Alignment Number Conditional Format as Cell Cells - Formatting - Table - Styles Styles Given the following information complete a CVP analysis INSERT 11 U . B. Font o Paste Clipboard Editing A1 T 4 5 Init sales elling price per unit 'ariable expenses per unit ixed expenses 11,200 units $75 per unit $45 per unit $210,000 7 9 Ise the data to answer the following Compute the CM ratio and variable expense ratio 12 elling price per unit ariable expenses per unit ontribution margin per unit per unit per unit per unit 16 17 M ratio ariable expense ratio . Compute the break-even point reak-even in unit sales reak-even in dollar sales 20 21 units . Compute the margin of safety 24 largin of safety in dollars 25 1argin of safety percentage 27 Compute the deeree of operating leverage ... Sheet1 ... # READY Attempt(s) -- + 100% Show Me Hint

Answer 1

3 4 Sales Units 5 Selling Price Per unit 6 Varaibel Expense Per unit 7 Fixed Expenses 11200 Units 75 Per unit 45 Per unit 210000 75 Per unit 45 Per unit 30 Per unit 9 Use the Data To Answer the Following 10 11 1. Compute the CM ratio and Varaible Expense ratio 12 Selling Price Per unit 13 Variable costPer unit 14 Contribution Margin per unit (Selling Price - Varible Cost) 15 16 CM Ratio =( CM/Selling Price) 17 Varaible Expense Ratio = ( Variable Expense /Selling Price ) 18 19 2. Compute the Break Even point 20 Break Even in Unit Sales =( Fixed Cost / CM Per unit) 21 Brake Even in Dollar sales =( Fixed Cost/ CM ratio) 40% 60% 7000 Units 525000 Dollars

22 315000 37.50% 23 3. Compute the Margin of Safety 24 Margin of Safety in dollars =(units *selling price - BEP Dollars) 25 Margin of Safety in Percentage =(Margin of safety Dollars / Actual sales ) 26 27 4. Compute the Operating Leverage 28 Sales 29 Varaible Expenses 30 Contribution Margin 31 Fixed Cost 32 Net operating Income = (CM-Fixed Cost) 840000 504000 336000 210000 126000 33 34 Degree of Operating Leverage =(Contrubution Margin/ Net operating Income) 2.67 times

3 4 Sales Units 5 Selling Price Per unit 6 Varaibel Expense Per unit 7 Fixed Expenses 11200 75 Units Per unit Per unit 45 210000 9 Use the Data To Answer the following 10 11 1. Compute the CM ratio and Varaible Expense ratio 12 Selling Price Per unit 13 Variable costPer unit 14 Contribution Margin per unit ( Selling Price - Varible Cost) =B5 =B6 =B12-B13 Per unit Per unit Per unit =B14/B12 =B13/B12 16 CM Ratio =( CM/Selling Price) 17 Varaible Expense Ratio = ( Variable Expense /Selling Price ) 18 19 2. Compute the Break Even point 20 Break Even in Unit Sales =( Fixed Cost / CM Per unit) 21 Brake Even in Dollar sales =( Fixed Cost/ CM ratio) =B7/B14 =B7/B16 Units Dollars

в А 22 23 3. Compute the Margin of Safety 24 Margin of Safety in dollars =(units *selling price -BEP Dollars) =B28-B21 25 Margin of Safety in Percentage =(Margin of safety Dollars / Actual sale =B24/B28 26 27 4. Compute the Operating Leverage 28 Sales =B4*B12 29 Varaible Expenses =B4*B13 30 Contribution Margin =B28-B29 31 Fixed Cost =B7 32 Net operating Income = (CM-Fixed Cost) =B30-B31 33 34 Degree of Operating Leverage =(Contrubution Margin/ Net operating =B30/B32 times