In: Finance
Suppose you have an opportunity to build a business. It would involve acquiring a piece of land for $750,000 today at time zero. A facility for your business would be constructed on the land. It would be a multi-unit commercial facility and you would occupy half of it and hope to rent out the other half. The construction cost of the building would be $2,800,000. Allocate half of the capital cost today and assume the remaining 50% would be incurred in year 1. Start building depreciation on the total cost in year one assuming the building was ready for occupancy and placed into service in October of calendar year 1 (this is non-residential real property). Equipment related to the business will cost $1,500,000 at the end of year 1 and should be treated as 100% bonus depreciation beginning in year 2. Rental income from the rest of the building will be $500,000 per year in years 2-5 and should be treated as ordinary income. Property taxes and insurance for the entire facility are estimated at $55,000 per year in years 1 through 5. Your business is expected to generate $4,000,000 per year in revenues in years 2-5 with cost of goods sold estimated to be $2,300,000 per year inclusive of labor, overhead, and energy. Working capital will represent a $250,000 year 1 investment and will be returned in the final year 5 when the project is sold for an assumed $10,000,000. Assume a LIFO treatment of inventory with no changes in inventory levels predicted over the project life. Legal fees and commissions on the sale will be 5% of the sale value and deductible against the sale revenue in year 5. Write off all remaining book values including land and working capital at the end of year 5 and assume the project is held in a corporation and subject to an effective state and federal income tax rate of 25%. Assume other income exists (Expense Scenario) which you could use if you have negative taxable income. Part A) Calculate the project after-tax cash flow in years 0 through 5 and using a minimum rate of return of 20%, determine the project after-tax NPV and DCFROR. Please be sure to include a cumulative column in your ATCF model to make sure the model is balanced. It will be part of your grade in this exercise. Part B) Make a sensitivity analysis to Part A assuming 7-Year MACRS Depreciation was taken beginning in year 2 on the equipment cost only. Remember to write off the remaining tax book value at the end of year 5 when you sell the business. Recalculate the NPV @20% and the new project DCFROR.
A)
Years | 0 | 1 | 2 | 3 | 4 | 5 |
Land | (750) | - | - | - | - | - |
Building | (1,400) | (1,400) | - | - | - | - |
Equipment | - | (1,500) | - | - | - | - |
Working capital | - | (250) | - | - | - | 250 |
Revenue | - | - | 4,000 | 4,000 | 4,000 | 4,000 |
Cost | - | - | (2,300) | (2,300) | (2,300) | (2,300) |
Rental income | - | - | 500 | 500 | 500 | 500 |
Property tax & insurance | - | (55) | (55) | (55) | (55) | (55) |
Sale of project | - | - | - | - | - | 10,000 |
Legal fees | - | - | - | - | - | (500) |
Depreciation | (18) | (1,572) | (72) | (72) | (72) | |
Net income | (73) | 573 | 2,073 | 2,073 | 11,573 | |
Tax (25%) | - | - | (518) | (518) | (2,893) | |
Net cash flow | (2,150) | (3,205) | 2,145 | 1,627 | 1,627 | 9,002 |
Discount rate | 1.0 | 0.8 | 0.7 | 0.6 | 0.5 | 0.4 |
Net PV cash flow | (2,150) | (2,671) | 1,490 | 941 | 784 | 3,618 |
NPV | 2,012 |
b)
Years | 0 | 1 | 2 | 3 | 4 | 5 |
Land | (750) | - | - | - | - | - |
Building | (1,400) | (1,400) | - | - | - | - |
Equipment | - | (1,500) | - | - | - | - |
Working capital | - | (250) | - | - | - | 250 |
Revenue | - | - | 4,000 | 4,000 | 4,000 | 4,000 |
Cost | - | - | (2,300) | (2,300) | (2,300) | (2,300) |
Rental income | - | - | 500 | 500 | 500 | 500 |
Property tax & insurance | - | (55) | (55) | (55) | (55) | (55) |
Sale of project | - | - | - | - | - | 10,000 |
Legal fees | - | - | - | - | - | (500) |
Depreciation | (18) | (500) | (378) | (290) | (350) | |
Write off | (268) | |||||
Net income | (73) | 1,645 | 1,767 | 1,855 | 11,027 | |
Tax (25%) | - | - | (442) | (464) | (2,757) | |
Net cash flow | (2,150) | (3,205) | 2,145 | 1,703 | 1,681 | 9,138 |
Discount rate | 1.0 | 0.8 | 0.7 | 0.6 | 0.5 | 0.4 |
Net PV cash flow | (2,150) | (2,671) | 1,490 | 986 | 811 | 3,673 |
NPV | 2,138 | |||||
Depreciation | ||||||
Equipment | (429) | (306) | (219) | (279) | ||
Building | (18) | (72) | (72) | (72) | (72) | |
Months of depreciation | 3 | 12 | 12 | 12 | 12 |