10.3 Working Capital
К оглавлению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. 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.