| 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) | 12.00% |
| Cost of Truck | $200,000 |
| Cost of additional equiment attached to truck | $15,000 |
| Tax rate | 35% |
| Annual Before Tax & Depreciation Truck Projected Earnings → | | $70,000 | $70,000 | $65,000 | $60,000 | $55,000 | $50,000 | $40,000 | $30,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 | Year-1 | Year-2 | Year-3 | Year-4 | Year-5 | Year-6 | Year-7 | Year-8 |
| Annual Before Tax & Depreciation Truck Projected Earnings → |
| Depreciation Expense |
| Annual Before Tax Truck Projected Earnings → |
| Tax |
| Annual Projected Truck Earnings → |
| Depreciation to add back |
| Projected Truck Net Cash Flow |
| Decision Criteria: |
| Pay Back Period | | Years |
| Discounted Pay Back Period (DPB)** | | Years |
| Net Present Value |
| Internal Rate of Return |
| Profitability Index |
| Discounted Cash Flow Needed for DPB Calc. |
| Recommendations: |
JessAdams
spreadsheet.xls
