Create the Perfect Company Bill Format in Excel for Real Estate

Company bill format in excel for Real Estate

[Music] to help you best use models on adventures in CRE let me speak to formatting basics and these are font color conventions that we follow here at adventures in CRE and many in the industry follow the first two I'm going to speak to our Universal the next three are widely used but in some cases there's there some tweaks and so I'll get into that but largely what we're going to be discussing today is how each one of these these cells interact with one another and how the font color helps you identify what that cell does and then track from there through other font colors ultimately what is going on in the model so we start with blue font cells these are our inputs or our assumptions I call them required input because if you see a blue font sell myself as the modeler expects that you as the user will change that cell and so likely you'll be working with one of our templates and so we've put some hypothetical inputs in but the assumption from our standpoint is as the creator of the model is that you the user will change that and in fact you better change it because more than likely it's not applicable to your deal so I'm here in our all-in-one model on the equity cash flow tab this is where partnership level returns are calculated and there's some basic assumptions here right we've got what type of hurdle would be used well your deal structure will be different than this hypothetical deal and perhaps you're using an equity multiple hurdle or maybe you are using an IRR hurdle but that is a input that you must look at and have an answer for and it will be different from the hypothetical deal then we look at say equity contribution well the sponsor in this hypothetical will be contributing 10% well in your deal that likely will be different it's 5% maybe it's 12% maybe it's 20% whatever that may be that's an input a required input and if you don't change it or whether whether you change it or not you ought to have a read for why you entered some value in there again these are required inputs the second formatting convention widely used is black font cells and obviously most of our cells are black fonts and black font cells are either labels you'll see them in headers or describing line items but more importantly they're calculations either calculation modules or outputs themselves and I say calculation modules that's where an input goes in and it goes through some process where through a series of cells rows columns what-have-you some calculations are made and then eventually the out pops some and in this case output right so as an example this equity multiple is one of our outputs we're solving for equity multiple that's one of the things that we as as a user want to understand what is the equity multiple for this deal I'm underwriting and that's an output that is resultant from various calculation modules okay but these calculation modules are driven by our blue font cells so you see this here total LP contribution well if we take this to 5% that now changes which ultimately impacts our equity multiple these are our black font cells in most cases never change a black font cell next we have green font cells and this is another financial modeling convention but it differs some so we generally use green font cells kind of sporadically depends on the model and if it makes sense in the all-in-one given the size I found it necessary and in this case I use the green font cells to link to either calculations inputs or outputs on another worksheet and that just tells you that these items here were calculated through some calculation module in a different place and if you want to track that back you're gonna have to leave this worksheet this tab to understand how this string of cash flows in this case or this value here how they were arrived or how they were entered those are green font cells next we have a formatting convention that is specific to adventures in CRE and that is orange font cells and orange font something we introduced early on 2015 when when we were just starting the website and I was working on early the all-in-one and I kept running into these situations where I had a calculation cell a black font cell that in some instances needed to be changed okay or in Reverse input cells that 99% of the time 95% of the time didn't need to be changed and so I wanted to differentiate between an input a true required input and an optional input and so the orange font convention was born and again it's pretty simple this is an optional input it's a cell that you ought to look at and understand make sure that the value is what you would expect it to be we use it sparingly in many models we don't use it at all but the more complex models where these sorts of situations arise more prevalent so finally we have red font cells and in our templates we don't purposefully add red font cells red font cells are for users and what red font cells do is they they call out to others who might look at the model times where black font cells have been changed let me give an example let's imagine a scenario here we have the sponsor and they're getting 1.5 million roughly in distributions and let's pretend that there's a side payment being made the sponsor related to this deal that's not built into these cash flows and we but we want to model the returns that that or maybe just the the equity multiple for that additional cash flow that the sponsor is getting at the very beginning of the deal okay so we wanted in the equity multiple we also want it in the IRR and so we say in this time in this year zero they're gonna get a payment of five hundred thousand let's pretend and so what I would do here is I come to this formula and I'm gonna change my base formula I'm just simply going to add five hundred thousand well what happens you present the model to someone else on your team someone else pretty soon it's passed around to a few people and unless you specifically call out to let the others who are looking at the model know that this change was made they men may not necessarily know and so what we do is we change this cell to red okay now you'll notice this became an air and that's because we don't have a negative cash flow in time zero so let me make this smaller just keep this clean let's call $200 okay and now we have this additional cash flow in that year zero the red font signals to everyone else who's using the model hey that cell was changed now keep in mind if you ever wanted to change the cell back most times it's a matter of grabbing the cell next to it with the same formula and just copying over and we can revert to what we had before in this case we use our red font cells so that's basic formatting rules and real estate financial modeling specific to models on adventures in CRE if you have any questions please reach out and thanks for your time [Music]