Using Microsoft Excel Charts:

a. Prepare a profit graph for D Company. To prepare your graph, use sales volumes of zero units and 50,000 units as shown in cells B23 and B24. As a first step, create a formula in cell C23 that computes the profit when unit sales are zero. Your formula should reference data from the contribution format income statement provided in rows 4 through 18 of the worksheet. In creating your formula, you may need to use absolute cell references (for example, instead of referencing B6, reference $B$6) As a second step, copy your formula from cell C23 to cell C240. The profit (or loss) shown in cells C23 and C24 provides the two data points that you need to create a profit graph. Highlight cells B22 through C24, then click on Insert, then Recommended Charts, then All Charts, then X Y (Scatter), then (at top) Scatter with Straight Lines, then choose second chart, then OK, then click on corner the proft graph and move using the left/right/up/down buttons.

b. Prepare a CVP graph for D Company. To prepare your graph, use sales volumes of zero units and 50,000 units as shown in cells B29 and B30. As a first step, create formulas in cells C29, D29, and E29 that compute the total sales dollars, total fixed expenses, and total expenses (fixed and variable) when unit sales are zero. Your formulas should reference data from the contribution format income statement provided in rows 4 through 18 of the worksheet. In creating your formula, you may need to use absolute cell references (for example, instead of referencing B6, reference $B$6) As a second step, copy your formulas from cells C29, D29, and E29, to cells C30, D30, and E30. The information shown in rows 29 and 30 provide the data points that you need to create a CVP graph. Highlight cells B28 through E30, then click on Insert, then Recommended Charts, then All Charts, then X Y (Scatter), then (at top) Scatter with Straight Lines, then choose second chart, then OK, then click corner of the on the CVP graph and move to using the left/right/up/down buttons.

c. Next, assume the company can decrease its variable expense per unit from $7.50 to $7.00 in cell B7 and decrease its fixed expenses from $180,000 to $150,000 in cell B8. Notice how your profit and CVP graphs automatically update. Save your file then upload your revised profit and CVP graphs to the assignment using the controls provided in the assignment.

A1 Xfx D Company A B C D E F G G . 1 D Company % 100% 60% 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% 40% 3 4 Data Inputs: 5 Unit sales 40,000 6 Selling price per unit $ 12.50 7 Variable expense per unit $ 7.50 8 Total fixed expenses $ 180,000 9 9 10 Contribution Format Income Statement 11 Total Per Unit 12 Sales $ 500,000 $ 12.50 13 Variable expenses 300,000 7.50 14 Contribution margin 200,000 $ 5.00 15 Fixed expenses 180,000 16 Net operating income $ 20,000 17 18 Net profit margin percentage 4.00% 19 20 21 Profit Graph Information 22 Units Profit 23 Data Points 24 50,000 25 26 27 CVP Graph Information 28 Units Sales Dollars 29 Data Points 30 50,000 Fixed Expenses Total Expenses IM IMI $ % 48-58 Formatas Table Call Styles x Delete BS Ax fx 40000 Ed Format B D 1 E G H D Company X 100% 60% 40% Margin of Safety Actual sales (a) Break-even sales (b) Margin of safety in dollars (a) (b) Margin of safety percentage 5 $ $ 500,000 450,000 50,000 10.00% 3 4 Data Inputs: 5 Unit sales 40,000 6 Selling price per unit $ 12.50 7 Variable expense per unit $ 7.50 8 Total fixed expenses s 180,000 9 10 Contribution Format Income Statement 11 Total Per Unit 12 Sales $ 500,000 $ 12.50 13 Variable expenses 300,000 7.50 14 Contribution margin 200,000 $ 5.00 15 Fixed expenses 180,000 16 Net operating income $ 20,000 17 18 Net profit margin percentage 4.00% 19 20 21 Profit Graph Information 22 Units Profit 23 S (180,000) Data Points 24 50,000 25 26 27 CVP Graph Information Units Sales Dollars 28 29 Data Points 50,000 30 Solver Goal Seek Charts + Template Fixed Expenses Total Expenses A1 Xfx D Company A B C D E F G G . 1 D Company % 100% 60% 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% 40% 3 4 Data Inputs: 5 Unit sales 40,000 6 Selling price per unit $ 12.50 7 Variable expense per unit $ 7.50 8 Total fixed expenses $ 180,000 9 9 10 Contribution Format Income Statement 11 Total Per Unit 12 Sales $ 500,000 $ 12.50 13 Variable expenses 300,000 7.50 14 Contribution margin 200,000 $ 5.00 15 Fixed expenses 180,000 16 Net operating income $ 20,000 17 18 Net profit margin percentage 4.00% 19 20 21 Profit Graph Information 22 Units Profit 23 Data Points 24 50,000 25 26 27 CVP Graph Information 28 Units Sales Dollars 29 Data Points 30 50,000 Fixed Expenses Total Expenses IM IMI $ % 48-58 Formatas Table Call Styles x Delete BS Ax fx 40000 Ed Format B D 1 E G H D Company X 100% 60% 40% Margin of Safety Actual sales (a) Break-even sales (b) Margin of safety in dollars (a) (b) Margin of safety percentage 5 $ $ 500,000 450,000 50,000 10.00% 3 4 Data Inputs: 5 Unit sales 40,000 6 Selling price per unit $ 12.50 7 Variable expense per unit $ 7.50 8 Total fixed expenses s 180,000 9 10 Contribution Format Income Statement 11 Total Per Unit 12 Sales $ 500,000 $ 12.50 13 Variable expenses 300,000 7.50 14 Contribution margin 200,000 $ 5.00 15 Fixed expenses 180,000 16 Net operating income $ 20,000 17 18 Net profit margin percentage 4.00% 19 20 21 Profit Graph Information 22 Units Profit 23 S (180,000) Data Points 24 50,000 25 26 27 CVP Graph Information Units Sales Dollars 28 29 Data Points 50,000 30 Solver Goal Seek Charts + Template Fixed Expenses Total Expenses