12.1 Based On Accounting Profit
К оглавлению1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 1617 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
85 86 87 88 89 90 91 92 93 94 95 96 97
Problem. A project has a fixed cost of $30,000, variable costs of $4.00 per unit, and generates sales
revenue of $6.00 per unit. What is the break-even point in unit sales, where accounting profit exactly
equals zero, and what is the intuition for it?
Solution Strategy. First, we solve for the break-even point in unit sales using the formula. Second, we
use Excel's Solver to back solve for the break-even point using the income statement. Lastly, we will
determine the sensitivity of costs, revenues, and accounting profits to unit sales. This will allow us to
graphically illustrate the intuition of the break-even point.
FIGURE 12.1 Spreadsheet for Break-Even Analysis - Based On Accounting Profit.
How To Build This Spreadsheet Model.
1. Inputs. Enter the inputs into the range B4:B6.
2. Break-Even Point using the Formula. The formula is: Break-Even Point = Fixed Costs / (Sales
Revenue/Unit - Variable Costs/Unit). In cell B9, enter =B4/(B5-B6) We see that the Break-Even
Point is 15,000 units.
3. Back solve for the Break-even Point using the Income Statement. Create the Income
Statement using these simple steps.
o Unit Sales. Enter a trial value for the break-even point. In cell B12, enter 12000
o Sales Revenue = (Sales Revenue/Unit) * (Unit Sales). In cell B13, enter =B12*B5
o Variable Costs = (Variable Costs/Unit) * (Unit Sales). In cell B14, enter =B12*B6
o Gross Margin = Sales Revenue - Variable Costs. In cell B15, enter =B13-B14
o Fixed Costs = input value for Fixed Costs. In cell B16, enter =B4
o Accounting Profit = Gross Margin - Fixed Costs. In cell B17, enter =B15-B16
Then call up Excel's Solver from Excel’s main menu by clicking on Tools and then Solver. (If
you don't see Solver on the Tools Menu, then click on Tools | Add-Ins, check the Solver Add-In
box, and click on OK.) Set-up the Solver dialog box by entering Accounting Profit in cell B17 as
the Set Target Cell. In the Equal to row, click on the option button for Value of and enter 0 in the
adjacent box. Enter Unit Sales in cell B12 as the By Changing Cell. See the figure below.
FIGURE 12.2 Solver dialog box.
Then run Solver by clicking on the Solve button. By trial and error, the Solver adjusts the value of
Unit Sales in cell B12 until the Accounting Profit in cell B17 equals zero (within a very small
error tolerance). This results in an Break-even Point of 15,000, where Accounting Profit equals
zero. Your results may differ by a slight amount depending on Solver's error tolerance. This
verifies that the Break-Even Point is 15,000 units.
4. Create A List of Input Values and An Output Formula. Create a list of input values for Unit
Sales (0, 5,000, 10,000, etc.) in the range C21:G21. Create output formulas that reference the
pieces of the accounting profit calculation. Specifically:
o for Fixed Cost, enter =B16 in cell B22
o for Variable Costs, enter =B14 in cell B23
o for Total Costs, enter =B16+B14 in cell B24
o for Sales Revenue, enter =B13 in cell B25
o for Accounting Profit, enter =B17 in cell B26
FIGURE 12.3 Spreadsheet for Sensitivity of Costs, Revenues, and Accounting Profits to Unit Sales.
5. Data Table. Select the range B21:G26 for the Data Table. This range includes both the list of
input values at the top of the data table and the output formulas on the side of the data table. Then
choose Data Table from the main menu and a Table dialog box pops up. Enter the cell address
B12 (Unit Sales) in the Row Input Cell and click on OK.
6. Graph the Data Table Results. Highlight the data table C21:G26 and then choose Insert
Chart from the main menu. Select an XY(Scatter) chart type and make other selections to
complete the Chart Wizard.
The graph shows visually that the Break-Even Point is 15,000 units. The graph illustrates two equivalent
intuitions for this result. First, the Break-Even Point is where the Sales Revenue line (in blue) crosses
Total Costs line (in red). Second, the Break-Even Point is where Accounting Profit (in orange) hits zero
and thus decisively switches from negative to positive.
Problem. A project has a fixed cost of $30,000, variable costs of $4.00 per unit, and generates sales
revenue of $6.00 per unit. What is the break-even point in unit sales, where accounting profit exactly
equals zero, and what is the intuition for it?
Solution Strategy. First, we solve for the break-even point in unit sales using the formula. Second, we
use Excel's Solver to back solve for the break-even point using the income statement. Lastly, we will
determine the sensitivity of costs, revenues, and accounting profits to unit sales. This will allow us to
graphically illustrate the intuition of the break-even point.
FIGURE 12.1 Spreadsheet for Break-Even Analysis - Based On Accounting Profit.
How To Build This Spreadsheet Model.
1. Inputs. Enter the inputs into the range B4:B6.
2. Break-Even Point using the Formula. The formula is: Break-Even Point = Fixed Costs / (Sales
Revenue/Unit - Variable Costs/Unit). In cell B9, enter =B4/(B5-B6) We see that the Break-Even
Point is 15,000 units.
3. Back solve for the Break-even Point using the Income Statement. Create the Income
Statement using these simple steps.
o Unit Sales. Enter a trial value for the break-even point. In cell B12, enter 12000
o Sales Revenue = (Sales Revenue/Unit) * (Unit Sales). In cell B13, enter =B12*B5
o Variable Costs = (Variable Costs/Unit) * (Unit Sales). In cell B14, enter =B12*B6
o Gross Margin = Sales Revenue - Variable Costs. In cell B15, enter =B13-B14
o Fixed Costs = input value for Fixed Costs. In cell B16, enter =B4
o Accounting Profit = Gross Margin - Fixed Costs. In cell B17, enter =B15-B16
Then call up Excel's Solver from Excel’s main menu by clicking on Tools and then Solver. (If
you don't see Solver on the Tools Menu, then click on Tools | Add-Ins, check the Solver Add-In
box, and click on OK.) Set-up the Solver dialog box by entering Accounting Profit in cell B17 as
the Set Target Cell. In the Equal to row, click on the option button for Value of and enter 0 in the
adjacent box. Enter Unit Sales in cell B12 as the By Changing Cell. See the figure below.
FIGURE 12.2 Solver dialog box.
Then run Solver by clicking on the Solve button. By trial and error, the Solver adjusts the value of
Unit Sales in cell B12 until the Accounting Profit in cell B17 equals zero (within a very small
error tolerance). This results in an Break-even Point of 15,000, where Accounting Profit equals
zero. Your results may differ by a slight amount depending on Solver's error tolerance. This
verifies that the Break-Even Point is 15,000 units.
4. Create A List of Input Values and An Output Formula. Create a list of input values for Unit
Sales (0, 5,000, 10,000, etc.) in the range C21:G21. Create output formulas that reference the
pieces of the accounting profit calculation. Specifically:
o for Fixed Cost, enter =B16 in cell B22
o for Variable Costs, enter =B14 in cell B23
o for Total Costs, enter =B16+B14 in cell B24
o for Sales Revenue, enter =B13 in cell B25
o for Accounting Profit, enter =B17 in cell B26
FIGURE 12.3 Spreadsheet for Sensitivity of Costs, Revenues, and Accounting Profits to Unit Sales.
5. Data Table. Select the range B21:G26 for the Data Table. This range includes both the list of
input values at the top of the data table and the output formulas on the side of the data table. Then
choose Data Table from the main menu and a Table dialog box pops up. Enter the cell address
B12 (Unit Sales) in the Row Input Cell and click on OK.
6. Graph the Data Table Results. Highlight the data table C21:G26 and then choose Insert
Chart from the main menu. Select an XY(Scatter) chart type and make other selections to
complete the Chart Wizard.
The graph shows visually that the Break-Even Point is 15,000 units. The graph illustrates two equivalent
intuitions for this result. First, the Break-Even Point is where the Sales Revenue line (in blue) crosses
Total Costs line (in red). Second, the Break-Even Point is where Accounting Profit (in orange) hits zero
and thus decisively switches from negative to positive.