Finance King David only
773jdee
cash_flow_analysis_week_4.xlsCapital Budgeting
| Frank Smith Plumbing | ||||||||||||
| Depreciable value = | $215,000 | |||||||||||
| 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 → | $70,000.00 | $70,000.00 | $65,000.00 | $60,000.00 | $55,000.00 | $50,000.00 | $40,000.00 | $30,000.00 | ||||
| Depreciation Expense | 43,000.00 | 68,800.00 | 41,280.00 | 24,725.00 | 24,725.00 | 12,470.00 | 0.00 | 0.00 | ||||
| Annual Before Tax Truck Projected Earnings → | 27,000.00 | 1,200.00 | 23,720.00 | 35,275.00 | 30,275.00 | 37,530.00 | 40,000.00 | 30,000.00 | ||||
| Tax | 9,450.00 | 420.00 | 8,302.00 | 12,346.25 | 10,596.25 | 13,135.50 | 14,000.00 | 10,500.00 | ||||
| Annual Projected Truck Earnings → | $17,550.00 | $780.00 | $15,418.00 | $22,928.75 | $19,678.75 | $24,394.50 | $26,000.00 | $19,500.00 | ||||
| Depreciation to add back | 43,000.00 | 68,800.00 | 41,280.00 | 24,725.00 | 24,725.00 | 12,470.00 | 0.00 | 0.00 | ||||
| Projected Truck Net Cash Flow | $60,550.00 | $69,580.00 | $56,698.00 | $47,653.75 | $44,403.75 | $36,864.50 | $26,000.00 | $19,500.00 | ||||
| Decision Criteria: | ||||||||||||
| Pay Back Period | 3.17 | Years | cumulative cashflow | $205,000.00 | $10,000.00 | |||||||
| Discounted Pay Back Period (DPB)** | 3.59 | Years | cumulative cashflow | $186,828.00 | $28,172.00 | |||||||
| Net Present Value | $28,681.97 | |||||||||||
| Internal Rate of Return | 0.00% | |||||||||||
| Profitability Index | 1.68 | |||||||||||
| Discounted Cash Flow Needed for DPB Calc. | ||||||||||||
| Recommendations: |
Frank should undertake the project of buying the proposed new truck. It will be profitable considering that based on the information gathered the project will have a positive net present value and profitability index that is greater than one. Even though there is no acceptable payback period that Frank has established, the project has reasonable payback period of less than 4 years.
