Donaldson Company's contribution format income statement for the most recent month is shown below: Per Unit...

Transcribed Image Text:

Donaldson Company's contribution format income statement for the most recent month is shown below: Per Unit $12.50 7.50 $5.00 Sales Variable expenses Contribution margin Fixed expenses Net operating income Total $500,000 300,000 200,000 180,000 $ 20,000 The company sold 40,000 units in its most recent month and its relevant range of production is 30,000 to 50,000 units. Click here to download the Excel template, which you will use to answer the questions that follow. Click here for a brief tutorial on Goal Seek in Excel. Click here for a brief tutorial on SOLVER in Excel. Click here for a a brief tutorial on Charts in Excel, 4. Using Charts: a. Prepare a profit graph for Donaldson Company. To prepare your graph, use sales volumes of zero units and 50,000 units as shown in cells 823 and 824. 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. As a second step, copy your formula from cell C23 to cell C24. The profit (or loss) shown in cells C23 and C24 provide the two data points that you need to create a profit graph. b. Prepare a CVP graph for Donaldson Company. To prepare your graph, use sales volumes of zero units and 50,000 units as shown in cells 829 and 830. As a first step, create formulas in cells C29, D29, and E29 that compute the total sales dollars, total fixed expenses, and total expenses 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. 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. 5. Next, assume the company can decrease its variable expense per unit from $7.50 to $7.00 and decrease its fixed expenses from $180,000 to $150,000. Based on these assumptions: a. Prepare a profit graph for Donaldson Company. b. Prepare a CVP profit graph for Donaldson Company. 1 3 4 5 Data Inputs Unit sales 6 Selling price per unit 27 Variable expense per unit 8 Total fixed expenses 9 10 11 12 Sales 13 Variable expenses 14 Contribution margin 15 Fixed expenses 29 30 31 16 Net operating income 17 18 Net profit margin percentage 19 20 21 Profit Graph Information 22 23 27 CVP Graph Information Contribution Format Income Statement Total Per Unit Data Points $ $ $ Data Points $ 40,000 12.50 7.50 180,000 $ 500,000 $ 300,000 200,000 $ 180,000 20,000 4.00% 50,000 50,000 Profit 12.50 7.50 5.00 Sales Dollars Donaldson Company % 100% 60% 40% Fixed Expenses Total Expenses Margin of Safety Actual sales (a) Break-even sales (b) Margin of safety in dollars (a)-(b) Margin of safety percentage $ $ $ G 500,000 450,000 50,000 10.00% H Donaldson Company's contribution format income statement for the most recent month is shown below: Per Unit $12.50 7.50 $5.00 Sales Variable expenses Contribution margin Fixed expenses Net operating income Total $500,000 300,000 200,000 180,000 $ 20,000 The company sold 40,000 units in its most recent month and its relevant range of production is 30,000 to 50,000 units. Click here to download the Excel template, which you will use to answer the questions that follow. Click here for a brief tutorial on Goal Seek in Excel. Click here for a brief tutorial on SOLVER in Excel. Click here for a a brief tutorial on Charts in Excel, 4. Using Charts: a. Prepare a profit graph for Donaldson Company. To prepare your graph, use sales volumes of zero units and 50,000 units as shown in cells 823 and 824. 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. As a second step, copy your formula from cell C23 to cell C24. The profit (or loss) shown in cells C23 and C24 provide the two data points that you need to create a profit graph. b. Prepare a CVP graph for Donaldson Company. To prepare your graph, use sales volumes of zero units and 50,000 units as shown in cells 829 and 830. As a first step, create formulas in cells C29, D29, and E29 that compute the total sales dollars, total fixed expenses, and total expenses 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. 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. 5. Next, assume the company can decrease its variable expense per unit from $7.50 to $7.00 and decrease its fixed expenses from $180,000 to $150,000. Based on these assumptions: a. Prepare a profit graph for Donaldson Company. b. Prepare a CVP profit graph for Donaldson Company. 1 3 4 5 Data Inputs Unit sales 6 Selling price per unit 27 Variable expense per unit 8 Total fixed expenses 9 10 11 12 Sales 13 Variable expenses 14 Contribution margin 15 Fixed expenses 29 30 31 16 Net operating income 17 18 Net profit margin percentage 19 20 21 Profit Graph Information 22 23 27 CVP Graph Information Contribution Format Income Statement Total Per Unit Data Points $ $ $ Data Points $ 40,000 12.50 7.50 180,000 $ 500,000 $ 300,000 200,000 $ 180,000 20,000 4.00% 50,000 50,000 Profit 12.50 7.50 5.00 Sales Dollars Donaldson Company % 100% 60% 40% Fixed Expenses Total Expenses Margin of Safety Actual sales (a) Break-even sales (b) Margin of safety in dollars (a)-(b) Margin of safety percentage $ $ $ G 500,000 450,000 50,000 10.00% H