| Frank Smith Plumbing |
| Data Needed for analysis: |
| | Project | Year-1 | Year-2 | Year-3 | Year-4 | Year-5 | Year-6 | Year-7 | Year-8 |
| Cost of Capital (borrowing) | 6.50% |
| Cost of Truck | $175,000 |
| Cost of additional equiment attached to truck | $15,000 |
| Tax rate | 35% |
| Projected Annual Earnings (Before Tax & Depreciation) → | | $68,000 | $74,000 | $78,000 | $80,000 | $84,000 | $83,000 | $84,000 | $85,000 |
| Depreciation Percentage Rate (MACRS)* | | 20.0% | 32.0% | 19.2% | 11.5% | 11.5% | 5.8% | 0.0% | 0.0% |
| * The proposed truck has an estimated economic life of seven years but will be treated as a five-year MACRS property for depreciation purposes. |
| Calculate the following -- light yellow highlighted cells need to be completed |
| | Year-0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
| Projected Annual Earnings (Before Tax & Depreciation) → | | 68,000 | 74,000 | 78,000 | 80,000 | 84,000 | 83,000 | 84,000 | 85,000 |
| Depreciation Expense | | 35,000 | 56,000 | 33,600 | 20,125 | 20,125 | 10,150 | 0 | 0 |
| Annual Projected Earnings (Before Tax) → | | 33,000 | 18,000 | 44,400 | 59,875 | 63,875 | 72,850 | 84,000 | 85,000 |
| Tax Expense | | 11,550 | 6,300 | 15,540 | 20,956 | 22,356 | 25,498 | 29,400 | 29,750 |
| Annual Projected Earnings → | | 21,450 | 11,700 | 28,860 | 38,191 | 41,519 | 47,352 | 54,600 | 55,250 |
| Depreciation to add back | | 35,000 | 56,000 | 33,600 | 20,125 | 20,125 | 10,150 | 0 | 0 |
| Projected Net Cash Flow | (190,000) | 56,450 | 67,700 | 62,460 | 58,316 | 61,644 | 57,502 | 54,600 | 55,250 |
| cumulative cash flow | | (133,550) | (65,850) | (3,390) | 54,926 | 116,570 | 174,072 | 228,672 | 283,922 |
| discounted cash flow | (190,000) | 53,005 | 59,688 | 51,707 | 45,330 | 44,993 | 39,408 | 35,135 | 33,384 |
| cumulative discounted cash flow | (190,000) | (136,995) | (77,307) | (25,600) | 19,730 | 64,723 | 104,131 | 139,266 | 172,650 |
| Decision Criteria: |
| Pay Back Period | 2.94 | Years |
| Discounted Pay Back Period (DPB)** | 6.28 | Years |
| Net Present Value | 162,114 |
| Internal Rate of Return | 27.02% |
| Profitability Index | 0.85 |
frank_smith_plumbing_worksheet1.xls
