Streamline Your Processes with the Excel Pro Forma Template for Procurement

Excel pro forma template for Procurement

hi and welcome to real estate Financial modeling's demystifying the Excel proforma what it is and how you can begin to master it the goals of this session are to identify the major components of the proforma and their functions illustrate the relationship of the components to one another learn the Cardinal rules of modeling with Excel and to learn best practices for inheriting a model model the general framework that we are representing in a proforma is that of a timeline where on the left hand of the timeline we have today which is also known as time zero and as we move across to the right on the timeline we are moving into the future the flow for a commercial real estate transaction is as shown here where we start on the left where equity and debt will fuel a real estate transaction and moving across to the right the asset will will operate and generate positive cash flow those cash flows will fund the repayment of debt and the return of the invested equity and the excess cash flows will be the profits to the equity investors this linear process is all occurring as a projection into the future when we ask the question what is financial modeling the answer is pretty simple it's the forecasting of future Financial outcomes with which are calculations based on current assumptions meaning we are sitting here today in time zero and we have to assume certain things such as a rent and a growth rate on the rent and cost of construction and so on WE format the word assumptions and inputs here in this bold blue type because this is how assumptions will always appear in our spreadsheets and we format the assumptions differently so that they're easy to identify a proforma is really just a story about a financial transaction that attempts to predict how and when cash will be invested in the transaction and repaid borrowed for the transaction and repaid and hopefully generated by the transaction and spent or paid the proforma does three things relative to the dollars going into out of and being applied within the transaction first it identifies the purpose of the monies second it quantifies the amounts and naturally we will deal with applying expense inflation and income growth factors as applicable over the timeline of the transaction in third the performa times the flows so in essence what we're doing for every line of a proforma is asking a few questions and these are simple questions we're asking how much and when and why as an example if real estate taxes are paid twice a year in January and July then we will want them to appear in our future projection as occurring as cash flows in January and July we are always asking these two questions for every line item in a proforma how much and when and the when is informed by the why such as the D date for the real estate taxes you might wonder why is timing important when we're dealing with real estate transactions well timing is important to everybody owners of properties generally do not want to spend any money before they absolutely have to their vendors and service providers generally want to get paid as quickly as possible and are typically paid monthly investors in a transaction want to get paid based on the trans transaction documents and the transaction documents will specify the profit sharing terms that were architected or structured based on the objectives of the investment for example if an investor makes a long-term investment five years or longer they don't want to get their Capital back before year five because what they'll have to do at that point is then reinvest it lenders want to match the length of their assets and their liabilities lenders want their borrowers to abide by the term of the loan and as a result there are prepayment penalties for when borrowers want to repay the loan sooner than the timing prescribed in the loan documents and lastly the internal rate of return or irr depends partly on the timing of cash flows so timing is important to all of the players in a a real estate transaction let's take a look at the assumptions of this simple 3-year proforma let's assume that we will purchase a newly built retail strip center with four tenants dry cleaner Sandwich Shop bakery cafe and a convenience store let's assume for the sake of Simplicity that no financing is used meaning the purchase will be all cash and the leases for the tenants are triple net meaning that the tenants will pay all of the expenses related to their Suites the leases in aggregate will total $60,000 a year and all have 3% annual escalation Clauses and let's assume additionally that there is a percentage rent estimated to be $5,000 in year 1 looking at this figure in the Excel file we can see all of the individual line items such as a cash investment treated as a negative value or cash outflow in Time Zero row 14 is the expected annual net operating income or noi that comes from the base rent which we said was $660,000 per year row 15 is the base rent adjustment for inflation related to the growths of the rents as manifested in the escalation clauses we assumed that to be 3% we do not show any growth in year 1 because the nominal rent is the real rent in year one but in year two we show that there is 3% growth on top of that $60,000 base rent which is equal to $1,800 and in year three there is 3% growth compounded twice for a total of $3,654 we show the percentage rent in row 16 and we assume that that grows mildly from years 1 to two to three and we sum those three line items up in row 17 to what we call the expected return we then calculate the year-over-year growth and the cash onh return which is the annual cash return divided by the total cash investment and we make an assumption in row 21 as to the net sale amount that we will be able to achieve as of the end of year three for a sale at the end of year three when we take the sum of those line items we have what we call the expected value in row 25 but we note that these values are the forecasted values the net cash flow is the sum of all of those values including the time zero investment amount and the process of discounting will then entail taking the future projected amounts and discounting them by the discount rate that is assumed which in this case is 7% annually so for Year One we take the assumed $65,000 future value and divide it by 1 plus the 7% in year two we take 67,341 zero amount and these discounted amounts we can also use the npv function which Excel gives us to apply this 7% discount rate to the Future assumed amounts and then add back the time zero amount and this naturally will give us the same result so what this simple model says is given all of these assumptions we perceive this investment opportunity will create the equivalent of today's value of $196,000 in value if the discount rate were to be 0% meaning we had 100% confidence that all of these future amounts will in fact come to pass exactly as we project them the Net Present Value would be equal to the net cash flow amount but since we do not have 100% confidence we will apply a discount rate and thus perceive a lesser amount of value being created as we sit here today in Time Zero there are several different types of transactions that we represent in Pro foras first acquisition or disposition of an existing income producing property which could be Apartments office industrial Hotel retail or Self Storage second the renovation and continued operation of an existing incom producing property sometimes also known as the repositioning of the existing property third land entitlement subdivision and the sale of lots to builders fourth groundup new property development whether it be for a unit sales project such as single family houses town homes or condominiums or for an income producing property fifth the refinancing of a property which is the replacement of the existing loan with a new loan and lastly the purchase of a mortgage note that is secured by a property now let's take a look at the components of a typical proforma and first let's look at an example of a groundup development projection model in this case it's for an apartment building and so what we see here are six major tabs that will exist in the Excel spreadsheet at a minimum in this pro forma starting in the top left we will have an assumptions tab where we make all of our inputs and where we will also digest key performance indicators with respect to the returns of the investment moving across we will have a tab which will describe the development site or or the lot and the building to be built on the lot moving to the right again we will have a tab that describes the capital structure of the transaction meaning the various sources of funds on the bottom left we see that there will be a separate tab to describe the apartment units and the mix of units within the apartment building in the bottom middle we see that there will be a dedicated tab for the sources and uses of funds projection which matches the costs in each period to the sources of funds that will pay for that cost in that period and lastly in the bottom right we see that there will be a dedicated tab for the cash flow projection of the built asset now let's take a look at these in more detail one by one this is a snapshot of a section of the assumptions tab for a groundup apartment building Building Development analysis again all of the inputs or user assumptions are formatted in bold blue type the lot and building characteristics tab performs the function of describing the zoning and the physical characteristics of the land and the structure that you intend to develop and build specifically it calculates the square footages of the various uses associated with the structure both on a gross and rentable basis and it also will calculate the number of parking spots and levels of parking this is a snapshot of what the building profile section might look like the capital structure tab describes the different sources of funding that will pay for the development of the Project funding sources fall into two categories either Equity or debt Equity can be either Cash Land or tax credits converted into cash and debt can take the form of land loan construction loan mezzanine loan and sometimes a permanent loan to replace the construction loan this is an example of the capital structure section we show all of the equity sources and the debt that comprise the total project cost the unit and mix details tab will describe the various apartment types the number of each and the expected rents for each of those unit types this is an example of what the unit and mix tab might look like the sources and uses of funds tab describes how the development budget is spent across the various cost categories over time the cost categories are land acquisition hard costs soft costs FFN which stands for Furnishings fixtures and equipment and financing the sources and uses of funds tab will match sources of funding to each of the uses of funds this is an overview of the sources and uses of funds tab across the top we see we will have our timeline which will either be monthly quarterly or annual periods starting at the top we see the uses of funds first comprised of land costs hard cost soft cost FFN totaling to what are known as the eligible senior loan costs meaning all of those costs are eligible to be funded by the senior loan because they have collateral value associated with them below the eligible senior loan cost line are the financing costs which are all the costs associated with borrowing funds to fund the project SU up to the total project cost exclusive of operating deficit operating deficit will be carried next and included in the overall total project costs line total project costs are equal to the total uses of funds next we see the sources of funds section which will include all of the sources of equity and debt that will contribute towards funding the project as well as any property cash flow that can be used to pay financing costs during the development period the total project costs or total uses of funds will equal the total sources of funds this is a partial snapshot of the uses of fund section where we see the months going across the top and the major cost categories along the left Land Park working and acquisition costs hard costs including contingency and so on the cash flow tab describes the Dynamics of cash relative to the newly developed asset with growth factors taken to account in the same way that we showed the rents escalating for our 4 tenant retail Plaza due to the 3% escalation Clauses the cash flow tab will capture the revenues that are received operating expenses paid and real estate taxes paid and it will calculate net operating income or noi this is an example of what the cash flow tab might look like for the apartment use of our project across the top we will once again have the timeline as well as the unit absorption schedules or the lease up schedule for the apartment building the top line is gross potential rent or GPR against which we will make adjustments for elements such as vacancy and concessions to get us to effective gross income or egi from egi we will deduct operating expenses and real estate taxes to get us to net operating income or noi from which we will deduct replacement reserves and capex amounts to get us to adjusted noi if the apartment building were to have have a retail component this is what the retail section of the cash flow tab might look like again the top line is the gross potential rent to which we will make adjustments to get to effective gross income from which we will deduct operating expenses and real estate taxes to get to noi from which we will deduct again any replacement reserves or capex as well as leasing commissions related specifically to the retail component and this will get us to the adjusted noi for the retail piece this is a partial snapshot of the cash flow tab showing operating expense and real estate taxes broken out into various line items the second part of the cash flow tab will lever or adjust the cash flows produced by the property by accounting for all of the equity and financing cash flows in the transaction the net result of that will be what is known as the net levered cash flow this is what you make on a pre- income tax basis on the transaction here is an example of what the second part of the cash flow tab might look like we start first with the combined adjusted noi from both the residential and Retail components when we add the amounts from the capital events or the sale of the assets that will get us to a net revenue after Capital events but before financing costs line from which we can then calculate the unlevered cash flow which requires us to deduct all of the eligible costs and all of the operating deficits to look at the transaction on a levered basis we will then add in all of the financing costs that are paid in cash which will get us to the net revenue la before Capital events but after financing costs to which we will add net sales proceeds from the capital events which will get us to net revenue after Capital events after financing costs and we will then make the financing cash flow adjustments to get to our project levered cash flow which again is the pre- income tax cash flow amount here is a partial snapshot of the second part of the cash flow tab starting at net revenue after financing costs and the adjustment line items for the various equity and debt amounts getting us to the bottom line of project levered cash flow switching over to looking at the components of an existing incom producing property we note the fundamental difference between the nature of a development project and the nature of an existing property namely the revenues for a development project or the income does not begin for quite some time whereas with an existing property the income or the revenues are in place and hopefully we will have pre tax profit from the very first period versus in a development project where we generate losses for some time before the property is generating enough income to generate a profit there are fewer tabs required for a performa for an acquisition analysis starting at the top left we have again assumptions and key performance indicator returns reporting moving across sources and uses of funds a rent rle which will detail all of the tencies historical operating data and once again a cash flow projection here was a partial snapshot of the assumptions tab for the analysis of the acquisition of an existing multif family apartment building we show deductions each year to the gross rents assumed for loss to lease vacancy rent concessions and writeoffs here is a snapshot of the sources and uses of funds which shows the equity and debt that come together to fund the acquisition of the property this is an example of what a rent roll for an existing multif family building might look like which describes the unit types the square footage of each the lease start dates and the current rent this is a partial snapshot of the historical operating statements for an existing property where TTM stands for the trailing 12 months this is an overview of a cash flow tab for an existing apartment building across the top we have the timeline and any unit absorption assumptions then we have gross potential rent to which we will apply the loss to the lease which gets us to Total minimum rent from which we will deduct vacancy and concessions which will get us to effective rental income to which we will add any other income deduct write off amounts and add back any pass through amounts which will get us to effective gross income or egi from which we will deduct operating expenses real estate taxes and reserves that will get us to net operating income from which we will deduct dollars for Capital expenditures any tenant buyouts any unit renovation cost and any asset management fee which will get us to our operating cash flow which is another name for adjusted noi to which we will add dollar amounts related to the acquisition and disposition of the asset which will get us to the unlevered cash flow to which we will add Debt Service expense which will get us to the net revenue after Debt Service and we lastly make our financing cash flow adjustments to get to the levered cash flow and this is a partial snapshot of the cash flow tab showing the detail of the operating expenses when we talk about the Cardinal rules of financial modeling there are are basically six that we would like to communicate to you the first is garbage in garbage out which simply relates to the fact that if you put in an assumption that does not have any current or reliable bases your calculations are going to drive off that assumption so if it's a meaningless assumption or highly exaggerated such as a rent that is extremely too high the calculations will simply magnify the irr relevancy of that assumption we encourage you to annotate your models as much as you can tolerate and you can do this using the ALT key I which we'll put in a yellow comment box in which you can put a running record of comments related to the model change is definitely the only constant when you are modeling so it's good to keep records of why you made certain decisions we encourage you to learn and use the keyboard shortcuts also to save multiple copies of your models so that should Excel or your computer crash you don't lose a lot of work additionally to sanity check your outputs by asking yourself out loud if your numbers make sense if something looks too good to be true it typically is so before you send your work off to your manager it's wise to make sure that the transaction looks reasonable and lastly pay now or pay later meaning if you know that you need to do something in your analysis it's best just to do it now while you understand your circumstances as opposed to saying I'll just do it later where you don't know what type of other pressures you might be under best practic is when inher ing a model are first to find the buried bodies you can do this by unhiding all of the Hidden sheets unhiding all of the Hidden rows and columns ungrouping all grouped rows and columns and additionally painting each sheet by selecting all and then putting a background color other than white so that you can make sure that there is not any invisible text or invisible inputs which are driving the model next you will audit every cell and every formula to make sure that you could explain every formula to somebody else and make sure you understand them all and lastly to the extent that you can stay in touch with the original builder of the spreadsheet that's always beneficial so that you can contact them with questions thank you for watching this video be sure to take advantage of all of the free tools available on our blog and the free tools and professional tools available at get fm.com