How to look for errors in a development model?
I work as a development analyst and one of our JV partners sent over a proforma they made for a mixed use development. Includes their construction budget, draw schedule and proformas for resi, retail and hotel. I was told to review it for any errors. Any advice on specific things to look out for? The construction budget is from a gc we work closely with so I feel confident about that. What do you generally look for when it comes to errors in a dev model?
I have a harder time finding errors in already made models than my own since the format is always different, and it’s not productive to just click through cells to look for errors in the formulas
You're really starting out by looking for legitimate mathematical errors - does everything tie out where it should, is your cash flow built out properly, are your inputs feeding into calculations the way they should be, etc. you can check a lot of this just by thinking through the numbers logically.
Yes, first question is does the shit tie.
Look at the foundation, does it look like things are connected with formulas and dynamic.
Change a number to something crazy like a million or 100,000. Did all the sum formulas update, did the IRR drop, did xyz happen. Look at this visually and then ctrl+z to put it back to its original form.
Quickly drag and highlight expenses in a column/group of cells. Are the sums all correctly summing. Do this for year 2 or 3 of a rent or mortgage schedule are the
If a formula looks a little complex, hit f2 to see which cells it is targeting. growth rates being correctly applied.
You go in with a blank slate, no preconceived notions and see where the model takes you.
It might take 10-20 minutes of poking around. If there are a lot of hard coded values and formulas/data aren't linked very well--such as a schedule might be correct, but all changes to that schedule are not actually feeding sources and uses correctly, you have a few more things to worry about.
Once you feel a little more confident that things are generally in line, you start looking and poking holes in the assumptions. This construction time line is too short, these cost number are off, this cap rate is too low for the market/product type. What interest rate are they using on their debt. You can ask to see the signed contracts, supporting invoices, closing statements.
It's not really about finding mistakes, it's about asking questions to connect the dots and seeing what gets unearthed in the process. There will always be something you find, it just depends on how material it is to the deal.
I guess there are two approaches, depending on whether they're asking you to review a model in good faith to see if there or errors, or if they know there is an error and want to see if you can spot it. For the first, holistically go through the model, look at all the assumptions, see where they feed into, maybe save a copy to screw around with so you can see how everything moves when individual numbers change, and get an overall understanding for how everything fits together. And if you find an error, great. For the second, look for what doesn't make sense. I sincerely doubt the error you're looking for is something small and meaningless enough that it wouldn't make the end result of the underwriting look anomalous - that would be a shitty thing to do on their part.
As Gekko said above, the point of all this isn't to play "gotcha!" with you, or it shouldn't be. It is to see whether you understand how the pieces of the model fit together, and what inputs are going to change which outputs, and why.
I have been solely doing development JVs as LP equity for the past 2-years.
When we start reviewing a deal to make sure the Sponsor’s numbers / model are/is working properly, we take all of the Sponsor assumptions, and recast them in our own model. If we’re within a tight range (+- 50bps of IRR or less than 2.5% +- of equity multiple) we generally conclude that the Sponsor’s model is working properly.
When you do this, and you get a discrepancy larger than the range above, the fastest way I’ve found to start narrowing down where the discrepancies are, is to simply create a new tab in Sponsor model, and create an annual cash flow that includes income, expense, debt service, loan fundings/paydowns, and capital events, with free cash at bottom.
Once you have that in simple visual format, it’s easy to narrow down where your annual cash flow differs from theirs.
Doing this can be tedious, but it’s helped me find strange assumptions being made by the Sponsor that they may have overlooked in their initial business plan description.
Would love to hear more about others’ more efficient processes.
Macabacus has visualization functions that show you inconsistent formula flows. Can save a ton of time instead of hitting F2 Esc a million times.
Et non a quidem assumenda minus perspiciatis. Animi est ut rerum praesentium culpa aut.
Ea ipsum et fugit id sapiente. Ipsam quam quo voluptatem voluptatem. Dolores numquam dolor distinctio sed rem.
Quod harum iusto odit suscipit. Culpa beatae dolorem temporibus quis laudantium ut. Dignissimos veritatis numquam et soluta ipsa dolorum quidem.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...