# Excel Finance Class 14: Financial Statement Ratio Analysis – #1 Trick For Ratio Analysis

welcome to Excel finance video number 14 if you want to download this PDF or the excel file that we'll be using for chapter 3 you can click on the link below the video and go to the finance section the file business-to-business 233 chapter 3 or the PDFs for chapter 3 now let's start over in the PDFs hey chapter 3 financial statement analysis using ratios now ratios are used everywhere in almost all endeavors to compare numbers using division and some of the things we learn in this chapter are not only awesome for finance accounting financial statement analysis but also other fields also our topics why use financial statements as we talked about in earlier videos accountants do not necessarily use cash flow numbers finance people are interested in cash flow numbers and if the financial statements have been around for a while are they relevant so we'll talk about why we use them we'll talk about problems with financial statement analysis we'll talk about the vast majority of what we do we'll just be calculating financial statement ratios not only common size statements but ratios and then why do we use ratios to analyze financial statements alright let's start off with ratios and financial statement analysis financial statements such as the balance sheet and the income statement or accounting information as we mentioned although financial managers will prefer market value information and there's lots of market value information out there and in fact there's all sorts of different information all right but sometimes you know we would prefer this but accounting information is often the best we can get right and here's really what happens and why accounting information financial statements can be so important here's inside the corporation here's outside people are out here creditors suppliers customers stockholders governments managers are inside they control the assets the accounting records all sorts of things now presumably we have independent auditors coming in and making sure and in fact that's why auditing exists auditors come in and make sure that the managers are following the regulation that the government creates for financial statements the regulation created by the government is its sole intent is to make sure that good information comes out of here to all the outs all the people outside good regulation will and good incentives from various places will force the managers to create good accounting information but accounting is the mechanism to get that information out so financial statements convey information from inside the corporation controlled by managers to outside the corporation problems so this is why we use financial statements because a lot of times that's the best access we have to getting information there's lots of other ways to get information but that's one particularly good one and that is the mechanism in the market to get information from outs inside to outside right now our problems with financial statement analysis hey accounting accounting rules right that's problem enough there's so much gray area in accounting but accounting rules in the United States allow for different firms to use different accounting procedures for example in the inventory you can use the FIFO LIFO weighted average or even other methods same with depreciation so accounting rules themselves are oftentimes gray and you can move around a lot within those rules so ultimately when you're looking at financial statements you have to look at them carefully and go to the back and read the notes and see oh they're using LIFO for inventory and when you're compared to other companies make sure they're using LIFO or if they're using FIFO then you have to take that in consideration number two International Rules can be very different so when you're comparing financial statements from different countries you have to be aware of the rules from each of those countries conglomerates like GE right they have all sorts of component businesses so who are you going to compare GE to there's no other business exactly like them in that case you look at the component business and then find a parallel similar business outside and you can compare that analysts may use different techniques for calculating ratios not not May it's analysts do use different techniques with ratios you always have to know how the ratio is being calculated you look at the numerator and the denominator the top the bottom and see what did they use to get those numbers and number five not even on this list kind of up here with accounting but number five is accounting is gray and accountants and Finance ears do all sorts of tricks obfuscation is the word in fact in obfuscation just means to gray out or make so difficult to understand not only can you do it with accounting and even simple things we have an example kind of up later in this chapter with current ratio and how we can change the numbers a little bit to manipulate them so you have to watch out for that in addition of course Finance ears can do all sorts of tricks to move things around you've heard of off-balance sheet financing all sorts of tricks to actually hide debt one trick that people used in the recent financial crisis was they had a bunch of debt and then they bought insurance on the debt and then that meant for some reason they could not show it on the balance sheet many other tricks that people do so number five is here accountants and fie try to finance ears try to trick you now if you're doing this as a profession your job is to figure out where they're trying to trick you and then undo those numbers and really analyze what you see so those are problems but again often times you know that seems to be at least this best starting point is that information that comes out that accounting information all right ratios totally exciting and what we're going to do here first is talk about what is a ratio we have a great example the current ratio now our example down here but before what is a ratio a ratio shows a relationship between one number and unit and a second number and unit using division example find ratio of current assets to current liabilities now in chapter two we talked about working capital right current assets are all of the assets that could be converted to cash within a year current liabilities are all the debts we owe within a year so really you think about it as current assets cash coming in current liabilities cash going out so we want to compare these working capital we just took current assets and subtracted current liability ah but there's a ratio called the current ratio now we'll talk a lot about the current ratio later here we're just going to talk about what is a ratio so we're just going to take it okay this would be current assets and we're going to represent it as CA \$200 current liabilities CL a hundred bucks now before we do this ratio let's look at this ratio of something to something else every time you see this construction you know the of part you come down here the of part always goes in the numerator the number is 200 and the unit you always got to think of the unit now I'm going to show you here how to with this simple one-page guide to understanding a ratio how you can understand any ratio that's given to you and the way you do that is you make sure you put the right thing of into the numerator and the 2-part to current liabilities in the denominator you understand the number and the unit most people do their ratios and they leave out the unit's this this book we're using doesn't talk about the unit's the business math class accounting class I teach they never talk about the unit's as much but for us we're always going to explicitly leave the unit's here all right and so the same thing here two goes in the denominator number and you better list the units so all we have is 200 bucks of current assets in dollars and hundred bucks of current liability in dollars so when you do the division since that's what a ratio is the trick is you take out your XL you say 200 divided by one most of us just go to our book goes to but if you leave the units in the numerator and the denominator you have the meaning so really you don't have to memorize all those meanings in the textbook you just go oh this is two dollars of current asset for every one dollar of current liability now since this is cash going out cash coming in and this is cash going out that means we have pretty recovered pretty well two dollars of current assets for every one dollar of current liability now I can't stress this enough the trick to ratios when we get to asset turnover ratios and days payable and all sorts of other ratios return on assets return on equity keep that one here and it will tell you what the meaning is keep those units there and I'll take you with them tell you what the meaning is for every one dollar of CL we have two dollars of current asset now I want to go over and do an example we're going to go to our Excel workbook so there it is same thing right here I'm going to do my calculation here equals that equals that divided by this it shows us – now what I'm really going to do right here just since I'm we're just learning this is I'm going to put two dollars of current assets divided by one dollar of current liabilities and there's the meaning you just say and you could say it in either direction I have two dollars of current assets for every one dollar of current liability or you can say it the other way for every one dollar of current liability one dollar of short-term debt I have I have two dollars of current assets or two dollars of these assets I can convert to cash quickly all right now this is silly here look at this our next topic is common size financial statements and actually this whole video is just kind of a brief introduction to ratios the next number of videos will go into great detail about solvency liquidity profit ratios etc but common size financial statements is a good topic to talk about when you're learning about ratios a standardized financial statements presents all items as in percentage terms balance sheet and income statement are the ones we're going to see most commonly right balance sheet items are shown as a percentage of total assets that means you have to compare every item on the balance sheet to total assets total assets will be the denominator income statement you show every single item as a percentage of sales or sales will always be the denominator now look at this isn't it ridiculous it's how we would do it by hand here's our income statement net sales cost of goods sold other expenses Abbott earnings before income and tax interest tax net income you take every one of these numbers and you divide it by the same number every time okay when we get over to Excel or absolute cell references are going to help us there so here's all of our answers well that's one right well because net sales by my net sales anything divided by itself is one and then you get these percentages now what is so beautiful about this for the income statement is that you can say for every \$1 of net sales or we have it listed as 25% let's go over here it's easier here for every \$1 we have of sales 25 cents is used up for cost of goods sold 15 cents is used up for other expenses 60 cents is our earnings before interest in tax 70 10 cents is our interest 17 senses our tax and 33 cents is our net income okay so usually when you get used to us you see these in percentages and you automatically think that so as you see this income statement percent is right for every \$1 that we sell in our store 33 cents is net income let's go see how to do this in Excel all right so here's that same little financial statement now remember every one of these numbers divided by the same number they're equals one two three cells to my left divided by B nine now from chapter zero zero remember that B nine we need to lock that so we hit our f4 key and it puts the two dollar signs and that means that's locked this one's always going to be a relative cell references copy now this one's locked so ctrl enter and then I'm going to do two things here I already have some formatting here so when I copy this down I'm going to point to my little fill handle when I see that crosshair click and drag now over here if I click you can see it got wiped out the formatting because when you copy a formula copies the formula and the formatting so I'm quickly going to point to my smart tag and say fill without formatting and now when I click over here you can see I retained my formatting and there you go that is just awesome not only is this great for saying things like 25 cents out of every dollar is consumed for cost of goods sold but you can also use these to make predictions right for next year now usually you don't base it on one year you take an average of many years but another great use for our common size financial statements other uses and for common size financial statements we'll look at going forward let's go back to our PDFs why do we use ratios to analyze financial statements there's a bunch of reasons we have one two three four five and then five we'll talk about common size financial statements the first one and the obvious one is we can see relationships between numbers so for example if we have net sales and net income right oh we just did this calculation right this was part of the common sized income statement our profit margin it's always going to be net income in dollars divided by net sales in dollars so 200 keeping the units divided by 5,000 keeping the unit's and what do we get for cents of net income for every \$1 of net sales that is another way we just talked about it from a slightly different point of view we said oh four cents of every dollar we sell is goes to net income but here we can see it from a slightly different point of view right one dollar of this yields four cents of that so net income so that means what four percent for every one dollar we sell we earn four cents in profit again that's looking here for every one dollar we sell of net sales we have four cents let's go over so and this is again seeing the relationship between numbers what are the two numbers every dollar that comes into the cash register what's the number up here every our net income or accounting earnings now I'm going to come over to excel all right let's do the calculation equals the two hundred of net income divided by our five thousand point zero four what does it mean too bad I can't type so four cents of net income for every \$1 of net sales all right number two these are the why we use financial ratios number two we can see trends over time without distortions of different numbers so here it is these are and ultimately ratios for us you can't really do a single ratio and have it mean much you do a ratio and then another ratio the next year and another ratio the next year and then you compare them and see the change or you do a ratio for your company and the average for the industry or your company and another company so here's an example internally we see cash in 2001 is 200 total assets in 2001 are 2000 but in 2008 a bunch of years later we have cash of 600 and our assets are 5,000 now look at this it's grown by three times 200 times 3 is 600 right so it looks like we have three times as much cash but forget it that's not really the case if we look at it in terms of all of our assets right when we do the ratio 2001 cash divided by 2001 total assets we ended up doing that means 200 divided by 2000 we got 10 cents cash for every \$1 asset or 10% here 600 divided by 5,000 we got 12 cents of cash for every \$1 of total asset or 12% so what it looks if we compare the naked numbers looks three times bigger but forget it in terms of the cash in relation to assets total assets its 10% compared to 12 so although the cash is three times bigger in 2008 we get a percentage change of not very much so although and you can see I did spell it right I hope I spelled it right you know the problem with these handwritten notes is I don't see the squiggly red line so although the cash amount of 2008 is three times bigger than 2001 as a percentage of total assets it's only gone from 10% to 12% there's not been much change now let's go over to excel again we're going to take two different ratios 2001 cash compared to total assets and again here 600 divided by 5000 and so the point here is that we can understand relationships between numbers better when we use these ratios instead of comparing just the same numbers let's look at another great example along the same exact lines we can compare small and big companies without the distortions of different numbers that's basically the same thing here here's Microsoft cash this is 2006 data 23 billion Microsoft total asset 63 Google cash 11 billion Google total assets 19 billion we can compare these two right here looks like Microsoft has way more cash now they do have more cash right but what about as a percent of total assets here we see Microsoft is at 37 23 divided by 23 divided by 6 degrees about 37 and 11/19 so Google in 2006 had 58% of their assets in cash they were probably on the hunt they were buying lots of new assets so they had a lot of cash that's that's a huge proportion now we mentioned this earlier but cash what can you do with cash if you put it in the bank what can you earn mm couple percent or maybe you have a lot you know billions of dollars you can put it and earn whatever the market rate is more than just your savings but that is so you earn 5% on 11 billion really what you want to do is you want to go out and buy your long-term assets those are the assets that determine your business and presumably earn a much higher return than put it in in the bank so when you see this much cash there there could be a couple reasons back then it presumably meant that they were on the hunt to buy more assets now during the financial crisis and after 2008 2009 2010 you see people with a lot of cash the reason why is they're still scared they're a Oh conserving cash because during the financial crisis everybody ran out of cash so they want to hold enough cash in case they get into trouble again and maybe during this period right now they're not sure what to go and buy so the point here is we can compare different companies with percentages let's go over here do that same thing I'm going to by the way click in this cell the number 23 is up there if you go to format cells right click format cells are control 1 you can see there's a custom format there anytime you put a 0 or if you want decimals you can put zero point zero zero if you actually put some text number this is number down here in custom if you actually put text the text will show up in the cell but not there we should not be too we shouldn't be too surprised by that because here's a number format we use all the time right that dollar sign appears there but the numbers not there all right so Microsoft equals 23 divided by 63 and Google is 11 divided by 19 number-5 sound like David Letterman right the top five reasons to use ratios racial analysis and common size financial statements are a convenient way to look at and compare financial statements for different companies across time many people use ratios and financial analysis and common size financial statements such as all of these people now just whoops I skipped over number for this example here is just a great example of why ratios and common size financial statements are so good because you can compare different size companies I skipped over four but number five it's just everyone uses this kind of analysis now remember fun as in Chapter two cash flow analysis and this cat Chapter three these are all were analyzing financial statements so all of these numbers that we get are just like the starting point if it seems normal maybe we don't look much further but anytime we see a difference or an exception called management-by-exception we see something different like we better go investigate but all these people use ratios creditors ask the question should we loan right they're using current ratio or something stockholders our managers doing a good job maybe profitability ratios auditors they use all sorts of ratios where do we need to look closely supplier should we extend credit employ should we work for this company stockbrokers should we invest investment banks how am I going to cheat again oh no investment banks should we underwrite underwrite means should we help them issue stock or issue debt that's one of the big things investment banker banks do research analysts is this a good company now research analysts are the ones that dig really deep but even research analysts they can start with a financial statement in some financial statement analysis and then they go and dig deep Financial Research Analyst I mean they go and interview anyone they're detectives just like auditor should be detectives contract writers every debt contract usually oh wait a second not every debt contract during the financial crisis they didn't really spend much time writing the contracts they just said okay I'll loan to you okay I'll loan to you remember they didn't really check to see whether people had enough funds to pay off but in normal times debt contracts for example have lots of covenants that means rules about what you have to do so for example they will have the current ratio and the debt contract will say if the current ratio gets below this then you're in default and you have to pay us it's not usually just the current ratio it's other things but that is one of the things that they can use now I skipped over number four we can compare financial statements that are in different currencies but be careful about the accounting method so if we have company one equity is this amount total assets is this amount so equity and total assets here equity is 500 total assets here a thousand pounds right these are in dollars these in pounds and now I rigged this one here so they come out the same but look at this when we do the division we get for the dollar one two dollars of total assets for every one dollar of equity this is called a leverage ratio because here's all of our assets here's our equity this one has to last it says debt plus equity so when you divided by equity says from every one dollar of equity how much assets do we have to so there's the how you get the meaning of it but when you do a straight percentage or like two hundred percent here the same thing in pounds you get the same ratio two hundred percent all this means is although the currencies are different we can see both companies have two units of assets for every one unit of equity in other words because of the use of debt total assets equals one debt plus one equity right if we broke it down simply right it really could be you know like 2 billion and then 1 billion debt 1 billion equity both corporations have leveraged up by 200% so you can see with ratios here that we can compare different currencies um let's see okay let's go over to excel do one last one I bet you when we do these ratios total assets divided by total equity and this one total assets divided by total equity now I put some fancy formatting there but that's just the meaning of it and then this doesn't have any formatting so I'm going to do that same calculation again I should get to here now equals and the same thing for the pound one now when we make formulas like this it actually does suck the formatting from the cell references right so if I want to see this without just as a number to which some ratio people like to see just the number no formatting I'm going to control one and then click general there's the number tab format cells number tab general if they want to see percent control one number this is format cells number and then percentage all right that was our introduction to ratios when we come back the next few videos we're going to go into great detail about different types of ratios like liquidity solvency profit etc alright see you next video test attribution text