12  Break-Even Analysis

12.1  Based On Accounting Profit

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.

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.

 

  1. 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:

FIGURE 12.3  Spreadsheet for Sensitivity of Costs, Revenues, and Accounting Profits to Unit Sales.

  1. 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.
  2. 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.

12.2  Based On NPV

Problem. Suppose a firm is considering the following project, where all of the dollar figures are in thousands of dollars. In year 0, the project requires $11,350 investment in plant and equipment, is depreciated using the straight-line method over seven years, and there is a salvage value of $1,400 in year 7. The project is forecast to generate sales of 2,100 units in year 1 and grow at a sales growth rate of 55.0% in year 2. The sales growth rate is forecast to decline by 15.0% in years 3 and 4, to decline by 20.0% in year 5, to decline by 25.0% in year 6, to decline by 30.0% in year 7. Unit sales will drop to zero in year 8. The inflation rate is forecast to be 2.0% in year 1, rising to 4.0% in year 5, and then leveling off. The real cost of capital is forecast to be 11.0% in year 1, rising to 12.2% in year 5, and then leveling off. The tax rate is forecast to be a constant 35.0%. Sales revenue per unit is forecast to be $9.70 in year 1 and then grow with inflation. Variable cost per unit is forecast to be $7.40 in year 1 and then grow with inflation. Cash fixed costs are forecast to be $5,280 in year 1 and then grow with inflation. What is the project NPV? What is the NPV Break-Even Point in Year 1 Unit Sales, where NPV equals zero? What is the NPV Break-Even Point in the Year 2 Sales Growth Rate, where NPV equals zero? What is the NPV Break-Even Contour in the two-dimensional space of Year 1 Unit Sales and Year 2 Sales Growth Rate?

 

Solution Strategy. Start with the Project NPV - Basics spreadsheet. Move the Unit Sales line out of the Key Assumptions area, since that is what we are going to solve for. Restructure the Unit Sales forecast to depend on the Sales Growth Rate, which we be a key variable. Structure the Sales Grow Rate forecast over the entire to period to depend on how fast the growth rate is initially. This will make it easy to use Solver and to create a Data Table later on. Project the cash flows of the project and calculate the NPV. Use Solver to determine the amount of year 1 unit sales that will cause the NPV to equal zero, when the sales growth rate is at the base case level of 5% per year. Use Solver to determine the sales growth rate that will cause the NPV to equal zero, when the year 1 unit sales is at the base case level of 39,000. Create a two-variable data table using two input variables (year 1 unit sales and sales growth rate) and the output variable: NPV. Use the data table to create a three-dimensional graph showing the NPV Break-Even Contour.

 

FIGURE 12.4  Spreadsheet for Break-Even Analysis Based On Net Present Value.

 

  

 

How To Build This Spreadsheet Model.

 

  1. Open the Basics Spreadsheet and Add Rows. Open the spreadsheet that you created for Project NPV - Basics and immediately save the spreadsheet under a new name using the  File | Save As command. Select the cell A15 and click on Insert | Row. Select the range A5:I5, click on Edit | Cut, select the cell A15, and click on Edit | Paste.

 

  1. Inputs. Enter the year 2 sales growth rate into the cell D5, the change in the sales growth rate into the range E6:I6, and the year 1 unit sales of 2,100 in cell C16.
  2. Sales Growth Rate on date t = (Sales Growth Rate on date t-1) + (Change in Sales Growth Rate on date t). Enter =D5+E6 in cell E5 and copy it across.
  3. Unit Sales on date t = (Unit Sales on date t-1) * (1 + Unit Sales Growth Rate). Enter =C16*(1+D5) in cell D16 and copy it across.

The project NPV is $3,217 and should be accepted. But how sure are you of this result? How sensitive is this result to small changes in the assumptions? The Break-Even Point gives you an idea of the robustness of this result.

  1. NPV Break-Even Point in Year 1 Unit Sales. Use the built-in Solver tool to numerically solve for the NPV Break-Even Point in Year 1 Unit Sales. From Excel’s main menu, click on Tools and then Solver. (If Solver does not appear on the Tools menu, then click on Add-Ins, check Solver Add-In, and click on OK.) In the Solver dialog box, enter the Net Present Value cell B40 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 the Year 1 Unit Sales cell C16 as the By Changing Cell. See figure below. Click on the Solve button.

FIGURE 12.5  Solver dialog box.


 
By trial and error, the Solver adjusts the value of the Year 1 Unit Sales in cell C16 until the Net Present Value in cell B40 equals zero (within a very small error tolerance). This results in a NPV Break-Even Point in Year 1 Unit Sales (shown in cell C16) of 1,853.

  1. NPV Break-Even Point in Sales Growth Rate. Repeat the NPV Break-Even Analysis only using Year 2 Sales Growth Rate as the changing cell. Enter 2100 in cell C16 in order to restore the default assumption for Year 1 Unit Sales. From Excel’s main menu, click on Tools and then Solver. In the Solver dialog box, enter Year 2 Sales Growth Rate cell D5 as the By Changing Cell. Click on the Solve button. By trial and error, the Solver adjusts the value of the Sales Growth Rate in cell D5 until the Net Present Value in cell B40 equals zero. This results in a NPV Break-Even Point in Sales Growth Rate (shown in cell D5) of 49.5%. 

 

FIGURE 12.6  Two Way Data Table and 3D Graph.

 

  1. Create A List of Input Values and An Output Formula. Create a list of input values for the Year 1 Unit Sales (1,700, 1,900, 2,100, etc.) in the range C45:F45. Similarly, create a list of input values for the Year 2 Sales Growth Rate (45.0%, 50.0%, 55.0%, etc.) in the range B46:B50. Create an output formula that references the Net Present Value by entering the formula =B40 in cell B45.
  2. Two-Variable Data Table. Select the range B45:F50 for the Two-Variable Data Table. This range includes both the list of input values at the top and side of the data table and the output formula in the upper left corner. Then choose Data | Table from the main menu and a Table dialog box pops up. Enter the cell address C16 (Year 1 Unit Sales) in the Row Input Cell, enter the cell address D5 (Year 2 Sales Growth Rate) in the Column Input Cell, and click on OK. The data table shows what combinations of Year 1 Unit Sales and Year 2 Sales Growth yield a positive NPV. Thus, you can assess how optimistic vs. pessimistic your assumptions have to be in order to a get a positive NPV. Thus, you see how robust your conclusions are to variations in the inputs.
  3.  3-D Graph. Highlight the interior of the data table (excluding the top or side) in the range C46:F50 and then choose  Insert | Chart from the main menu. Select a Surface chart type and make other selections to complete the Chart Wizard.

 

The 3-D Graph shows the Net Present Value of the project for combinations of Year 1 Unit Sales and Year 2 Sales Growth Rate. The multi-color surface illustrates various ranges of NPV. In the top corner, the dark blue color is for NPV > $15,000. Below it, a light red section is for a NPV of $10,000 to $15,000. And so on. At the intersection of the Light Green section ($0 to $5,000) and the Light Yellow section (-$5,000 to $0) is a contour highlighted by the arrow. This is the NPV Break-Even Contour, where NPV = 0. Every point on this contour represents a combination of Year 1 Unit Sales and Year 2 Sales Growth Rate for which the NPV = 0. The 3-D Graph shows that project's positive NPV is very sensitive. If the Year 1 Unit Sales are a little bit lower than assumed or if the year 2 Sale Growth Rate is a little bit lower than assumed, then the whole project could have a negative NPV.

Problems

Skill-Building Problems.

  1. A project has a fixed cost of $73,000, variable costs of $9.20 per unit, and generates sales revenue of $15.40 per unit. What is the break-even point in unit sales, where accounting profit exactly equals zero, and what is the intuition for it?
  2. Suppose a firm is considering the following project, where all of the dollar figures are in thousands of dollars. In year 0, the project requires $24,490 investment in plant and equipment, is depreciated using the straight-line method over seven years, and there is a salvage value of $5,800 in year 7. The project is forecast to generate sales of 4,800 units in year 1 and grow at a sales growth rate of 72.0% in year 2. The sales growth rate is forecast to decline by 12.0% in years 3, to decline by 15.0% in year 4, to decline by 18.0% in year 5, to decline by 23.0% in year 6, to decline by 29.0% in year 7. Unit sales will drop to zero in year 8. The inflation rate is forecast to be 2.7% in year 1 and rising to 3.5% in year 7. The real cost of capital is forecast to be 10.2% in year 1, rising to 11.9% in year 7. The tax rate is forecast to be a constant 38.0%. Sales revenue per unit is forecast to be $12.20 in year 1 and then grow with inflation. Variable cost per unit is forecast to be $7.30 in year 1 and then grow with inflation. Cash fixed costs are forecast to be $6,740 in year 1 and then grow with inflation. What is the project NPV? What is the NPV Break-Even Point in Year 1 Unit Sales, where NPV equals zero? What is the NPV Break-Even Point in the Year 2 Sales Growth Rate, where NPV equals zero? What is the NPV Break-Even Contour in the two-dimensional space of Year 1 Unit Sales and Year 2 Sales Growth Rate?

Live In-class Problems.

  1. Given the partial Based On Accounting Profit spreadsheet BevenacZ.xls, do step 3 Back Solve for the Break-Even Point using the Income Statement.
  2. Given the partial Based On NPV spreadsheet BevennpZ.xls, do steps 7 Create a List of Input Variables and an Output Formula and 8 Two-Variable Data Table.