Question

how do i answer these questions using this spreadsheet at the bottom
2. Change all of the numbers in the data area of your worksheet so that it looks like this: BC 1 Chapter 6: Applying Excel 2 3 4 5 6 7 Data Unit sales Selling price per unit Variable expenses per unit Fixed expenses 70,000 units $ 20 per unit $ 12 per unit $ 448,000 If your formulas are correct, you should get the correct answers to the following questions. (a) What is the break-even in dollar sales? Break-even in dollar sales $ 1,120,000 (b) What is the margin of safety percentage? Margin of safety percentage 23 %

Answer 1

Answer is given below

Please link the excel cells accordingly to get below answers

a) Break Even Point in $ sales (Fixed Cost/Contribution Margin) $1,120,000 Dollar Sales Fixed Cost Contribution Margin % $448,000 40% $1,120,000 Per unit $20 $12 Selling price Variable cost Contribution margin per unit Contribution Margin % (Contribution/Sales)% 40% b) $280,000 A-B Margin of Safety (Total Sales - Break even sales) Total Sales Break even sales $1,400,000 A $1,120,000 B c) Degree of Operating Leverage Contribution Operating profit $560,000 $112,000 5 Sales Less: Variable cost Contribution Les: Fixed cost Net Operating Income $1,400,000 70000*20 $840,000 70000*12 $560,000 $448,000 $112,000 3 L 100%(20% *5) Percentage change in net operating income (Sales growth * Degree of Operating leverage) Unit Sales Selling price per unit Variable cost per unit Fixed Expense 84,000 units $20 per unit $12 per unit $448,000 Sales Less: Variable cost Contribution Les: Fixed cost Net Operating Income(Loss) $1,680,000 84000*20 $1,008,000 84000*12 $672,000 $448,000 $224,000 4a) b) % Increase in net operating income 100% A/B Existing income Increase in net income $112,000 A $112,000 B ($224,000-$112,000) a) Break even point Unit Sales Fixed Cost Contribution margin per unit $487,500 3,250 150 Selling price Variable cost Contribution margin per unit Contribution Margin % (Contribution/Sales)% Per unit $13,000 $9,750 $3,250 25.00% b) $1,950,000 Margin of Safety (Total Sales - Break even sales) Total Sales Break even sales $3,900,000 (300*13,000) $1,950,000 (150*13,000) c) Degree of Operating Leverage Contribution Operating profit $975,000 $487,500 2 Sales Less: Variable cost Contribution Les: Fixed cost Net profit $3,900,000 300*13000 $2,925,000 300*9750 $975,000 $487,500 $487,500 Sales Less: Variable cost Contribution Les: Fixed cost Net Operating Income $3,240,000 300*10800 $2,925,000 300*9750 $315,000 $385,500 -$70,500