10.2 Forecasting Cash Flows

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