Create an Excel Pro Forma Template for Planning Seamlessly

Excel pro forma template for Planning

[Music] hi everyone thanks for watching today I'll be presenting my single family home construction performa model now as many of you know I spent earlier in my career some time in the residential development side of the business and in my library I have a handful of uh single family models models for home builders uh be it horizontal construction uh the lots to the vertical construction the home itself now many of those models are probably more complex than what I wanted and so I wanted to build something that you as a potential home builder might use to uh assess the feasibility of a project so right early on you're presented an opportunity maybe to buy a lot or or a neighborhood that comes available and you're thinking of of going after lots and you want to to do some analysis to conclude whether uh it's it's it makes sense right so at that stage you're going to grab a just a quick back of the envelope model such as this drop in some assumptions that you know about about home building and determine whether this makes sense as such the model is built with just one tab okay uh everything happens on this one Tab and the reason I did that is it gives you the ability to duplicate this tab however many times you need uh for those situations where maybe you have multiple homes you're going to be building in this in this neighborhood and you could duplicate these tabs and then just create one tab where you roll up the results from from each one of the each of the tabs different homes You're Building to come up with kind of a a neighbor neighborhood level uh analysis but here we have just one tab uh just starting out we have a summary section at the top blue fonted cells are input cells and so in the summary section above this is just uh a basic proforma profit of profit and loss if you will of the project and then the cash required both Total uh Builder cash or Builder equity and Builder cash net of overhead that you'll be charging to the project throughout the length of the project in this summary I also have the ability to drop in just a picture of the elevation of the home you'll be building this is for presentation purposes you might be showing this to a lender or internally or to to equity investors and this just a way to to make the presentation look better next we have timing and there are few inputs here timing again is is a a rough and simple project schedule so the first input we have again our first blue fonted cell is the project start dat and I have dropped some outputs so that you can see the results of say in this case the day that the the date that you choose so let's imagine we chose February 1 and that is a Monday uh the project start day and and this and this model uses day periods right uh so day 17 day 67 Etc so obviously the project starts on day one and then I have a month and year just to give you a feel for when these things start in terms of months and years it also if you were to roll this up into a kind of a neighborhood level analysis these dates are important because you're going to be needing to match uh day month year uh and period for the different projects and so this uh this table here gives gives you that ability next we have construction start and so rather than choosing date here you're going to drop in a the the day as in period in your analysis and so uh in my case here I'm assuming 29 days after the project start the project start being the first day we spend money uh construction is going to start and so thinking uh those 29 days what might happen there well we're pulling permits per perhaps there's some design happening um so I'm going to call it 29 days and then we have construction end right so the difference between these two is our construction period over here it tells us how long these periods are so 29 days is the difference between our project start and our construction start uh the 180 days is the difference between our construction start and our construction end so we're assuming here a six-month construction just just a matter of doing that plus 180 and that's it's actually 181 days if I add 180 because it includes construction start this day 29 so what we might do is go this plus 179 days that gives us our 180 day length uh construction end ends on a Friday and that's probably a nice day for it and then we have the home sale so right the the the construction ends and then there's some marketing period now it may be as simple as this is pre-sold and you could close on the day construction ends which is likely there's likely to be some sort of inspection that happens maybe an a reappraisal at that point so you'll need some period I'm going to call it 90 days again uh this marketing period begins on the day construction ends and ends on the date that the home sells uh so home sale would be the home closing and in fact now that I see that I think I'm going to before I upload this I'm going to call this home closing okay I think that did makes make more sense and then this tells us the total project length roughly 300 days so you know we're right around 90 a little bit more than nine I'm sorry nine months uh and that seems like a reasonable construction period for a single family home like this so then we have project description this is where're going where we're going to drop in things like the name of the neighborhood maybe the lot block the size of the lot uh the address city state Etc again for informational purposes then we drop in the home plan name so each one of our our homes might have a a name or plan has a name so that's the name then you choose uh call this you know the story I'm calling it stories here but it's really like the type of home from a single story two story single story with a basement two story with a basement you can add or change this in fact if you want to change this to type and and and again this is just for Des descriptive purposes but if you want to change what ha what comes up in this drop-down menu you come down here to data validation and you change these inputs and if you want to add more you just simply uh select the row uh right click and put insert and as you insert that that will then insert a um new Option here and maybe we'll call this uh two and half story I like that all right um I don't know whatever so two and a half story so when you come up to this drop down menu now two and a half story is there then we have finished and unfinished square footage uh different Builders have different um um conventions for what they consider finished versus unfinished in my case here un finished does not include garage so when I say finished neither of these include the garage by the way so when I think about my finished square footage that is um finished but excluding the garage unfinished would be basement square footage that comes unfinished but we're building out a basement that a buyer could finish in the future maybe a bonus room that's unfinished uh but then when I when I assume my building cost below it's going to have assum some assumption that the garage is part of that so uh then we drop in bedrooms bathrooms and garage again this is just for informational purposes doesn't flow through to the financials next we have project costs we have our lot price uh it's just a dollar amount and then that's going to tell us what percentage the lot price is of the total project cost then we have design and Engineering this would also include our um so this would write our plans any engineering um that would go into uh the design uh this would include our permits any uh City County um Etc fees impact fees whatever right so this 4500 is probably really light here but I'm just going to call it 4500 for for now then we have finished square footage building cost now what this number is is it takes this per square foot 75 bucks a foot it's going to multiply by this finished and now in my assumption here the garage is built into this number okay even though the garage square footage isn't included here the garage is built in the $75 per square foot number and and again the thinking is I I know what it cost me to build a 2500 foot home excluding lot excluding overhead uh excluding profit excluding marketing fees that would be maybe realtor fees including closing cost right so it's kind of my hard cost for a 2500 foot home and that's why I drop in here I'm going to call it 75 bucks a foot 75 a foot times 2200 squ feet gives me $165,000 just hard building cost but then I have this un unfinished square footage and that does have uh you know there's less costs involved there so in this case I didn't include any unfinished square footage I would imagine in most cases that that would uh that would be the situation uh but here so I left it at 25 time Z is giving us zero then we have overhead and that's a percentage of our hard cost okay so I'm going to call it 10% uh some will have higher uh and again that's just a percentage of of these hard costs then we have uh other costs and this was where you might include really anything else that that doesn't fall within these other buckets um one example would be staging uh so if this is a spec probably want to put some furniture in there to Stage it and so I'll call it 4500 now keep in mind when when we're looking at debt debt is a percentage of uh hard cost uh lot lot design hard cost and overhead in this case it is not a it does not include other costs so that's why I put non financi financeable so then we have Finance costs and these would a combination of any origination fees uh when you're taking out a construction loan together with the construction interest during the period and those are calculated first this uh value here is the interest rate charged I'm just going to put 6% that's an annual rate now how it's calculated though it's actually calculated on a daily basis which uh I think most lenders would would Compound on a monthly basis so just keep that in mind I it's not going to have a huge impact but uh the daily compounding will charge a little bit more than uh than probably what happens in practice and then we have uh this number here uh is the fee charged and I have a formula in here that takes the debt and multiplies it in this case by one and a half percent so uh you'll either you know just drop in a a round number or you can do a calculation like that and then what happens is this is going to calculate based on uh all of our other assumptions timing um uh you know costs the loan amount Etc it will automatically calculate right here your Finance cost which again is a sum of your interest plus fees then we have marketing again this is likely real estate commissions I put 6% that is 6% of the sales price then we have closing costs that in is a again a percentage of your sales price and then we sum all those and in this case it's 290 just short of 295,000 I notice in eror this isn't adding up to 100% because this needs to be copied down okay and that will be corrected in the model and I upload so we have our total project costs then we move to the home set proforma now you you'll notice this is very similar to what we saw up top in the summary because it essentially is but there is one input and that's your your home price so what will you sell this home for at the end of the period uh and then I have right here it tells you how how many days you've assumed up in your timing this is going to take the sell from uh construction end to closing date so in this case I just used a nice round 150 bucks a square foot uh then we have all of the costs above somewhat summarized uh down to marketing and then it to takes the difference between the home sales and your cost and that gives you 35,1 se1 then it tells you your margin which is your net profit profit divided by the sales price right finally we have sources and uses uh this is calculating the where the cash is going to come in to cover your expenses right so your sources would be debt uh equity in this case in interest reserve and then proceeds from closing and a debt is a calculation of the hard cost Land Design and overhead at some percentage of those right so that loan to cost and in this case I put 90 U you can change that to 85 and and when you do that obviously you'll have to come up with more cash or more Equity so in this case I'm going to call it 90 we then have an interest Reserve which is part of the loan but I separate out from the the loan amount on your total cost so so that uh you get a feel for how much uh of your loan is the actual interest Reserve itself then we have proceeds from closing and the reason this is separated out is this is uh right your real estate commissions your closing costs that happen at closing you don't have to come up with cash for this but it is ACC accounted for because it's netted out essentially simultaneously at closing and then we have the Builder Equity that's the amount of cash you'll have to come in with uh first the total and then the difference between the overhead that you're charging to the project if you're a smaller Builder right uh this may be just simply a salary to yourself if you're a larger Builder this is going to cover your larger you know that that nut your overhead and so this is the net of overhead 13,600 um with an overhead included your total Equity that you'll need to come in with is 30,100 in this case then we have uses which is just a mirror image of these costs here and again those are the total costs you have uh to the project obviously your sources should match your uses if they don't then uh you're either going to be short on cach or well there's something wrong with the model for one but uh uh you'll need you need your sources to match your uses and then finally data validation which we discussed before now you'll notice in this section here the the calculations aren't included now if you want to see the calculations you either come up there's this little plus button you can click that or even easier press this two and that opens up the calculations off to the right and then there's an air check that confirms to you that the sum of all of these cash flows in each one each row here equals your uh inputed amount off to the left here and if there were an error there shouldn't be uh this is more for my purposes as I'm modeling this out but there were an error like for instance if we were to hardcode something here this value turns red that alerts me that there is an error in this but all of them are black uh all of them match up correctly and so the assumption is at least uh in in you know my way of uh modeling this that there aren't errs that uh the cash flows in these sections here are matching up with the total that we're inputting off to the left here and then you can close this by pressing this one button here we close those up and there you have it uh if you have any questions feel free to reach out and thanks for watching