Determinants of Capital Structure Spreadsheet/ Discounted Cash Flow Analysis Spreadsheet
GNate94
Ch07_DCF_Analysis_SPREADSHEET1.xlsxProject #1
| DCF Valuation | ||||||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| Assumptions | ||||||||
| Tax rate | ||||||||
| MACRS (10-year) | ||||||||
| WACC (KW) | ||||||||
| Resale value of PPE | ||||||||
| Balance Sheet Items | ||||||||
| Net working capital | ||||||||
| Net PPE | ||||||||
| Cash Flows | ||||||||
| Revenue | ||||||||
| Operating expense | ||||||||
| Depreciation | ||||||||
| Income before tax (EBIT) | ||||||||
| Taxes | ||||||||
| Income after tax (EBIT(1-t)) | ||||||||
| Investment in PPE (CAPX) | ||||||||
| Sale of PPE (Terminal value) | ||||||||
| Increased working capital (ΔNWC) | ||||||||
| Free cash flow | ||||||||
| Net Present Value |
Project #2
| DCF Valuation | |||||||||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| Assumptions | |||||||||||
| Tax rate | |||||||||||
| MACRS (7-year) | |||||||||||
| WACC (KW) | |||||||||||
| Resale value of PPE | |||||||||||
| Balance Sheet Items | |||||||||||
| Net working capital | |||||||||||
| Net PPE | |||||||||||
| Cash Flows | |||||||||||
| Revenue | |||||||||||
| Operating expense | |||||||||||
| Depreciation | |||||||||||
| Income before tax (EBIT) | |||||||||||
| Taxes | |||||||||||
| Income after tax (EBIT(1-t)) | |||||||||||
| Investment in PPE (CAPX) | |||||||||||
| Sale of PPE (Terminal value) | |||||||||||
| Increased working capital (ΔNWC) | |||||||||||
| Free cash flow | |||||||||||
| 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 |
