DCF Analysis
Hang_321
ComputerLab2Solutions.xlsProject #1
| PROJECT NPV | ||||||||
| (in thousands of $) | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 |
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | |
| Assumptions | ||||||||
| Tax rate | 30.0% | 30.0% | 30.0% | 30.0% | 30.0% | 30.0% | 30.0% | |
| MACRS 10-year | 10.0% | 18.0% | 14.4% | 11.5% | 9.2% | 7.4% | 6.6% | |
| Discount rate | 9.3% | |||||||
| Resale value of PPE | $0 | |||||||
| Balance Sheet Items | ||||||||
| Net working capital | $5,000 | $5,000 | $5,000 | $5,000 | $5,000 | $5,000 | $5,000 | $0 |
| Net PPE | $20,000 | $18,000 | $14,400 | $11,520 | $9,216 | $7,372 | $5,898 | $4,588 |
| Cash Flow Forecasts | ||||||||
| Sales revenue | $22,000 | $22,000 | $22,000 | $22,000 | $22,000 | $22,000 | $22,000 | |
| Operating expense | $18,000 | $18,000 | $18,000 | $18,000 | $18,000 | $18,000 | $18,000 | |
| Depreciation | $2,000 | $3,600 | $2,880 | $2,304 | $1,844 | $1,474 | $1,310 | |
| Operating profit (EBIT) | $2,000 | $400 | $1,120 | $1,696 | $2,156 | $2,526 | $2,690 | |
| Taxes | $600 | $120 | $336 | $509 | $647 | $758 | $807 | |
| Net profit (EBIT(1-t)) | $1,400 | $280 | $784 | $1,187 | $1,509 | $1,768 | $1,883 | |
| Investment in PPE (CAPX) | $20,000 | |||||||
| Sale of PPE (terminal value) | $1,376 | |||||||
| Investment in working capital (ΔNWC) | $5,000 | $0 | $0 | $0 | $0 | $0 | $0 | ($5,000) |
| Cash flows | ($25,000) | $3,400 | $3,880 | $3,664 | $3,491 | $3,353 | $3,242 | $9,569 |
| Net Present Value | ($4,203) |
Project #2
| PROJECT NPV | |||||||||||
| (in thousands of $) | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 | 2011 | 2012 | 2013 |
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | |
| Assumptions | |||||||||||
| Tax Rate | 35.0% | 35.0% | 35.0% | 35.0% | 35.0% | 35.0% | 35.0% | 35.0% | 35.0% | 35.0% | |
| MACRS percentages | 14.3% | 24.5% | 17.5% | 12.5% | 8.9% | 8.9% | 8.9% | 4.5% | 0.0% | 0.0% | |
| Discount rate | 11.5% | ||||||||||
| Resale value of PPE | $30,000 | ||||||||||
| Depreciation method (MACRS=1) | 1 | ||||||||||
| Net Working Capital | $25,000 | $25,000 | $25,000 | $25,000 | $25,000 | $25,000 | $25,000 | $25,000 | $25,000 | $25,000 | $0 |
| Net PPE | $100,000 | $85,710 | $61,220 | $43,730 | $31,240 | $22,310 | $13,380 | $4,450 | $0 | $0 | $0 |
| Cash Flow Forecasts | |||||||||||
| Sales Revenue | $70,000 | $75,000 | $80,000 | $85,000 | $90,000 | $95,000 | $100,000 | $105,000 | $110,000 | $115,000 | |
| Operating Expense | $49,000 | $52,500 | $56,000 | $59,500 | $63,000 | $66,500 | $70,000 | $73,500 | $77,000 | $80,500 | |
| Depreciation | $14,290 | $24,490 | $17,490 | $12,490 | $8,930 | $8,930 | $8,930 | $4,450 | $0 | $0 | |
| Operating Profit (EBIT) | $6,710 | ($1,990) | $6,510 | $13,010 | $18,070 | $19,570 | $21,070 | $27,050 | $33,000 | $34,500 | |
| Taxes | $2,349 | ($696) | $2,279 | $4,554 | $6,325 | $6,850 | $7,375 | $9,468 | $11,550 | $12,075 | |
| Net Profit (EBIT(1-t)) | $4,362 | ($1,294) | $4,232 | $8,457 | $11,746 | $12,721 | $13,696 | $17,583 | $21,450 | $22,425 | |
| Investment in PPE (CAPX) | $100,000 | ||||||||||
| Sale of PPE (Terminal value) | $19,500 | ||||||||||
| Investment in Working Capital (ΔNWC) | $25,000 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ($25,000) |
| Cash Flows | ($125,000) | $18,652 | $23,197 | $21,722 | $20,947 | $20,676 | $21,651 | $22,626 | $22,033 | $21,450 | $66,925 |
| Net Present Value | $13,243 | ||||||||||
| IRR | 13.7% | ||||||||||
| Discount rate | |||||||||||
| 8% | 10% | 12% | 14% | 16% | |||||||
| $13,243 | $39,346 | $23,600 | $10,035 | ($1,710) | ($11,930) |
MACRS
| Recovery Period Class | ||||||
| Year | 3 | 5 | 7 | 10 | 15 | 20 |
| 1 | 0.333 | 0.200 | 0.143 | 0.100 | 0.050 | 0.038 |
| 2 | 0.445 | 0.320 | 0.245 | 0.180 | 0.095 | 0.072 |
| 3 | 0.148 | 0.192 | 0.175 | 0.144 | 0.086 | 0.067 |
| 4 | 0.074 | 0.115 | 0.125 | 0.115 | 0.077 | 0.062 |
| 5 | 0.000 | 0.115 | 0.089 | 0.092 | 0.069 | 0.057 |
| 6 | 0.000 | 0.058 | 0.089 | 0.074 | 0.062 | 0.053 |
| 7 | 0.000 | 0.000 | 0.089 | 0.066 | 0.059 | 0.049 |
| 8 | 0.000 | 0.000 | 0.045 | 0.066 | 0.059 | 0.045 |
| 9 | 0.000 | 0.000 | 0.000 | 0.066 | 0.059 | 0.045 |
| 10 | 0.000 | 0.000 | 0.000 | 0.066 | 0.059 | 0.045 |
| 11 | 0.000 | 0.000 | 0.000 | 0.033 | 0.059 | 0.045 |
| 12 | 0.000 | 0.000 | 0.000 | 0.000 | 0.059 | 0.045 |
| 13 | 0.000 | 0.000 | 0.000 | 0.000 | 0.059 | 0.045 |
| 14 | 0.000 | 0.000 | 0.000 | 0.000 | 0.059 | 0.045 |
| 15 | 0.000 | 0.000 | 0.000 | 0.000 | 0.059 | 0.045 |
| 16 | 0.000 | 0.000 | 0.000 | 0.000 | 0.030 | 0.045 |
| 17 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.045 |
| 18 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.045 |
| 19 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.045 |
| 20 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.045 |
| 21 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.023 |
Answers
| Q1 | $ (4,203) |
| Q2 | $ 14,692 |
| Q3 | $ 13,243 |
| Q4 | 13.7% |
| Q5 | $ 1,869 |
| Q6 | $ 1,996 |
| Q7 | $ 6,677 |
| Q8 | $ 3,955 |
| Q9 | $ (9,456) |
| Q10 | $ 9,255 |
