10.3 Working Capital

К оглавлению1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 
17 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. 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.

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

o Investment Cash Flow = (Investment in Working Capital) + (Investment in Plant &

Equip). Enter =SUM(B51:B52) in cell B53 and copy it across.

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

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.

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

o Investment Cash Flow = (Investment in Working Capital) + (Investment in Plant &

Equip). Enter =SUM(B51:B52) in cell B53 and copy it across.

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