10.2 Forecasting Cash Flows
К оглавлению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 - 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.
o Total Variable Cost / Unit. Enter =SUM(C18:C21) in cell C22 and copy across.
o 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.
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.
o Total Variable Cost / Unit. Enter =SUM(C18:C21) in cell C22 and copy across.
o 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.