Collaborate on Free Pro Forma Template Excel for Engineering with Ease Using airSlate SignNow

Free pro forma template excel for Engineering

[Music] hi and welcome to this tour of re FM's mix use apartment multif family Building groundup Development Excel template this is Bruce kersch the creator of the model this is the most advanced and flexible of The Suite of groundup development tools that we offer for multifam properties let's go ahead and start on the navigation homepage and what you see on the left here is a list of all of the tabs in the model and it's a large model 34 tabs and because there are so many tabs these are similar to links that you would have on a web page so you can click on any one of those and in the top left cell cell alpha 1 on every sheet is a link to get back to this homepage next we'll go to the assumptions input Tab and we're zoomed all the way out here purposefully so we can show you that there is effectively a leftand page and a right hand page and this was designed such that if you have two computer monitors you can place this Excel Window across those two such that these sections sit on the left and the right hand side now what we'll do is we'll zoom in to this top left section here and one of the things that you'll notice Above This section is a dedicated area for model checks which will show green if there are no issues isues in the current run of the model and if there is an issue you'll see it shaded red and it'll say check and these are all here just to protect us from drawing faulty conclusions based on a run of the model that is not fully balanced let's say in terms of sources and uses of funds or based on a loan that's only repaid in part as opposed to being repaid in full this top left box addresses the property and the building to be developed on the property so what we'll do is we'll enter in some details for a hypothetical groundup development site and some of these inputs in this top leftand box are just informational in nature the f will populate once we put in a gross square footage and I'm going to skip over some of these that are just informational in nature I'll enter in details for parking next we'll move over to the grow square footage of the building and this model supports two phases and let's just say for the sake of simplicity that it's two towers of the same size and next what we'll do is enter in some quantities for parking and storage next what we'll do is we'll populate the unit mix the model supports unit mix is for affordable or rent control controlled units as well as market rate components for both phase one and Phase 2 each of these tables is set up in the same way every one of the lines has choices that are pre-populated in addition to these choices you can add to the list or change the choices that will show in the list by by making edits to the block of list choices below the tables now that all the tables are filled in our summary shows at the top here giving us our totals project wide across both phases in this case for a total of 255 units that generate at today's rents 6.35 million in Gross potential rent now we'll return to the building profile section of the assumptions Tab and we see our averages are now showing here as well as the totals next let's move down to the project timing section we'll go ahead and get some dates in here for the land and for the start of construction and enter in the duration of construction and the lead time for the first CFO and number of months to stabilization after the last unit is leased for the absorption we can enter any number of pre-leased units that we wish followed by our average monthly absorption rate for both uh affordable and the market rate units and we'll do the same in time lapse for the second phase next we'll proceed to the uses of funds we'll assume we have a 10% deposit on a $15 million land cost with an additional $250,000 of land acquisition costs and we'll put in some unit cost figures for the hard costs for both of the faces and a separate input for the parking hard costs on a Pur BAS basis we enter in an additional $5 million for retail hard costs and 100,000 for owner directed hard costs and a half a million or FF which we'll say is going to be spent during the final two months of construction and will include a 5% contingency for soft costs we'll put in a per square foot number assuming a 5% contingency there as well we'll size the developer fee off of hard costs and assume that just 10% is available for draw during preconstruction so before any financing costs we have a development cost of $82 million now we'll go down to the sources of funds and we'll assume that the sponsor owns the land and has one and A5 million in land Equity that is part of the capitalization of the transaction and in addition they put in a million dollar in cash and they have a programmatic partner that is putting in $4.5 million and there's a third-party investor entity that puts in 15 million for a total of 22 million in equity since the land is already owned by the sponsor we have no need for a land loan and we'll go straight to the the mezzanine loan and we'll assume that that's $10 million with 5% current interest and 3% acree interest in addition and a 2% floor in 6% cap next we'll do the senior loan next we'll move on to our operating assumptions which will show inputed in time lapse and now that those are inputed we have our active graphs not only for GPR for both of the phases but also for noi next we'll go over to the refi section and make our inputs which will show in time lapse and last we'll move to the disposition position section which will require us to input a sale cap rate and while we see initial returns we need to Circle back to our check section in the top left and we'll see that there are still a number of items that need to be sorted out before we can rely upon the results of the model most of these checks relate to the soft costs not having been distributed in the monthly Tab and so if we put in a set of percentages that in total will give us 100% most of these issues should resolve themselves the other item that we need to do is we need to resize the senior loan by clicking on the macro buttton and now that all of our budget items are correct we need to look at the insufficiency of the permanent loan to pay off the in place debt and that can be resolved with a loosening of the tests that size the loan or alternatively an increasing of the rents and the unit mix and or a decrease in the operating expenses and once all of the checks are showing as green we can now rely upon the returns that are being reported back