Excel Crash Course | Functions & Formula (Part 6)

formulas and functions in this session we're going to talk about the importance of having good functions and formulas when building financial models these formulas and functions will help make the models more dynamic sophisticated and detailed but at the same time we want to balance complexity with simplicity we're not shooting for the most complicated model in the world but rather the most effective all the formulas and functions we're going to teach you are designed specifically for financial analysis and modeling let's jump into excel now and get going here we are back in our advanced financial analysis tab let's open up all of the group sections and let's scroll down to the bottom here where we've performed our analysis and let's label this time periods I'm going to put monthly data here and then annual data below that and I'm going to introduce an interesting function in Excel which is the today function if you type equals today and then open and close parentheses and press Enter you get today's date whatever the current date is that you're working on this file so that can be a very handy function press control one if it's not displaying as a date and make sure that the number setting is set to date now let's copy that and paste it in the cell above alt H V s and then V for values when you do that you see that it just pastes it as a number and not a date so press control one and make sure you set the number to be a date you can pick the format that you like we're gonna leave it as this default format then let's label it blue because it's a hard code so pick our blue formatting for hard codes now we've got a static version of today's date and a dynamic version of today's date every time you reopen the excel file on a new day this will automatically update to whatever date that is but with this cell here we've preserved the date so we have a comparison between the original starting date and the current date let's delete the today formula and we're gonna type in some monthly formulas here now so let's type a formula that's e o M o n th end of month you pick a start date and then you add to it after a comma the number of months that you want to increase it by so in this case 0 we just want to go until the end of August 2018 so we put you a month refer to that cell comma zero now let's see what happens if we were to add to it so we would take vo month the start date comma 1 close bracket and then let's copy and paste here the formatting so that these are all working properly alt H V s T and we get September 30th 2018 now this formula unfortunately is not very dynamic if we wanted to make it more dynamic what we could do is we could put an assumption here for the number of months that were increasing it by so let's make a formula that builds by one month and fill it right so that we can have a series of months and we can change this formula now I'm pressing f2 to get into the editor instead of manually typing the number one let's actually link to this cell and since we're gonna copy the formula across we need to practice our anchoring and press f4 to make an absolute reference to cell D 54 close bracket now we can fill that write and you see that it's automatically moving across by one month at a time one last thing we can do here is cut this cell ctrl X and then paste it with ctrl V we can start the counting periods at zero and then rebuild this formula here so that we're making things a little more dynamic and we can copy this formula and paste it over so that now all of these are proper formulas let's look at how we would use annual data and a dynamic formula this time we're going to use the date function the date function creates a date in Excel if you tell it the year the month and the day that you want it to be based on so what we can do for the year is actually use the Year function which extracts the year from a date so what we're doing is pulling 2018 out of cell C 54 and using it as the starting point for this date let's anchor it in place with F 4 then we're gonna add to it whatever's contained in the rows above and that's the increment that we're adding to it so in this case we're not adding any years since it's annual data and the fiscal year-end for this company is December 31st we can just hard-code in 12 for December and comma and then 31 for the 31st day of December and press Enter so you can see that it now tells me that the fiscal year-end for this company is December 31st 2018 and if I've done it properly and I fill it right here it automatically adds a year to each of the dates so we've got monthly data here and annual data here all based on formulas and all based on this starting point if I change this to be starting in 2020 you can see that all of the dates automatically update so this is a great way to make your financial model more dynamic and move between different time periods I'm going to introduce another very useful formula for time periods let's type a monthly period and annual period and the function is the year frac so we type equals Year frac which is short for fraction we have a start date which we lock in place with f4 and then we pick an end date so for the monthly data we will pick the end of the first month here not anchor it in place because we wanted to move and close let's press ctrl 1 to quickly format this cell we want to have just 3 decimal places let's say and press ok then let's fill this right so what you can see is that you're building over time towards a full year at the end of the year this this number this fraction of a year will be 1 now let's copy these and fill them down so we can see what's happening on an annual basis so there's a stub period year between the current date in August and the end of the year it represents 0.38 9 of a year and then everything from there on out adds an entire year so one point three eight nine two point three eight nine etc so this can be very helpful when discounting cash flows or calculating stub periods where you may only receive a portion of a company's cash flow or costs or revenues etc and you need to multiply it by the fraction of a year that you're counting in your model let's create another little section in our model let's call it cost analysis let's bold it and if we scroll up to the income statement let's analyze just the cost line items and if I hold down control on the keyboard and click on just the cost line items that excludes all the subtotals essentially and I copy them this is a handy function you can copy excluding certain lines and then paste them down below with just control V and it pastes them in that layout now let's link each of these up to the top so we've got cost of goods sold we've got SGA depreciation interest expense and taxes so everything's linked up and then beneath that we want to have the total this is the total expenses line we can quickly sum with alt equals if you just press alt equals that's the auto sum function let's do that one more time alt equals very fast way to sum now if we select all these and hold shift press the right arrow and then ctrl R we can fill all of this right let's bold the total and out of order alt H bp4 top so we add to the top of order there now let's add below this average and weighted average so average we can simply calculate using the average function and select all of the costs close bracket and fill it right there we have the average now we can calculate a weighted average and let's say we have some criteria for a risk adjusting each of these costs suppose that 10 is a neutral position on the cost meaning neutral as to whether it would be higher or lower in the future than the forecast if we put 12 we think it's risky and that it might increase over time if we put something below 10 we we view it as low-risk it might actually decrease over time let's say so we can just fill these out I'm actually doing this randomly but you get the idea the point is to show you how to risk adjust using a weighted average and the weighted average formula in Excel is some product so type some product Open bracket and then in array1 select the waiting's and lock them in place with f4 then for array to select the costs that we want to apply them to and do not lock them because we're going to fill this formula right and then the last thing we do is divide by the sum of the waiting's so just make sure your cursor is in the right spot here select the sum of the waiting's anchored with f4 and close bracket so let's look at the logic of this formula it's taking the sum product function which multiplies each of the waiting's by the corresponding costs so it multiplies 10 by 67 and then 12 by 16 etc working its way down then we divide by the total weightings to get the weighted average so let's press ENTER and we're just gonna copy the formatting here we'll show you another way to copy and paste formatting you can just use the format painter sometimes that's faster than using the shortcut keys and then let's see how this works so we're moving across over time so what you have is first here just the simple average not weighted in any way and below it a risk-adjusted average where you can see that it actually is working out to be a little bit higher than the simple average and the reason is because we've said that some of these costs are actually a bit risky some of them are lower risk and some are higher risk but together the weighted average shows that there is a risk of cost going over let's add one more line item here let's add median it can also be helpful to see the median so it's just a very simple function median just like average select the numbers and fill that right so when you're doing statistics or showing some analysis you might want to include total weighted average and median last thing is let's just select these and make them blue to continue following best practices and we also had an assumption up here that we should format as blue as well there we go let's look at some examples of using if statements in our model suppose we want to insert a row here press alt H I for insert are for row there's the shortcut and we want to know if the time period we're in is a stub of period or a full year for these annual periods we can create an if statement that says if and this is where we set up a logical test the logical test checks to see if this number here is less than one meaning less than a year and if that's true if it is less than a year comma we indicate what we want as the result which is the word stuff otherwise we want to say whole year close the quote and then close the bracket and let's align this to the right with alt H AR that's the shortcut and fill the formula right so you can see that it automatically knows this is a stub period because it's less than one and that these are automatically full years great that was one example of how to use it now let's look at another example let's type return total expenses and we're going to create a function that's going to return total expenses if they are less than 150,000 or if they are greater than or equal to 150,000 so let's type 150,000 here and then we can set up a formula that says if total expenses are less than 150,000 anchored with f4 then we want to do display that number so we refer back to it otherwise we want to return zero close bracket so we get the number here because it's less than 150,000 let's fill it right then it flips to zero in this period which makes total sense now let's build the opposite formula let's say if the number here is greater than or equal to anchor this cell with F for 150,000 then return that number otherwise return 0 in closed bracket so we get 0 there we fill it right and then it flips on its own this this example may not be the most useful thing but it shows you how you can think about using if statements in interesting ways and at the bottom here we've just got the total so we've got our total expenses control B to bold it alt age VP to insert a line above it and then let's select all of these press ctrl 1 and we just want to format the number so that it uses a comma separator one decimal place and brackets for negative numbers let's also select these and make them blue since they are assumptions alt H FC and select the recent blue color there so that's another example of if statements alright the last example that I'm going to show you how to do is an error check using an if statement so we've done some calculations here we're using formulas and there's a lot of moving parts and we want to make sure at the end of our analysis that is all functioning properly so we can build in an error check using an if statement we can say if open bracket the number that's calculated here is equal to the total cost that are calculated up here a different way then everything is OK close quote and put a comma otherwise there's an error close quote and close bracket press ENTER press alt H AR to align it right so this is great because we've calculated total expenses two different ways they're equal to each other so everything must be ok let's fill that to the right then let's see what would happen if for some reason has changed even by one dollar we would get an error message because these two numbers here are no longer the same let's undo that change with control Z but you can see how building in error checks is a powerful way to make sure that your financial model is error-free in our next little tutorial let's look at how we can round numbers if we go up to this section here where we have average weighted average and median and holding control shift the down arrow and right arrow I can select everything there and then to increase the number of decimal points let's press alt H 0 alt H 0 again and one last time so we can see quite a few decimal places there then down below let's do a little rounding exercise let's type equals round let's select the weighted average and let's round it to four digits when I do that I get the exact same number as we have above I'm pressing f2 to edit the formula now let's try three digits you can see that it's rounded off now two digits one and zero which rounds it to the nearest whole number so you can see how that might be useful in performing your analysis and you can actually pull out an assumption here and let's make that blue so instead of actually just typing in 0 we could refer to this cell here anchor it with that 4 and fill that formula right now let's go here and change this to three decimal places seven one whatever number you want you can just control it right here and let's Center this align Center ace is AC alt H AC now let's look at another kind of rounding let's put in here decimals and then let's fill that down and this time we're going to round multiples so we could put a multiple here we'll fill that assumption down as well let's say a hundred and this time we'll use the function M around M stands for multiple and we take the number that we want around and then we refer to a multiple which we lock in place with f4 and close bracket so look at what happens it rounds it off to the nearest hundred I fill that right with control R we could do it to the nearest thousand the nearest 10,000 the nearest whole number any multiple that you want that you're going to round it off to so that can be also very useful in your financial modeling especially for creating outputs and you want those outputs to have round two numbers let's just tidy this up by selecting all of these pressing alt H to 0 and alt H 9 to reduce the number of decimal places let's set this back to three decimal places and leave this as a multiple of ten so it just looks a bit cleaner there the last thing we will show you how to do is an absolute function where you can take an absolute value so let's imagine that we actually had the total expenses listed as a negative number in our model and we wanted to show them in absolute we would use the ABS formula and select that number and it makes it a positive or absolute so absolute can also be useful when creating outputs or depending on what you want to show in your analysis I'm just gonna delete absolute but now you know how to do it the next two functions we're gonna look at our min and Max these take the minimum and maximum numbers in a series so let's take the minimum of all of the expenses that we have listed in our cost analysis the minimum expense is 1,000 or 1 million since since our units here are in thousands and that's the interest expense let's fill it right and we see that it's the same in every period as expected let's calculate the max of the same expenses closed bracket the max is cost of goods sold sixty seven point five million and fill that right so that's how we can quickly pick out the biggest or smallest value in a series or an array but let's look at two more functions that can actually be even more powerful which are small and large sometimes you don't always want the absolute minimum or maximum you want the second smallest or third smallest or second biggest so we're gonna type some assumptions here let's actually copy this because it's got good formatting and paste it down here we'll start with one which would make it the absolute smallest or the absolute largest just like min and Max let's type small select the array just like we did with min and Max comma refer to this cell that tells us if we want the first second third fourth etc smallest anchor it with f4 and close bracket let's take the formatting there and let's apply it across all of this so we got a thousand let's try two though what's the second smallest expense depreciation third smallest that's sgna fourth smallest that's taxes fifth smallest which is actually the largest is cost of goods sold let's put that back to one now let's do the same thing with large we're going to go up to the array control shift is the fast way to select those comma and then refer to this cell which we anchor with f4 and close bracket now let's copy all of these to the right by pressing ctrl R so I can take the second largest which is taxes in this case or I can take the third largest and so on so let's put this to the second smallest and the third largest and then up here we have the absolute min and Max as well so all four of these can be used very effectively in financial modeling and analysis let's look at how to handle some error messages that commonly come up in models one is when we divide by zero so let's make a row here called div zero and we can have an assumption I'll actually copy this and paste it down and for now I'll set it to be equal to one and we're gonna take some number here let's say our total expenses and divide them by this assumption let's fill that right let's also copy and then paste the formatting alt H V s and T so everything's fine but what if I change this to 0 I get div 0 errors on all these cells how could we avoid this let's go back to one here for a minute now what we can do in the row below this is use if error as a function to say that if this cell contains an error then we want to display an A so let's change this to 0 and you can see that it displays any let's align that to the right but we still have an error here so what we can actually do instead then is apply it directly in this cell if you press f2 and you wrap this formula with if error open bracket and then close and then comma what you want as the error message and press Enter press alt hkr2 align it right you can see that this actually now solves the error message I can put it back to 1 and we got the number if it goes divided by 0 we get n/a there but let's look at another type of error which could happen if we have say some expenses that we are then adding another number to and that number is contained here since there's no actual number there there's just an n/a we get a value error now let's solve this with an if error function so we're going to say if error and let's refer to the same calculation we had before dot number divided by this assumption here so if it's an error then we actually want to display zero instead of any and fill that right so when we change this to 1 in both examples the div 0 example and the value example we get the number as we'd expect if we change this to 0 instead of error messages one version gives us na the other version gives us 0 so this would enable us to now have that function we tried to build before where we're adding some number to it so instead of having this give you a value error when there's a problem it actually just adds zero to it unless you continue on building your model let's look at how to use the find and replace function press ctrl F to bring it up now let's say we want to search for USD we know USD is located in cell a1 now we want to replace it with C ad let's click replace all one instance and you can see he was automatically replaced let's close and press ctrl Z to undo so that would be a handy way to find all instances of USD and change them to another currency now let's try another example this time let's make a selection let's select these cells here and press ctrl F and we want to change the n/a so we click replace to be not applicable and replace all so that was an easy way of replacing all the text in the selection let's close that and then let's undo it with ctrl Z but now let's look at an even more interesting example where we can actually change a formula so with small and large here you'll notice that we're referring to cell c87 let's take an assumption and place it in B 87 and let's make it different so with all of these cells here we can press ctrl F and we can actually use Find and Replace to edit a formula so all references to C 87 type it exactly as it appears in the formula bar with dollar signs for anchoring let's just copy that and paste it here we want to actually replace it with a reference to be 87 let's click replace all ok and close so now all of these formulas have been adjusted and whatever we change the assumption to here is going to impact the formula whatever we change this to is now not impacting anything at all so that's how you can use Find and Replace to edit formulas a very powerful tool in financial modeling

Channel: Corporate Finance Institute

Views: 32,114

Rating: 4.9636364 out of 5

Keywords: excel crash course for finance professionals, excel crash course, free excel crash course, how to use excel formulas, excel formulas and functions in finance, excel shortcuts, excel training, excel tutorial, excel for financial modeling, corporate finance, finance training, finance education, excel formulas and functions

Id: q5uTUJkC5Ro

Length: 30min 26sec (1826 seconds)

Published: Sun Mar 31 2019