NEED IT ASAP WITH EXCEL CODES AND STEP BY STEP!!! THANK YOU
Instructions: Rocky Hill Center Case, Part 1 This document may be modified for clarification, especially in response to student questions. Instructions Conduct a DCF analysis of the Rocky Hill Center property, using Excel as covered in class. Your Excel file should include the following spreadsheets (tabs): Assumptions, PGI, VC, MI. OE, CAPX, NSP, and Pro forma property. The Assumptions tab should house your assumptions, which are described in the case discussion below. Compute potential gross income (PGI), vacancy \& collection losses (VC), miscellaneous income (MI), operating expenses (OE), capital expenditures (CAPX), and net sales price at the end of the holding period (NSP) in the relevant tabs. In the Pro forma property tab, reference relevant figures from other tabs and perform any needed calculations. The goal is to present all relevant information along with the estimated value of Rocky Hill Center. The estimated value (in the Pro Forma property tab) should be highlighted in yellow so it's very easy to see. I Make sure the Excel file is set up for dynamic analysis, meaning changes in inputs "ripple through" everything. A subsequent assignment may involve valuing Rocky Hill Center with different figures for assumptions than the ones laid out below. You want to be able to change the assumptions in one place and then have the spreadsheet recompute everything. To achieve this, hard code assumptions and then reference those assumptions when they're needed, so that a change in the assumptions results in everything else changing as well. In addition, you want the various spreadsheets to reference from the relevant pro forma tab where possible. If a figure, such as total potential gross income, is incorrect, then your answer will be overwritten in the pro forma spreadsheet (but not the "work" spreadsheets) by me with the correct answer. If this correction results in other figures, such as vacancy and collection costs, becoming correct, then no additional deduction would be taken. If changing PGI in the pro form sheet does not "ripple" through, then additional deductions will be taken for all incorrect figures. Rocky Hill Center Case, Part 1 Rocky Hill Center is an office park in Denver, Colorado, with 2 buildings, a separate parking garage, and a small, private park between the 2 buildings. Building 1 has 18 floors, with a total of 340,000 square feet of nice professional office space. Building 2 is larger. It has 26 floors with a total of 620,000 square feet. In building 1,220,000 square feet are currently occupied. The associated leases end in 11 years. Monthly rent will be $1.63 per square foot in year 1 and then increase annually each year by 2.60% for 4 years and then remain fixed until the end of the lease term (meaning that rent will be $1.63 per square foot in year 1 and then increase 4 times, before no longer changing). In year 2 (so the tenants move in at the start of year 2), Rocky Hill Center management expects to lease half of the vacant office space in building 1 for $1.20 per square foot per month. The monthly rent on the newly leased space is expected to increase annually by 1.0% over the life of the lease, which will be 10 years. Currently, 530,000 square feet of building 2 is occupied. One lease, which covers a suite that is 80,000 square feet ends in 6 years. The tenant is not expected to resign and the space is expected to remain vacant for at least 4 years. Leases covering the other office space in building 2 er. d in at least 12 years. In year 1 , the monthly rent paid by tenants in building 2 will be $1.11 per square foot. This monthly rent on the currently occupied space will remain fixed through year 3 . In year 4 , monthly rentrwill increase by 23 cents per square foot. It will remain at this new level until the leases end. In year 1 , the monthly rent that building 1 could generate would be $1.25 per square foot. Market conditions are expected to drive this potential rent down by 1.57% each year. Monthly rent that could be charged for building 2 is expected to be $1.12 per square foot in year 1 and is also expected to subsequently decline annually by 1.57%. Rocky Hill Center has several other sources of income besides rent from tenants, In year 1 , the property is expected to generate $260,000 per year from its fitness center. This amount is expected to increase by 3.20% each year. Rocky Hill Center also earns income from parking. Monthly revenue from parking is expected to be $17,000 in year 1 and monthly revenue from parking is expected to increase by $800 each year for at least 12 years. Lastly, Rocky Hill Center earns income from other sources such as signs on benches in the green space, vending machines on all floors, and a small newsstand in the lobby. Income from these other sources is expected to decrease slowly for a few years before declining rapidly. Income from these other sources is expected to be $124,000 per month in year 1. In years 2, 3, and 4, income is expected to decrease by 2.72% per year, before declining annually by 7.27% in year 5 and all subsequent years. The holding period for Rocky Hill Center is 6 years, the required rate of return is 8.23%, and selling expenses as a share of the property's sales price are expected to be 10.34%. Assume all changes become effective at the start of the year. In other words, annual changes take effect in month 1 and then monthly figures remain constant in months 2 through 12 until the next month 1. For example, if monthly rent in year 1 is $100 and monthly rent is expected to increase by 10% annually, then rent would be $100 in each of the 12 months in year 1 and $110 in each month in year 2 . Likewise, if monthly rent in year 1 is $100 and monthly rent is expected to increase by $10 each year, then rent would be $100 in each month in year 1 and $110 in each month in year 2. The following table provides some additional assumptions. Make sure to read them carefully and apply them appropriately. Questions \& output What is the estimated value (as of today) of the Rocky Hill Center property based on the information provided in the case description and using discounted cash flow analysis? Highlight your answer to this question in yellow in the "Pro forma property" spreadsheet in your Excel file, In addition to value, make sure that each of the following are clearly presented in an easy-toread way for each relevant year in the "Pro forma property" tab: potential gross income (PGI) for building 1, PGI for building 2, total PGI for the property, vacancy \& collection losses (VC). miscellaneous income (MI), effective gross income (EGI), operating expenses (OE), capital expenditures (CAPX), net operating income (NOI), and net sales price at the end of the holding period (NSP), Other information can be presented in the "pro forma property" spreadsheet as well. You're not limited to what's listed above. Note that all required figures relate to the overall property except the separate PGI figures for buildings 1 and 2. Also, make sure your table has an informative title such as "DCF analysis of the Rocky Hill Center
