AMA - Analysis Spreadsheet

Today is another AMA episode.

Carolyn asks. I’m not an expert in Excel and I paid to purchase an Excel based tool for analyzing multi-family apartment projects. I’m still learning about everything the tool can do. What is your recommendation for analyzing project?

Carolyn, this is a great question. In my experience, there are several different types of analysis that need to be performed depending on the exit strategy for your project.

It’s that exit strategy that fundamentally changes the type of analysis you’re going to do. If the project has a long term hold component, then you want to model the construction phase, the leasing phase, the steady state operation, and finally the exit. But the exit will be different depending whether you sell the building, or refinance it.

We tend to break down the project into those individual phases. Each phase has to be analyzed separately and each phase has to work on a standalone basis.

For example, it won’t help to have a great long term hold if you can’t get through the leasing phase. Leasing won’t matter if you can’t get through construction, and so on.

I find that most of the pre-packaged software solutions assume a single model. They assume a straightforward purchase, improvements, and sale. But the truth is that most projects are really executed in phases. The financing of those phases will often vary. For example, you might purchase the land with a small amount of equity. You might raise additional financing to go through the zoning process with a small interest reserve for the debt during that phase. From there, you will raise additional equity and debt for the construction phase. Once construction is complete, you might have a short term bank financing, and then after a seasoning period you would re-appraise the property and replace the financing with permanent financing.

For that reason, we create our own custom spreadsheet each time we undertake a project. Each of these phases look like a separate project with their own financial metrics and criteria. A separate financial model is needed for each phase of the project, and then they need to tie together.

When you add the different types of financing terms, that affects how the project is modelled. I’ll give you a simple example. Let’s say that you have two classes of investors, the first class of investors are straight equity investors who have a share of the ownership of the project. The second class of investors might be preferred investors. They have a rate of interest calculation on their investment and perhaps a lower ownership. Maybe their interest rate only starts to accrue when you get the building permit and then becomes payable to the investor when you get your occupancy permit, and then the interest accrual terminates when the refinance into permanent financing is complete.

What I’ve described is a perfectly normal situation. But I can guarantee you that very few of the canned software solutions out there will model this correctly.

By the time you’ve figured out all the formulas in the spreadsheet you just purchased don’t model your specific situation properly, you have expended the same effort as if you would have created the spreadsheet yourself.

When you’re dealing with investors, or even if it’s your own money, you need to understand the formulas in the spreadsheet and make sure they accurately reflect what is actually going to happen in your project. If the financial model is different from your assumptions, then you’re going to have a problem. It’s a problem that could have been avoided if you had an analyst who is an expert both in Excel and underwriting projects of your type. That analyst needs to audit the spreadsheet multiple times until they are no longer finding mistakes in it.

I realize this is probably not what you wanted to hear. But it’s my best advice based on seeing many projects.