Financial Modelling – Tutorial Exercises Monash University Department of Banking and Finance - Clayton BFX3355 Property Investment Team Assignment 2 (Group size 3 or 4) Due Date: Fri 22 May 5PM 2020...

hi there I have a property investment assignment which requires me to do an excel and I need it by tonight, can you guys see if you can help me with it? its basically completing an excel sheet, and they gave sort of a guide , where they gave solutions to it. I will attach the excel files related to the notes. please have a look for me I really appreciate your help.


Financial Modelling – Tutorial Exercises Monash University Department of Banking and Finance - Clayton BFX3355 Property Investment Team Assignment 2 (Group size 3 or 4) Due Date: Fri 22 May 5PM 2020 Submission: Assignment upload via Moodle link by the group representative. This assignment contributes 20% to the overall assessment for this unit. Format: MS Excel 2010/2013/2016 workbook. MAC submissions are unacceptable. Use the Faculty of Business computer labs to ensure that the electronic file (for the Excel component) is readable and also checked for viruses. Excel version: Any version of Excel up to and including Excel 2013 is acceptable. One assignment for each group should be submitted. Assignment cover sheet: A scanned copy of the Assignment Cover sheet is to be placed on the 1st worksheet of the Excel spreadsheet you are submitting. A copy of the Assignment Cover sheet can be found in the Assignment Folder on Moodle. The cover sheet needs to be completed for you assignment to be marked, Excel spreadsheet: The names and student numbers of all students in a group must appear on the worksheet named GroupNum_??. The team number, issued by the assignment coordinator, together with key assignment data, has been placed on this worksheet and should not be removed. NOTE: • Students should retain an identical copy of the submitted assignment. • Forming groups and group management is the sole responsibility of students. • Students are not permitted to submit individual assignments and any individual assignments submitted will not be marked. Note: The number in a group cannot exceed 4. A group cannot have less than 2 members. Students are required to form their own groups. It is not necessary for group members to attend the same tutorial. - 2 - Project description An unlisted property trust is examining a proposal to purchase a mixed use building located in Melbourne’s CBD. The building consists of ground floor retail stores, 3 floors leased by an established convention centre operator, 5 floors of modern office space leased to a state government department with a long lease and the remaining 15 floors of modern office space are leased to various business tenants. All available space is assumed to be leased during the holding period with a provision for vacancy, reflected in the occupancy rate, to allow for changes in occupancy. All leases have annual upward rent reviews which reflect the expected market rent. There is an ongoing refurbishment program in place for the top 15 floors when a tenant vacates. One floor is refurbished each year, starting with floor 9 in year 2 and progressing up a floor each successive year. During the refurbishment of the floor, no rent is received but all other operation expenses are still incurred. Further, the cost of refurbishing each floor is 80% of lost gross rent adjusted for occupancy during the year of refurbishment. After the refurbishment, floor is let out at the expected market rent with a 1% premium to the annual increase in office rent. This refurbishment constitutes a yearly capital improvement from Yr 2 onwards. The purchase will be funded by debt and equity. The trust may be required to contribute up to a maximum of 50% of the project acquisition cost to satisfy lender requirements. Borrowed funds cannot exceed 70% of the purchase price. Loan finance is interest-only and for 20 years. The investor can meet any annual operational shortfall in cash flow. Operational shortfalls in cash flow are funded at the same rate as the project’s required return. Cash throw off (after tax operational surpluses) are re-invested at the prevailing safe rate. (given with data: ReinvestRate) The trust is subject to the corporate tax rate. Australia’s corporate tax rate, land tax and stamp duty rates for Victoria apply. GST is assumed to be neutral and may therefore be ignored. Data for all variables will be provided to each group by the assignment co-ordinator. The worksheet this data is provided on will be uneditable. Students are free to create a copy of this worksheet, within the same workbook or in another workbook for your valuation. The property is held for 10 years and the selling price is determined from the in- going cap rate. Each group is assigned a team number and a unique set of data for their assignment. The data provided cannot be changed and will result in a mark of zero if the assignment submitted does not correspond with the data supplied. Required (i) (i) Construct a spreadsheet model that will value the property and identify its profitability (via NPV, IRR and MIRR). a. Your spreadsheet model should be able to accommodate alternative scenarios (different sets of input variables) and the flow on effect to profitability. - 3 - b. And allow for the use of Goal Seek and Solver to examine the effect of changing key input variables (e.g. LVR) on result variables (e.g. NPV). (ii) Is the project profitable? At what LVR is the project profitable, if profitable at all? At what LVR is profitability maximised, if profitable at all? Would you recommend the property to the fund manager? Provide these answers in the Executive Summary. Provide a sensitivity table for LVR (in the range 50% - 85%) changes impacting upon project NPV and IRR. Discuss the results in the Executive Summary. (iii) How sensitive is the IRR to changes in occupancy rates for non-food retail outlets? Occupancy is provided in page 3 of this assignment information. Provide a sensitivity table for changes of non-food outlets occupancy rate (in the range 70% to 95%) incrementing by 5%, impacting upon IRR. To classify non-Discuss the results in the Executive Summary. *Note: Producing a correct sensitivity table is a critical part of your assessment. Assignments with incorrectly working sensitivity tables will at most score a pass mark. (iv) What are the limitations of your valuation model? Discuss in the Executive Summary. (v) The investor has requested a 1,000 word Executive Summary that highlights your findings and recommendations about the property. The Executive Summary is to be placed in the 2nd worksheet of your Excel workbook. Your Group Assignment coversheet is to be signed, scanned and placed as a pic file into the 1st worksheet of your Excel workbook. All students will find the Excel workshop materials available on Moodle beneficial when completing this assignment and are required to work through these materials before commencing the assignment. Students who see staff in consults asking how to do Excel Data Tables, Goal Seek or Solver will be directed to complete the Excel Workshop. - 4 - Requesting Assignment Data Students must form their own groups. An assignment discussion board on Moodle is available for students to post their availability for a group and to post their need for group members. After joining a group, students will then receive an email providing you with a workbook, called StudentData_??, containing the following information: • Your group assignment number. • Data for variables on pages 2 and 3 above. All students are advised that: • The data provided to you must be used for your assignment. If you change the data in any way your assignment mark will be zero. • You will not be assessed on the magnitude of the NPV or IRR obtained for the data provided to you, rather for your overall approach and the structure of the spreadsheet model. • Each group is provided with a different set of data and you will be assessed on the data you are provided with. • The data provided to each group is recorded, together with the student numbers of group members. This forms the basis for assessing your submitted assignment. • Only one group representative from each group should make a request for data. • All requests for assignment data must be made by email. Assignment submission Assignment submission is via a Moodle assignment upload link. The group representative is responsible for the assignment upload by 5PM Fri 22 May 2020. Late submissions will be penalised by -4 mark from the 20 marks total for each late calendar day. Ensure that a scanned copy of the completed Assignment Cover sheet is placed on the 1st worksheet of the Excel spreadsheet you are submitting. Ensure your Executive Summary is placed on the 2nd worksheet. Completion of the Excel workshop by every group member is mandatory requirement for completing the assignment. Preceding assignment consultation, students will have to complete the Excel workshop. Students who have not completed the Excel workshop will be ineligible for assignment consultation. Assignment Hurdles: • The assignment is marked out of 20 marks. • "Hardcoding" in the model will result in an automatic -4 mark penalty. See staff in consults if you are unfamiliar with hardcoding in spreadsheets. • Incorrect adherence to capital structure constraints will incur an automatic -4 mark penalty - 5 - • Inability for markers to apply goal seek successfully -4 mark penalty as hardcoding will have occurred • Missing sensitivity tables/"hardcoding" of sensitivity tables will incur an automatic -4 mark penalty. Note: only Data Tables are to be used for sensitivity tables, not any other type of reference function such as vlookups Assignment Tips: • Teams are shown how to complete 95% of the assignment via the Excel Tax Lecture, final 2 projects in the Excel Workshop. • The remaining 5% including how to handle insurance premium cash flows, setting 1 occupancy rate for multiple occupancies and how to correctly spreadsheet the refurbishment into the model is up to individual teams to solve Prop_Investment AFX3355 Property Investment Improving Your Excel Skills Excel 2007and 2010 Workshop notes prepared by John Flaherty AFX3355 Property Investment Page 2 © John Flaherty Workshop outline 1. Introduction 3 1.1 Spreadsheet Modelling 3 1.2 Building the Model 6 1.3 Interrogating the Model 7 2. Building a Simple Model 9 2.1 Problem Definition 9 2.2 Solution Design 9 2.3 Model Interrogation 19 2.4 Other commonly used excel Functions 23 2.5 Enhancements with