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.

All answers must be in a formula not just numbers

Answer 1

Check the row number:

They are in the same row and the formula for the same is mentioned in last column, Can copy and paste it in excel.

Row No
	Particulars			Links
4	Units Sales	11,200	Units
5	Selling price	75	Per unit
6	Variable Expenses	45	Per unit
7	Fixed Expenses	210,000
8
9	Use the date to answer the following:
10
11	1. Compute the CM Ratio
12	Selling price per unit	75	Per unit	=B5
13	Variable expense per unit	45	Per unit	=B6
14	Contribution Margin per unit	30	Per unit	=+B12-B13
15
16	CM Ratio	40.00%		=+B14/B12
17	Variable expense ratio	60.00%		=+B13/B12
18
19	2. Compute the break even sales
20	Break Even in unit sales	7,000		=+B7/B14
21	Break Even in dollar sales	525,000		=+B7/B16
22
23	3. Compute the margin of safety
24	Margin of safety in dollar	315,000		=+(B4*B5)-B21
25	Margin of safety percentage	37.50%		=+B24/(11200*75)
26
27	4. Compute the degree of operating leverage
28	Sales	840,000		=+B4*B5
29	Variable Expenses	504,000		=+B4*B6
30	Contribution margin	336,000		=+B28-B29
31	Fixed Expenses	210,000		=+B7
32	Net Operating income	126,000		=+B30-B31
33
34	Degree of Operating Leverage	2.667		=+B30/B32
35