I need an excel sheet with the tables etc as well as the questions answered
Woodland Street Office Project Deadline: Due by 11:59pm on July 20th, 2020 Note: This homework is designed to be structurally similar to the Rolling Meadow Center case as discussed in class. Hence I strongly suggest that, before you start working on this homework, you shall first watch the lecture video and follow my lecture to replicate the Rolling Meadow Center pro-form sheet. For the same reason, there is no template specially offered for this homework, as you can base your work on Rolling Meadow Center case, subject to some modifications as you see fit. This is part of learning objective, that is, to learn how to adapt and modify from existing resources as there is no “one template fits all”. As the financial advisor for Georgetown Development Co. (GDC), you are responsible for analyzing the newly proposed Woodland Street Office Project. Your boss has given you the construction and marketing studies for the project. The plan calls for a construction phase of one year and an operational phase of five years, after which the property will be sold. Your boss has informed you that the required return on the project is 15% (after tax). Part 1: Construction Phase The marketing staff has found a 2.2 acre site that can be acquired for $412,500. They have concluded that the 2.2 acre site will provide enough space for the proposed building, as the site will support an office building with a gross building area (GBA) of 62,000 square feet. The gross leasable area (GLA) will be 55,800 square feet, giving it a leasable ratio of 90%. The construction division of GDC had told you that they can keep construction hard costs to $48/square foot (GLA) and soft costs to $5/square foot (GLA) (here $5/sf soft cost is before including (construction loan) interest carry and all loan fees). The construction division has also informed you that they will have to spend $100,000 for site improvements before construction of the office building begins. Goliath National Bank has agreed to provide the construction loan for the project. The bank will authorize you to draw funds enough to cover cost on site improvements, all the hard cost plus eligible soft cost as defined above, i.e., 5$/sf. The construction loan interest carry will be at an annual rate of 11.5%, plus a loan origination fee of 2 points. The construction division estimates that 40% of the total direct costs will be taken down evenly during the first four months, with the remaining 60% of total direct costs being taken down during the final eight months of the construction phase. Permanent financing for the project will come at the end of the first year at the amount equal to the ending balance of the construction loan. GDC plans to obtain permanent financing from Trust Insurance Co. at an interest rate of 11% with a 3% prepaid loan fee. The loan has a 10 year term and is to be paid back monthly over a 25-year amortization schedule. a) Calculate the construction draw schedule. What will the yield to the lender of the construction loan be? (20 points) b) Calculate the total direct costs of the project (interpret it as total draw plus loan fees and construction loan interest carry). (20 points) c) Create a statement of cash flows for the construction phase. Use this to determine how much equity is needed to fund the project. The statement of cash flows should include outflows relate to purchase and improvement of the site, as well as loan fees and interest. Inflows should include the construction loan. (20 points) Part 2: Operational Phase The marketing staff has assured you that office space can be rented out for $21.50/square foot. In the first year of operation, tenant reimbursements for the project are expected to be $4.50 per square foot (GLA) and the building is expected to be 70% leased (70% of leasable area will be leased). After that, vacancies should be 10% of leasable area. Operating expense in the first year will be $12/square foot. Operating expense, rent, and tenant reimbursement and expected to increase by 4% each year. The sales price of the property after the operational phase is expected to be $4,200,000. The project will incur a sales expense of 3% on the sale of the property. Your boss has informed you that the relevant marginal and capital gains tax rate for the project is 35%. For depreciation purposes, assume 90% of depreciable costs are from capital improvements and 10% of depreciable costs are from tenant improvements. Capital improvements shall be depreciated over 31.5 years using straight line method, and tenant improvements shall be depreciated over 7 years by Double Declined Balance method (DDB). (We used DDB method in Rolling Meadow center case in class. See accounting prep video offered in Module 1 for details). d) Calculate the cash flows from operations and sale of the property. (20 points) e) Combine the cash flows from c) and d) to determine the before-tax IRR, and after tax IRR and NPV. Should GDC do the Woodland Street Office Project with the after-tax numbers? (20 points) Note: No template is provided for this homework as the overall structure of this project is very similar to the Rolling Meadow Center case that we discussed in Module 3. Hence you can utilize and modify (if needed) the template provided there. You can either HIGHLIGHT your answers within your spreadsheet, or to prepare a separate word document (and submit both the spreadsheet and the word file) to report your answers. Grading: Although each question is worth 20 points, keep in mind that there can be chain effect on early mistakes. For example, if you make a mistake in part b, it is likely to affect all of your subsequent answers. Even if you define everything else correctly, there will still be a partial grade deduction on later parts as the reported answer won’t be correct. So outcome driven.