DCF Analysis
Hang_321
ComputerLab2Spreadsheet.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 | ||||||||
| MACRS 10-year | ||||||||
| Discount rate | ||||||||
| Resale value of PPE | ||||||||
| Balance Sheet Items | ||||||||
| Net working capital | ||||||||
| Net PPE | ||||||||
| Cash Flow Forecasts | ||||||||
| Sales revenue | ||||||||
| Operating expense | ||||||||
| Depreciation | ||||||||
| Operating profit (EBIT) | ||||||||
| Taxes | ||||||||
| Net profit (EBIT(1-t)) | ||||||||
| Investment in PPE (CAPX) | ||||||||
| Sale of PPE (terminal value) | ||||||||
| Investment in working capital (ΔNWC) | ||||||||
| Cash flows | ||||||||
| Net Present Value |
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 | |||||||||||
| MACRS percentages | |||||||||||
| Discount rate | |||||||||||
| Resale value of PPE | |||||||||||
| Net Working Capital | |||||||||||
| Net PPE | |||||||||||
| Cash Flow Forecasts | |||||||||||
| Sales Revenue | |||||||||||
| Operating Expense | |||||||||||
| Depreciation | |||||||||||
| Operating Profit (EBIT) | |||||||||||
| Taxes | |||||||||||
| Net Profit (EBIT(1-t)) | |||||||||||
| Investment in PPE (CAPX) | |||||||||||
| Sale of PPE (Terminal value) | |||||||||||
| Investment in Working Capital (ΔNWC) | |||||||||||
| Cash Flows | |||||||||||
| Net Present Value | |||||||||||
| IRR |
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 |
