Create Your Taxi Bill Format in Excel for Real Estate Effortlessly

Taxi bill format in excel for Real Estate

[Music] hello and thank you for purchasing re FM's excel for real estate level one bootcamp over the course of this module you will learn basic intermediate and advanced techniques and functions to allow you to perform Excel based analysis specific to real estate applications if you have not done so already please find the excel file that goes along with this video in the downloads of your account alternatively you can go to the individual order in which you purchase this product and you will find a link to the excel file in the order detail once you've downloaded it please do go ahead and launch the file and have it up such that you can follow along and perform the exercises as we move forward let's go ahead and get started by flipping to the table of contents to give an overview of what we will cover arranged from top to bottom are the names of the tabs in this workbook and we will proceed from top to bottom which will be from left to right in the workbook each one of these titles is actually a hyperlink so you can click on any one of them at any time and you'll be taken to that particular tab and click on the table of contents link in cell a is an alpha 1 at any time to go back to the table of contents in the course of this lesson we will impart keyboard shortcuts periodically and I want to point out the next tab which houses keyboard shortcuts both for PC and for Mac and instead of navigating forward to the next tab or one tab to the right by clicking on it I could alternatively do on PC control page down or to go one page back to the left control page up and I'll do ctrl page down to go back to the shortcuts and on Mac its function control down arrow holding all three down at once to go to one sheet to the right and to return she to the left function control up arrow this is a handy tab to keep around in addition we have this available as a PDF downloadable on our website for free under free tools so let's go ahead now to the next tab spreadsheet and formula basics and I'll do ctrl page down on PC before we dive into the exercises we'll give just a brief background about Excel for those who are not familiar with it Excel is a spreadsheet application and there's a graphic interface that essentially sits over a programmable database each cell in each worksheet has a unique grid coordinates this cell in particular is f6 and the column header is shown here and the row header is shown here when we built formulas we are going to start all formulas with the equals sign and Excel has many functions that assist us in our formulas functions can be accessed in a couple of different ways first they can be accessed through the insert function button which is up here next to the formula bar and you can click on this F sub X and the insert function wizard will come up and you can follow the prompts within this dialog box for instance type of brief description of what you want to do I want to take the sum product let's say click go and it will suggest the function that is the best match if I want to insert that function I can leave it highlight it and click OK and it will bring up a wizard for inserting the arguments or the elements of that formula and I can build my formula in this way and then click OK and then Excel will insert the formula for me alternatively I can simply access a batch of functions if I simply type in in the beginning of the function name and Excel will display a range of functions that contain that string of text values so if I type in equals s um you'll see that this is automatically displayed and then I can click on any one of these individual functions and Excel will show me in a tooltip what that function does it will describe that sum product for example returns a sum of the products of corresponding ranges or arrays tags it out of this cell I can simply click escape when I do construct a function let's say I'm going to do the sum function equals s um open parentheses we'll notice that there is a generic version of the formula that will display immediately below the active cell and this is essentially telling me where I am in this function by showing me one of the arguments in bold so in this case since I'm just to the right of the open paren I have number one which is the first number we will add in bold and then it's showing me there's a comma to separate those two arguments and I can have number two and so on and I will eventually close out the function with a close parenthesis so I can complete this by doing equal sum 8 comma 2 and close parentheses and that will give me 10 every formula in Excel can be thought of in words and those words essentially describe the elements that are within the formula and the actions that the formula is carrying out for example some the numbers 8 and 10 or some these three values or some of these 5 values or in a logic situation for instance if the rent is paid late then collect a penalty fee otherwise don't collect anything it's important to know that there's often more than one way to get to the answer that you're seeking when you're in Excel the two most important things are that a your formulas right under all conditions and B that you are comfortable with the formula and you can explain it easily to others because that will build their confidence in your skills in your ability to solve problems in Excel okay let's proceed to the next tab cell formatting and I'm going to do ctrl page down to go one tap to the right and on Mac its function control down arrow on this tab we will talk briefly about formatting conventions that we apply in our tutorial modules and these are conventions that we will observe throughout the entire module and in all of our other products as well the first is that inputs which are the assumptions that the user will make are formatted in bold blue type everything else whether it be a label or a calculated value will be formatted in a regular thickness black font and we will show negative values in red type for instance let's say that we have a basic computation of annual rent and we want to convert the annual rent per square foot of $35 to the whole dollar amount given square footage of 5000 square feet and so you see that both of these are inputted values if you look in the formula bar you see this is the value here there's no formula that sits in the formula bar simply a numeric value same for 35 and if we look at cell C as in Charlie 9 this is an output you see in the formula bar there is a formula there and so the inputs are formatted as bold blue font the output is formatted as regular thickness black font one of the conventions that we use is what is known as custom formatting to bring up the set of formatting options for a cell we will do control one on PC or command one on ma for instance if I go to cell C as in Charlie seven select that and then hold down control and then hit number one will see that this format cells dialog box comes up and the leftmost tab of the dialog box is that for the type of number and so in this case the current setting of this cell as a number is that for currency and we see that there are two decimal places selected and I can change this by going down or going up and if I don't want any I go down to zero and there is a symbol for currency and I can change that symbol depending on the currency involved and then I can also select how the negative numbers will appear in this case it's showing the negative numbers are going to be in the regular font with a negative sign in front of it alternatively I can choose for them to show as red or the regular font with parentheses around it or red font with parentheses around it in this case the number that I'm trying to represent is a per-square-foot number and so it might be more helpful in some cases for this thirty-five dollars to say thirty-five dollars with the PS f suffix after and as we show down here so to change this formatting to this formatting I will select custom so with cell season Charlie seven still selected and the format cells box active for that cell on the number tab I will go down to custom and click that and now I will see there is a whole list of custom format choices and you'll see here there is a scroll bar so I can scroll up and I can scroll down and there are many custom formats that are already being applied or have been applied at one point in this file that travel with this file and so the one that's currently selected is simply the traditional currency one with the dollar sign and then sample numbers and then zero point zero zero well if I wanted to alter this such that it showed and displayed the PSF at the end as a suffix I can go into the box it says type click there hit the spacebar and then anything that I want to appear I will place within quotation marks so I'll do shift quote and then capital P SF and then Shift quote to close out the quotation marks and now I'll see in the sample above it shows me how that value will display if I accept this new custom formatting and so I can simply click OK if I want it to be applied up here and it is now showing in that cell regardless Excel is still just interpreting this as an input and as a simple numeric value this suffix is simply for display purposes and does not impact calculations the other way to apply custom formatting is using the format painter so let's go ahead and try that and we can select the five thousand square feet here and we can clone this formatting and then apply it up here so let's go ahead and select C as in Charlie 17 go to the clipboard on the Home tab of the ribbon click format painter once and you'll see that it's now gray and we'll see that we also now have these dancing ants as they're called going around this cell see 17 and you'll notice that next to the plus sign of my cursor there is this paintbrush icon and now any cell that I actively click and apply this formatting to will show in exactly the same way so I could click here and now it shows 5,000 SF as a suffix and you'll notice that the format painter was no longer dark gray so it's not active anymore if I want to apply this formatting to more than one cell I can double click on this and now I can click wherever I would like and the formatting will be applied there but the paintbrush will remain until I actively hit escape so I can click here and then hit escape next let's do the first method to add a prefix of the word month to this number one so let's select cell C as in Charlie 10 do control 1 on PC or command 1 on Mac and now let's go under the number tab under category click custom and we'll notice that this is the syntax that is currently active I will click to the left of the leftmost pound sign to open quote capital M o nth close quote and then a space and we'll see that that's looking exactly how I want it to look and then I'll click OK as an exercise you can practice doing the format painter taking the formatting from cell B's and beta 27 and applying it here or anywhere else on the tab ok let's go ahead to the arithmetic tab arithmetic in Excel is mostly straightforward let's start first with addition and let's say we have these three inputted values and we want it to add the 3 I'm going to expose a contents of cell G as in golf 5 by hitting f2 I can make the cell contents inactive by hitting escape alternatively to make them active I can double click on the cell or on Mac I do control you as an umbrella with the cell contents active I can see that this formula is simply starting with the equal sign adding 3 cell coordinates D is in Delta 5 he is an echo 5 and F and Frank five and when I hit enter it's going to add one five and three which gets us to nine and so we can repeat that together by doing equals in cell G five through left arrow to D 5 + left arrow to e5 + left arrow to F 5 and hit enter alternatively exposing the contents of cell G is in golf six it can use the sum function which we started to look at earlier and I am simply going to provide the span of cells that are going to be added so in this case these cells are contiguous and there's nothing in between these cells that I don't want to add it so I can define this range by simply selecting the first leftmost value in the range and holding my left mouse button down move over to the right until I've defined the entire array or range of cells that I want added and then I do shift 0 to close out the parentheses the alternative to that is to use the sum function equals sum open paren click on the individual cells comma in between each the subtraction operation is very straightforward I simply type the equal sign cell coordinates and then the dash which is to the right of the number 0 on your keyboard and that serves as the subtraction operator and then whichever cell I want to subtract from the first one and so on and so forth you can combine the sum function with the subtraction operator if it's more efficient for instance while some these 3 values here and from those we will subtract the sum of these three values multiplication uses the asterisk as the operator that's produced by holding down the shift key and typing the or eight if you don't have a ten key on your keyboard and division is carried out by doing a forward slash which is to the immediate left of the right-hand shift key on your keyboard exponents are commonly used in pro forma analyses and they are produced by using the caret operator which is shift 6 and so if we were to do $20 raised to the second power that would be $400 20 times 20 is $400 but they are applied more so in the context of compounding a constant growth rate for instance if we wanted to know what is a quoted rent in a particular year in the future knowing that the escalations the contractual growth rate on that rent is a certain percentage and that's a constant percentage we would apply exponents to simplify that calculation for instance if we wanted to know what is that $20 per square foot your one rent in year 5 we would do the 20 dollar amount times 1 plus the 3% constant growth rate raised to the power of 4 because in year 1 the quoted rent is the real rent and we grow that four times not 5 times on a compounded basis to get to the year 5 rent so that's 22 51 so let's do that again together equals $20 times open paren one plus 3% raised to the power of 4 and then hit enter again 22:51 and so this is how we would typically apply exponents but we don't want to get in the habit of hard keying values into our Milas so what we'd like to do instead is use cell references and write the formula in a generic way such that it can be applied in other circumstances and across other periods so for instance for the year 5 rent we can do the following equals D 20 times open paren 1 plus D 21 D 21 close paren shift 6 open paren D 22 minus 1 close paren and so in this case we take the base value we grow that at that constant rate raised to the power of the future year the future targeted year minus one where the exponent itself sits in parentheses such that the math works correctly