Question

help with the following pls

and excel 1 Saved Help Sev Required information Chapter 5: Applying Excel The Chapter 5 Form worksheet is to be used to create your own worksheet version of the Review Problem example in the text. Chapter 5: Applying Excel: Excel Worksheet (Part 1 of 2) Download the Applying Excel form and enter formulas in all cells that contain question marks. For example, in cell B13 enter the formula "=85". After entering formulas in all of the cells that contained question marks, verify that the dollar a match the example in the text. Check your worksheet by changing the fixed expenses to $270,000. If your worksheet is operating properly, the degree of operating leverage 10. If you do not get this answer, find the errors in your worksheet and correct them. Save your completed Applying Excel form to your computer and then upload it here by clicking "Browse." Next, click "Save." You will use worksheet to answer the questions in Part 2. download reference file upload a response file (15MB max) Choose File No file chosen Chapter_5_Applying_Excel_Student_Form.xls

Answer 1

Answer 1

E F G BC C D 3 Data 4 Unit Sales 20000 units 5 Selling price per unit $ 60 per unit 6 Variable expenses per unit $ 45 per unit 7 Fixed expenses $ 240,000 8 9 Enter a formula into each of the cells marked with a ? below 10 Review Problem: CVP Relationships 11 12 Compute the CM ratio and variable expenses ratio 13 Selling price per unit 60 per unit =B5 14 Variable expenses per unit 45 per unit =B6 15 Contribution margin per unit 15 per unit =B13-B14 16 25.00 =(B15/B13)*100 =(B14/B13)*100 =B7/B15 =B21*B13 =(B4*B5)-B22 =B25/(B4*B5)*100 17 CM ratio 18 Variable expense ratio 75.00 19 20 Compute the break-even 21 Break-even in unit sales 16,000 22 Break-even in dollar sales $ 960,000 23 24 Compute the margin of safety 25 Margin of safety in dollars $ 240,000 26 Margin of safety percentage 20.00 27 28 Compute the degree of operating leverage 29 Sales $1,200,000 30 Variable expenses $ 900,000 31 Contribution margin $ 300,000 32 Fixed expenses $ 240,000 33 Net operating income $ 60,000 34 35 Degree of operating leverage 5.00 ► Sheet1 Sheet2 Sheet3 =B4*B5 =B4*B6 =B29-B30 =B7 =B31-B32 =B31/B33

Answer 2 & 3

Chapter 5: Applying Excel Data Unit Sales Selling price per unit Variable expenses per unit Fixed expenses 50000 units $ 60 per unit $ 24 per unit $1,710,000 Enter a formula into each of the cells marked with a ? below Review Problem: CVP Relationships Compute the CM ratio and variable expenses ratio Selling price per unit 60 per unit Variable expenses per unit 24 per unit Contribution margin per unit 36 per unit CM ratio Variable expense ratio 60.00 40.00 Compute the break-even Break-even in unit sales Break-even in dollar sales 47,500 $2,850,000 Ans for 2(a) Compute the margin of safety Margin of safety in dollars Margin of safety percentage $ 150,000 5.00 Ans for 2(b) Compute the degree of operating leverage Sales $3,000,000 Variable expenses $1,200,000 Contribution margin $1,800,000 Fixed expenses $1,710,000 Net operating income $ 90,000 Degree of operating leverage 20.00 Ans for 2(c) Ans for 3 Increase % in Profit 400.00 = Increase % of Sales x Degree of Operating Leverage = 20 x 20.00 = 400%

Answer 4

Chapter 5: Applying Excel Data Unit Sales Selling price per unit Variable expenses per unit Fixed expenses 60000 units $ 60 per unit $ 24 per unit $1,710,000 Enter a formula into each of the cells marked with a ? below Review Problem: CVP Relationships Compute the CM ratio and variable expenses ratio Selling price per unit per unit Variable expenses per unit 24 per unit Contribution margin per unit 36 per unit CM ratio Variable expense ratio 60.00 40.00 Compute the break-even Break-even in unit sales Break-even in dollar sales 47,500 $ 2,850,000 Compute the margin of safety Margin of safety in dollars Margin of safety percentage $ 750,000 20.83 Compute the degree of operating leverage Sales $3,600,000 Variable expenses $ 1,440,000 Contribution margin $2,160,000 Fixed expenses $1,710,000 Net operating income $ 450,000 Ans for 4(a) Increase % in profit 400.00 Ans for 4(b) = ( New net operating income - Old net operating income) / Old net operating income )*100