A detailed way to analyze a commercial real estate opportunity is by using a pro forma model in excel. However, not every pro forma model is created equally. Watch out for these tips and tricks. Don't Inflate. A common mistake is creating a pro forma model that is driven by your inflation assumption. It is necessary to capture growth in rents and expenses, but if your inflation input is too high, it will become the main driver of your rents. A critical error is setting your inflation higher for revenues than for expenses; although this would give you a better model result over time, it is unrealistic.
The Cap Rate Game. In a period of high appreciation (2002-2006), it was common to buy at a high cap rate (ex. 7.5) and sell at a low cap rate (ex. 6.5). This automatically juiced your IRR by giving your asset appreciation even if your cash flows remained constant. When looking at a sale or exit 10 years in the future, it is not a guarantee and probably less likely that you can automatically sell at a lower cap rate. For a safe approach, set your going in (purchase) and exit (sale) cap rate to be the same. For a more conservative model, set your exit cap rate higher than your purchase cap rate.
Create to Copy In Excel. Whether creating a 5, 10 or 20 year cash flow model the set-up is all the same. Year 1 cash flows are based off of operating assumptions and Year 2 cash flows are based off of growth assumptions and Year 1 cash flows. Each consecutive year uses growth assumptions and the previous year's cash flows. To make this easier to create in Excel (especially for 10 or 20 year projections), create your entire Year 2 cash flows to be easily "copyable" (i.e., all formulas with no hard coding, locked variables, etc.). This way, you can copy and drag out to 10 or 20 years in the future with ease.
Highlight Assumptions. The assumptions in your pro forma model will get the most use (once you create your cash flows and calculations you leave them alone to do the work). A model can have many assumptions and different people making adjustments. Make sure your assumptions are highlighted in excel either with a shaded box or colored text. Another tip is to move them all to one page and an even better option is to move them all to one page and highlight them. This way if somebody new wants to test your model, they know what cells they can change without breaking the whole model.
Sensitivity, Sensitivity, Sensitivity. A sensitivity analysis in a pro for model is a must. After all, a pro forma by definition is a guess at future cash flows and performance. It is critical to show how changes in your assumptions affect your results. Typical format is a sensitivity table which shows how key results (ex. IRR) change over a range of 1-2 assumptions.
0 comments:
Post a Comment