In the spring of 2020, your friend inherited a commercial real estate property in the Madison Park area of Seattle. This property is located in the heart of an expensive residential area on the west side of Lake Washington. Currently the property has a 3,000 square foot one story building that contains a convenience store (a small grocery store). Your friend now owns and operates the store which is only moderately profitable. She is considering changing how this property is being utilized. Her strategy is to close the convenient store and renovate the building in order to open up a coffee shop that also serves light healthy lunches and dinners. She has come to you for advice and is hoping you would analyze this decision and provide a recommendation on whether she should close the convenience store and renovate the building in order to open up the coffee shop. Assume a start date (time 0) as the end of 2020 (12/31/2020). Additionally, your friend has stated that she is planning on living in this area for another 10 years and is likely to sell the property at that time. Assume the sale price in 10 years will be the same regardless of the decision to renovate or not.

The revenue from the convenience store is expected to equal $360,000 in 2020 and, based on recent growth, the forecasted revenue in 2021 is $370,000. After 2021, revenue is expected to grow by 2% per year in the future. The operating margin of the convenience store is expected to stay constant overtime at 7.5%, there is no depreciation expense, and working capital is historically 20% of revenue. 

The expected costs of renovating the building is $950,000 and it will take six months to complete the renovation (assume cash outflow for the renovation occurs at time 0). The renovation expense falls into the 10-year depreciable life. Assuming the renovation starts in January 2021, the revenue from operating the coffee shop over the remaining six months of 2021 is expected to equal $140,000. Revenue in 2022 is expected to equal $340,000 and revenue in 2023 is expected to equal $460,000. The expected annual growth in revenue is 10%/year from 2024 to 2028 and then revenue is expected to increase by 3% per year after 2028. The expected operating margin (excluding depreciation expense) is 30%, and the required working capital will be 5% of revenue.

To finance the renovation your friend will invest $300,000 from her savings and has secured a bank loan for the remaining $650,000 at a rate of 6%/year. Assume, given the risk of this investment, that she will require a 10%/year return on her $300,000 equity investment. The expected income tax rate is 20% for both businesses. Assume your friend has enough income from her current job to benefit from a potential tax savings from any losses created from the coffee shop.

After estimating incremental after-tax cash flows from the renovation of the Madison Park property, calculate the NPV, IRR, and the Payback Period in order to provide a recommendation to your friend.  Please provide a short memo written to her (no more than one-two pages) describing your process, results, and the implication of your analysis for your friend's decision to renovate followed by your Excel spreadsheets.   

Note: A MACRS Depreciation Schedule to be used to calculate depreciation expense from the renovation is provided on the following page.

MARCS: Recovery Allowance Percentages for Property

 

OwnershipYear          3-Year    5-Year    7-Year    10-Year

           1                     33%        20%        14%        10%

           2                     45%        32%        25%         18%

           3                     15%        19%        17%        14%

           4                     7%          12%        13%        12%

           5                                     11% 9%         9%

           6                                      6%           9%         7%

           7                                                        9%         7%

           8                                                        4%         7%

           9                                                                        7%

           10                                                                      6%

           11                                                                    3%

                                   100%     100%       100%      100%

Depreciation Expense in year t = Initial Outlay * Recovery Allowance Percentage in year t.     

Note: there is no adjustment for salvage value in the calculation of depreciation expense using the MACRS method of depreciation.

Also Note: This MACRS schedule assumed a June 30 acquisition of the asset and, therefore, year 1's allowance and the last year's allowance are already adjusted to reflect depreciation expense for the half year.