Discover the Proforma Invoice Excel Format for Real Estate that Simplifies Your Workflow

Proforma invoice excel format for Real Estate

[Music] [Music] welcome to another tutorial video this one is going to be focused on real estate and specifically the real estate pro forma which is extremely important if you want to do financial analysis of real estate deals and investments now normally these videos address questions that come in and we try to answer them as quickly and concisely as possible but this one's actually not a response to a reader or viewer question it's just an important topic and it actually corresponds to a blog post on mergers and acquisitions on the real estate pro forma but we're going to expand on the article here and show you more of the excel parts and also show you a few of the more advanced features that you might see with real estate pro formas so the plan for this tutorial first is to cover why the real estate pro forma matters and exactly what it is then we'll go through a simple real estate pro forma and show you some of the calculations in Excel and how to come up with the numbers then I'll show you how to build scenarios into a pro forma using an example for a multifamily property and you'll see how the number is different and some things to consider when you're including scenarios in this type of model and then in part 4 I will explain some of the differences for other property types such as hotels and explain a few of the more advanced items in pro formas let's go to part one first and talk about why the real estate pro forma matters and what goes into it the basic idea here is that just as companies have financial statements the income statement balance sheet and cash flow statement properties also have financial statements but just like you don't really need all the financial statements to model or value a company you can also take some shortcuts and skip the full financial statements with properties you see this all the time with companies when we have DCF analyses for example when we protect the companies free cash flow we start with the revenue we list operating expenses to get to operating income we get to notepad we adjust for non-cash charges and we list working capital and capital expenditures to get to unlevered free cash flow but we don't include full three statement projections for the company all we need are the items that actually go into free cash flow so we can use that to value the company in a DCF with properties you can do the same thing and so a pro forma is like a combined and simplified income statement and cash flow statement for a property rather than a company now the structure of a real estate Proform I'm moving into part two now is that you always start with potential revenue which represents what a property would earn from rental income if it were one hundred percent occupied at market rates in our pro forma here you can see up at the top this base rental income line represents that potential rental income if the property were one hundred percent occupied at market rates would make some deductions then we'd list the properties operating expenses items like maintenance utilities insurance property taxes things like that then we list capital costs which are very similar to capital expenditures and the change of working capital for normal companies these correspond to longer-term items that are going to last for more than one year you can see that in this section down here capex TI's and lcst is stands for tenant improvements LLC stands for at least in commissions and we'll get into both of those in a little bit and then below that we show the debt service the interest expense and the debt principal repayments below it and that gets us to cash flow to equity investors so that's the basic shape and structure let's go into each part now in a little bit more detail so I can show you some of the numbers here so the base rental income as I said is the potential rental income if the property or a hundred percent occupied and every single tenant they're paid proper market rents so in our example here you can see what we're doing we are simply taking the base rental income from each tenant and in this case there is no difference between market rents and in place rents what the tenants are actually paying and then what we're also doing in the last part of the formula is we're taking the total square feet in the property and subtracting the number that are actually occupied and then just multiplying by one of the tenants rates so that even though the property is not a hundred percent occupied we are pretending like it is a hundred percent occupied in this line-item common deductions and adjustments here include the absorption and turnover vacancy concessions and free rent expense reimbursements and general vacancy so let's go into each of those and explain what they mean the absorption turnover vacancies for when a tenant leaves and to take several months to find a new tenant this is not annex it's just a loss of potential rental income so it's foregone rental income when there's no tenant debt if you look at some of our calculations for the absorption and turnover vacancy here what's happening is that we take the base rental income that a tenant would normally be paying but then we say actually there is a chance a 30% chance here because our renewal probability 70 70 percent so there's a 30% chance this tenant will not renew and if the tenant does not reduce it will take us six months so half the year to find a new tenant therefore we're going to reduce this by half of the year's rental income and we're going to multiply by that 30% non renewal probability and that's what gets us to our absorption at Turner vacancy concessions in free rent is another common adjustment here and the idea is that it's an incentive for a new tenant to move in or for an existing tenant to renew when this happens you might give the tenant a few months of free run so maybe six months on a five-year lease or twelve months on a ten-year lease or something like that in our model here we separate it into non renewal and renewal cases but the basic idea is pretty similar which is that we look at the base rental income that the tenant would normally be paying and then if we give the tenant three months of free rent we deduct that amount so three over twelve twenty five percent of the year and we deduct that and we count that as concessions in free rent and then in the case where the tenant does not renew the new tenant case we give the tenant six months of free rent and so we reduce the base rental income by 50 percent in that case and we multiply by the non renewal probability of 30 percent expense reimbursements represent another common adjustment these are simply the amounts of property taxes insurance and maintenance and utilities that you that tenants are responsible for and this varies greatly based on these types for example going back to our pro forma for a single net lease the tenant is not responsible for all that much they're basically just responsible for real estate and property taxes so for ten at number two here all we do is look at their proportion of the space they're occupying and then we multiply that by the total realist in property taxes for the property we met the factory and some other things but that's the basic idea we just include their proportionate share of the real estate and property taxes however tenant number three is on a triple net piece which means that their expense reimbursements correspond to maintenance utilities insurance and property taxes so we have to add up all those and then multiply by their proportional share and then one final adjustment here is the general vacancy this is for spaces that are permanently vacant which means there are no current tenants and there are no plans to get any additional tenants anytime soon so you can see the line item right here all we're doing is taking our total total rentable square feet we're subtracting the amount that is actually occupied right now and then we're multiplying by the market rental rate which we're assuming corresponds to what ten at number one here is paying in terms of rent per square foot now once we've made all those deductions and adjustments we get to something called effective gross income this is sort of like net sales or net revenue for a normal company but it's on a cash basis instead so we're not using a cruel accounting here this is much more of a cash based number let's go to the next section now and talk about some of the expenses common ones here are the property management fee which is often a percentage of effective gross income maintenance and utilities these are often based on a per-square-foot number and then they grow to a certain rate each year or maybe the per square foot number changes on a certain base on a certain radius here the real estate and property taxes are almost always based on a percent of the property value so if we paid 25 million for our property and property taxes are 4% per year and they grow at a certain rate we'll use that for our assumption there and then we have something called the reserve so the reserve for capital costs or capex t is and Elsie's here the reserves just to smooth out the property's cash flows as large a regular capital costs come up for example if we allocate 200,000 per year over five years to this item then if we have no capital costs in year one and two that's fine we build up a reserve of 400,000 but then when we get six hundred thousand of capital costs in year three we'll have six hundred thousand exactly in our reserve by then so we can use that to cover those costs then in year four we go to zero initially we allocate another two hundred thousand we allocate another two hundred thousand in year five and then your five when we have four hundred thousand capital costs we just deduct those from our reserve and use our reserves to cover them without having to dip into our cash flows and you can see the way it works here we have our reserves allocated and then when capital costs come up as they do in years three and four here we simply pay them out of our reserves and in year four we fall a little bit short of that we don't have quite enough in our reserves to cover everything but we do cover the majority of these expenses with our reserves and that's the purpose of this item let's go to the next part now and talk about capital costs the three main items here are capital expenditures or capex tenant improvements or tea eyes and then leasing commissions or LCS capex includes items that are not specific to one tenant but are just general improvements maintenance and upgrades for the property so it might be a new roof a new elevator new air conditioning a new heating system something like that tenant improvements are items that are specific to an individual tenant paid as an incentive to the tenant for the tenant to move in or new the lease so this might be something like modifying the space and adding an additional wall or doors or setting up the office differently or something like that and then leasing Commission's are paid to brokerage companies and real estate agents and brokers to find new tenants and these are typically a small percentage of the total lease value of the tenant tenant improvements are almost always based on a per square foot value or per square meter value so we've calculated them like that for the individual tenants here leasing commissions though we have to take the tenants rate that they're paying multiplied by the number of square feet and then multiplied by the lease term which is 10 years case so we do all that and then we multiply by whatever small percentage it is 1% 3% 5% something in that range usually and if we want to be really fancy we can take into account the fact that leases escalate over time and so this rate will go up over time and so we should be taking that into account but in this simplified model we just sort of skip over it stepping back and looking at this at a high level now we have a couple items here at the bottom of our pro forma or toward the bottom that are worth discussing first is net operating income then adjusted net operating income and then cash flow to equity investors after the debt service net operating income is similar to EBIT off for normal companies and it's critical in valuation because you almost always base a property's value on net operating income divided by something called a cap rate or capitalization rate which represents the yield of the property 4% 5% 8% something like that adjusted Noi represents ny- net capital costs and it's similar to unlevered free cash flow for normal companies because it's the core business cash flow after capital costs ignoring capital structure and you can see that here because the adjusted Noi line item is before we show the interest expense and debt principal repayments and then right below that we have cash flow to equity which is adjusted Noi - the debt service it's closer to the distributions made to the equity investors or the owners of the property properties rarely accumulate large cash balances as companies sometimes do they tend to simply pay out the cash they generate each year to the property owners unless they need to set aside some for the reserves or some other major upcoming item or something like that so that's it for the most common items on a real estate pro-forma let's go to part 3 now and talk about scenarios on a pro forma and to do this we'll walk through a quick example of a multi-family pro forma for a property in Seattle on apartment building back scenarios are important to factor in because all investing is probabilistic you need to think about not what happens in just one case but also what happens if the deal goes very well or it's just average or it goes really poorly in this multifamily pro forma we have a couple new items we have something called loss to lease that were since the difference between in place rents and market rents which is very common we have bad debt and concessions which represents tenants literally just not paying just disappearing or being late and then ending up not paying items that wouldn't really come up with an office or industrial property but which could easily happen when individuals are paying we have some more details on the expenses here as well although they're broadly the same as what we saw in the last example for an office or retail property the typical approach when you have scenarios is to show a base upside and downside case sometimes you'll show not just three cases but maybe five or seven cases and then to have differences in rent vacancy bad debt expenses tenant improvements and leasing commissions in each of these cases now in credit cases when you're not looking at the company as an equity investor but rather as a credit investor so a senior lender or mezzanine investor you will often focus on the base downside and extreme downside cases because the upside is very limited and so you want to assess your chances of actually losing money in the deal that is exactly what we do here because we have base downside and extreme downside cases but we don't even care about the upside case at all the key idea with scenarios is that everything is connected so if there is a recession which pushes down market rents then the vacancy rate is also going to increase as a result because it's harder to find tenants and it's harder to fill properties when there is a recession like that so here for example if you look at some of our cases in the downside and extreme downside cases we assume a recession in years two and three which is why the rents fall here and sure enough when the rents fall the general vacancy rates rise the bad debt and concessions also rise when that happens and expenses grow at a slower rate in a recession as well so that's the key principle you have to keep in mind you can't just blindly assume that rents go up and then the vacancy rate also goes up that doesn't really make any sense everything here has to be interconnected it'll be harder and more expensive to find new tenants and so the tenant improvements and leasing commissions will also go up which is why these grow at higher rates when there's a recession and then when the recession is over these fall back down to their normal levels in this model the idea is that there's currently a 7.5 percent discount to market rents across all the tenants in the building so we're going to spend on capex improve the building maybe a new roof maybe improve the interiors and lobbies other things like that and reduce that discount over time regardless what happens we're going to spend that money and the discount will go away but depending on market conditions all the other numbers here will differ if you look at the base case here the base case is pretty much stable growth we have rent growing between 3 & 5 percent per year we have the vacancy rate staying the same the bad debt stays the same the expenses grow at around 2 to 4 percent per year just under the rental growth rate tenant improvements grow at about the same rate and leasing commissions remain at 3 percent of effective rent in the downside case we have a mild recession and so market rents here fall in years 2 & 3 before recovering the general vacancy rate rises before recovering back to its 3 percent level the bad debt and concessions rise before recovering back to their 3 or 4 percent level expenses fall in years 2 & 3 before recovering and then rising again and then the tenant improvements and leasing commissions go up to much higher levels before falling back to the normal levels and then the extreme downside case is just even more severe so we don't have a rental drop of just 3 percent or 1 percent but rather it goes all the way down to a 6 percent drop which is pretty significant and all the other numbers are following the same basic format but the but they're just more extreme in general so you might be asking what's the point of this what advantage do we really get from scenarios in a deal like this and the answer is here for example they let us figure out that the proposed financing structure will just not work to show you an example I'll go down to the sensitivity tables here and you can see here that things look pretty bad for the equity investors they lose money pretty much all the time in the extreme downside case and even most of the time in the downside case but what's really notable is that even the lenders here the mezzanine investors could potentially lose money in the extreme downside case the preferred equity investors which is all to a form of debt frequently lose money in the extreme downside case as well so we can just look at these scenarios and tell based on the IRS and the recoveries that the deal is not going to be viable with 85 percent leverage as it currently uses normally the goal for credit investors lenders is to avoid losing money no matter what happens even if there's a disasterous recession and to come close to their target at IRR in other cases the goal for equity investors is usually to aim for a certain IRR in the base and upside case and then to avoid losing money in the downside case so that's a bit about scenarios let's go briefly into a few more advanced items and see what performers and other sectors like Hotel properties look like so the basic difference is that for a hotel the pro forma is quite different and it looks more like the income statement of a normal company we have revenue categories like rooms and food and beverages and then for the expenses we have fixed versus variable expenses sales and marketing and GNA can be much bigger and margins tend to be lower here's an example of a hotel pro forma for revenue it's split in two rooms food and beverage and other expenses departmental expenses rather are also split into those categories these are sort of like cost of goods sold or cost of services or cost of sales for a normal company we have gross operating income and then we have undistributed expenses which are variable expenses and then fixed expenses which as the name implies are fixed expenses so you can see items like sales and marketing in general administrative repairs and maintenance are all categories here we still get to net operating income but it's a little bit different here margins are quite a bit lower margins are between 20% and 30% whereas you might see 50% to 70% margins for multifamily properties apartment buildings for example so it's much closer to what a normal business outside the real estate sector would look like because really a hotel is the closest thing to a normal operating business within the real estate sector a few more advanced items on the pro forma are the loss to lease which we actually just covered it just represents the difference between market rents and in place rents and then there's also something called percentage rent where retail tenants might pay a percent of their monthly sales in addition to fixed rent all this is used to negotiate a deal and maybe a tenant will agree to lower fixed rent if they pay more in percentage rent or vice versa so it's often used as a negotiating tactic when setting up leases that's it for this lesson so let's do a recap and summary now the real estate performer is super important because it lets you analyze a property decide whether or not a deal is good and then also value the property and see what it looks like after you buy it after you renovate it or after you develop their property and it's similar to a combined and simplified income statement and cash flow statement but for a property rather than a company we went through a simple real estate pro forma Excel and you saw some of the calculations here and how we arrived at some of these numbers on a tenant buy tenant basis in a simplified model we always start with our potential revenue we make deductions and adjustments and then we list our operating expenses to get to our net operating income we list the capital costs and then we get to our adjusted net operating income and our debt service and then our cash flow to equity investors below that when we incorporate scenarios into a pro forma as we did in the multifamily example here the key is to realize that everything is interconnected so if we assume there's a recession where rents fall the vacancy rates should also rise bad debt should also rise expenses should fall or rise at a slower rate and items like tea eyes and LCS should also rise at a higher rate as it gets harder to find tenants and then when there's a recovery it should reverse and all those numbers should go back to their normal estates then we went through a Hotel performer very briefly and you saw how it's different it's much closer to what you see for a normal company and we talked about a few of the more advanced items like loss to lease and percentage rent that's it for this tutorial hopefully now you have a better understanding of how real estate Pro farmers work some of the key items and what goes into the calculations you can also look at the excel file and the accompanying blog post right below this video which I'll link to in the description below