Teaching real estate finance with cases SUNLIGHT VILLAGE
- Slides: 34
Teaching real estate finance with cases: SUNLIGHT VILLAGE case study Giacomo Morri - Paolo Benedetto presented at the 18 th Annual ERES Conference June 15 th - 18 th, 2011 Eindhoven ERES 2011 Giacomo Morri - Paolo Benedetto
Introduction • Purpose of the work • Industry needs – Real estate & Finance – Computer skills – Team working • From theory to practice – Learning by doing – Problem solving skills – Presentation skills ERES 2011 Giacomo Morri - Paolo Benedetto 2
Use of Sunlight Village case study • Different RE finance courses – Master of Science – Master in Real Estate (post experience) – Executive courses for practitioners • Presentation tool only – Class presentation – Spreadsheet creation by the teacher • Exercise & Examination tool – – – Initial presentation by the teacher Spreadsheet creation by students Final class presentation & discussion by students Valuation by the teacher Delivery of spreadsheet & PPT solution files for self-assessment ERES 2011 Giacomo Morri - Paolo Benedetto 3
Example of a case study SUNLIGHT VILLAGE ERES 2011 Giacomo Morri - Paolo Benedetto 4
The role of the story • The topic: – Land development, financing & acquisition • Players involved: – Mary Watson attorney, land owner & seller – Julie Holmes MD real estate development company & buyer “Mary Watson, a well reputed attorney in a top legal firm, and Julie Holmes, managing director of an international real estate development company, have been friends since they were children. They attended the same schools and they have been playing volleyball in the same team for many years. Later on, even if they went to different universities, respectively studying law and finance, they have always kept in touch and now, that they are older, their daughters are best friends at primary school. ” ERES 2011 Giacomo Morri - Paolo Benedetto 5
The object • • 10, 000 sqm plot of land The bid € 5 million Seller: Land “A few years ago, Mary’s family purchased a 10, 000 sqm plot of land for € 2 million that has recently obtained a building permit for 5, 000 sqm for residential from the municipality. The family accountant has suggested selling the property for not less than € 5 million, but Mary doesn’t trust him too much and she has therefore proposed the deal to Julie, knowing that they might find an agreement which can satisfy both of them. ” ERES 2011 Giacomo Morri - Paolo Benedetto 6
Input data – development • • Direct construction costs € 1, 100/sqm Land drainage € 400, 000 Urban land development costs 12% of direct building costs Other costs as % of the direct building costs: – designing costs 3% – project management 2% – contingency 5% • • Development Timing Sales € 3, 500/sqm ERES 2011 3 years from 2011 3 years from 2012 (preliminary contract) Giacomo Morri - Paolo Benedetto 7
Input data – development Additional operating costs • Annual development management fee: 5% of the GAV WARNING • Marketing fees to enhance the sales: € 30, 000/year • General administrative costs: € 5, 000/year (from 2011) • Selling brokerage fees: 2% on the sale price (to be paid at the notary’s deed) WARNING Annual development management fee: 5% of the GAV as % of the GAV is calculated as the sum of all direct and indirect construction costs from the beginning of the project until the end of the construction phase The model has to be set up in order to consider both production and sale phases ERES 2011 Giacomo Morri - Paolo Benedetto 8
Input data – funding issues • • • Expected equity return 15% Corporate tax rate 35% No constant capital structure, but structured finance approach: – purchase of the land without recurring to bank debt – capital expenditures financed with debt – no VAT financing • Construction facility financing – loan to cost on CAPEX: 80% – interest rate: 5% – upfront fee: 0. 60% over the total amount – Bullet financing with repayment accordingly to the notary’s deed sales (release price 1. 00) • VAT rate – 10% on sales and on capex – 20% on all services ERES 2011 Giacomo Morri - Paolo Benedetto 9
Problem solving – investment decisions Two questions arise • Would you buy the land @ € 5, 000? – Just yes or no… and why? • Offering € 3, 000 fixed payment at the beginning, what is the maximum amount of extra return (in %) above a 10% hurdle rate (levered IRR) that can be granted to the seller as deferral payment at the end of the project? – Calculating earn-out without any previous explanation: § Knowledge of financial models § Use of IRR § but no previous theory about earn-out scheme construction ERES 2011 Giacomo Morri - Paolo Benedetto 10
Use of the case study SUNLIGHT VILLAGE ERES 2011 Giacomo Morri - Paolo Benedetto 11
Use of SUNLIGHT VILLAGE case study • • • Task: Investment valuation, financing & negotiation Object: Residential development scheme Main issues: – Investment spreadsheet model for DCF investment Analysis – Project financing approach (equity side) • Students’ task – Case Study Requested Output Take decisions! § Yes or no § Earn out – Complete land development report using DCF (formula working spreadsheet) – Power point presentation – Class presentation ERES 2011 Giacomo Morri - Paolo Benedetto 12
Use of SUNLIGHT VILLAGE case study • Team working – Group composition – Working hours decision (When? Where? How? . . . ) • Data selection – What is really useful? – What is sensitive for the final decision? • Application of finance to real estate – Accounting (P&L - Balance sheet) – Cash flow models (investment, financing, covenants etc. ) – Investment criteria (IRR, NPV, PBP etc. ) • Practical skills – Use of spreadsheet – Power point presentation – Class presentation & Communication ERES 2011 Giacomo Morri - Paolo Benedetto 13
Use of spreadsheet • Not only finance, but also practical skills with daily tool – more and more companies require people already able to create and properly manage DCF models & presentations • Some major tips to use spreadsheet for case solution – – – • Colors Cell (data or formula) Data input & calculation sheet Output data sheets (for presentation and print) Data analysis tools: Sensitivity & Goal seek Tips related to Power Point presentation – Executive summary – Use of attachments – Public speech presentation and complete document ERES 2011 Giacomo Morri - Paolo Benedetto 14
Use of spreadsheet – colors & cells • • Yellow cells with blue writing input data (numbers to be plugged in) White cells with black writing calculation (formulas) or output data ERES 2011 Giacomo Morri - Paolo Benedetto 15
Use of spreadsheet – data input & calculation • Data input and calculation formulas all in the same sheet to facilitate computations and links ERES 2011 Giacomo Morri - Paolo Benedetto 16
Use of spreadsheet – output data sheets • Output data separate from input and calculation sheet If input data changes, output tables and graphs are always ready to be copypasted in presentations and reports ERES 2011 Giacomo Morri - Paolo Benedetto 17
Use of spreadsheet – data analysis tools • Tables (1 or 2 entries) – Sensitivity analysis • Graphs ERES 2011 Giacomo Morri - Paolo Benedetto 18
Solving a case study SUNLIGHT VILLAGE example ERES 2011 Giacomo Morri - Paolo Benedetto 19
Solution: main steps • • Analysis of the problem Data preparation – Building, operating and company costs – Work in progress: investment • Accounting – Profit & loss statement – without debt – Unlevered cash flow statement – VAT flows • Bank Financing – Profit & loss statement – with debt – Levered cash flow statement & Investment criteria • • • Balance sheet Sensitivity analysis Deferral payment ERES 2011 Giacomo Morri - Paolo Benedetto 20
Solution – building, operating & company costs DATA PREPARATION ERES 2011 Giacomo Morri - Paolo Benedetto 21
Solution – work in progress: investment DATA PREPARATION ERES 2011 Giacomo Morri - Paolo Benedetto 22
Solution – P&L statement (without debt) Accounting ERES 2011 Giacomo Morri - Paolo Benedetto 23
Solution – unlevered cash flow statement Accounting ERES 2011 Giacomo Morri - Paolo Benedetto 24
Solution – cash in & cash out CHECK ERES 2011 Giacomo Morri - Paolo Benedetto 25
Solution – VAT Accounting ERES 2011 Giacomo Morri - Paolo Benedetto 26
Solution – capex financing FINANCING ERES 2011 Giacomo Morri - Paolo Benedetto 27
Solution – P&L statement (with debt) FINANCING ERES 2011 Giacomo Morri - Paolo Benedetto 28
Solution – levered cash flow statement FINANCING ERES 2011 Giacomo Morri - Paolo Benedetto 29
Solution – cash in & cash out CHECK ERES 2011 Giacomo Morri - Paolo Benedetto 30
Solution – balance sheet BALANCE SHEET ERES 2011 Giacomo Morri - Paolo Benedetto 31
Solution – sensitivity analysis SENSITIVITY ANALYSIS ERES 2011 Giacomo Morri - Paolo Benedetto 32
Solution – deferral payment The maximum amount that can be granted to the seller at the end of the project is € 1, 352, 287, i. e. 62% extra return ERES 2011 Giacomo Morri - Paolo Benedetto DEFERRAL PAYMENT Offering € 3, 000 fixed payment at the beginning, what is the maximum amount of extra return (in %) above a 10% hurdle rate (levered IRR) that can be granted to the seller at the end of the project? 33
"In theory, there is no difference between theory and practice. But, in practice, there is. " Johannes Lambertus Adriana van de Snepscheut (computer scientist and educator) Contacting Author Giacomo Morri, Ph. D SDA Professor & Director Master in Real Estate Accounting, Control, Corporate Finance & Real Estate Department SDA Bocconi School of Management Milan – Italy giacomo. morri@sdabocconi. it www. propertyfinance. it www. sdabocconi. it/mre ERES 2011 Giacomo Morri - Paolo Benedetto 34
- Real estate finance fundamentals
- Rabo real estate finance
- 11245 lantern road fishers in 46038
- Criminal cases vs civil cases
- Earth's revolution
- What is gravity
- Solar declination
- Is sunlight necessary for seed germination
- Siklus hidup produk sunlight
- Abiotic and biotic
- Full sunlight lux
- Sunlight melts a wax crayon left outside.
- Cole crops definition
- Raindrops sunlight and refraction lyrics
- Beauty poem class 6 ppt
- Hawk us
- Sunligj
- Pelagic zone
- How much sunlight does the marine biome get
- An abiotic factor
- Elevator pitch for real estate agent
- What is a mofir
- Real estate stakeholders
- Greek real estate market
- Sandy miller realtor
- Littoral rights vs riparian rights
- What are the 4 types of real estate
- Fundamentals of analyzing real estate investments
- Crm software for real estate law
- 4 quadrant model real estate
- Involuntary alienation real estate
- Blind ad real estate
- Emblements real estate
- Panama real estate law
- Encumbered real estate