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.

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

3. 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.

4. 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.

5. 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.

6. 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.

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

8. 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.

9. 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.

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.

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

3. 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.

4. 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.

5. 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.

6. 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.

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

8. 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.

9. 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.