Get Your Free Pro Forma Template for Teams

Free pro forma template for teams

[Music] hi and thanks for watching in this watch me build tutorial i'll be taking my single family home construction proforma and expanding it to accommodate a multi-home analysis now this is a request that i commonly get and i specifically built this model to allow for this type of work to be done essentially the model as it stands has one worksheet and on that worksheet you'll find the inputs the calculation modules and the outputs you'll find the investment cash flows and you'll find the reversion cash flows for your dcf all of that is encompassed on one worksheet and that worksheet can analyze the construction and sell of one single family home however uh let's say that you want to analyze for the construction of four homes and you want that all in one analysis maybe over a two year period well i'm going to show you how to take this model and expand to really an unlimited number of homes using this methodology so let's go ahead and get started i'm using version 1.2 of the model uh so you may be using a newer version and if so it might look slightly different but it really doesn't matter for this exercise and then i have this one worksheet here the analysis worksheet and this is where everything happens and the first step here is i'm going to be duplicating this analysis worksheet several times in this scenario we're going to say okay we're going to build four homes over two years and we want this all part of the same analysis and so what will happen is we'll have four worksheets one for each home plus a rollup worksheet now rollup is a term that i use fairly often and that really is it's summing up cash flows from different properties or different uses and in mixed use or in portfolio level uh modeling we use this roll-up concept quite a bit uh here we'll call this roll-up tab the summary tab and that will summarize our the results of our analysis as well as summarize the cash flows of our rolled up four properties and then again the four uh property tabs will include the inputs the calculation modules and the outputs for each of those properties so let's go ahead and get started we'll come down to this analysis tab i'll right click it i'll go move or copy and then i'm just going to move to end and create a copy and hit okay and what happens is this tab is is duplicated identical right so if we click between the two you'll see they're identical i'm going to rename this analysis to to be summary and this tab will become our summary tab i'll move it back to the left and what i need is i need to ensure that my property tabs are next to one another and that's important because the formulas we'll be using on the summary tab require that those property tabs are next to one another the next thing i'm going to do before i duplicate this further i'm going to make a minor change to our property tab just come here and where it is our pro where we have our project start date i'm going to link that to the summary tab and that that way and i'll turn this green and that way if we want to change the project start date we just simply change that here on the summary tab the other thing i'll do is rename this analysis tab in this case okay this first home is lot 11 block a and each of our homes will have its own name and and again we'll just name it lot block so we have our first yeah we have our first property tab done let's go ahead now and duplicate this so move or copy create a copy move to end and this will call what lot 12 all right so we change the lot and block within our project description and then down here we'll rename this worksheet to be lot 12. all right now the next thing i'll do and this is unique to our subsequent tabs here is i want the design and engineering and overhead i want those to be now inputs and the reason why this matters is the design and engineering and our overhead costs will start later and by default this model had that starting just in month one but if we do that then that means the the later homes that will be starting a construction later on we'll we'll have some design and engineering costs and overhead costs initially and that we may or may not want that so i change those cells to blue otherwise i think we're happy with this for now oh you know what i'll do i'll delete that picture and otherwise let's go ahead and copy this again come back down here to lot 12 block a right click move or copy move to end create a copy and then let's rename this to be let's call as lot 13 and then the block here will change to 13 okay and then again nothing needs to change yet so we right click move to n create a copy hit ok and then rename this to be lot 14. okay now a few things you'll notice everything else on these four worksheets everything else is identical such that our resulting unlevered irr should be the same 39.73 with 69 247 of net profit on an unlevered basis and we can confirm that here 69 247 69 247 and 69 247 the reason i do that and that's important is that will help us to air check if we set up our summary tab correctly so for now we have our property tabs done now it's important that we don't add anything inside of this area where we have our property tab so if we if we want what we could do is we could just create a section and just call this prop and then another worksheet here we call this end and then maybe change the color of these worksheets to black here and black tap color black and that just kind of alerts us that we don't want anything else to we don't want any other worksheets to be added within this section what it also means is that any worksheet added really between lot 11 block a and lot 14 block a any new worksheets added there will be will be included in our roll up and you'll see that here in a second when we get to the summary tab so we come over to the summary tab let's change the color to green the reason i do that is i like my input worksheets to be blue i like my report worksheets to be green there's only going to be one input on this summary tab so green still makes sense now the next step is we are going to both make changes to the summary tab to make it a summary uh worksheet and then we'll be rolling up the cash flows that we need to roll up and so the first thing we'll delete that picture and then the label here will be picture of plat map all right and this is the the the roll up the summary of of this subdivision and so we it makes more sense we'd have maybe a picture of a plot map here let's call this a project summary as the title of the worksheet and then here we'll just simply call this foggy glenn oops foggy glenn that's the name of the subdivision the neighborhood here all right so also as we're just setting up this worksheet we really don't need neighborhood makes sense but we don't need addresses those are going to be on the individual property tabs we don't need a lot size delete that well you know actually let's keep that let's delete lot block but let's call this average lot size okay and we'll change that here in a minute there's no home plan there's no stories so we can delete those and then there are no bedroom bathrooms and then no garage so i delete those items uh and then we'll come in here okay neighborhood is foggy glenn average lot size we'll we'll get to that here in a minute otherwise let's see we'll delete the data validation here at the bottom we don't need that and by the way when i delete what i do is i select the rows and i use a control minus sign and that's just deletes the row and then i'll i'll select all of those rows right click and choose hide i'll just hide those so that they're not in the way anymore otherwise everything in here will be included it'll just be uh edited such that it's a roll-up of all of our cash flows on each of these property tabs now uh let's start then with the summary well what do we know well we have these this with three columns within the summary section and this is the summary of the project right so we have our inflows which in this case is just simply our sales price uh and then we have our outflows which are project costs plus marketing and closing costs and the result of those is our net profit and we note that this is on a levered basis meaning after debt and so if we scroll down to our levered cash flow we'll see the sum of all of our leverage cash flow 57 431 57 431 so we have then these three columns uh two of them are really just metrics that tell us something about uh our the actual line item right so percentage of cost and per square foot but sales price right now right this sales price is pulling from uh the sales price there however what we want it to do is add up the sales price from each of the properties and so this is where we're going to use a feature that excel has where it can so it will sum the same cell across a a range of worksheets so we just start with a sum as usual and then we choose the first worksheet in this range so in that case it's the very the furthest left worksheet within this range of property worksheets and then we hold down shift and we click the last worksheet within this range here all right and so now you see it says sum and it's just the first worksheet in the range lot 11 block a uh colon the last worksheet in the range lot 14 block a and then asks for a cell well which sell well the sales price i'm gonna leave that relative hit close parentheses and enter and now here on the summary tab you'll notice we have a roll-up or a sum of the sales price for each of the properties now currently because the sales price is the same if we take that a value and we divide it by four just as an error check 399 9 right which is the sales price of each of our individual properties but we could go and change the sales price in each of those properties and this right here then would change it would be the sum of those four i'm going to change this green and then all i have to do is copy this down so i uh highlight all those hit alt h vf that copy or that copies and pastes as formulas only and then i change those cells green now i'm using an excel add-in called wst macros and so you'll see me make quick keyboard shortcuts such as that to change it to green if you want to change the green on your own yeah you just have to highlight these come up to your font color and then change it that way all right uh and by the way green denotes a link to either an input or an output on a different worksheet that's why i changed those green this stays black all of these stay black because they're simply metrics based on calculations on this worksheet now we can do one quick air check let's take that net profit divided by four and what's the result 57 for 30. remember that 57 430 well 431 when you round up so there we have our summary section is done and all the rest of these are links within this workbook that will update once we do a similar thing to what we just did down below next we have timing so we are going to leave the analysis start date the same and that's all of the worksheets will be driven by this date project start is month or i'm sorry day one and and these periods are daily periods right if you're familiar with the model so the construction starts what day of the analysis period well uh it's not driven from this tab so we need a link back to our range of properties and the construction start is going to be the minimum day across that range or equals min open parentheses and then we use the same technique we used before we come to the very first property tab here click shift select the last property tab within that range and then choose the cell that cell there close parenthesis hit enter turn that green that's our construction start our construction end is the same logic so paste those down except it's the max not the min so i copied that down then i change that function to max instead of min hit enter and then the home closing i can copy and paste that down it's the max of both the construction end and the home closing day everything else should automatically calculate and we're done with timing project description what's the average lot size well again we hit average of the range same technique as before choose the lot size hit enter turn that green and then finish square footage is what well it's the sum of the range finish square footage [Music] turn that green and unfinished is also the sum of the unfinished across that range a total square footage is a calculation that happens on this tab so we can leave that black 12 000 square feet is the total across our four properties and that's we air check it 3 000 square feet come back here 3 000 square feet per property so we're done with the project description next we move into project cost and you know you're starting to get the hang of this right again each one of these values is just simply going to be a roll up of of the four individual properties we go sum select our range choose the cell hit enter we're going to copy and paste down as formulas only alt h v f and then select all these and turn those cells green now these cells out here had been inputs so for instance finished square footage building cost was driven by a per square foot cost well this is now going to become just a metric it'll tell us something really we want to understand what is the blended cost per square foot based on the aggregate of our building cost both finished and unfinished and so we do that by just going that amount divided by the finished square footage there hit enter turn that black and then copy that down and that will now calculate for the unfinished square footage and then overhead as a percentage of hard costs so we turn this black and then we go our overhead divided by hard costs which is uh let's see negative hard costs here okay and the reason i turned it negative is because this is a negative value and that way we result in a positive metric there six percent so our overhead uh equates to six percent of hard costs we can turn this other cost non-financiable label black and then our finance costs we can delete that and then we want to understand okay well this is the rate so we can delete that one as well we don't need those two metrics and then marketing is a percentage of sales price right it's marketing divided by our total sales price which is six percent turn that black and then closing costs as a percentage of sales price turn that black and then that divided by our total sales price okay one percent and again the sum of that let's air check this take that divide by four 342 469 342 469 actually you see it right uh here 3 42 469 all right so we can delete that and then we come down uh so this should automatically calculate correctly our day start for lot price is going to be equal to our construction start that's the first lot closing so i'll just turn that black rather than being blue and we're done with project costs at least our inputs for project cost out to the right we'll do the d all of the cash flows in a minute okay next we have home sale pro forma and again this is a roll up so i can just borrow these formulas here just grab any of them copy that come down here paste as formulas alt h v f then turn them green and the resulting net profit 229 723 if we come here oops i got two let's divide that by four again we're just air checking as we go 57 431 round it up 57 431 okay good and then we move to sources and uses again we do the same thing let's just copy and paste alt hvf and then let's select all of these cells and turn them green and then we can just divide that by four 342 469. 340 yep that's what it was you can double check but yeah 342 469 342 469 okay so our sources and uses are almost done the only thing we have left is the blended loan to cost and to do that we go let's turn this black we go equals our total debt across the four properties divided by the sum of and remember the debt is a percentage of land design hard and overhead so we come up land design hard costs and overhead so it excludes other financing marketing all right hit enter and that's 80 percent which again is 80 okay so we have our sources and uses done our other return metrics are calculated based on our cash flows out to the right so let's come out and do those and again the cash flows are simply a rollup of the cash flows from the other from the range of property so we'll just grab really doesn't matter but we'll grab this cell here come out here to the right and we're going to select this entire section all right so we we select the entire section there and we paste alt h v f as formulas and then turn those green now you'll notice there's an air check tool already included in the model and so it has now properly and so it's working so next we're going to do the same thing we'll just copy that cell again and just come down to this next one under home sale performa select that entire range here alt hvf turn those green and then keep going we'll go to this next one we go yep alt hvf so we copy over for sources turn those green come down to uses paste those alt hvf as formulas turn those green and then we come to our unlevered cash flow alt h v f turn that green and then leverage cash flow alt hvf turn that green and now we have rolled up all of the cash flows and you'll see that the let's divide this by four as an air check we can do the same for our levered cash flow total profit 69 247 per property or the sum 276 988 229 723 or 57 431 per property 57 431 per property now we also need to look at the irr and the equity multiple 39.73 on an unlevered basis with a multiple of 123 39.73 123. so our summary tab now is complete and and we have really finished uh now there's a couple things i want to point out the first is right now we haven't modeled out the individual lots and so you'd come here and you say okay we have lot 11 and this was what uh the rosebud the name of the home plan it's 3 000 square feet 800 square feet of which is unfinished it's selling for 3.99 maybe we go to lot 12. instead of the rosebud this is uh the lilly tree i don't know i'm just making this up and it is 2500 square feet finished nothing unfinished these are the cost uh but this one sells for i don't know 389.99 all right and you go you'd go through each one and maybe we we don't feel like we can borrow quite as much i don't know why and and so you would make adjustments to each one of your homes and you come back here and now your summary will capture those differences uh let's make a change here so lot size 7 500 let's say the lot's slightly smaller here 6000 such that our average lot size 7125 now instead of 7 500. now the other thing to point out and this is where it gets cool so we've we've modeled this for four properties let's imagine that you wanted to create a template for using this exercise create a template for future projects that you have but you don't know how many lots or how many homes you may be analyzing at any given time so long as the worksheet that you duplicate falls between the first and the last worksheet within this range it will automatically be added to your summary tab so for instance we could come to let's come to lot 12 here let's duplicate this again move or create a copy create a copy and then just i don't know drop it in there hit ok again it just needs to fall between this and that worksheet and we'll rename this one i don't know lot one block b oops and here lot one block b we can come over here and you're gonna see now the total is higher 34 455 because it's now capturing five properties not four and you'll see this right 324 if we delete this here it's back to 266.098 and that was after we made some adjustments a lot 12 to make it slightly smaller and sell for slightly less so that is my tutorial taking my single family home construction performance expanding it for multiple properties let me know if you have any questions about this watch me build or about the model specifically otherwise thanks for your time [Music]