please show steps with excel codes! urgently needed!!

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 HillCenter. The estimated value (in the Pro Forma property tab) should be highlighted in yellow so its very easy to see.

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 theyre 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.