Unlock Efficiency with the Excel Pro Forma Template for Customer Service

Excel pro forma template for Customer Service

hey there this is Ike Hoffman with tactical Real Estate Solutions and today we're going to be building a simple one-page multi-family pro forma Excel template from scratch it's meant to serve as a quick and simple evaluation tool when you're doing your initial due diligence on a prospective investment opportunity this is a tool that I offer to site visitors for free you can get it from the link below right from the Tactical website I'd recommend going there now downloading it on your computer and you can use it as a reference point as you complete the tutorial I'll also be adding some additional features to the version I'm creating today I'd recommend that you stick around to the end of the video to learn what those additional inputs are if you've been enjoying tactic as video content I'd really appreciate if you gave the video a like subscribed and allowed us to notify you when we're releasing new content let's go build a model [Music] thank you before we start building our one-page analysis tool let's set up a theoretical investment with actual numbers in my experience it's much easier to build a tool when you have numbers to work with so let's say we we received an email from a broker and there's an apartment complex for sale and pricing guidance is 25 million dollars there's 200 rentable units and those units consist of 175 000 square feet we have a debt quote we got quoted at 65 percent loan to value an interest rate of 5.75 25-year amortization in a one percent loan cost we also have some historical operations information the current rent at the property the current average rent I should say is eleven hundred dollars the current vacancy is six percent and the 2022 noi was a hair north of 1.3 million just looking through some pictures making some rough estimates we think there's going to need to be about three hundred and ten thousand dollars of capital needs and then we pulled property tax information as well from the County website the current assessment is 23 million five hundred and sixty seven thousand and the current property tax is payable are 465 000. we'll be referencing these terms repeatedly as we build out the model in this section we're going to input the address City state zip code units and rentable square footage if you've ever used a tactica analysis tool you know that all input cells are brown text cells meaning it's up to you to enter in an assumption or a label in Black text cells are outputs meaning they should not be amended unless you're incredibly comfortable with the tool and you know what you're doing so in this section we'll just make up an address 111 First Street we'll say in in Saint Paul Minnesota then it's 200 units that was in the initial data drop that we looked at before we began building and the rentable square footage was 175 000. I'm going to format this slightly these would all be Brown Tech cells like when there's a comma so it's easier to read and then I'm going to highlight the section header with black fill make the text White just a few other formatting tweaks all right so we have our general property information now part two is the capital stack I'm going to copy and paste section header all a capital stack and in this section first thing we need to enter in is the price we also want to know that on a per unit basis the price per unit and the per square foot and if you recall the pricing guidance was 25 million I'm going to format this cell as well I'm going to make it this would be a brown Tech cell it's going to be currency and then the per unit we're going to take the 25 million divided by the 200 units and then the price per square foot will take the 25 million and divide by the 175 000. and then we have our debt assumptions next for our debt inputs we need a few different things I'm going to indent these labels so it's clear that it's falling under the debt section in this particular example we're saying we we have a bank loan but if you have agency Bridge debt you could you could type in the loan type here these will also all be brown text cells the LTV is going to be 65 percent the interest rate 5.75 percent amortization was 25 years and then the loan cost was one percent I'm going to add some decimals there and we'll wrap up this section with loan cost loan amount annual payment and the total equity required I'm going to do a quick spell check I make a ton of spelling errors I'm always more focused on the numbers I'm going to also indent these cells so it's clear that they fall under the debt category and then I'm going to just highlight this green so it stands out and also the price that's obviously a very essential input in this particular model so with the loan cost we're going to just take one percent times the amount of the loan which we don't have a loan amount yet the loan amount is going to be the 65 LTV times the price and now the loan cost populates although I don't like the format so I'm going to change that and then the annual payment will do a payment function we'd be making monthly payments we'll take the interest rate divided by 12. the amortizations multiplied by 12. current present value is C13 and then we'll leave the future value argument blank so the 102 000 is the monthly payment so the last step we need to multiply that by 12 and then I remove the decimal places to make it cleaner the final calculation we need to make is total Equity required I'm going to leave this cell blank for now as we need to populate other sections of our model before we can make this calculation now let's create a capex section if we recall we were estimating that there would be approximately three hundred and ten thousand dollars in capex needs so I'm just going to start adding some of those Capital items we envision spending money on working capital common area carpet common area paint and some signage we've entered in four different items so far but there may be more we'll make it big enough to host 10 different Capital repairs if if need be hopefully not and I'm going to reformat these are all brown Tech cells because you can label this section however you like of the three hundred and ten thousand we're estimating for Capital we'll say there's a thousand dollars per unit in working capital sixty five thousand and common area carpet thirty five thousand in common area paint and ten thousand in signage I'm going to steal the currency format and make it a brown text cell I'm going to steal the format from the rentable square footage for the remaining entries and highlight that all the way through item 10. and it would be nice to have a total calculation so total capex and we'll sum everything above we label this section as capex and I'll make the total row a dull Gray in that section is complete now that we've finished the capex section we can come back to the total Equity required right now the model assumes because we're running an LTV scenario that the bank would cover 65 percent of the purchase price and Loan proceeds but you'd be on the hook for funding the capex so this calculation for total Equity required is going to be the purchase price minus the loan amount plus the loan Cost Plus the total capex but what if you received a loan quote that was LTC or loan to cost meaning that not only would the bank fund the purchase of the asset but they would also fund some of that Capital expense let's set this model up so it could handle both scenarios right now it can only do a loan to value financing quote so let's go back to the loan amount formula and adjust it to be able to handle LTC so we're going to put an if formula in here if this if B8 what currently says LTV equals LTV then we'll just take 65 percent times 25 million but if it doesn't meaning if it says LTC instead then it's going to take 65 percent times the purchase price Plus the capex and we'll close that bracket if we change it to LTC the loan amount jumps up to 16.45 million let's make this a brown text cell and let's do what's called a drop down so we go to data data validation and we're going to create a list that has two options we're going to type in LTV comma LTC and now we can just swap between LTV and LTC let's move on to operations for Revenue there's three columns there's the revenue line item the monthly per unit and then the annual you know widen the annual column and then there's a couple different things that we will be tracking so average rent on a monthly basis this column a little wider rent premium parking other these would be brown Tech cells as your invited as as you're likely going to need to tweak these labels from the deal details earlier we know the average rent is currently eleven hundred dollars let's say we did a very thorough rent comp analysis and we think there's potential for a 125 rent premium above where the rents currently sit and for our parking assumption we'll say that there's 200 units that we could charge forty dollars for and then we're going to divide that by the the the total amount of units at the prop property and then other will just assume that we estimate about 50 dollars per unit annually in other income items these are going to be brown text cells I'm going to do the first one as a currency and then the rest has numbers the current vacancy at the property is six percent and we think 5.5 percent is a reasonable assumption once our ownership team takes over so I'm going to type in 5.5 percent and then I'll format this cell I'm going to do a custom format foreign and then I want it to save vacancy thank you again making a brown text cell the text reads as five point five zero percent vacancy but when we click in the formula bar it shows up as just being 5.5 that's exactly what we want because then we can just take all of the data from above and multiply it by negative 5.5 vacancy and make this cell red so it pops as being negative and then we can take all of our assumptions multiply them by the number of units in the complex 200 and then again by 12. to get the total annual revenue cash flow turn this cell red again so it stands out and I'm going to steal this format from the capex section we'll do an autosum and then the one small adjustment we need to make is multiply this value by 12. so while the data through rent in the vacancy is the monthly per unit this value here is the annual per unit in other words we're projecting that each one of the 200 units in the complex would generate nearly fifteen thousand dollars of Revenue per year and now let's move on to expenses for expenses we're going to make these assumptions on an annual per unit basis and then our model will annualize that for us I'm going to copy and paste expense labels and then I'm going to take the format painter and take the format from the labels above I'm going to turn the property taxes to Black text we'll eventually input a separate section from property taxes that will flow into this part of the model and then for management fee I'm basically going to do the same formatting trick as we did with vacancy where we'll do a custom format it's going to be 0.00 and management fee and then we can begin making our assumptions so I have some numbers here ready to go we're going to say that admin would be 175 dollars per unit I'm going to link to the 200 units just to reach just to show you again I'm going to link up here and lock the cell now I'm going to go ahead and just drag all of these formulas down management fees a little different we enter in the Assumption to the left the four percent so then we take four percent times the annual per unit that's totaled in this gray row I'm gonna lose the decimal places and then the model will multiply it by 12 for us and then let's finish with our other assumptions until we get to property taxes and then there's some expense line items for other there are some expense lines for other items we're gonna plug in zeros for now and then reserves we're gonna be conservative and go with 300 per unit steal this format once again and then we just need to sum these two columns it's not fully accurate yet because we're still waiting to input our property tax exemptions but let's go ahead and calculate an expense ratio and that's going to be total expenses divided by total revenue make that a percentage and now we can calculate an noi so I'm going to just take the format and delete the the middle column data that that doesn't matter I'm going to take the total revenue minus the total expense black text and then a few rows below will grab the mortgage payment data which is equal to the 1.226 million that we calculate in the capital stack section of the model and then let's also calculate a debt service coverage ratio which is the noi divided by The Debt Service we'll lose some of those decimal places and I actually need to relabel this row is as noi and then finally the cash flow after that service which would be the noi minus the mortgage payment and I'll make the mortgage payment red because it's a cash outflow highlight this row green so it stands out and this is not Capital stack information it is our operations and just because I'm I will extend the formatting out through column d and I don't like this decimal place here either and now I just want to make a few quick adjustments to the formatting I'm everything to be perfect and now let's move on to property taxes I will take the header from capex label it right away this time and then there's a few different things that we need to input in this section so if the current taxes there's there's information online we already pulled it saying the current assessment is 23 million five hundred and sixty seven thousand the taxes payable today were 465 000. I like to calculate those property taxes on a per unit basis we're going to take that and divide it by 200 units just 2 325 per unit in property taxes and then the applicable tax rate is the taxes today divided by the assessment which equals 1.97 the assessment today and the taxes payable today are manual inputs or the taxes per unit and the tax rate would be calculations the final assumption we need to make in this section is what property taxes will reassess to in other words if the property sells above where the current assessment levels are today will the assessor take it all the way to the sale price will they come up a little short a lot short it really depends on the market you're working in just a common rule of thumb I've seen 85 percent to a hundred percent being the most common since we're underwriting conservative at this juncture let's just say they go to 100 percent of purchase price make that a brown Excel because that is your decision so then the future tax assessment would be the sales price times 100 percent your future property tax exposure would be 1.97 times 25 million and then the future tax per unit is the future tax liability divided by 200 units it's 24.66. now we can go to the property taxes line item and our operating assumptions and grab this stabilized taxes future taxes per unit once we've plugged that in the 2466 the future annual tax liability also populates and now our expense section is complete our noi is accurate our dscr is updated to reflect the property tax info and then our cash flow is is just shy 500 000. and then the last piece is the return metrics before we start calculating cap rates cash on cash return yield on cost let's just summarize the total cost basis so we have a price we have capex we have long cost price 25 million capex 310 000 the loan cost 162 500. and then total cost basis hit a quick sum formula 25.47 million now let's calculate a cap rate cap rate will be the noi of 1.7 million divided by the purchase price so this is the pro forma cap rate of 6.87 percent the cash on cash return will take the cash flow after the mortgage payment and divide it by how much Equity you have in the deal and then finally the yield on cost takes the project noi divided by the total cost basis these are very important metrics so we'll highlight them green to stand out the model we just completed is what tactica offers its website visitors for free but if you want to take this analysis just a little farther there's a couple extra things we could do to help assess this investment opportunity these are all pro forma metrics this is what we're projecting to happen once we take over the project but let's slide this down quickly we have the current ownerships 2022 noi if we take that which was approximately 1.3 million and then we divide it by the purchase price we have a historical cap rate so I'm going to say percentage the 2022 cap rate it's a good sign that our pro forma metrics to cap rate cash on cash and yield on cost are all higher than the 2022 cap rate another interesting exercises is looking at the current deal like this we're buying this deal on the owner's financials at a five and a quarter cap if we execute our business plan hit an noi of 1.7 million and sell it back to the market at a 5.25 cap we'd be in line to make a pretty significant profit if we take the 1.7 pro forma noi and divide it by the market cap rate the 5.25 percent assuming we execute our business plan we can in theory sell it back to the market for about 32.7 million therefore we have a profit potential of about 7.7 million another quick tip involves the debt service coverage ratio you notice it's it's quite a ways away from the debt information so what you might consider doing is setting up some conditional formatting where you want to highlight this cell if it's less than one two make it yellow with dark yellow text a lot of lenders won't let debt coverage go below 1.2 or some more aggressive Banks I've heard it go as low as 1.15 or 1.1 but we we want the model to alert you if you're being too aggressive with your debt assumption so if we come up here and we jack up the LTV from 65 to 80 percent now it drops below and it shines yellow to alert you hey you might need to tweak some assumptions because as the model currently sets most lenders aren't going to check off on a 1.14 debt service coverage ratio with that the model's complete the brown text cells are your playground you can adjust your purchase price your financing assumptions your operating assumptions your capex requirements and see how it would affect your investment metrics and that concludes the tutorial we quickly built a one-page multi-family pro forma from scratch it's meant to serve as the initial analysis tool when you're considering an investment you don't quite know if it's going to pencil if it's going to make sense yet hopefully this helps you vet it and it helps you determine if you should dive in deeper and take a take a more thorough look at a potential investment opportunity this is a template that I offer tactica site visitors for free there's a link below you can download it there and it will be emailed to you in short order I really appreciate you taking the time to watch this tutorial if you've been enjoying the tactica content that we've been putting out please give the video a like subscribe and allow us to notify you when we're releasing new prevalent commercial real estate content thank you so much for your time and we'll see you next video foreign [Music]