Excel Crash Course | Advanced Financial Analysis Setup (Part 5)

advanced financial analysis set up in this session we have several objectives the first one is to enable you to handle more complex situations when performing financial analysis the second is to make your analysis more dynamic that means it automatically updates and can handle changes easily we also want to make sure that your analysis is more detailed and more precise finally this will enable you to produce professional grades of output and become a world-class financial analyst let's flip over to excel now and get going let's get into some more advanced financial analysis setup if we right click on financial analysis we can move or copy and we can create a copy so by creating a copy we duplicate the worksheet click on research so that the new worksheet goes between financial analysis and research and press ok we can double click on it to rename it let's delete the number two at the end and let's add advanced to the beginning of it let's press control page up to go to our original tab and let's call that basic and press Enter so now we've got three worksheets let's actually insert one more and we can drag it and move it in between advanced and research and let's call it extra data and add a double arrow sign to the end of it so what we're actually doing here is inserting a sheet simply to use it as a way of organizing our information so we have our two active pieces of analysis on the left here and then a separator that's pointing to extra research or extra data that we don't want to delete but we're not necessarily actively using we can further separate things by clicking on extra data right to click and then tab color we could actually change this to be a dark gray for example keep extra research on the end with an unchanged color and perhaps select both of these right-click on them and then tab color and we might want to make the active ones a particular shade of blue or whatever works for you so now we've got these active sheets as financial analysis extra data and research the next thing we're going to do is use some grouping and some more shading to further organize the information so imagine if we wanted to create a section just for the income statement and a section just for the assumptions that we could easily open and close we're going to do that with grouping so we're gonna select all of the cells below income statement all of the rows and press shift spacebar shift spacebar automatically selects those rows and we're gonna group the cells by pressing alt a for data G for group and then press G again to confirm grouping and what that does is that gives you a little minus button you can click here that collapses the entire income statement section and then opens it up again you'll notice that I did not group the title of the section as then when it's closed I wouldn't be able to see a label for what's contained there we can further format the section by selecting these cells pressing alt H age for background M for more and you can set a custom color here or you could pick a preset color whatever you like we're going to use another CFI color and I'm press ENTER to apply these formatting changes to the rest of the sheet we can copy this paste here alt H V s and T for formats and then select the cells below shift spacebar alt a G and G again and now we've grouped the assumptions as well so we can collapse everything and we have each section as being independently able to be opened or closed and this is very nicely formatted now let's continue building out our analysis by creating a new section we're gonna copy one of these headings from above and paste it down below and let's rename it analysis now what we're gonna do is label this common size income statement and then below it we're gonna copy and paste all of the line items on the income statement let's paste them here alt H V S for special and then V for values so we've got the entire income statement right here we can bold this heading and what we do with a common size income statement is take every line item on the income statement in each year and divide it by revenue so we start with revenue divided by itself and this is where we want to do some anchoring if we press f4 once it adds to dollar signs that means that if we copy and paste this formula anywhere else it's gonna freeze the reference to D and for if we press f4 again it's only gonna freeze the reference to row 4 once again it's only gonna freeze the reference to call them D and if you press f4 one more time there's no anchoring so let's press it twice so that is always locking in row 4 but it's not locking in the column press alt H P to make it a percentage and all to page 0 to add a decimal place now because we've done this anchoring we can fill everything down with just ctrl D and when we do that if we press f2 to take a closer look at what we've got we can see that it's referencing whatever line item on the income statement we want and dividing it by revenue and what's more we can take all of this using shift on the right arrow select everything to the right press ctrl R and fill it across and then we can check and make sure that this is working properly which it is now these numbers are all the same in each year because we've just kept the assumptions constant but you could see how this would change over time if all we do is change any of these assumptions for example you can see that then everything else below changes so this is dynamically linked and setup now let's add another piece of analysis let's call it percent change and this is a basic form of sensitivity analysis and we can go up and copy all of the line items that were on the common size income statement and paste them below and then in this cell here where we have percent change and call them see we can add a number let's say 10 percent altered age P to make it a percentage alt H FC to make it the hard code blue color and now we've got a cell here that we can reference everything to and we can calculate then the percentage change in all numbers as that assumption changes so we're gonna make this formula equal to revenue and each line item in each time period times 1 plus this assumption here now this time we anchor with f4 just pressing it once so that we are always referring to cell c36 no matter where we copy and paste the formula now let's borrow some formatting from elsewhere in the model by copying and pasting alt H V s and then T for formats we can copy this to the right by pressing ctrl R or filling right and you can see that the reference is always to the same cell here and if we scroll up it's moving across two different revenue numbers now we can take this and fill it down with ctrl D and what we should see is that it moves down the income statement which it does but as we move across it always refers to the same cell here and continues to move across the income statement which it does if we change this number here to 0 then all of these numbers here are exactly the same as the numbers on the income statement up here you can compare this number if you want to double check and you see that it's exactly the same now we can see what happens if everything increases by one percent by two percent by five percent etc or even if it declines you can see what happens so this is a powerful way of building some more dynamic functionality into a piece of analysis the last thing we will do here is bold this title and we will select all of the cells here all of the rows that are part of the analysis section here press shift spacebar to highlight everything and then alt a for data G for group and G for group again now what I can do is press 1 and 1 collapses all of the group cells 2 opens all of the group cells so we've got some very organized analysis here in our little model let's continue working through our advanced financial analysis one little tip we're going to give you now is how to change multiple sheets at once so if you notice that we've got two sheets here that are identical other than some grouping and additional analysis under the Advanced section if you hold down shift and then select both of these worksheets both tabs you can now make edits in this worksheet that will apply to both so for example you could change the starting year to be 2020 and if you do that and you click away so you've unselected both of these sheets you'll notice that that change is applied to both of them so this can be very helpful when you need to work on multiple sheets and make multiple changes to them but it can also be very dangerous if you're not paying attention and you sort of forget both of them are connected and you're editing two sheets of months so let's change it back to 2016 but that can be a very handy tool when you're performing your analysis now let's look at how to name cells and how to name ranges so imagine that we're continuing with our analysis here and we want to make a calculation down here for a beta and if we open up every section here ie beta is obviously located on the income statement but we could also calculate it by taking gross profit and deducting SGA that will give us a beta and we could build a formula the traditional way where we take gross profit and we subtract SGA and we get a beta but let's look at another way to do this using named ranges and named cells if we select this entire row and we press alt M for formulas and n for name manager and then press new you'll notice that it's suggesting a name for us for this range which is gross profit because it's the leftmost cell in the range and you'll notice that it refers to all of the cells in this row so let's just press ok so gross profit has now become a name in our name manager you'll also notice that these print areas we set up earlier on each of the worksheets also has a name to it so now let's select let's close that select sgna alt M for formulas and for name manager new and now sgna you could edit this if you wanted by the way you could you could type in whatever else you want to it and press ok and close so now when we're down here we want to type a bit out we can type equals gross I'm going to start typing gross profit and you see how it comes up as a suggestion here press tab on a keyboard to accept that suggestion - s and now sgna is coming up tab to accept that and press enter we got the same number as we calculated before we can fill that right with ctrl R and if we press f2 on any of those formulas you see that it's referring to the entire named range is up above so the formula always corresponds to the cells in the same column that is referring to in the range so that is a way to use named ranges to calculate yadda now instead of naming an entire range let's look at how to name just one specific cell let's put your ebody sensitivity and that's going to be equal to e beta in any given year times 1 plus if I scroll up our sensitivity driver here but instead of anchoring this a place with f4 as we would have to do here to make this formula we're going to name the cell and build the formula that way so if you click on that cell that you want to apply a name to and you can actually click in the left corner here where it says c36 you can override that and let's give it the name sensitivity so now not an entire range but just one cell is called sensitivity you'll see if you move to the right is d-36 back to c36 it's now called sensitivity and then you have v36 so you can see how that works now we can build the formula very easily where we take the avada in a period and multiply it by 1 + and you can either refer to that cell and you see that it brings the name into the formula automatically or you can just start typing it and you see that it comes up press tab to accept it and close the bracket notice that I do not use any anchoring here by pressing f4 it's not required I can actually take that and fill it to the right with ctrl R I'm just going to copy the formatting from above alt H V s and T and if I press f2 to audit these cells you'll see that it's always referring to that one cell named sensitivity and if I go up there and put 10% in you can see that UB does sensitivity is always 10% higher than regular Eva death so now we've shown you how to use named ranges and named cells it's important to point out though that there is a downside to naming cells which is that if someone else is auditing your model and they look at this formula they don't necessarily know where sensitivity cell is located sure they can press f2 and if it happens to be nearby they'll see it but it's much easier for them to understand where a cell c36 on advanced financial analysis is located then just sensitivity now let's take a look at how to trace precedents and dependents in a model this can be a very useful exercise when you want to audit your own model or someone else's work here's an example let's go to cell e4 where we have 2017 revenue let's trace what cells are dependent on this cell press alt M for formulas and D for trace dependents so what you can see then is that based on these arrows gross profit the revenue growth rate the margin the common size analysis etc and 2018 revenue are all dependent on 2017 revenue which makes perfect sense let's remove the arrows now let's see how trace precedence works if we go to net income in 2022 and we use our shortcuts so we press alt M and this time P for precedents we can see that net income is dependent on taxes and EBIT but I'm gonna use my mouse here for a second if we keep pressing trace precedents we can see how it goes back and we can actually keep pressing it until it goes back to the last point where it stops which is that 20:22 net income goes all the way back to being a function of 2017 revenue and you can see what else it's a function of as well so this is a very good way of auditing the model let's remove the arrows there's another way to do this as well if we take the 2018 revenue and this time we just press control open square bracket control open square bracket traces precedents so you can see the 2018 revenue is a function of 2017 revenue and the growth rate assumption let's look somewhere else at how this works let's go to this cell here ether t7 and press control open square bracket on our keyboard you can see it's a function of 2017 revenue and our assumption here for the sensitivity analysis let's click on this cell now and let's trace the dependent cells on this one because quite a few cells should be dependent on this let's press alt number formulas and D for dependents and you can see we get a very bright screen here because this sensitivity cell is links to everything in this area which is what we would expect and let's remove the arrows so when you're working on either your own model or someone else's model that you've inherited be sure to use trace precedents and trace dependents to audit and make sure the models working as expected now it's time to talk about how we can audit a model using the go-to special function imagine that you just inherited this model and you need to figure out if all of the blue cells are actually hard codes and if all of the black aunt cells are actually formulas you'd have to go through and check them one by one if you really wanted to know by pressing f2 on each cell is this actually a formula or is this a hard code luckily there's a way to quickly audit the entire spreadsheet and what we can do is press alt H for home FD for find and s4 special and in the and I'm going to press cancel to show you an even faster way is to just press f5 when you press f5 you then click special and you can use the go to special function so we can automatically go to all cells that contain constants meaning hard codes and you can include numbers text logicals and errors or you can for example exclude text and just go to the numbers that are hard codes and then you can also go to all formulas so let's look first at constants but let's exclude text because we just want to know which numbers are hard coded and press ok let's look at what we see we see the blue salt here that we know is a hard code all of these assumptions are hard codes and the historical numbers that we thought were hard codes are and it also points out the cell d1 is actually a hard code whereas these other years are formulas so that's perfect that's very helpful to see now let's try it again press f5 again and special and this time let's go to formulas and press ok so here's what we can see all of these black font cells our formulas just as expected and so are these years here so that's very helpful that's a very quick way of seeing how the model is set up now let's see how we can use conditional formatting to see how a model works let's select all of the percentages on the common size income statement press alt gauge for the home ribbon l4 format cells and age for highlight cells rules and then we want L for less than so we want to see any cells that are less than 6% and we want to highlight them with a custom format so the fill we could actually say no fill and and no border here but then font color we could actually make a light gray so what we're doing is receiving these numbers we're saying anything that is below 6% is not that material so let's make it a light gray color so it fades into the background as an example now let's look at a different way of using conditional formatting let's highlight the EBIT done numbers here and let's press alt h l for conditional formatting age for highlight cells rules G for greater than and let's say if it's greater than 90 million then we want it to be green press ok now let's repeat that let's press alt H L for conditional formatting h4 highlight and this time L for less than and we want to say if it's less than 90 million then we'll make it red with light red press ok so now we can quickly see where the turning point is where ebody becomes greater than 90 million it's in 2020 so that's a helpful way of flagging information in a model let's look at how to incorporate dynamic headings into our model if we close all the group cells by pressing 1 and actually we can select these cells here and actually group them alt a for data G for group and G again so that it's nicely grouped there if we have all these closed so it's easy to look at we might want to add a label to the income statement that indicates it's in USD thousands in order to do that we can change this from just being text by pressing f2 getting into the cell editor typing equals opening quote and going to the end of income statement adding a space and closing the quote then using an ampersand to join it with whatever is contained in cell a1 so refer to a 1 and press Enter so as you can see it automatically adds whatever is in that cell to the end of the name so the way it works is that we make a formula whatever we want written as text as permanent text we put in quotes including spaces we have to add the spaces and then we use ampersand to join whatever is contained in a cell let's look at another example here let's open up the analysis section and we can do something even more dynamic we could say that we want a cell that's equal to me Buddha join with ampersand open quote space in space and close quote then an ampersand to refer to the Year 2022 so we've now got a formula that's actually connected to these two cells if I press f2 I see that it's connected to I beta and 2022 and I can actually then just refer here to be beta in 2022 just so that it makes sense here we understand why we've got this label and let's group this alt a GG press 1 to collapse everything down and we've now incorporated some very cool dynamic headers

Channel: Corporate Finance Institute

Views: 58,256

Rating: 4.938983 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: rldnzFUjkv0

Length: 25min 33sec (1533 seconds)

Published: Sat Mar 30 2019