Excel Crash Course for Finance Professionals

hi and welcome to the corporate finance institute this is our excel crash course designed for finance professionals at cfi our mission is to help you advance your career and with that in mind we've designed an excel crash course that takes you from start to finish through everything you need to know about excel to perform world-class financial analysis let's review excel's layout we're going to quickly talk about the difference between a workbook and a worksheet we'll look at where our cell names are contained and later in the course we'll learn how to change cell names let's understand the formula bar and how to use it as well as how it can help us find formulas and then let's explore the ribbon and look at all the various functions contained within the ribbon first off when you open a new excel file you're creating what's called a workbook a workbook is the entire file in the bottom left corner you have tabs that represent worksheets you can click the plus arrow to add a new worksheet you can also change the name of the worksheet by double clicking on it the cell name is contained in the top left corner as highlighted here in this case it's cell a1 later in the course we'll show you how to change the names of cells which can be very useful for certain types of financial modeling to the right of the cell name we have the formula bar in the formula bar we can type in formulas or we can use the fx function to find a list of formulas or suggestions or even search for formulas in this course we'll review all the key formulas we need but you may want to search for your own as well finally above that you've got the ribbon the ribbon contains an assortment of useful functions that we're going to explore together we're going to walk through all sections including home insert page layout formulas data review and view this is excel 2016. if you're working in 2013 or 2010 you'll notice almost no difference between 2016 maybe some minor formatting changes but 2016 will serve as our guide for this course let's get going please open up a new excel file and by doing so you'll create a new workbook you'll see at the top here this is labeled book one if you have more than one file open they'll be labeled two three four etc in the bottom left corner you'll see sheet one if you're in cell 2016 the default is to have one worksheet per workbook in older versions it's three let's quickly add another worksheet here and we can change the name of these simply double click on it and type in the new name here you'll see that by moving the cursor with the arrow buttons i'm in cell a1 and the name of it is displayed right here i can actually change this name but i won't now we'll deal with that later here's our formula bar i can type a formula three different ways the first is right in the cell i can type equals sum to start a sum function or i can click into the formula bar itself and type equals sum to also start the function or thirdly i can click on the fx insert function here in the search area i can type sum and i see that sum comes up which i can select and then select the range that i want so these are the three ways to create a formula flipping back to our excel workbook let's work together here to set up a basic financial analysis in the top left corner i'm going to put the units for example let's start with usd in thousands it's helpful to have this in the top corner so that anyone who looks at the model immediately knows what type of units we're dealing with here i'm going to give a couple columns of space because we're going to be putting financial metric names across some of these rows and so i'm going to start in column d here and type the first year of historical data that we're going to input let's say that we're going to start with 2014. to the right of that i'm going to type i can use the plus sign to quickly start a formula instead of typing equals which is a little bit harder to reach if you're using the number keypad on the right you can just plus the plus press the plus sign and select 2014 and then press plus one so now here we've got and i'm pressing f2 here to see the formula we've got the previous year plus one i'm going to hold down shift and press the right arrow and then ctrl r to fill that right so just to do that one more time here quickly type in 2014 to the right of it press plus left arrow plus 1 enter to get 2015 hold the shift key and press the right arrow to select the cells to the right and press ctrl r this will fill right so now what we've got here is two years of historical data and five years of forecast data what i want to do is label this so that it's easy to understand what's going on here so i'm going to select holding down the shift key in the right arrow i'm going to select 2014 and 2015 which are the historical periods i press ctrl 1 to access the formatting area and under the number tab here i'm going to select a custom type of number what i want to do here is type i'm going to put a 0 to indicate that it's a regular number but at the end of the number i want to put an a a letter a for actual this is common convention in finance then click ok so as you'll see here we've got 2014 and 2015 formatted with an a at the end so i know that those are historical actual numbers but they're still treated as numbers in excel then i'm going to select holding down control shift and right arrow the forecast period to the right by pressing ctrl 1 i can access format cells go to custom once again and this time i'm going to put in quotes a capital e at the end to indicate that it's an estimate and then i'm going to press ok so now what i've got here is two years of actual historical five years of forecasted estimate and it's very easy to see that these are historical and these are simply estimates so we've got our numbers in place what i next want to do here is i want to frame this piece of analysis i really like to frame things up so they're easy to see holding down the shift key in the right arrow i can select this entire area what i want to do is put a bold dark background behind it and change the lettering to be white so to do that i'm going to using the practicing my shortcuts here i'm going to press alt h for the home screen and h for the background color using the arrow keys i can move across here and select something that i think looks good i'm going to go with this very dark blue here it's almost bordering on black and then i'm going to press enter now obviously i can't see the font very well so the next thing i'm going to do is press alt h and fc for font color and i want to make it white i think this really pops the header out and in fact what i might do next is selecting the years press ctrl b to bolt them taking this one step further i'm going to press ctrl 1 again so that i get into the type of number that it is select font and i'm going to make it size 14. i think this really makes the numbers pop out at the top and i can quickly see wherever i am in this sheet what year we're in now you'll notice that these cells are starting to look a little bit crowded so let's hold down the shift key and the right arrow and then press control spacebar control space bar helps us automatically select the columns because what i want to do here is actually adjust the column width and in order to do that i'm going to i'm going to press alt and then h for home and o for format and w for column width so the default is set here but i'm going to increase this to 12 and press enter i think now that gives us a nice column width where we can easily fit several numbers in this cell the next thing that i'm going to do is that you'll notice there's a dotted line that's appeared here that means if i were to print this page on the current settings i would actually miss two of these columns so what we should do here is is using our shortcuts again press alt p for page layout and o for orientation so i'm actually going to change this to be a landscape i think landscape works better for financial models typically when there's several years that are going out to the right so now we have it all fitting in one page we've got several spaces and we've got some quite bold headings here but you'll notice now if i start to scroll down i quickly can't tell what year it is that i'm referencing if i'm in a cell down here this can become quite challenging when a model gets big as you always want to know what year you're referencing so what i recommend doing is putting your cursor in at the left most cell in row two and what we're going to do here is freeze panes in order to freeze the paint i'm going to press alt i'm going to press w for view and then f for freeze panes and f one more time to freeze pains so now if i scroll down you'll notice that i can always see what year is across the top i think this is really important for setting up a good piece of financial analysis it'll make your life a lot easier the next thing that i'm going to do is create a category heading here so i'm going to create an income statement [Music] and press enter i'm going to bold it by pressing ctrl b and let's just quickly enter a very basic income statement here we're going to input revenue cost of goods sold which will give us gross profit [Music] and then we're going to deduct our sg a expenses which will allow us to arrive at ebitda we'll then deduct depreciation and interest which will give us earnings before tax we'll then calculate our taxes and finally our net income so this is going to be our simplified income statement here over on the left now that we have a basic income statement set up for financial analysis let's put some numbers in place pressing control and page down allows you to move between tabs so control page down to move over to research control page up to move back to financial analysis so if you've downloaded the excel file that i included at the beginning of this course please press control page down where you'll see some data here that you can copy over using control and shift holding them both down then press the right arrow and the down arrow to quickly select the entire set press control c to copy and then press ctrl page up to move back over here this is where i'm going to teach you how to paste special there are many different ways to paste in excel and if i'm just clicking on it quickly here you can see that paste special is an option now there is a shortcut but i just use the mouse to bring it up here there are all sorts of different ways to paste here you can paste as formulas values you can copy and paste just the formatting not the numbers you can also multiply or divide by a certain number that you've copied so for example you can copy the number 1000 and then click multiply and multiply everything else by a thousand or divide by a thousand you can also transpose which takes a vertical list of numbers and paste them horizontally or takes a horizontal list and paste it vertically so in this case we're gonna paste as values because i don't want to copy over any formulas or formatting or anything else that might be associated with the numbers we just copied so now i've got them pasted here the first thing that i'm going to do here is uh basic formatting i'm going to press ctrl 1 and i'm going to change the type of number here from general to a number with a comma and one decimal place i'm also going to select that the negative number will be in parentheses this is the most common in financial analysis then press ok so now we've got some more nicely formatted numbers here but there's more we can do on the formatting side and we're gonna we're gonna make this a little more dynamic too so right now all of these are hard-coded numbers but i know that things like gross profit ebitda ebit and net income can all be sum functions or difference functions so what i'm going to do is delete the gross profit to lead to ebitda the earnings before tax and the net income so what i've simulated here is if you were to copy over some financial data that was sent to you historical financial data this is what you would do you would paste all the numbers in delete the subtotals so we can make things a little more dynamic here so what i'm going to do for gross profit is set it equal to revenue minus cost of goods sold then what i do is holding down the shift key and pressing the right arrow i can select the cell to the right and press ctrl r to fill it right next i want to calculate ebitda ebitda is going to be my gross profit less my sg a and once again i'm going to select the cell to the right and press ctrl r if i press f2 i can quickly check the formula next is earnings before tax which i'm going to set equal to ebitda less the sum [Music] of these two items here depreciation and interest close bracket and press enter i can then holding shift and the right arrow select the cell to the right and press ctrl r and finally i can calculate my net income being the earnings before taxes less the taxes and i will also fill that right so now what i've got is a little bit more of a dynamic income statement where i have these subtotals here that will all automatically update if i make changes to any of the inputs but if you look at this it's not very clear what's an input and what's a form formula so this is where we're going to start to use some formatting to make it much easier to understand we're going to make all hard-coded numbers in blue and leave formulas in black so what i'm going to do is select these four here that are all hard codes i'm then going to go and press alt h and fc to get into font color now i'm actually going to press m for more colors because there is a specific blue that i like to use which is this right here and it's actually from an older version of excel it's very common to use this particular shade of blue and financial analysis as it's very easy to identify when i press ok here this blue really stands out it's very easy to see relative to the black so i'm going to do that now what i'm going to do is i'm going to show you how to copy and paste special again but this time paste just the formatting so i'm going to select just one cell here that has the proper formatting for hard codes by pressing ctrl c then what i'm going to do is scroll down to the cells that i want to apply the formatting to and select them then i'm going to press the shortcuts alt h for home v for paste s for special and then in this area here i want to select formats and i can see that the t is underlined so i'm going to select t and press enter as you'll see what i've done here is i've simply copied down the formatting and not the numbers now because i haven't pressed escape or enter yet again you can see that this number here still has this revolving green border around it as long as that's happening i can now quickly copy the last action that i did and speed up formatting a lot here so i want to apply to depreciation and interest the same formatting if i select those cells and press f4 i can repeat the action if i select taxes as well and pressed just f4 i can repeat the action again now i'll hit escape so as you'll see here we can go back and using f2 we can quickly audit and review that all these black cells are formulas all the blue ones are hard codes and we've got a more dynamic income statement in place now that we have our historical data in place let's do a very basic forecast so we can see how to set this up properly with formatting and formulas so i'm going to start a new subheading here for assumptions and i'm going to bold it and i'm going to want to understand what revenue growth is going to be so i'm going to put revenue growth i want to understand what cost of goods sold is as a percent of revenue i'll want to put in sg a as a dollar forecast i want to put in depreciation as a percent of sales in this case or percent of revenue as a very very simplistic way of forecasting depreciation in our financial modeling courses we will go into much more detail of how to forecast depreciation properly interest in this case will just be in dollars i want to know the tax rate so with these assumptions here i'll be able to forecast the income statement going forward the first thing i'm going to do here now is calculate the historical revenue growth rate so i'm going to set it equal to the 2015 revenue divided by the 2014 revenue minus one and i'm going to press alt h p to quickly make that a percent and if i want to add a decimal point to it i can press alt h 0 alt h 0 adds decimal places alt h9 removes decimal places i'm going to leave it at one decimal place i can only calculate it for 2015 as in 2014 i obviously don't have a prior period to compare it to for cost of goods sold i'm going to take that divided by revenue and press alt hp again and alt h0 to calculate cost of goods sold as the percent of revenue holding shift and pressing right arrow i can fill it right with control r next i'm going to reference sg a and i'm just going to reference it as a dollar value so i'm just going to set it equal to that cell and then press ctrl r to fill it right depreciation i want to calculate as a percentage of revenue so i take depreciation and divide it by revenue i then press alt hp to make it a percentage and alt age 0 to add a decimal place interest will be a direct reference to the dollar values and tax rate will be taxes divided by earnings before tax i press alt hp and alt h 0 to make it a percentage i then select these cells using control sorry using the shift and down arrow and the right arrow and pressing ctrl r so i now have in place all of the historical assumptions that will influence what i want to use as the forecast assumptions i'm going to make this very straightforward and just make the forecast more or less equal to the historical numbers so we can start with the revenue growth and make it ten percent cost of goods sold i'm going to keep constant at 45 percent sg a i will step up a little bit to a rounder number of 20 million depreciation i will round up to 5 interest i'll keep constant at one million and taxes i'll keep constant at thirty percent so what i'm going to do now is format this number using alt hp for percentage and alt h is zero to add a decimal place and then press alt fc pardon me alt h fc to grab the blue font color which i can copy and paste special using alt h v s for special t for formats i can go down to this number here and press f4 to repeat and tax rate f4 to repeat again on these next two cells i'm also going to copy and then paste special alt h v s t and i'm going to repeat that using f4 down here and i actually am going to also make it a blue font color so that's alt h f c alt h f c so i've now got my forecast assumptions in place and since i'm just going to straight line this forecast we can select these holding shift in the down arrow still holding shift press the right arrow to get to 2020 and press ctrl r so i now have a straight line forecast here with no changes to the assumptions we're just focusing on the formatting and structure here now that we have our forecast assumptions in place let's move up to the top of the income statement and start filling out the forecast we're going to set the first year of forecast equal to the historical year times one plus and if i press control and the down arrow i quickly jump to revenue growth where i can close bracket the formatting here is not correct so what i'm going to do is copy this cell here which is the formatting i want to borrow and apply to the entire forecast area so i select the area and i press alt h v s t enter the entire forecast area is all formulas so it's all going to be black next i'm going to forecast cost of goods sold as being revenue times and i press control down and then down arrow one more time percent of revenue gross profit i can copy over using control r sg a can be a straight link down pressing ctrl and the down arrow to then select sg a ebitda can be copied over with control r depreciation will be a percent of revenue so revenue multiplied by the appropriate percentage and you'll notice here that i'm using control and the up and down arrows to quickly move around the sheet so interest equal to control down arrow control down arrow again brings me to the bottom of the assumptions and then up arrow once earnings before tax i select and fill right with control r taxes is equal to earnings before tax multiplied by and then i'm going to press control down once control down twice to jump all the way to the bottom and net income i can copy across with ctrl r so i've now got the entire income statement forecast here for in order to fill in the rest of the table press ctrl and shift as well as the down arrow to select the entire year let go of control and just holding shift and the right arrow select the area to the right now press ctrl r to fill it right as you can see this allows us to build a forecast very quickly in a very organized and logical way so we've now got all of our drivers in place down here all of the formulas copied across here we can use f2 to quickly audit a few of these cells and make sure they're linked up correctly so i'm just sort of picking some random spots here and making sure that yes these formulas are what they're supposed to be this is really just for sanity and confidence building more than anything else the next thing i'm going to do is a little bit of formatting so i think it's nice to hold the revenue line at the top and hold the net income line at the bottom so i'm using ctrl shift and right arrow to select these areas and then i'm using ctrl b to bold the next thing i'm going to do is add in a few borders i think putting a border above a subtotal is a nice way to group excel works so let's select a gross profit here and then press alt h b for border p for top so as you can see i've got a line here at the gross profit subtotal let's do the same with ebitda alt h bp earnings before tax alt h bp and with net income let's do a slightly different one let's do alt h b and u which does a top and bottom line the next thing we're going to do here is remove the grid lines so we can really see the nice formatting in order to do that let's use the shortcuts alt w vg and as you can see we've now got a clean sheet here with no grid lines basic formulas and functions now that we have the layout and structuring under our belt let's take a look at formulas and functions that will improve the sophistication of our model they're in very important part of financial model building they make models more dynamic and sophisticated and they add detail and precision the key is to balance not getting overly complicated with providing a high level of accuracy and detail overall we want to have a simple model but we want to use intelligent functions and formulas to get us a very detailed solution these functions and formulas that we're going to discuss in this section are specifically designed for financial analysis and financial modeling by learning all of the formulas that we teach you here you'll be able to build world-class financial models and stand out from the crowd now let's get going all right the first thing we're going to cover on the functions and formulas part of this course is dates and time periods let's scroll down i'm in the basic financial analysis tab let's scroll down to new area here and let's assume we're going to perform some analysis that requires specific dates so i'm just going to create a header here that is time periods i'm going to bold that and then i'm going to create a line here for monthly data i'm going to create one here for annual beta and i'm going to show you a few things here the first thing is we're going to learn how to make today's date and so you can set equal to today and then just open and close parentheses and you get today's date if you press ctrl 1 you're going to want to go into the number format and make sure that it's set to be a date so this can be useful if you want to have today's date in the excel file as a starting period it's important to note though that this will automatically update each day when you open the file so it will change over time if however you wanted to have it be static so that every time you open the file it has the same start date i'm going to press ctrl 1 here to change this to a date then i would recommend hard coding it and using alt hfc changing the color to be blue so we know that it's a hard code so now i've got two versions of today's date i've got a dynamic version and a static version so let's look at some monthly data now if i want to imagine that these numbers were monthly instead of annual what i can do here is i can use a formulas for the end of the month so it's e o m o n t h and i'm gonna select a start date and in this case i'm not gonna add a time period to the end an increment i'm just gonna take the end of the month which happens to be today and then so let's just let's just put in a different start date here to make it more interesting let's say it was august 15 2016. so it automatically takes that to the end of the month now what we can do is across the top here i'm going to add increments that we want to increase the monthly numbers by i'm going to fill that right with control r so across the top here you can see this is sort of month zero and then you have month one two three four five six so what we can do now is we can make a similar formula that's dynamic that moves across so i'm gonna set it to be equal to the end of month function i'm going to select the start date as our hard coded number in d23 i'm going to lock it using f4 so it's an absolute reference comma and then i'm going to reference the cell above which is the number of months out that i want to move it and i'm going to close bracket so now i get it moving from august 31st to september 30th i'm going to select the area to the right and press ctrl r and then i see that it moves september october november december etc and it's always picking you can see february 28th is always automatically updating for the last day of the month so this can be very powerful if you have a monthly forecast and you want to be accurate and detailed about what time period you're referencing so now i'm going to show you how to do annual data where you're going to pick the last day of the year as the time period instead of the last day of the month and we can set it up to make this dynamic which can be very useful so the first thing that i'm going to do actually is i'm just going to move the absolute reference date of today over one and the reason i'm going to do that is i'm going to put a 0 here and change this formula to be 0 plus 1 and move it across so now what we can do is copy cell e24 and paste it in d24 so we've now got a zero month as our start which is the end of the month that our current date that we've put in here is so now what we can do is we can do the annual version of this let's set this equal to date the date function just returns a date in excel and within the date function we're going to also use the year function your function will take a date and turn it into whatever year that date is so in this case we just want to take the 2016 portion of that date and then we're gonna we're gonna add to it a time period so we're going to add to it either in this case zero years or one year two year three year etc and then we're gonna set the format to be the month is always gonna be december and the date is always going to be the 31st and the last thing that we're going to do in this formula going back into it with f2 is we're going to lock the reference to c23 so that when we copy this formula over it copies over properly so we've got the december 31st uh 2016 being being the date here and then i'm going to select this area and press ctrl r and you can see now that it it goes 2016 17 18 19 etc so we've got these dynamic end-of-year dates based on a start date and if i were to change this to 2020 as you can see all these dates automatically update the monthly and the annual and you can build this into your header dates at the top or you may use it for a separate piece of analysis down like i've done like i've shown you down here but in any event um you're going to want to have it dynamic so that you can always input a start date and you can add increments to it and the model will always be up to date the last function i'm going to show you how to use is called year frac which represents a fraction of the year and this can be very important for discounting and dcf analysis when you need to know what time period you're discounting whether it's half of a year at the beginning three quarters of a year um et cetera et cetera so what we can do actually is let's let's make another section here that says monthly period and then annual period and what i'm going to do is for the monthly i'm going to i'm going to type equals year frac open bracket the start date is always gonna be this one hard code to start date so i'm gonna press f4 and the end date is gonna be whatever the current month that i'm looking at is and i'm going to close bracket i can take that and fill it right so as you can see we're building up over time each month is representing a small fraction of a year so this can be very powerful for discounting and then with the annual i'm going to do the same equals your frac open bracket absolute reference this cell by pressing f4 and then select the end of year end date and fill it right so as you'll see all these time periods because we're starting on august 15 2016 you're moving along an entire year in each case but you've always got this fraction this sort of stub year if you will of 0.37 so this can be useful for discounting let's just clean this up a bit if you press ctrl shift down arrow and then right arrow you can then press alt h 9 alt h 9 a few times to just reduce the number of decimal places i think i think two decimal places is sufficient in this case to clean it up let's take a look at some average and some product functions these can be very useful for performing analysis let's assume that we want to analyze the cost of this business so what i'm going to do is down below here let's create a little header called cost analysis i'm going to bold that and i'm going to scroll up and copy i want cost of goods sold i'm going to hold down uh control here sgna depreciation interest and taxes i'm going to copy those and paste them here so what i want to do is i'm going to link each of these up so for cogs i'm going to scroll up select that sg a depreciation interest and finally taxes [Music] and then below that i'm going to write total [Music] so the first thing that we can do is we can practice filling right press ctrl shift down to select the entire area let go of control and just holding shift use the right arrows and press ctrl r now the first thing i want to show you how to do is a quick auto sum autosum is a very fast way to to sum a group of cells and it's just alt equal sign so i'm going to do that one more time on your keyboard press alt and then equals and enter and you'll automatically sum and then you can fill that right so i'm pressing f2 here to look at these formulas a very fast way to auto-sum so the next thing we can do is let's compare the total to an average let's type average here and then we're going to use the function equals average open bracket and select the entire list of expenses and we're going to fill that right so as you can see we just quickly took a simple average of the group the next thing that we can do is we can we can look at exploring the idea of a weighted average or some product now this is just sort of dummy data in this example but you might wanna you might wanna wait things based on some sort of criteria now in this case you could take for example uh you could type some weightings here and let's just pick some random numbers and i'm going to put zero waiting on the taxes and then what i could do is take a weighted average and to do that i'm going to use the sum product function some product multiplies out two arrays and it and it moves it sort of moves across the top so it multiplies 10 by 67 million 10 by 16 etc etc all the way down so it'll apply zero weighting to this one here and then we divide by the total to get the weighted average so the first thing we're going to do is is is take this array and then we're going to weight it against this array which i'm going to lock in place with f4 because we're always going to be want to referencing those and then i'm going to close brackets so first i'll just take the sum product of all those and i won't i won't i won't divide by the total and let's do some formatting ctrl c alt h v s t so as you can see you get a much bigger number because we multiply these these numbers out and if i if i go here below the weightings and i use my alt equals shortcut to quickly sum that what i'm going to have to do here is divide this by and i'm pressing f2 to to move around with the cursor divided by 30. and then i can see in this case when i created a weighted average uh it was 29 million instead of 21 million and now i mean it's kind of a silly number because we're just waiting expenses randomly but the point here is to see how you would do a weighted average based on some criteria and if i press if i press f2 in here one more time i'm also going to need to anchor cell c36 to be an absolute reference by pressing f4 so go ahead and press f4 there and then this formula can be filled right and as you'll see i'm always using these same weightings right here and applying these weightings to all these various costs and then dividing them by the total of the weightings so i can see here i've got an average and then i've got a weighted average and then the last thing i'm going to show you is how to add let's add a median as well and it's quite simple in excel it's just the median median function and again i'm going to select all the expenses close bracket and i see that sgna 16 and a half million that's the median of the group so another piece of statistical analysis that you can use here and so you've got an average a weighted average and a median and a total and one last thing to clean this up for best practices these are all hard codes here so let's press alt h fc and make them blue this is a formula here everything else is a formula a reference to another cell or a function so we're in good shape in terms of formatting here let's continue building our analysis down below here and start using some if statements if statements are very powerful in financial modeling and used very frequently so there's a bunch of different ways to use them and i'm going to walk through some examples with you now let's first off let's let's take a look at these periods and let's use some if statements around these periods the first thing i want to know is for the annual period if it's less than a full year i want to flag it as a stub year and if it's as long as it's a year or more then i'm just going to call it like a normal year or something like that so let's put our cursor right below annual period then let's practice inserting a row so let's press alt h i for insert and r for row and then i'm going to put here um stub for full year and let's practice a very basic if statement here let's say if open bracket if and then we're going to have a logical test a value if true and a value if false so the logical test is if the number in this cell here is less than one meaning it's less than a full year then i want to i want to return in quotation marks stub close quotes otherwise i want to say full year close quote and close bracket so what you can see here is that because this is less than one it's just returned the word stub um let's let's practice aligning right here so let's press alt h ar for align right and i'm going to hold down shift in the right arrow and press ctrl r to fill this right and we see that it's working so we've got a stub year in the first period and then we've got full years thereafter so this is a way to use an if statement to do a logical test and then return a string of text let's let's look at another example down below where instead of returning a string of text we're going to return a number so let's say that we want to we want to know which years have total operating expenses that are greater than a hundred and fifty thousand so let's say we only want to highlight those years otherwise we want zeros so let's put here and what i want to do is i want to put down here if less than 150 000. and i'm actually going to type that number here to make this dynamic and then i'm going to copy this and paste it below and then i'm going to say if greater than or equal to 150 000 i'm going to select these i'm pressing alt h f c make them this the shade of blue so now what i can do is i can make a formula that says if open bracket if the total expenses pardon me if the total expenses are less than this number here which i'm going to lock in place with f4 then i want you to return that value of the total expenses otherwise i want you to return nothing so in this case you see that this is indeed below 150 000 and so we return the value if i select this and fill it to the right you see what happens here is that as soon as it crosses over to 150 000 we return the value zero now let's do the second example this time we're going to say if the total operating expenses are greater than or equal to and i've said it greater than or equal to so that we capture the instance where expenses might be exactly 150 000. if we only did the first line as less than and the second line is greater than we would actually miss the instance where it might be 150 000 evenly so i've said greater than or equal to and then i've said if it is greater than or equal to i want you to return the number otherwise i want you to return zero so in these first years we should have zero and let's just go back into this formula and make sure we've done our locking properly so we know that we need to lock c45 because we're always going to be referencing relative to that number and the other parts of this formula we want to be dynamic so let's let's try copying that right and see if that works and great we see that in these earlier years we're always less than 150 000 and then we switch over and we can see that these two if statements are working correctly because we're just highlighting on the first row uh only they should be less than 150 and on the second row they should be greater than so this is a powerful way to do some if analysis and i'm gonna just format these quickly to be the same as everywhere else in the model so i copied and i'm going to paste special some formatting and there we go we've got some another use of the if statement so we've got an if statement that's returning some words here we've got an if statement that is is returning a value a number or a zero and let's work through a couple other examples of using if statements another use of if statements is for error checking and ensuring the model is working properly so let's do an error check here and what i want to do is and actually i'm going to i'm going to copy this and move it down because i'm going to add an extra line here that just says total expenses and on this line here i'm going to use alt equals to do our quick auto sum i'm going to take the the total of these two rows and fill it right and essentially what i want to do is make sure that for some reason i'm not double counting here that this formula is not double referencing the total expenses so what i would do is i would say here if the total expenses in this line equals the total expenses in this line then let's say okay so we're error-free otherwise say error and then close bracket i can press alt h and ar to align right and then i can fill this right so i can see that we're okay here there's no double counting of expenses uh with these greater than or less than functions and but just just in case let's just type an example to see what would happen if even this was off by a dollar all of a sudden i've got an error message there so we can see that this is working properly i'm going to undo that control z so now we've got an error check here it's very common to use this on the balance sheet to make sure that assets equals liabilities plus shareholders equity you might want to set up this um okay or error function to make sure that they're balancing correctly and one final thing that i'm going to show you how to do it we've covered it earlier in the course but just to review and make this extra dynamic let's adjust these labels here so that if i change this number here to be 170 000 instead of 150 it's actually going to update in the label here so to do that press f2 and using control on the left arrow get to the beginning of the string of text now we're going to change it to a formula so it's going to be equals and i'm going to open quote now we're going to change it to a formula so it's going to be equals and i'm going to open quote if less than and then i'm going to close the quote here i'm going to delete the 150 000 that i've typed in as a hard code and i'm going to put an ampersand and then pressing f2 to get out and move the cursor i'm going to select this number here so now it says it's the same as before except when i change this so let's say i change this to be 180 000. it's actually updated uh in my formula here and i can now see that i've got i've got some issues here because that's actually triggered an error here where i'm double counting expenses so i'm going to change this back to 150 000 and i'm going to make this dynamic as well by pressing f2 going back to the beginning of the string of text setting it equal to a formula where i open quote then i close quote delete the text use an ampersand and pressing f2 to get out and move the cursor i select cell c46 so now we've got this totally dynamic and if i want to go one step further to avoid double counting i'm actually going to set this number here to be equal to the number above and then i'm going to change it to be black because it's now a formula what that means is that if i change this number i can't inadvertently double count like it was earlier so that actually changes the way this analysis is performed so these i always want to just capture the expenses once so now i've got a dynamic set up here where the labels are changing and all these functions are changing and i and i'm always in an error-free situation because i've made it more dynamic so a few more examples here of how you can use if and dynamic headers to create powerful analysis let's take a look at how to incorporate rounding and absolute references in our analysis the first thing we're going to do is take a look at rounding with decimal places so if if i take these numbers up here and let's select this entire section with ctrl shift down and right arrows and using alt h 0 we can add decimal places so what we'll see here is that particularly in this weighted average section we have a lot of decimals here let's take a look at how we can use the round function to change the number of decimal places so first off if i take the if i want to round off the weighted average in the first case i want to have it to let's say four decimal places well i see that i get the same numbers up above because that is only showing four decimal places now if i want to change that to be three decimal places you can see that the last number's rounded off to zero we can also make it two decimal places let's make it one decimal place and finally you can actually make this a zero as well so showing to no decimal places so this can be very useful if you want to show a long string of decimals but round them off or if you just want the numbers to be rounded in the background for for calculations actually for math so that's a very useful way to adjust the number of decimal places but what if you don't want to show something with decimal places what if you will actually want to round before the decimal so let's take a look at how to do that and i'm just going to remove some of the decimal places here to go back to our original formatting so this is 2967 and let's think about how we could round this you know you might want to round this all the way up to the nearest thousand um so it might actually just become 29 000 even let's look at how to do that so this is function is called m round and m just stands for multiple so we're rounding we're going to round this number here to the nearest thousand so just type in a thousand close brackets so now is 29 thousand we could round it to the nearest hundred we could round it to the nearest ten or we could round it to the nearest dollar which is the same as when we did the decimal places to zero with the regular round function so they overlap at this point and we could also make it even let's go bigger and say to the nearest if we went as big as the nearest hundred thousand then you see we'd get zero of course because it rounds it all the way down so depending on what you want it you know the nearest uh ten thousand then we get thirty thousand as an as an output so this can be very useful if you want to take some numbers and and make them round for either output or for calculation purposes if i take this number and multiply it by 10 you can see that i get 300 000 which is very different than if i take this number and multiply it by 10. so you can actually see how you would use this to change the way math flows through in a model if you wanted to be rounding every step of the way it could be very powerful and it could also be used just for output in terms of formatting so this is some very useful analysis that we can do and one and one other function that i want to show you is the absolute function so let's suppose for example that i was showing my expenses as negative numbers which is common practice in a lot of cases or for example i had a cash balance or some some other number that went negative in my calculations i may want to have an absolute reference so that i want this number to be shown as a positive so i can use the absolute function to do that and in some later examples we're going to use the absolute function where we want to show in sensitivity analysis the plus and minus impact of a change from an assumption we're going to use absolute function to align that and i'll show you a bit later so absolute is also a powerful tool so i'm just going to change these back to be one decimal place using alt h9 so now our formatting is right back in place in this chapter we're going to cover off min max as well as large and small functions and these are all very powerful tools for performing analysis so first of all let's let's just type here min max and then i'm going to leave a space and put small large so these can be very powerful ways to to analyze data so the first thing i want to do is is type in min and i want to take the minimum of all of the expenses that i've laid out here i want to find out what's the minimum expense line on the income statement and i see here that it's a thousand and then i'm also going to type here max and scroll up to the expense lines and select select each line of expenses and i can see that the max is 67. so i can copy these across and see how they change over time so the minimum is not changing it's um the interest expense which is fixed and then the maximum the maximum if i scroll up here i can see that it is cost of goods sold so that's all well and good and and fairly straightforward but a lot of times it's helpful to say what is the uh second largest or second smallest or third largest third smallest so we can use the small and large functions and what i'm going to do here is just type two numbers that will be our inputs and i'm going to change theme to be blue colored so alt h fc and then select that that blue there and what i'm going to do then is in this function i'm going to say equals small i'm going to select the expenses in the same way that i did with the min and max functions except this time i'm going to add a comma and i'm going to select um an absolute reference locking with 84 to this cell here so in this case it's picking the very smallest one which is the same as the min so in this case there's no difference but what happens if i want to see the second smallest or third smallest fourth smallest fifth smallest etc so here you can see that this is actually uh small and large are actually much more interesting and powerful functions than min and max and they can be used as min and max functions if you use one in the place of the ranking of smallest or largest so this is a great tool and let's uh let's link up large as well so we're just going to link up to the same area as before all the expense lines and then we're going to add a comma and we're going to select this cell here c54 as an absolute reference by pressing f4 and then close bracket so again this is the same as the max function you can see that these two numbers are equal to each other but then i can see what the second largest expense is and if i scroll up it looks to be accurate the taxes are the second largest expense and then the third largest you know i can see that right here as being sgna so a very powerful tool here if i want to take say the second smallest and the second largest and what i'm going to do is copy and then paste some formatting down here and i'm going to fill this to the right so now i've got a very dynamic piece of analysis where i can take i can rank first second third largest and smallest and we're going to find this very useful later on in our sensitivity analysis so the min and max are are certainly useful and if you know you're only going to ever want to look at the minimum or maximum in a range then i suggest using those but if you want to be more dynamic i suggest using small or large pretty much you know always even in replace of min and max because as i pointed out you can actually just put a 1 there and it's the same as min and max let's take a look at how to avoid errors in excel that may mess with the rest of our financial model so we're going to use if error as a powerful way to avoid errors so let's say for example that i've got a hard-coded number here and for whatever reason it happens to be zero and i'm taking some other numbers here like for example the largest expense on our income statement and i'm dividing it by zero and i'm gonna i'm gonna lock that in place with f4 to zero and copy it across and i get a whole bunch of errors but if i change this to a one or a two or a three it works and there may be some situations where uh zero only applies to part of the analysis or or you may actually want to be able to enter it so what we can do here is change this to say equals if error open bracket and so we have the function here that we want to calculate so we we want to put a comma to say so if that if the result of that calculation is an error then what should be output and in this case let's just say n a in quotes here and then press enter and if i use alt-h-a-r to align right i have that aligning right control shift and right arrow highlights everything and i press ctrl r so now what happens is if it's a one or a two it's fine and if it's a zero it just says n a so i don't get an error message this can be really useful because there may be down here other functions that are dependent on that number and you know i don't want them to necessarily all ref out so in this case i get a different type of error message than i got in the one above so i could also change this to say um if error and i'm going to take this here put a comma and then this time i want to change it to a 0 instead of an n a if i change it to be a zero instead of an n a and let's see what happens as i as i toggle this okay so now what's happening is i can actually continue to build the function even though i've got an error message that would normally be displayed here and here i can actually continue to let my model flow through so this is a very powerful way to not have one error message that might occur here or here impact the financial model down here so what i'm able to do is just continue building and i'm not being impacted by what would actually be an error up above so when it's a one these functions would work fine but if i change this to a zero and i didn't have the if error messages here then none of these calculations down here would work so this is a very powerful way to avoid errors that that screw up your entire model now the last thing i'm gonna do here is just just label these rows so we know what's going on here and i'm gonna i'm gonna just label them as one is dividing by zero and the other is adding with an error and then these are also examples of adding with errors so um essentially what you've got now is is a clean layout of our financial model the next thing i'm going to show you how to do is use find and replace to edit formulas so the find and replace function in excel is very powerful if you use if you press ctrl f we can do a simple example where we're just going to change text so let's say for example i want to change i want to find usd which i know is in the top left corner here and it may be in other places throughout the model but what i want to do is replace usd with cad so what i'm going to do is i'm going to then select replace all and it tells me that there was just one spot in the model that had it it was the top left corner we changed it to c8d i'm going to close that and then just press ctrl z to leave it in usd so that's fine and relatively straightforward but it's much more interesting to use uh find and replace to edit formulas so one thing we can do is that if i want to change this n a here to be something else i could go into the actual formula itself and change n a the other thing that i could do that would probably be faster is i could press ctrl f and i could find n a and excel is going to look in the formulas and then replace it with um replace it with um not applicable and i can replace all and it tells me that it happened eight times so now i actually have not applicable here i'm going to control z undo that but you can see how you're starting to get into the formulas so let's also imagine that i want to change a reference of a cell so if in these large and small functions here i want to now instead of instead of referencing cell c 53 in the case of small i actually want to reference b53 i could select this area here so now i'm doing find and replace within a selection if i don't have a selection i'm just in one cell it will find and replace for the whole sheet if i have a selection i can press ctrl f what i want to do is i want to look for c53 which has dollar signs around it locking in place and i want to replace that with b53 then i'm going to press replace all and i can see that there were seven replacements here so now it's actually referencing b53 instead of c53 as it was earlier and this was a very simple and straightforward example but you can imagine how with a much bigger spreadsheet you may want to start adjusting formulas and it may be easier to use find and replace to actually change a lot of formulas all at once so find and replace is a very powerful form of model editing now it's time to keep practicing the key to being proficient at excel is a lot of practice and experience we've got several courses at cfi that will help you take your excel skills to the next level now that you've got the foundations the first three that are critical are financial modeling fundamentals building a financial model in excel and then fp a rolling cash flow forecast once you've worked your way through those you may want to try some more advanced courses like our startup or e-commerce financial modeling course mining financial modeling and sensitivity analysis these courses will take you to a professional level of financial modeling and excel use thanks so much for taking this course with the corporate finance institute

Channel: Corporate Finance Institute

Views: 1,839,293

Keywords: excel crash course for finance professionals, excel crash course, free excel crash course, excel tips and tricks, excel formulas and functions, excel shortcuts, excel training, excel tutorial, excel for financial modeling, corporate finance, finance training, finance education

Id: kjoldYi7eH0

Length: 75min 57sec (4557 seconds)

Published: Sat Sep 03 2016