10  Project NPV

10.1  Basics

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,000 units in year 1, rising to 7,400 units in year 5, declining to 1,800 units in year 7, and dropping 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 7. 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?

Solution Strategy. Forecast key assumptions, discounting, sales revenue per unit, variable costs per unit, and fixed costs over the seven year horizon. Then, forecast the project income and expense items. Calculate the net cash flows. Discount each cash flow back to the present and sum to get the NPV.

Modeling Issue. The inflation rate is forecast separately and explicitly enters into the calculation of: (1) the discount rate (= cost of capital) and (2) price or cost / unit items. This guarantees that we are consistent in the way we are treating the inflation component of cash flows in the numerator of the NPV calculation and the inflation component of the discount rate in the denominator of the NPV calculation. This avoids a common error in practice that people often treat the cash flows and discount rates as if they were unrelated to each other and thus they are inconsistent in way that they implicitly treat the inflation component of each.

FIGURE 10.1 Spreadsheet for Project NPV - Basics.

How To Build Your Own Spreadsheet Model.

1. Set-up Titles and Freeze Panes. Enter column titles, such as 2001, 2002, etc. in row 2 and Year 0, Year 1, etc. in row 3. Then, place the cursor in cell B4 and click on Window | Freeze Panes. This freezes the top three rows to provide column titles and freezes the first column to provide row titles. 

 

2. Inputs. Enter the key assumptions in the range C5:I8, the year 1 price and cost inputs in the range C15:C17, the year 0 investment in plant and equipment (as a negative number) in cell B35, and the year 7 salvage value in cell I35.
3. Discounting. Calculate the (nominal) discount rate, which is the (nominal) cost of capital. Then calculate the cumulative discount rate.
• Discount Rate = Cost of Capital. The formula for the (Nominal) Discount Rate = (1 + Inflation Rate) * (1 + Real Discount Rate) - 1. Enter =(1+C6)*(1+C7)-1 in cell C11 and copy it across.
• Cumulative Discount Factor. This is the product of the year-by-year discount factors cumulated to a given date. Enter 0.0% in cell B12. The rest are calculated as (This Year's Cumulative Discount Factor) = (1 + Last Year's Cumulative Discount Factor) * (1 + This Year's Discount Rate) - 1. Enter =(1+B12)*(1+C11)-1 in cell C12 and copy across.
4. Forecast Price and Cost Items. The price and cost items are projected by growing the item at the inflation rate. This Year's Price/Cost = (Last Year's Price/Cost) * (1 + This Year's Inflation Rate). Enter =C15*(1+D$6) in cell D15 and copy the it to the range D15:I17. The $ signs in D$6 locks in row 6, which the inflation rate row.
5. Cash Flow Forecasts. Forecast each of the cash flow items as appropriate.

 

• Sales Revenue = (Sales Revenue / Unit) * (Units sold). Enter =C5*C15 in cell C20 and copy across.
• Variable Costs = (Variable Costs / Unit) * (Units sold). Enter =C5*C16 in cell C21 and copy across.
• Gross Margin = Sales Revenue - Variable Costs. Enter =C20-C21 in cell C22 and copy across.
• Cash Fixed Costs = Cash Fixed Costs. Enter =C17 in cell C24 and copy across.
• Depreciation = -(Investment in Plant and Equipment) / (Number of years to fully depreciate). Depreciation is held constant each year, because the straight-line method is being used. Enter =-$B$35/7 in cell C25 and copy across.
• Total Fixed Costs = Cash Fixed Costs + Depreciation. Enter =C24+C25 in cell C26 and copy across.
• Operating Profit = Gross Margin - Total Fixed Costs. Enter =C22-C26 in cell C28 and copy across.
• Taxes = Operating Profit * Tax Rate. Enter =C28*C8 in cell C29 and copy across.
• Net Profit = Operating Profit - Taxes. Enter =C28-C29 in cell C30 and copy across.
• Add Back Depreciation = Depreciation. Enter =C25 in cell C32 and copy across.
• Operating Cash Flow = Net Profit + Add Back Depreciation. Enter =C30+C32 in cell C33 and copy across.
• Cash Flows = Operating Cash Flow + Investment in Plant and Equipment. Enter =B33+B35 in cell B36 and copy across.
5. Present Value and NPV. Discount the forecasted cash flows back to the present as follows:
• Present Value of Each Cash Flow = (Cash Flow) / (1 + Cumulative Discount Factor). Enter =B36/(1+B12) in cell B37 and copy across.
• Net Present Value = Sum of Present Value of the Cash Flows. Enter =SUM(B37:I37) in cell B38.

The Net Present Value of the project is $6,117. The project should be accepted.

10.2  Forecasting Cash Flows

Problem. Consider the same project as Project NPV - Basics. Let's examine the details of how you forecast the project cash flows. Suppose that Direct Labor, Materials, Selling Expenses, and Other Variable Costs are forecast to be $3.50, $2.00, $1.20, and $0.70, respectively, in year 1 and then grow with inflation. Lease Payment, Property Taxes, Administration, Advertising, and Other cash fixed costs are forecast to be $2,800, $580, $450, $930, and $520, respectively, in year 1 and then grow with inflation. What is the Total Variable Cost / Unit, the Total Cash Fixed Costs, and the project NPV?

Solution Strategy. Forecast the variable cost / unit and cash fixed costs in more detail. Then sum up all of the items in each category to get the total. Feed these sums into the previous analysis of the project NPV.

FIGURE 10.2 Spreadsheet for Forecasting Project Assumptions, Discounting, & Price or Cost / Unit.

How To Build Your Own 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 A16:A21 and click on Insert | Row. Select A23:A29 and click on Insert | Row.

 

2. Inputs. Enter the Variable Cost / Unit inputs in the range C18:C21 and the Cash Fixed Cost inputs in the range C25:C29.
3. Forecast The Detailed Items. The detailed Variable Cost / Unit items and Cash Fixed Cost items are projected by growing the item at the inflation rate. Copy the cell D15 to the range D18:I21. Copy the cell D21 to the range D25:I29.
4. Totals. Sum up the Variable Cost / Unit and Cash Fixed Cost categories.

 

• Total Variable Cost / Unit. Enter =SUM(C18:C21) in cell C22 and copy across.
• Total Cash Fixed Costs. Enter =SUM(C25:C29) in cell C30 and copy across.

FIGURE 10.3 Spreadsheet for Cash Flow Forecasts.

The Net Present Value of the project remains $6,117 as before.

10.3 Working Capital

Problem. Consider the same project as Project NPV - Forecasting Cash Flows. Suppose we add that the project will require working capital in the amount of $0.87 in year 0 for every unit of next year's forecasted sales and this amount will grow with inflation going forward. What is the project NPV?

Solution Strategy. Forecast the working capital amount per next year's unit sales. Then multiply by the forecasted unit sales to determined the required working capital each year. Include the investment in working capital to the total investment cash flows and calculate the project NPV.

FIGURE 10.4 Spreadsheet for Forecasting Project Assumptions, Discounting, & Price or Cost / Unit.

How To Build Your Own Spreadsheet Model.

1. Open the Forecasting Cash Flows Spreadsheet and Add Rows. Open the spreadsheet that you created for Project NPV - Forecasting Cash Flows and immediately save the spreadsheet under a new name using the  File | Save As command. Select A32:A34 and click on Insert | Row. Select A51 and click on Insert | Row. Select A53:A54 and click on Insert | Row.

 

2. Forecast Working Capital / Next Year's Unit Sales. Enter the input in cell B32. This item is projected by growing it at the inflation rate. This Year's Work Cap/Next Yr Unit Sales = (Last Year's Work Cap/Next Yr Unit Sales) * (1 + This Year's Inflation Rate). Enter =B32*(1+C$6) in cell C32 and copy it across.
3. Forecast Working Capital. Working Capital = (This Year's Work Cap/Next Yr Unit Sales) * (Next Yr Unit Sales). Enter =B32*C5 in cell B33 and copy it across.

FIGURE 10.5 Spreadsheet for Cash Flow Forecasts.

4. Cash Flows. Track the working capital through the rest of the project analysis.

• Investment in Working Capital = (Last Year's Working Capital) - (This Year's Working Capital). It is negative cash flow as new working capital is added and a positive cash flow as working capital is recaptured. The first year requires a special formula to get started. Enter =-B33 in cell B51. Then, enter =B33-C33 in C51 and copy it across.
• Investment Cash Flow = (Investment in Working Capital) + (Investment in Plant & Equip). Enter =SUM(B51:B52) in cell B53 and copy it across.
• Cash Flows = (Operating Cash Flow) + (Investment Cash Flow). Enter =B49+B53 in B55 and copy it across.

The Net Present Value of the project drops to $3,476, because of the additional investment in working capital.

10.4  Sensitivity Analysis

Problem. Consider the same project as Project NPV - Working Capital. Assume that the product life-cycle of seven years is viewed as a safe bet, but that the scale of demand for the product is highly uncertain. Analyze the sensitivity of the project NPV to the units sales scale factor and to the cost of capital.

Solution Strategy. Copy the pattern of unit sales in the base case to a new location and multiply this pattern by a scale factor to get the new unit sales scenario. Assume that the real cost of capital is constant. Thus, forecast the future cost of capital by taking the year 1 cost of capital and adding the change in the inflation rate. Create a two-way data table using a range of input values for units sales scale factor and a range of input values for the year 1 cost of capital. Using the data table results, create a 3-D surface chart.

FIGURE 10.6 Spreadsheet for Two-Way Data Table and 3-D Surface Chart.

How To Build Your Own Spreadsheet Model.

1. Open the Working Capital Spreadsheet and Insert Rows. Open the spreadsheet that you created for Project NPV - Working Capital and immediately save the spreadsheet under a new name using the File | Save As command. Select the range A5:A6 and click on Insert | Row. Select the cell A9 and click on Insert | Row.

FIGURE 10.7 Spreadsheet showing modified Key Assumptions.

2. Unit Sales. Save the base case pattern and multiply it by a scale factor to determine unit sales.

• Base Case Unit Sales = the original sales pattern. Copy the range C7:I7 to C5.
• Unit Sales Scale Factor. Enter 100.0% in C6.
• Unit Sales = (Base Case Unit Sales) * (Unit Sales Scale Factor). Enter =C5*$C$6 in C7 and copy it across.

 

3. Real Cost of Capital. Save the base case changes as a set of increments and add the increments to the date 0 real cost of capital to determine the current real cost of capital.
• Real Cost of Capital Increment. Enter input increments in the range D9:I9.
• Real Cost of Capital on date t = (Date 0 Real Cost of Capital) + (Increment on date t). Enter =$C$10+D9 in D10 and copy it across.

 

4. Two-Way Data Table. Create a list of input values for Unit Sales Scale Factor (80%, 90%, 100%, etc.) in the range C64:G64. Create a list of input values for Cost of Capital (9.0%, 11.0%, 13.0%, etc.) in the range B65:B69. Create an output formula that references the product NPV by entering the formula =B60 in cell B64. Select the range B64:G69 for the Data Table. This range includes both the input values at the top of the data table and on the left of the data table. Then choose Data | Table from the main menu and a Table dialog box pops up. Enter the cell address C6 (for Unit Sales Scale Factor) in the Row Input Cell, the cell address C10 (for the Date 0 Real Cost of Capital) in the Column Input Cell, and click on OK.
5. 3D Graph of the Sensitivity Analysis. Highlight the range C65:G69 and then choose Insert | Chart from the main menu. Select a Surface chart type and make other selections to complete the Chart Wizard. To label the x-axis and y-axis, right-click on the chart, select Source Data ... from the pop-up menu, enter C64:G64 in the Category (X) axis labels text box, select Series1 in the Series pick list, enter B65 in the Name text box, select Series2 in the Series pick list, enter B66 in the Name text box, and so on until every series has a label.

The sensitivity analysis shows that the Project NPV is highly sensitive to the Unit Sales Scale Factor and the Cost of Capital. If the sales forecast is overly optimistic and/or cost of capital estimate is too low, then the project might actually have a negative NPV. Hence, it is worth spending extra resources to verify the accuracy of the sales forecast and the cost of capital estimate.

Problems

Skill-Building Problems.

1. 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 $37,500 investment in plant and equipment, is depreciated using the straight-line method over seven years, and there is a salvage value of $5,600 in year 7. The project is forecast to generate sales of 5,700 units in year 1, rising to 24,100 units in year 5, declining to 8,200 units in year 7, and dropping to zero in year 8. The inflation rate is forecast to be 1.5% in year 1, rising to 2.8% in year 5, and then leveling off. The real cost of capital is forecast to be 9.3% in year 1, rising to 10.6% in year 7. The tax rate is forecast to be a constant 42.0%. Sales revenue per unit is forecast to be $15.30 in year 1 and then grow with inflation. Variable cost per unit is forecast to be $9.20 in year 1 and then grow with inflation. Cash fixed costs are forecast to be $7,940 in year 1 and then grow with inflation. What is the project NPV?
2. Consider the same project as problem 1, but modify it as follows. Suppose that Direct Labor, Materials, Selling Expenses, and Other Variable Costs are forecast to be $5.20, $3.70, $2.30, and $0.80, respectively, in year 1 and then grow with inflation. Lease Payment, Property Taxes, Administration, Advertising, and Other cash fixed costs are forecast to be $4,100, $730, $680, $1,120, and $730, respectively, in year 1 and then grow with inflation. What is the Total Variable Cost / Unit, the Total Cash Fixed Costs, and the project NPV?
3. Consider the same project as problem 2, but modify it as follows. Suppose we add that the project will require working capital in the amount of $1.23 in year 0 for every unit of next year's forecasted sales and this amount will grow with inflation going forward. What is the project NPV?
4. Consider the same project as problem 3. Assume that the product life-cycle of seven years is viewed as a safe bet, but that the scale of demand for the product is highly uncertain. Analyze the sensitivity of the project NPV to the units sales scale factor and to the cost of capital.

Live In-class Problems.

5. Given the partial Basics spreadsheet ProjbasZ.xls, do steps 5 Cash Flow Forecasts and 6 Present Value and NPV.
6. Given the partial Forecasting Cash Flows spreadsheet ProjforZ.xls, complete steps 2 Inputs, 3 Forecast The Detail Items, and 4 Totals.
7. Given the partial Working Capital spreadsheet ProjworZ.xls, complete steps 2 Forecast Work Capital / Next Year’s Unit Sales, 3 Forecast Working Capital, and 4 Cash Flows.
8. Given the partial Sensitivity Analysis spreadsheet ProjsenZ.xls, complete step 4 Two-Way Data Table.