Excel Crash Course | Basic Financial Analysis Setup (Part 4)

basic financial analysis setup let's look at what we're going to cover in this section a good layout in structure is absolutely critical to excellent financial modeling and analysis let's look at what separates great work from everyone else having clean formatting is absolutely critical it makes all the difference for your users in addition to clean formatting we're gonna focus on the following things one is making the model easy to follow and understand so anyone can jump into it and use it it needs to be easy to audit this means someone can work backwards and see how you arrived at the final result it needs to be dynamic that means if you make a change in one area of the model it flows through to the other area of the model it needs to be detailed just the right amount of detail where you've captured everything but not overly complicated either it needs to be accurate it needs to have perfect precision in terms of the correct formulas functions and calculations and finally it needs to be easy to make changes to so that anyone can hop into your model and make sense of it let's flip over to excel now and get going on this please open up the downloadable file for this course that's called book 1 - blank inside the workbook if you press ctrl page down that's the shortcut to navigate to the next worksheet you'll see that on the research tab we've got some information here that we're going to use for our analysis you can press control page up to flip back to the main worksheet let's start by putting units in the top left corner we're gonna make the units for this model USD and in thousands so we can just type that in the top left corner it'll always be visible and helpful for us and across the top of any analysis we want to start by inputting the years or the time periods they could be quarters months weeks but most likely years so we're gonna start with two years of historical data 2016 and 2017 then we're gonna have a forecast beyond that so what we want to do instead of typing out all of the numbers in all of the years we're just going to for make a formula that refers to the starting year and add one so plus 1 and I can then press shift and the right arrow on cell a1 and I can select all of the cells to the right that I want the forecast and the analysis to include and press ctrl R ctrl R is the shortcut for Phil right that saves me the time of copying and pasting this formula which is a bit more of an extra step in terms of keystrokes so that was how we feel right to make the year's across the top of the model so recall that I said that there's two years of historical data and the rest is a forecast so what we want to do is label things more clearly select these two years press ctrl one ctrl one is the shortcut to format cells we want to select under number at the top custom we're gonna make a custom type of number here you can just select 0 since these years don't have commas or decimals or anything and at the end in quotes type a for actual and press ok you can see that that inserts an A at the end of the number for actual but if you click into the cell it's just a number there's no letter at the end is just formatting then select the forecast period you press ctrl + Shift on the keyboard and right to quickly select that entire area and press ctrl 1 to bring up format cells once again custom but this time we're going to put in it e in quotes for estimate or you could put F for forecast whatever you prefer as a convention and now we've clearly labeled these as being estimates what I want to do next is frame the top section of this analysis so that it really stands out and the best way to do that is to use a solid color background and then change the font color to white so using ctrl + Shift + the right arrow select the entire area there then we can practice our shortcuts press alt H for home and H again for background color here's where you can select any of the preset colors or you can press em for more and you could even create a custom color in our case I'm going to I'm going to use the custom CFI blue color and press ok this is our corporate color then I'm gonna use the shortcuts again and press alt H F C and we're gonna use the arrows to navigate around set it to be white then I'm gonna press control B to make it bold so now you can see that this really stands out we've got a nice solid blue background and white font it's very easy to see the structure and the layout of this one final thing that I'm gonna do is select all the years and I want to make them a little bigger let's press alt H and then let's press FS for font size and we could try making those years just a little bigger see what you think looks good maybe 12-point and then once we've done that we might find that the columns are a bit too narrow if we're gonna have a long numbers in here we don't want the years too close together so when we have the columns that we want to adjust selected here and I'm holding down shift and using the right arrow I can press ctrl spacebar control spacebar selects all of the columns that you are currently working in so we can do that and then we can press alt for our shortcuts here age for home oh for format and then we can adjust the column width so you could use W to go into column width you could use I to autofit so if you want it to auto fit it would just size to how long the numbers in the cells are or you can press W and we could maybe change that to 10 and there I think we have a column width that looks nice and we set this up entirely using shortcuts next thing we're gonna do is make sure this is properly set up from a page layout perspective so what we can do using our shortcuts we can press alt we can press W which is for view and I for page break preview just see what it looks like here and you're gonna see what's going to be printed so if you like it or don't like it you can adjust it now this is actually a view that you can drag and drop in so you could actually set it up to only print this area for example and you could also from here go back into your shortcuts press alt and then we can press P for page layout o for orientation and we can switch between portrait in this case and I think we want landscape for this one to be the best sup you can also change the scale here if you wanted to zoom out to 75% you could fit more on the page if you wanted to zoom in to 150 you could do that but then not everything would fit on the page and you can drag this again so that everything fits and we're going to change it to 100% zoom once you're done with that press alt W and L for normal so we're back in our normal view here now what you'll notice if we scroll down in this model to some lower cells is that we can't see the heading anymore and it's very important to always be able to know what your were in so if we're down in cell D 52 we can't see what year it is so what we want to do is freeze the top row and in fact what we also want to do is potentially freeze the left column because if we scroll over to the right you'll notice that we can't see call them a anymore and if we have labels here say revenue cost of goods sold etc and we always want to see them if say this was a 25 year model we'd have to freeze those as well so what we can do is we can go to the top left where we want everything frozen and we put the cursor where everything to the left of the cursor will be frozen and everything above the cursor will be frozen so let's press alt W for view F for freeze panes and F again F keeps the column and the row frozen so if you scroll down now we always see the year across the top that's quite nice and if we scroll to the right look at that we always see columns a and B which can also be quite useful the next thing we're going to do is press ctrl page down go to the information that's contained here and we're gonna copy these labels with ctrl C and go back with ctrl page up and then we can paste here starting in cell a3 these labels for our income statement in the next sections we're going to start filling in these numbers formulas and calculations and as you can see when we scroll down we see the years and when we scroll to the right we continue to see the labels that we've put in place so this is a very well-structured and well setup model for performing analysis okay we're going to continue building our financial analysis here press ctrl page down to move over and select the numbers using ctrl shift and the down and the right arrows to select these numbers here we're gonna copy them with ctrl C press control page up to flip back over and we're going to paste them here for 2016 and 2017 actual now we're gonna paste those numbers here in a special way we're going to use the shortcuts alt H for home V for paste and s for special once we collect s for special we see all these options we can paste as formulas values formats and we can do operations including multiplying and dividing as well as transpose where we can paste something that was vertical in a horizontal way and something that was horizontal in a vertical way and you'll see examples of these throughout the course but for now we are gonna pay special as values if we paste those values we don't carry across any formatting and we just paste the raw numbers press ok so now we have those numbers pasted there for the historical time period let's select them and press ctrl one to do a bit of formatting we want to format them to be a number with a comma separator and only one decimal place additionally if the numbers are negative we want them shown in brackets press ok now we've got these numbers looking a little bit more organized and formatted the next thing we're going to do in our financial analysis here is we're going to delete all of the subheadings so gross profit is equal to revenue minus cost of goods sold so we can delete that and replace it with a formula same thing with eBay de with earnings before tax and with net income so now what we're going to do is set a formula equal to revenue minus cost of goods sold equalling gross profit and I can fill that right with control our eBay de is going to be equal to gross profit minus the SGA selling general and administrative expense fill right with control R just keep repeating this process until now you have IVA de minus depreciation minus interest to get earnings before tax and fill right and then calculate your net income as earnings before tax minus taxes and fill that right with control R so we've now very quickly made this more dynamic by adding formulas instead of hard coded numbers for all of the subtotals so now that we've added some formulas here that's great in terms of making the analysis more dynamic and by the way I'm pressing f2 on each cell to see the formulas contained within it so I put my cursor on the cell and I press f2 and I can immediately Aude it and see what the formulas are but you'll notice that if you look at the entire area it's not clear which lines are formulas and which are hard codes so what we want to do is format all of the hard coded numbers to be blue so let's format these let's press alt H FC for color and if we press M for more colors we can see this color wheel here and there's a particular blue here that is common practice to use as the blue to indicate hard coats although you could use any color you want as long as you're consistent but we're going to use this particular blue right here so I know that revenue and cost of goods sold are hard codes and now that they're blue it's very obvious and gross profit below that is a black formula what I'm going to show you now is how to copy and paste formatting so instead of going through each of these and man changing them to be blue I can copy a cell that has the formatting I want and then go to the cells that I want to apply that formatting to and if we follow the shortcut for paste special again which was alt age V and s last time we pasted as values this time we're gonna paste formats and then press ENTER and look at that those cells just turned blue let's try it again with depreciation and interest alt H V s for special and T for formats press ENTER and we'll do it one last time here with taxes alt H V s and T for formats so now it's very clear that the hard coded numbers are blue and the formulas are in black now that we've got the historical numbers in place we can calculate some assumptions and use them to drive a forecast so let's make a section here for assumptions I'm going to bold it and then below that we want to have a few other things revenue growth cost of goods sold is a percent of revenue SGA in dollar amounts depreciation is a percent of revenue interest expense and the tax rate now let's calculate these values revenue growth I can't calculate in 2016 because I don't have a comparable period in the year before but we can calculate the growth in 2017 it's equal to the revenue in 2017 divided by the revenue in 2016 minus one and presenter if I put my cursor on the cell I can make it a percentage by using a short fault hp4 percentage and if I want to add a decimal place I can use the shortcuts alt H and zero to add a decimal place by pressing f2 I can audit the cell and make sure that the formula is correct now let's calculate cost of goods sold as a percentage of revenue and to quickly navigate here I'm using ctrl + the up arrow control in the up arrow takes me to the end of a series of data so I have this percentage here I can press all th P to quickly format it into a percentage and alt H 0 to quickly add a decimal I hold down shift on D 17 and press the right arrow and then I can feel it right with ctrl R so the cost of goods sold margin is the same in both ears for sgna I'm just going to refer to the cell above because we're not calculating a percentage or rather a dollar value for depreciation this time we are calculating a percentage again so I take depreciation divided by revenue and press Enter and if you want to practice paste as formats you can copy the formatting from this cell alt H V and s to pay special and then T for formats you can fill it right with ctrl R with interest we are simply linking to the interest expense the dollar values and to calculate the tax rate we take the taxes and divide them by the earnings before tax that gives us the tax rate all to H P and alt H zero give it the right formatting I can select both of these formulas using shift in the down arrow and then shift in the right arrow can control our to fill it right all with my keyboard now it's time to fill in some assumptions for the forecast let's assume that revenue growth is going to continue to be 10% that cost of goods sold are going to continue to be 45% that s is going to be 20 million that depreciation is going to be 5% of revenue interest will be 1 million and the tax rate will be 30% so those are my assumptions you'll notice they're not perfectly formatted so let's copy the formatting from the column to the left and paste special alt H V and s and then T for formats and press Enter now we can format these to be blue font since they're assumptions or hard codes press alt H F C and we can select under recent colors that blue if I hold down shift in the right arrow I can fill all the assumptions right for now we're just going to keep these assumptions constant over time later we would come back and change some of these assumptions as appropriate now we start building the forecast revenue is going to be equal to revenue in the prior period times open bracket 1 plus and if I press control + the down arrow I can jump straight down to that cell close bracket so there's our revenue and you'll notice that the formatting is not correct let's copy the formatting from a cell that has the right formatting let's select the entire forecast area and pay special as formats alt H V s and then T for formats since everything in the forecast is going to be a formula we can automatically format it all to be the same cost of goods sold is going to be equal to revenue multiplied by the percentage assumption gross profit is a formula that we can actually feel right from the historical period with control our SG&A is a straight link down to the dollar assumption ebody is a formula that fills write depreciation is equal to revenue and i'm pressing control up arrow to quickly jump to the top x control down arrow v and interest I linked to the assumption for interest earnings before tax I feel right with control our taxes I calculate as earnings before tax multiplied by the tax rate and finally net income I can feel right with control R now that the entire forecast for 2018 is complete I'm going to select the whole year hold down shift in the right arrow and fill everything right with ctrl R if we use f2 to audit some of these calculations we can see that in 2021 it is referring to the correct assumption I can see that revenue growth in 2022 is referring to 2021 times 1 plus the growth rate so everything seems to be linked up properly I'm just pressing f2 on a lot of these cells to do a spot check and make sure that everything works once I know that's the case I can do some final formatting here let's make revenue at the top of the income statement bold so that it really stands out now let's select the gross profit line and insert a border let's press alt H B and then P for top that gives us a border before this subtotal let's do the same thing before Avada and if I press f4 f4 repeats whatever the last action I took in Excel was so repeating the last action just automatically inserts a border so I press f4 and then the last one is net income we're gonna press control B to bold but do a slightly different border this time let's press alt H B for border and let's pick a top single border and double bottom border that is the shortcut you suppress you and now we have clearly highlighted net income at the bottom as a finishing touch for our formatting let's remove the gridlines let's do it by using shortcuts alt W which is for view and then V G for view gridlines that removes them and makes the model look super clean

Channel: Corporate Finance Institute

Views: 63,092

Rating: 4.9575114 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: YRqC6Ju4PBk

Length: 22min 17sec (1337 seconds)

Published: Fri Mar 29 2019