Finance Project (Need answers changed)
johndefeo1
FinanceProjectExcel.xlsxScenario 1
| Income Statement | Years | ||||
| Particulars | CF0 | CF1 | CF2 | CF3 | CF4 |
| Investment | |||||
| New Equipmet Cost | $ (200,000.00) | ||||
| Equipment Ship and Install Cost | $ (35,000.00) | ||||
| Related Start Up Cost | $ (5,000.00) | ||||
| Total Basis Cost | $ (240,000.00) | ||||
| Net Working Capital | $ (20,000.00) | ||||
| Total Inititial Costs | $ (260,000.00) | ||||
| Operations | |||||
| Revenue | $ 200,000.00 | $ 210,000.00 | $ 220,500.00 | $ 231,525.00 | |
| Less: | |||||
| Operating Cost (60% of Sales) | $ (120,000.00) | $ (126,000.00) | $ (132,300.00) | $ (138,915.00) | |
| Depreciation | $ (60,000.00) | $ (60,000.00) | $ (60,000.00) | $ (60,000.00) | |
| EBIT | $ 20,000.00 | $ 24,000.00 | $ 28,200.00 | $ 32,610.00 | |
| Less: | |||||
| Taxes (Rate of 21%) | $ (4,200.00) | $ (5,040.00) | $ (5,922.00) | $ (6,848.00) | |
| Net Income | $ 15,800.00 | $ 18,960.00 | $ 22,278.00 | $ 25,762.00 | |
| Add: | |||||
| Add Back Depreciation | $ 60,000.00 | $ 60,000.00 | $ 60,000.00 | $ 60,000.00 | |
| Total Operating Cash Flows | $ 75,800.00 | $ 78,960.00 | $ 82,278.00 | $ 85,762.00 | |
| Terminal | |||||
| Add: | |||||
| Changes in Net Working Capital | $ 20,000.00 | ||||
| Salvage Value After Tax | $ 11,850.00 | ||||
| Totals | $ (260,000.00) | $ 75,800.00 | $ 78,960.00 | $ 82,278.00 | $ 117,612.00 |
| Cost of Capital (10%)= 1/1+r*n | $ 1.00 | $ 0.91 | $ 0.83 | $ 0.75 | $ 0.68 |
| Present Value of Cash Flows | $ (260,000.00) | $ 68,909.02 | $ 65,255.70 | $ 61,816.28 | $ 80,330.17 |
| Net Present Value | $ 16,311.18 | ||||
| Internal Rate of Return | $ 0.13 | ||||
| Payback Period | 3.80 Years | ||||
| Payback Method | Year 1 | Year 2 | Year 3 | Year 4 | |
| Initial Investment - Year 3 Cummulative | $ 68,909.02 | $ 134,164.72 | $ 195,981.01 | ||
| $64,019.00 | |||||
| Value/Year 4 Cash Flow | |||||
| 0.7969500809 | |||||
Scenario 2
| Income Statement | Years | ||||
| Particulars | CF0 | CF1 | CF2 | CF3 | CF4 |
| Investments | |||||
| New Equipmet Cost | $ (200,000.00) | ||||
| Equipment Ship and Install Cost | $ (35,000.00) | ||||
| Related Start Up Cost | $ (5,000.00) | ||||
| Total Basis Cost | $ (240,000.00) | ||||
| Net Working Capital | $ (20,000.00) | ||||
| Total Inititial Costs | $ (260,000.00) | ||||
| Operations | |||||
| Revenue | $ 200,000.00 | $ 210,000.00 | $ 220,500.00 | $ 231,525.00 | |
| Less: | |||||
| Operating Costs (60% of Sales) | $ (120,000.00) | $ (126,000.00) | $ (132,300.00) | $ (138,915.00) | |
| Depreciation | $ (240,000.00) | $ - 0 | $ - 0 | $ - 0 | |
| EBIT | $ (160,000.00) | $ 84,000.00 | $ 88,200.00 | $ 92,610.00 | |
| Less: | |||||
| Taxes (21%) | $ 33,600.00 | $ (17,640.00) | $ (18,522.00) | $ (19,448.00) | |
| Net Income | $ (126,400.00) | $ 66,360.00 | $ 69,678.00 | $ 73,162.00 | |
| Add: | |||||
| Add Back Depreciation | $ 240,000.00 | $ - 0 | $ - 0 | $ - 0 | |
| Total Operating Cash Flows | $ 113,600.00 | $ 66,360.00 | $ 69,678.00 | $ 73,162.00 | |
| Terminals | |||||
| Add: | |||||
| Changes in Net Working Capital | $ 20,000.00 | ||||
| Salvage Value After Taxes | $ 11,850.00 | ||||
| Totals | $ (260,000.00) | $ 113,600.00 | $ 66,360.00 | $ 69,678.00 | $ 105,012.00 |
| Cost of Capital (10%) | 1 | 0.90909 | 0.82644 | 0.75131 | 0.68301 |
| Present Value of Cash Flows | $ (260,000.00) | $ 103,272.62 | $ 54,842.56 | $ 52,349.78 | $ 71,724.25 |
| Net Present Value | $ 22,189.21 | ||||
| Internal Rate of Return | 14% | ||||
| Payback Period | 3.70 Years | ||||
| Payback Method | Year 1 | Year 2 | Year 3 | Year 4 | |
| Initial Investment - Year 3 Cummulative | $ 103,272.62 | $ 158,115.18 | $ 210,464.96 | ||
| $49,535.04 | |||||
| Value/Year 4 Cash Flow | |||||
| 0.6906312719 | |||||
