1 of 3 Advanced Pro-forma Modeling Using Excel This workshop is designed especially for real estate finance professionals who want to build complex financial models using Excel. Students will develop their own spreadsheets for different property types that will allow them to model any real estate investment scenario quickly and effectively. Students will use advanced tools in Excel and apply them immediately to their own Excel models. Important skills that will be addressed in detail include building construction loan and development models, using statistical skills to better model real estate investments, developing reusable models, and auditing existing models. Students taking this workshop are expected to have a basic knowledge of Excel and a firm grasp of basic real estate finance concepts such as time value of money, internal rate of return (IRR), and net present value (NPV). 2 of 3 Schedule and Agenda 8:30 • Introduction and Terminology • Core Excel Functions for Real Estate • Unleveraged Before Tax Cash Flow • Amortization Schedule • Leveraged Before Tax Cash Flow 12:30 Lunch 1:30 • Depreciation Schedule • Leveraged After Tax Cash Flow • Partitioning the IRR and Advanced Analysis • Sensitivity Tables • Partnership Primer • Argus and Excel Integration 4:00 Review of Existing Models 5:00 End of day 8:30 • Partnership I – “Country Club” – Preferred Return plus Split of Cash Flow • Partnership II – “Wall Street” – IRR Lookbacks • More on Partnership II – Advanced IRR Lookbacks • Partnership III – “Catch-up” • Partnership IV – “Clawback” 12:30 Lunch 1:30 • Basic Construction Concepts • Construction and Unit Sales Schedules • Construction Lending and Developer’s Cash Flow • Construction I: Build and Hold – Construction and Permanent Financing • Advanced Scheduling • Construction II: Single Family Suburban and Condominium Development 4:00 Review of Existing Models 5:00 End of day There is a short break (15 minutes) each day in the morning and the afternoon. 3 of 3 Excel Functions and Tools In addition to covering a wide range of real estate finance topics, the course specifically covers the following Excel tools and functions. Additional tools and functions will be covered based on questions and as time permits. Functions SUM ROUND IF, AND, OR PV, FV, RATE, NPER PMT, IPMT, PPMT IRR, NPV XIRR, XNPV EDATE, EOMONTH, DATEDIF OFFSET TRANSPOSE ISERROR VLOOKUP, HLOOKUP INDEX, MATCH SUMIF Tools and Concepts Absolute and Relative References Keyboard Shortcuts (such as Control - ‘) Data Validation Conditional Formatting “What If Tools” Goal Seek Data Tables Solver Scenario Manager