D Data Inputs: Unit sales Selling price per unit Variable expense per unit Total fixed expenses...

Transcribed Image Text:

D Data Inputs: Unit sales Selling price per unit Variable expense per unit Total fixed expenses 2 Sales Variable expenses Contribution margin Fixed expenses Net operating income Net profit margin percentage $ $ $ Contribution Format Income Statement Total Per Unit $ 40,000 12.50 7.50 180,000 $ 500,000 $ 300,000 200,000 $ 180,000 20,000 4.00% 12.50 7.50 5.00 % 100% 60% 40% Margin of Safety Actual sales (a) Break-even sales (b) Margin of safety in dollars (a) (b) Margin of safety percentage $ $ $ 500,000 450,000 50,000 10.00% Open the Excel file and go to the "Template" tab. 1. Using Goal Seek: a. Calculate the unit sales required to break even. b. Calculate the unit sales required to attain a target profit of $25,000. What would be the company's margin of safety (in dollars) at this level of sales? c. Calculate the selling price per unit required to attain net operating income of $30,000. d. Calculate the variable expense per unit required to attain net operating income of $30,000. e. Calculate the unit sales required to attain a margin of safety of $75,000. 2. Using Solver: a. Calculate the unit sales required to attain a margin of safety percentage of 15%. b. Restore the original values in your Excel file. Calculate the unit sales required to attain a profit margin of 8%. c. Restore the original values in your Excel file. Calculate the selling price per unit required to attain a profit margin of 10%. d. Restore the original values in your Excel file. Calculate the variable expense per unit required to attain a profit margin of 6%. Please enter the answers for part 1 and 2 on Answers Tab D Data Inputs: Unit sales Selling price per unit Variable expense per unit Total fixed expenses 2 Sales Variable expenses Contribution margin Fixed expenses Net operating income Net profit margin percentage $ $ $ Contribution Format Income Statement Total Per Unit $ 40,000 12.50 7.50 180,000 $ 500,000 $ 300,000 200,000 $ 180,000 20,000 4.00% 12.50 7.50 5.00 % 100% 60% 40% Margin of Safety Actual sales (a) Break-even sales (b) Margin of safety in dollars (a) (b) Margin of safety percentage $ $ $ 500,000 450,000 50,000 10.00% Open the Excel file and go to the "Template" tab. 1. Using Goal Seek: a. Calculate the unit sales required to break even. b. Calculate the unit sales required to attain a target profit of $25,000. What would be the company's margin of safety (in dollars) at this level of sales? c. Calculate the selling price per unit required to attain net operating income of $30,000. d. Calculate the variable expense per unit required to attain net operating income of $30,000. e. Calculate the unit sales required to attain a margin of safety of $75,000. 2. Using Solver: a. Calculate the unit sales required to attain a margin of safety percentage of 15%. b. Restore the original values in your Excel file. Calculate the unit sales required to attain a profit margin of 8%. c. Restore the original values in your Excel file. Calculate the selling price per unit required to attain a profit margin of 10%. d. Restore the original values in your Excel file. Calculate the variable expense per unit required to attain a profit margin of 6%. Please enter the answers for part 1 and 2 on Answers Tab D Data Inputs: Unit sales Selling price per unit Variable expense per unit Total fixed expenses 2 Sales Variable expenses Contribution margin Fixed expenses Net operating income Net profit margin percentage $ $ $ Contribution Format Income Statement Total Per Unit $ 40,000 12.50 7.50 180,000 $ 500,000 $ 300,000 200,000 $ 180,000 20,000 4.00% 12.50 7.50 5.00 % 100% 60% 40% Margin of Safety Actual sales (a) Break-even sales (b) Margin of safety in dollars (a) (b) Margin of safety percentage $ $ $ 500,000 450,000 50,000 10.00% Open the Excel file and go to the "Template" tab. 1. Using Goal Seek: a. Calculate the unit sales required to break even. b. Calculate the unit sales required to attain a target profit of $25,000. What would be the company's margin of safety (in dollars) at this level of sales? c. Calculate the selling price per unit required to attain net operating income of $30,000. d. Calculate the variable expense per unit required to attain net operating income of $30,000. e. Calculate the unit sales required to attain a margin of safety of $75,000. 2. Using Solver: a. Calculate the unit sales required to attain a margin of safety percentage of 15%. b. Restore the original values in your Excel file. Calculate the unit sales required to attain a profit margin of 8%. c. Restore the original values in your Excel file. Calculate the selling price per unit required to attain a profit margin of 10%. d. Restore the original values in your Excel file. Calculate the variable expense per unit required to attain a profit margin of 6%. Please enter the answers for part 1 and 2 on Answers Tab D Data Inputs: Unit sales Selling price per unit Variable expense per unit Total fixed expenses 2 Sales Variable expenses Contribution margin Fixed expenses Net operating income Net profit margin percentage $ $ $ Contribution Format Income Statement Total Per Unit $ 40,000 12.50 7.50 180,000 $ 500,000 $ 300,000 200,000 $ 180,000 20,000 4.00% 12.50 7.50 5.00 % 100% 60% 40% Margin of Safety Actual sales (a) Break-even sales (b) Margin of safety in dollars (a) (b) Margin of safety percentage $ $ $ 500,000 450,000 50,000 10.00% Open the Excel file and go to the "Template" tab. 1. Using Goal Seek: a. Calculate the unit sales required to break even. b. Calculate the unit sales required to attain a target profit of $25,000. What would be the company's margin of safety (in dollars) at this level of sales? c. Calculate the selling price per unit required to attain net operating income of $30,000. d. Calculate the variable expense per unit required to attain net operating income of $30,000. e. Calculate the unit sales required to attain a margin of safety of $75,000. 2. Using Solver: a. Calculate the unit sales required to attain a margin of safety percentage of 15%. b. Restore the original values in your Excel file. Calculate the unit sales required to attain a profit margin of 8%. c. Restore the original values in your Excel file. Calculate the selling price per unit required to attain a profit margin of 10%. d. Restore the original values in your Excel file. Calculate the variable expense per unit required to attain a profit margin of 6%. Please enter the answers for part 1 and 2 on Answers Tab

Answer rating: 100% (QA)

Here are the answers on the Answers tab 1 Using Go... View the full answer