Transcripts
1. Course Introduction: Hi, everyone. Welcome to the course. Thanks for signing up. Financial statement analysis in Excel on business company financial ports. My name is Chris Benjamin, Um, CFO, an MBA and most importantly, your instructor. For this course, we're gonna be learning a lot in this course we're gonna be downloading financial statements from companies were going to be running sort of those key indicators that you should be running on financial reports that get a really in depth analysis of the numbers. It's one thing to do, sort of compare numbers, you know, the top line or the net income of different periods. But when you actually start doing compared analysis and running ratios and seeing how previous periods stack up or maybe how one company compares to another certainly you can't compare maybe their net cash or their netting cos they're in totally different businesses, so we can break it down to sort of a common level denominator. And that's gonna be all these financial ratios that we work on through the course. So the course will be structured. We have this nice introduction video, the next video. I'm gonna do a little more introductory about me and my background and why I'm qualified to teach you. And then from there, the rest of the course will all be on screen in excel. We're gonna be working hands on going through these different financial reports. You can certainly follow along auras. Well, you can be working on your own. Financial announces that maybe some other companies that you're really interested in as we watch as you work through the course. So I'm really excited. The course turned out terrific. I encourage you do during the course, ask any questions that you have a message me through the through the website on as well. If you could go ahead and give me your feedback, leave a review. I greatly appreciate. I really enjoy reading those that it helps me be a better instructor for you. So that said, let's go ahead and get started on the course
2. Instructor Introduction: everyone little introduction to myself and sort of my backgrounds. They have a better idea of who your instructor is again. My name is Chris Benjamin. I have an MBA from the university Washington. I work as a CFO and as well I'll be your instructor. So the last 20 years I've spent in accounting and finance, that's my background. The 1st 10 I spent in sort of corporate life, if you will. I work for corporations. I worked myself up the ladder to the CFO level. At some point, though, I really had a passion for working with entrepreneurs. So I left the corporate life. I went into consulting on my own, working with start up growth stage companies, implementing all the types of things the CFO would do for a company. And maybe they weren't at the place where they needed a full time CFL. What they certainly could use my help on a part time basis, and that's what I've been doing the last 10 years. I really enjoyed it. I've had a lot of success in it, and I've seen companies go from idea stage all the way to being publicly traded. We've done that twice now, So definitely it's been a fantastic ride. And the best part is I get to take all the knowledge and learning that I've done over the last 20 years and now share it with you guys here through these courses. So that said, over the course of my career, I've done a lot of work in Excel, all kinds of different financial planning, financial analysis ratios, forecasting, you name it. I've pretty much done in an Excel. So that said, Let's dive in the rest of the course we're gonna be looking at Excel and going through the content, so buckle in, Let's get started.
3. Introduction To Excel: how low everybody. So before we get in the course, basically we're gonna be doing a lot of work and excel and using a couple different formulas. So I just want to get people a primer on the different formulas will be using before we jump in and start actually looking at numbers and and honestly, it's nothing too difficult. So if you're familiar with, you know, just sort of basic calculating a percentage is, you know, dividing one number by another, adding some numbers and then maybe dividing by other numbers, you go ahead and skip this video. But if you're you know, when a refresher or you're just not familiar with, let's go ahead and learn. So basically what I'm gonna do is just type in some random numbers. I mean, they're not gonna mean anything. Maybe we'll give them some labels just for just to give him some context. Uh, let's do revenue have spelled cost of goods sold. Let her profit or I used to be gross profit girls profit, and we have some ah packs some R and D total experience, and then that income. Okay, so let's just put in some numbers here. We'll call this 100 50. This will be this. Subtract this. Uh, let's do 2030. Actually wants to smaller And that 10 equal some. And I'm gonna show you some of these formulas here in a second. Okay, so this might be, um And I'm just gonna quickly do some formatting here, and then we're gonna get into more of what we're calculating. Andi. So this is like a very simple income statement, if you will. So, um, it was gonna make these dollars for fun. Okay, So we might download our data, and you know, we'll have something along these lines now, Um, So what we're gonna be doing is calculating a lot of ratios. That's the whole point of the course and then analysing numbers based on comparing one number two, another two previous period. Some of it will be on the balance sheet as well, or will be using numbers from the balance sheet and the the income statement at the same time. Ah, whatever the case might be, um, So, first of all, some of the numbers today that I put in if you're not familiar with with some we're adding and subtracting numbers, it's very simple. So, you know, we might see numbers like this. So here we have our 100 we have 50 cost of goods sold. So for a gross profit, its revenue less cost of goods sold the formula for that. So all formula start with equal sign and all cells reference based on the column and the row. So this is column F. This is row for So the cell is F four. So what? We're send the cell than a subsequently F five. So we're saying, here is take F four, which is the 100. Subtract at five. Hit, enter and simple is at Excel, then does the math. Um, little tip. If you're on any selling, you hit the F two button, it will bring up the edit box, which had also sort of color codes, and you'll notice like F fours and blue on this. Boxes and blue F five is inbred. This box is red, so you're easily able to see what's. You know what's being subtracted from what it might seem simple here, but if say, we had all kinds of different numbers or wasn't laid out is nicely it it really helps out to see it like that. Well, I'm here. We had a simple addition. So we could have Did you know, f A plus F nine, which would worked fine. But sometimes when you have a lot of numbers, it gets difficult to do, you know, contain Lee. You know, plus have 10 plus F 11. So there's a some formula. Uh, and this is basically it's so in time, you type equal some and then you a bracket, and then you put a range of numbers so you could put half eight f nine. You could put f eight F 50 whatever, whatever it is that you want to capture, Um, and that's it. Close brackets. So that helps. So that's our some formula. And then here we just did another upset attraction. We said our gross profit, which was this number lesser total expenses. And that equals 25. So nothing too crazy there. Um, So what? I did a bit of formatting as well. I don't know if you noticed if you have laid any cell So, like, we put this underline right here is all reformatting tools, basically, actually, this whole kind of area, um right here this box. If you use the drop down, it gives you borders, and it's a bottom borders when I select it. But there's all kinds of different ones, and then you'll notice down here. I did a single line with a double line. That's right here. Top line and double bottom border. Let's just click it on. There you go. Such typically used for sort of grand totals where sort of intermediary totals just have a line above or they might have an upper and a lower total are line. Sorry. Um, what else? So let's get to some just percentage wise. So, um so? Well, the other thing is, you know, it's that format, these $2. So right here is kind of your formatting of numbers. Um, there's a drop down so you can drop down, says English. So they actually pretty fine ones have you come up to custom and she's all kinds of things currency, accounting dates, etcetera. We're gonna be doing a lot with percentages. So but for these, I just hit the dollar sign, and then I didn't want to see the decimal points. So there's a decimal point sort of add or subtract ER, so we just moved it to the right a little bit now, so to create percentages. So let's some. So first of all, let's calculated percentage and then let's calculate a number. And then let's format. So it's a percentage to say. We just want to calculate net income as a percentage of revenue, for example. Um, a lot of times we might. There's a couple ways to do it. It's gonna delete these, Um, we could put our percentages over here or we could, you know, make little notes down here. Since we're just kind of doing some simple stuff here, I'm just going to stick things next to number. So first of left. So let's just gorkss profit as a percentage of revenue. Now we know it's 50%. I mean, that's fairly obvious, but let's do the math. So again, it's all formula based. So if we had equals, we want to divide this cell so you can click on the cell you could arrow over, Um, then we want divide. So it's just the forward slash by this number you enter. It's a simple Is that 0.5? Um, so the format that you see we have a handy percentage box or button here, then click that 50%. And then we can also use our decimals if we want to move it out. A lot of times we might just use one decimal point when it comes to percentages on the same thing here. So just using the same example we're going toe equals. And this time I happened Arrow over that divided by, and I'm gonna just scroll up here. So 25% Um, obviously, but let's do this. And that's it. It's a simple is that, um, most of our calculations are gonna be percentages. Um, actually, I think pretty much all of them may well be so really, there's not too much more to learn. You know that we conform at things to make it look pretty. But we're really focused here on just calculate the numbers and kind of interpreting them more than you know too much formatting numbers. So with that said, I think, um, I think we've seen enough. If anything else comes up during the course, we'll just learn it. As you know, as we go when we're doing different ratio analysis,
4. Downloading Financials from SEC: All right, so now that we're ready to get started, Um, first, we need to find some financials to analyze. So for this course, we're going to use Apple's 10-K that's their annual report. Um, there are public companies, so it is available on the Securities Exchange Commission website. What you see here, I'm gonna show you how to go about getting to it. And then how you download it will download right into excel. And then, um so that'll get us the file and then going forward, we can do all of our ratio analysis using their numbers. So, um, first of all, so at the main ah Soucy website, if you come down here, so you scroll down. So it's www dot sec dot gov The Securities and Exchange Commission. Um, Edgar. So Edgar is thesis TEM they use for company filing. So we want apple, which is a PPL hit. Enter now they have quite a few fines because, you know, this has every single sec filing that they dio they do quite a few 10 K's. I'm just eyeballing here. I know that came out with 10-K Here it is, right here. Um on 11. 03 17. So we're going to use that one. So, um, feel for, I mean encouraging. If you just want to follow along and watch the videos, that's terrific. If you want to follow along, I'd encourage you to come here and download this file. I'm actually gonna upload. So I'm gonna download this to Excel, and then go include the download as a downloadable file. Further course that will be attached to this lecture, if possible, cereal to just have the data. But this is where you would come to get pretty much any public companies information. So I'm gonna hit on interactive data. See, it's thinking here. Okay, so once this brings it up, you'll see it has different things. Documents, notes that set your We just want the financial statements. We have a financial statements, and then we go to view X. Let's just go to view Excel document, actually believe Let's see you excel. Document. Okay. And then, um, we'll see down here popped up on my browser, so it actually downloaded. I believe it downloads all of these financial statements. Not just the 1st 1 we clicked on. Um and I don't think I had to click on one, but nonetheless, let's take a look. So if we open this now, just a second. You see, that's our file from her learning about excel. Okay, so we're gonna hit. Ah, Enable? Let it. Okay, it looks like we got everything. So I see. You know, it has information about the filing. If we look at the tabs here, consolidated statement of operations and we can see that's full name up there, consolidate statements, comprehensive income, and we're actually going to use all of these statements. But system the point being is that we did get all of the financial statements. So what we'll do is actually, let's just do that. Now, let's go ahead, and it's gonna come to come scuttling across honestly how far this goes. Allow. There's actually quite a bit here. Yeah, there. Uh, so it is pretty much every single table that's in the 10-Q Um, so we don't need all these. So what I'm gonna do is I'm gonna delete out almost everything other than the first few tabs, which is the balance sheet. Ah, statement equity, which I'm not even sure that we're going to use and, um, the statement of operations. And we're just going to use this one. Um, so let's rather than have you watched me just delete tabs. What I'll do is I'll go and delete what we don't need. And then in the first video, you'll see what we have left. And I'll kind of point that out as well. And the file that I upload will already have, um, the things delete it that we're not going to use.
5. Price Earnings Ratio: all right, so let's go ahead and get started. So, um so before we get into the first calculation, which is price earnings ratio, I'll just give you a quick tour of what I did the spreadsheet cause in the last video we downloaded sort of the raw data. So I went through basically the four spreadsheets that we need, and I'll said, I believe we're only going to use the P and L and balance sheet, but I kept the statement of equity and statement of cash flows as well. They were four of the pretty much first few sheets. There was a couple other things in there that you could have deleted, and, um, and then there was a lot of information after I still basically any table that's in the 10-K was downloaded. So, um, and they had much longer names. It was consulting statement of Bubba. I've renamed the Tabs Profit and Loss Balance Sheet Equity Cash Flow Statements. So, and as we have through each of these, you'll see the full name and you'll know which tabs they are. But there's a lot of comprehensive income and other sheets that we just don't need um the other thing I've added is basically a worksheet for us. This is where we're gonna put all of our ratio. So I'm gonna include this. So the file that you can download Ah, initially in the course we'll look just like this does. Now it'll have these four spread Are these four sheets and this ratio analysis. And at the end of the course, we'll include one for download which has already populated with all right calculations, so you can look at each of those. So that said so. First of all, our first. So what is going to go one by born in each of the uses individual lecture in the course up , We're going to start the top and work our way down. Just another note. Some of these we might not have the data for 2015 because, um, even though the P and L is through 2015 the balance sheets only through, um for 2016 and 17. So some of these ratios just won't apply. Um, and that's okay, but we put in place holders for the many ways over here on the right. Have the calculation just just written out exactly what we're gonna be doing. So let's get started. Um, so, first of all, we're gonna do our price earnings ratio. Let me just tell you as well, like what that represents, if you will. And then we'll do the calculation. Um, so basically, it's the price you pay for $1 of earnings. You know, very general thumb is that shares trading at a low P E ratio or a good value, although the definition of low varies on the industry. So, um, and then what that will be really interesting to is why we're going to do several years, is we'll be able to compare and see, you know, did it go up? Did it go down? Um, that's really the value in doing financial analysis is to see how things have changed. So let's get started. Let's just start with 2017. So, first of all, what do we need? We need the price per share and the earnings per share. Well, I know where earnings per shares on the income statement Look that, but the price per share. So we actually have the closing price per share at each of these times. So what, we're gonna do is anything we need to add. So, um, so on her piano, we have, you know, all the data and actually, right here, we have earnings per share and, um which is the denominator for what we're doing. And then So I will use the basic If you're not familiar with diluted, that basically means, after all, options and everything else has been liquidated. But for our calculations, let's just designs were consistent. Let's use this. And I'm actually gonna gonna highlight this just so you can see what we're doing. And then as we do each calculate each different ratio, I'll start clean. Um, I'm just gonna Adaline because we actually end up using stock price again down later and other calculations. So we're just gonna add it to the sheets? Let's just say stock price as of closing date. So that means, you know, the so if we look up here on the data, so we'll get the September 30th 24th and 26. Okay, so let's start with September 20. And so I've gone to actually have it open here. So then, lots of different websites. You can get this data, but I just happened open the NASDAQ website. I fetched four years worth of data so we can scroll down and get the numbers quickly. And I have a look him up yet. So because I want to wait, So it's just scoot back here. So we need September 30th 2017 some 9 30 17 Which would be in course we can't tell here. Which is the closing price. Which column? Open, High, low. Close. Okay, So close. It's gonna be this column here September 30th. So we'll take the 29th. 154 12. Let's go back. Put that in here. 12. And I'm just gonna format these and then they are dollars. Okay, so then the next one we knew September 24th 2016 just gonna scroll down, because I can. They said September 24th which would actually be, I guess, here. So 1 12 71 And you see, it didn't change my so even if you know, Ed should be correct, since that's I'm kind of surprised they dated their financial that of that day, not the 30th but nonetheless, let's go with what they have. So 1 12 71 scroll down here. 71. And then I believe this is September 26 2015. Let's go down. 15. September 26. There's 25th. So they always seem to pick the Saturday Her Saturday? Exactly. Ah, the proctors. If there's so that they have a full weekend, I guess. Um, so 1 14 71 That's so what makes sense. Okay, so we see, not much change here and there. We do. Okay. So you know what? We have the data to do our analysis now, So let's do some calculations. So since all the number and again, let's just let's highlight anything we're using And then, um well, clear this. Each time we do a calculation and save this on just a note, our need saved mine. And, of course, I'm gonna be applauding them for you. Um, this one. Ah, the original one That's just blankets. Just called Apple 10-K 2017 financial statements. This one is called financial statements with ratio. So the completed one will have all the ratios in there. Um, OK, so let's do our calculations. So we said its price per share divided by earnings per share. So we're going to equals here and we can reference the other sheep. Um, so price per share, which is this divided by earnings per share, which is this we hit enter and there it iss now everything. I didn't go over in the Basic Excel kind of, ah, video that I made. We can just copy this formula across. And what it will do is it will reference. Um, so so right now, the formulas this divided by this. So if we copy our formula over one, it'll pick up this number. Delighted by this number, this number divided by this number. So it's really handy that these were just kind of laid out right next to each other. It works really well for this. So we're just gonna copy this Copy and paste. Okay. So we originally said it's sort of what you'd pay for $1 of earnings. So of course you noticed this has gone up in 2015. It was 12. 36th and 13. It's gone up drastically in 2017. So, you know, think of that as, um, being a strong indicator of apple. They're obviously doing better. Um So what means, though, that as an investor, you gonna pay a little bit more in order to buy, you know, a dollar of of their earnings. So that is pretty much, it insists. Simple. Is that because that's how financial analysis works. So that's R P ratio. And then oops, that mother, you know, popped up. All right, so let's, um, and this video here and then we'll move on to this peg ratio.
6. PEG Ratio: Alright, guys. So before we calculate the peg ratio, which is this video I want to clarify. In the previous city I had these formatted is dollars and I kind of talked as if they were dollars. Um, you know, really, it's more of a ratio. So what I've done is I format this whole table now, so it always just come up his numbers. And if we do need to change something to a percentage when I will do at the time, really, this won't be a dollar sign type of numbers. There really are ratio, but that the fact is still the same. We see a growing, uh, P ratio. So for the peg ratio, what we're going to do, First of all, let me just talk about it and basically what it is, essentially, we're gonna take the P ratio, which is here, and divide it by the projected annual growth in earnings per share. So there's a little bit of ah, you know, a subjective value there. But I'm was able to google and find out what that was for 2017 um, as well, So we're probably only gonna calculate for this year just a little bit harder to find what the projected growth was for previous years. Um, you know, long after the fact, but will come up with a ratio, Um, and essentially that. The general consensus is if this number comes out below one, the ratio that's considered a good value. So let's take a look and let's just see what happens. So I went and Googled what? Um, their projected growth would be, and I've already adhere. So I took out the yellows. Projected annual growth was 23.5%. Now, when we do our, um, our calculation over here, we don't do it as a percentage. We just divide 16.6 by 23.5. So let's just do that on May. We can certainly just click here divided by, and I was gonna type in 23.5 because it's formatted as a percentage. Let's ah, out 0.71 So perfect. So it's considered a good value. Um, again, if we had the forecast, it's here. That would be great, although there's not as much value here because I mean that the reality is this. Those years have already come and gone, so uh, not a ton of value there. And really, this is a very subjective based on this projected growth, which I found analysts of Apple had projected for, uh, you know, the 2017. So just know that calculating this number, what you're essentially looking for the sort of the benchmark would be one. So, you know, if the growth waas you know, lower than 16.6 then that you know, we would have a bigger number here over one, and it would have been the kid. It's not as good of a value. So we're lower number, better value.
7. Price to Sales Ratio: All right. So next we're gonna calculate the price, um, price to sales ratio. So let's just go back here. Hoops already done. A little bit of work on something. Um, so price to sales ratio. So what? The formula will just be a price per share divided by our annual sales per share. So we'll have to coca annual sales per share, But be fairly simple. It's just gonna be the annual sales divided by the number of shares. Eso no problem there. And then what we're essentially doing What we're saying is, what would it cost to buy $1 of sales? Basically, and we want to see the trend over time, This one again. I mean, a lot of these, and pretty much everything it will go without saying are useful in comparing as well the other companies and seeing. So we want to see how Apple's trended. But we also will see we won't do us much in this course. But if you're comparing apples, say, to Google or Microsoft, whoever, um, see what they're ratios like and who looks stronger from a ratio perspective. So, first of all, we need to calculate this annual sales per share? Um, no. One fact, I just want to mention this easy to forget and, you know, do some calculations and correct. So it says right here. It's so USD. So everything's in U S. Dollars. Um, the shares. Aaron 1000. So this number of shares Ah, but the dollars Aaron Millions. So that's gonna give us some wonky results if we use. We're dividing the dollars by the number of shares, and there are different basis. So what we're gonna do is just make these also in millions. And simple is it is we're just gonna delete The last three numbers will round up if we have to. So in this case, no need to round up. It's 2 42 this one, it's a 20. So we'll make that 471 and here we'll just leave it, Um, and honestly, you know whether it's $1 upper down won't matter to too much. So right now, everything is in millions. I'm not gonna change, you know the wording here, but just know that that's the reasoning for that. So and you'll see it when we calculate some percentages here. Um, so we did say we need it sales divided by a number of shares. Let's just do that. This is, ah, sales sales per share. So it's very simple. These will be dollars as well. So let's just make it dollars and the formulas fairly. Is this sales that sales divided by the number of shares? Remember these air, both now based in millions? So that works out perfectly. Okay, So for our formula now, so price. So we're gonna compare price per share, which we have over here are closing price divided by our sales per share. And I guess we did use those other numbers in sales for Charlotte's. Like those highlighted for now. Um, so real simple. So let's just do our master hurt equals, uh, price per share, which is this number divided by this number loops December, and we can copy that ratio across. And there we go again. We see you know, that growth a slight growth there, and then it kind of a jump there. So I think we're going to see a similar story. Um, you have to realize too. So right here we're calculating price ratios. So a lot these are going to do with the stock price, um, or dividends. And then we'll move on to other types of ratios as we move through the course. So there you go. That's a simple is that it's Ah, you know, in the indicative that the company is becoming more stronger at least, um, and you know, that's a fairly from 2.8 to 3.5. That's a decent point. Seven jump. That's a 25% jump from from here. Some, uh, quite good.
8. Price to Book Ratio: All right, so next we're gonna do a price to book ratio. So what? So what's book value? First book? That is, honestly, just the equity value. And we're gonna find that on our balance sheet. Um, so we'll have to first calculate book value per share as you see right there and then we divide by price per share and come up with this ratio. So first of all, let's some on the piano. I'm just even though it's a balance sheet number, I'm gonna keep all of our sort of calculations here. I'm gonna get rid of all these these yellows. Sometimes these there's to highlight everything, since we're certainly scratch. All right, so book value per share that way, all of our you know, little calculations were having to do to feed into the ratios are just all in one place. It's a book value for share. Fairly easy is just the book value, which is our equity. And I've actually already gone over here and highlighted it, and just to check these air also in million's everything is still in millions. So there's our equity and make sure you pick up just equity, not equity and liabilities. And we're gonna divide that by the number of shares, which is Ah, we didn't highlight it, but ah, shares used in computing is right here. And then we can copy that formula Cross. And remember, the balance sheet only has two years of data, so we're only gonna be able to do those two years. All right, so we used this. Just give it some hoops. Um, highlights. All right, so there's their book value per share. Now, remember, that's just the denominators. Now, to do our actual ratio, we're going to buy the price per share by book value per share. So let's just do that price per share divided by book value per share. And we can copy that over here. And there we go. So we see some growth there as well. Not surprised. And we don't have 2015 again. You could compare these, then toe other companies and see how strong they are relative to other competitors.
9. Dividend Yield: so next we can talk about dividend yield. So first, the calculation dividend yield is just differed in per share, which were, um, gonna have to look up and calculate divided by the price per share, which we have. So dividend yield kind of basically represents how the company is returning money to its shareholders. Um, this is really useful comparing to other companies just cause, you know, if one company doesn't pay any dividends and one does obviously the company paying dividends back, um has some value there for sure. So let's cocoa with these numbers are so first of all, we need dividend per share. So first of all, let's just put in. Um, first of all, we need to know what the dividends are. So we're just gonna write dividends, do their downs and then did and per share. Okay, so I again have Googled, and I didn't put them in yet, but let's ah, add it up. And turns out Google actually pays a dividend every quarter, so we're just gonna add their quarterly dividend. So for 2017 remember this cut off? Um, September 30th. So we're gonna add from between these dates for this dividend. So okay, and as well the other thing, actually, no, Never mind. Let's see. Let's add these up. So I'm looking on my other screen, etc. And they tend to be fairly consistent amounts. So they did. 0.63. They did 0.63 63 groups put plus in their 63 points 57. And actually actually 63 incorrect there. So it's 57 plus 570.57. Okay. Oops, I didn't put the plus sign. You can always go back and edit. Okay, now, for between September 26 15 and 16 let's see what they did. Um, I believe it's against 160.57 plus 0.57 vacancy. They kind of do 4/4 at the same amount. Um, then 0.52 then 0.52. Okay. And then for the year before that, um, again, it's 0.52 plus 0.52 Plus. Before that, the dividend was 0.47. Let's 0.47. So should be interesting. They obviously increased their dividend every year. Um, these air dollar amounts or let's just put that in. I had saved real quick assessor difference. The dividends per share. Feel easy. We just divide this oops formula. There we go to buy that by the number of shares, which is still highlighted. Now it's quite a bit low. Um, let's zoom out here. It's a fairly small amount per share. Actually, you know what? I'm actually incorrect. That is the dividend per share. That's not totaled evidence. Completely. I was not thinking Yes, that makes much more sense. Dividend per share. Okay. Sorry for the misleader there. We've been so used to calculating, you know, taking totals and dividing them buy shares that I got carried away. All right, so these are the dividends, for sure. So now for our calculation, what is it? It's dividend per share, divided by price per share. Let's do that equals dividend per share, divided by price per share. You know, it's gonna be a fairly small number on this. Lonely will have toe zoom out. Okay, So one thing that is interesting is it actually decreased. Um, so even though. And this is kind of where the interesting part of racial announces comes in sometimes. Um, you know, even though the dividend per share increased so that's a good you know factor. The actual number shares decreased with apple, and that actually decreased over the years. And the reason is they start doing stock buyback. They want to reduce the number of shares out there on the market. Um, so here we have this increase, But, um, because of the way the dividends air out, that actually reduced the dividend yield. So that is something interest that you would find that if you were just looking at these numbers, you would never, never kind of put that together. At least I don't think intuitively you would just kind of look out, you know, dividends or increasing. That's a terrific sign. Although your actual dividend yield, uh, you know, kind of going down a little bit.
10. Dividend Payout Ratio: All right, so next is air dividend payout ratio. So it's really simply just trying to compare. You know how much money you know, the company made X amount in that income. How much of that did they then turn around and pay out as dividends? So, um, so we're gonna need the total dividend amount, and, you know, we're just going to basically kind of rough it, if you will. Where did can assume because the number of shares would have changed throughout the year. If you remember, this was a quarterly dividend. Um, added up, but we used the serve year ending number of shares. Um, and that's okay. I mean, we're just trying to get some nice Ruff rough, but somewhat accurate estimates time. But I don't believe it's worth e the effort to go on calculate quarter by quarter. What? That is Certainly if you want to get to the, you know sent would encourage you to, but for our purposes, this should works. Let's we did the total dividend and the way we're going to do that. It's just the dividend per share, times, the number of shares, and then we'll go across and do that and again because we have everything in, Well, let's move this back, um, been million's. This works out perfectly for comparison purposes. So now we need to if you remember our dividend payout ratios. So dividend divided by net income. It's fairly straightforward on this one. Hopefully, appreciate now, actually, financial ratio analysis and doing the math is not, um, not that if you wanted to net income. Sorry. So it's down here and then we can copy that across. So so 0.3. So are let's even just expand that one just for fun. Okay, so point to 1.26 point 26 So you know, they 1/4 of their income they paid out as dividends When I start to say is I hope you appreciate it's actually not that. I mean, doing the mass is not difficult. It's really just a lot of dividing, maybe having to calculate a few numbers like these, but the calculations aren't that hard. It's really then taking these numbers and interpreting them as to what's good and what's bad. Um, it's up for all things I would say for us look fairly strong. Everything seems to be increasing this one held consistent, which is a bad. I still think it's a fairly okay, Devon, pay. They're paying 25% of the and I didn't come out as dividends to the shareholders and consistently last two years. The only thing that kind of stood out a little bit with the dividend yield, um, went down slightly then. What part of that, too, is a factor of fact that they are doing that is shared by back. So, um yes, dividend payout looks good. So in the next ah, videos, we're going to start to turn the profitability ratios.
11. Return on Assets: Okay, so let's turn to these profitability ratios now and see what we come up with. So next three videos will do return on assets, return on equity and profit margin. So I'm starting off these to return on assets and return on equity were basically calculating how well the company does how efficient it is using its assets are its equity to make net income. And obviously we're looking for an increase in that ratio over time would be the what we want to see now. Since this is based on a balance sheet item, we're only gonna have two years based on the data. We download it. But that's OK. Certainly again, you know you can do with these ratios. You know, if you wanted to go and download, um, you know, 10 years worth of data you could and then obviously get much more of a trend. So it's fairly simple. Its net income, which we have, and it's average assets were actually for our purposes. We're just going to use the ending assets, and we're going to use the ending shareholders equity. If you wanted to go back and do calculate averages, you're kind of at least need the beginning and the ending amounts. And that's part of why we're just going to go with the ending amount, because we don't have 2015 to calculate an average growth. Um, let's just see what they did with those numbers. So real simple. So we have our numbers. Let's just highlight them first. So, net income, um, which is here, So we can actually probably unhygienic this now. I think we're were done with share calculations. Okay, that's all that was there. And then, um, we said total assets, and then I'm gonna go ahead and highlight till, like, we're actually still highlighted from a few videos ago. I'm just that will be used in the next video, so all right, so just it's a simple Isn't that income divided by, uh, average assets, or, in our case, just assets. So that income divided by total assets and then the same thing, We can actually just copy this across. If you ever want to check these, you could look, please, upsy 15. C 15. I'm gonna get stereo C 15 and C 15. So the formulas calculate correct anything to you? Notice a big discrepancy if it was picking up, you know, a completely different number. I'm gonna go ahead, just move these out and see what happens. Decimal points, that is. So it actually decreased a little bit of a decrease here in 2017 on the return on assets. So whatever the case might be there, maybe they invested heaven. And one thing I didn't notice was the assets did go up quite a bit, So that might be were using the average, you know, would be beneficial. You know, the average here would have been somewhere around 350 ish roughly, maybe just a little bit less. And who knows what it would have been here. Maybe it was consistent, so this number would have actually dropped, and this ratio would have been up a bit, so we're not totally sure on that one. Uh, but for our purposes here and since we don't have the 20 15 number readily available, uh, these are numbers that were coming up with, but at least you know now how to calculate it.
12. Return on Equity: okay, Similar idea here. Return on equity. So it's very similar calculation. So that income divided by stockholders equity again, we're just gonna use the ending equity. Um, and again, it'll shows how efficiently the company is using the equity to turn it into night income. So let's just do our calculation. I'm no, we already highlighted everything. So it's gonna be net income just here, divided by our equity, which is down here. And there wasn't too much, but change percentage wise and equity, so using the totals wouldn't be too too drastic. Okay, so let's just expand these as well. Okay, Slight increase, you know, good. That's a good sign. But I would say overall, not a not a lot of growth there. Honestly, 0.5 isn't quite anything to get too excited about. So, um, if anything, we can see that it's consistent again. These ratios are really valuable than when comparing to, say, another company, especially in the same industry and seeing how they have done so that encouraged to do that And honestly, the pride the best way to go about this would be just to completely populate something like this. Save it and then go run the same exercise. And maybe that could be your exercises. Go do all these ratios for a different company that you're interested in and then compare them back to, um, apples ratios.
13. Profit Margin: Okay, so next is profit margin. I think a lot of people of any of these ratios is probably one of people are most just familiar with. Basically, you're trying to figure out. I mean, it's your net income divided by your sales. You're trying to see how much of those total sales actually flow to the bottom line at the end of the year. Um, since it's all income statement based weaken do all three years, so we're gonna need that income, and we need total sales. All right, so let's ah, let's do some calculations here and see what happens. Okay, so it's our net income divided by our total sales. Again, we'll be able to just count copy that formula across. All right, we'll get some point tooth. So let's take a look and see what happens. Looks like they're fairly consistent. Had a bit of a drop here, but then between 2016 2017 I mean, very intangible. Ah, amount. Here. That a change. So, um, interpret this how you want. I mean, basically, they're still making 21% profit emergence. Not too bad. I would say 2123. Definitely like a few percentage points lower here. Ah, and for whatever reasons that might be. And that might be, You know, where you use your numbers you wanted. Then go and investigate and see. You know why we're profit margins shrinking. Ah, problem right is definitely number. That's than comparable to other companies. See how well there, you know, managing to get those net sales down to the bottom line at the end of the day. So, again, I encourage you to do that. Uh, compare this to another company and then be really interesting to see sort of who comes out on top when you sort of grade them, if you will and say, Well, apple's better at this, Um, you know there about the other companies better at return on assets or whatever it might be .
14. Current Ratio: All right. So the next two ratios air liquidity ratios, they're all gonna be off the balance sheet. Um, the 1st 1 is the current ratio, which we're gonna do now again. Well, we have two years of data. Basically, it's eso its current assets divided by current liabilities. See here formula essentially, what it does is it will represent the company's ability to pay its short term liabilities. So, for example, without doing any math, you know, say the company has, you know, $100 Morgan is very simple numbers $100 incurred assets and has $50 current liabilities. Well, you know that the company could pay all of its liabilities and still have some left over. Um, and generally that's a general rule. Time is like a 2 to 1 ratio is considered pretty good. You could pay your current liabilities twice over if you had to. Um, if the numbers below one that's obviously in the kid of a problem, you know, if all your current liabilities come, do which by the nature, you know, current liabilities are due within the next year. The company doesn't have enough current assets to pay those So let's see what number shake out. So it's very simple. Current assets, Current liabilities. Let's just go over here and highlight those. I'm gonna unhappily this just so it's not confusing, um, current. Okay, so we have total assets here, but that's not what we want. We want total current assets, and you'll see the number is on here. It's have to kind of hunt for it. And then same thing with liabilities. Total current liabilities. So see the value here and kind of highlight on the numbers, just that we don't get confused. Okay, so it's calculate current assets divided by current liabilities, Knesset's current liabilities, and can only see if you're looking at the number. They'll both be over one. But they're not necessary over to all right, and actually their ability. Let's just look at a few more decimal points just for fun. Um, we can leave it to. So they went down a bit. Not drastically, but still, you know, and they do have enough to cover the current liabilities. So it's not a bad thing. Um, no, it's not two times but the same time my argument could be made that if you had two times your current liabilities sitting and current assets. Maybe you're, you know, not properly using your money. You could be using that on other things which aren't current assets. Uhm so you know, and Apple might have set the standard. They might have their own internal sort of rules, if you will, or sort of ratio that they're looking for. So this might be perfectly acceptable for them. Um, at the end of day, we at least know as shareholders, investors or analysts that they could certainly pay their current liabilities if they were all to come due. Ah.
15. Quick Ratio: all right. So next we'll calculate the quick ratio, which is similar nature of the current ratio. Let's look here. So it's current assets, but less inventory divided by current liabilities. So the thought here is it's even a bit more conservative. The fact that saying well out of your current assets part of that is inventory and inventory really isn't quickly sold. You can't just turn inventory into cash like overnight. It takes several weeks, several months to sell off inventory. I'm sort of really looking at sort of more liquid current assets divided by your current liabilities. I'm so first of all, we'll have to, um, do this current assets less inventory, and we can actually just do it within the formula. Um, let's do that and I'll show you how you can do that. So first, let's go to the balance. See in this highlight our inventories, which are here, okay, and it looks like they're not a big amount. So we already kind of know that it's not going to make a big a huge impact on the number. But let's do the mass. So, um okay, so what we'll do is we'll make a bracket So we're gonna you know, it's kind of like the basic math rule. So we're gonna take our current assets, We're going to subtract our inventory, We're gonna close the bracket, so that calculation will happen first. Then we're gonna divide by our current liabilities. Simple. Is that so? Just going back to that formula, and I'm gonna just make these bit bigger. So it did drop in? Not too much. So this formula we have our current assets Subtract er inventory. That happens first because of the brackets. Then divide by the current liabilities. We can copy that across, and there we go. So we see for each each of the each of the years, that actually did not have to too much of an impact, which is a good sign. That's a terrific science. Even without inventory, they have a lot of current assets that they can use to cover those current liabilities. So not too much in risk. There
16. Debt to Equity Ratio: all right, so liquidity ratios look pretty good. We're happy with those next week. A move on the some debt ratios on the 1st 1 is debt to equity ratio. So the calculation very straightforward. It's off the balance sheet again. Your total liabilities divided by your shareholders. Equity. Um, basically, just comparing, you know, everything that you kind of owe to what the you know, the equity in the company is, um, and if that number you know is growing or substantially large, the ratio, it just means that the company's more and more subject to risk. You know, you're carrying a heavy debt load that you might not be able to pay, so let's just let's see what the ratio shakes out to be. Um, first, let's just go to the balance sheet and actually, let's, um, just get rid of the highlights first and kind of start fresh. So it's gonna be told live debt, which is liabilities divided by what's high with that yellow first divided by your total equity. Go. So we go back here, so it's our debt divided buyer equity and that we can copy that. Okay, so, um, we can certainly expand this. We do see it's gone up some. So that's one. The first thing we notice, Um, decent amount about, you know, 10.3, um, which is about 20% gross. So that's actually, you know, remarkable from here to here and then as well, you know, just doesn't the number that you know the debt or the liabilities of our company or 1.8 times the amount of equity current in the current year. Um, different industries have different benchmarks, so it's not necessarily a bad thing. Honestly, very seldom would you see a company that has very little, um, liabilities or debt. Compare the requisite Let's just take a look. I'm just curious. So we haven't done much of this, but see where the big areas there. So the totals to 41. Well, 100. Here's and current liabilities. I was sorry. That's the total. So let's see what that's made up of accounts payables, and they all bunch money, accrued expenses and then long term debt. So they have some long term dead. Um, certainly not do any time soon. Then if it's, um, a longer term debt, eso probably okay. Nothing to two concerning there. And I think I started to say to you tell them to see this up below one. You're not gonna see too many companies that have such a low debt or liabilities on the work. She does that say, um, that their their equity exceeds. And again, we're starting on the word debt and it is dead. But it's not, you know, don't think debt just in terms of loans. It's things just like accounts payable that's fairly standard part of business or crude expenses may be accrued payroll, things like that. So I'm not always necessarily bad, uh, to have you debt on the balance sheet.
17. Interest Coverage Ratio: Okay, so next is interest coverage ratio. So first of all, what is it? So it's e b i t. Which is earnings before income tax, which will find on the profit and loss divided by interest expense. So this ratio just really wants to check that your earnings before you have to pay out your income taxes is enough to cover your interest expenses. So again, if you covered, if you carried a lot of debt, had a lot of interest payments, you want to make sure that you can cover those interests. I should say the company can cover those interest payments up. Um, I took a look. So let's take the piano so they actually don't break out income. Her interest expense on here. Um, just I mean, this is a little bit summarized. So what I did is I went on and actually Googled and found the found the more breakdown version of this thespian L. So I've already written it down here just in the interest saving time. So, again, this is in millions. What's this is 2.32 billion, if you will, etcetera. So that's the interest expense for the year moms will highlight that. And then we're using our earnings. Um, e b I t. Which that actually don't call out. That's right. Hearings income. So income before prevented for income tax, if you will. Some people might use just operating income. Um, they have other income and expense. Um, you know, by definition of formula, says FBI T which would be this number. So let's go ahead and just use that. So, um, it's R e B I t divided by interest expense. Very simple again. No, this formulas aren't definitely has not been any difficult formulas. I would say it's just a matter of calculations. Okay, so we have. Ah, well, a couple things going here. One, um, certainly can cover it. You know, their earnings is 27.6 times the interest expense. So that's not an issue. Uh, what does? Kind of honest? Just the decline. I won't take a look at the numbers really quick. Okay, So part of it, too, is that, um, interesting expenses definitely gone up. You know, it's like, three times the amount it was three years ago for two years prior, I guess. And let's see. And they're actually b I t went down. So in this year, why that number is so, so big. So 98 compared to these is because we had, ah, you know, higher income. And then we had a much lower interest expense. So they've definitely done activities taking on debt that they're now paying interest on, Uh, and at the same time, their income has dropped. So it's actually an interesting, you know, you kinda have to Then start to think about that. So what What was the purpose of this debt that they've taken on that they're paying interest on? So it's not just dead, but it's whatever they're paying interest on. Um, you know, But it's obviously not turning a return in terms of e by T b I t has actually gone down, so I would definitely be something worth comparing. Um, you know, to other companies and also maybe just doing a bit of fishing around and seeing, uh you know what would be interesting about that? So, out of the, you know, the ratios so far, Woodcock it, I'd say everything kind of turned out pretty normal. You know, we're OK with everything. This is one first one that. I see. That's kind of you know, I understand why this number is big goofy. Um, but at the same time, there is such a drastic change. I mean, this is almost 1/4 of the mount, um, of coverage. So there's definitely been a drastic change that would, at a minimum, that be something that I would wanna watch in the future And just see, you know, how this number goes if it continued to dip down certainly would be somewhat alarming.
18. Asset Turnover Ratio: All right, everybody. We're down to our final two ratios, and it's the efficiency ratio. So you know how efficient they are using first of all their assets and then their inventory to generate sales. Um, so, actually, actually, second is just how efficient they used their inventory in order to turn it over. Nonetheless, 1st 1 let's look at Asset Turner's raise turnover ratio, So its sales divided by total average assets. Again, we're just gonna use total assets at the end of the year just because of lack of data. Um, and since we really only have two years here, so it should be fairly simple sales divided by assets. So ah, we'll be using both of these. So, um, let's get rid of some highlights here on we don't need this anymore. We do need sales. And while we're here, I'm actually gonna highlight cost to get sold, because I know that's what's used in the next video. And then we said it was Let's unhygienic the stuff. Oops, that's our total assets. So which is here? Okay, so our sales divided by our total assets. So let's take a look. See what numbers kind of come up from the soup sales is here divided by assets here. I'm not sure that quite worked. I think. Yeah, I lost my formula somewhere in there. Okay, let's try that again. Total sales divided by total essence again. You see how handy it is when you highlight everything. It just makes it so much quicker. Toe, uh, two spot numbers and just you know, when you're doing your form will make sure you click the right number. Okay, so we have a bit of a drop again. You know, we didn't actually do the average. So And like we said, the average for this year was probably somewhere around 3 50 a little bit lower. We don't know what the average would have been here. We don't know what this started with. Um, it could have been 3 21 Or maybe they only had, you know, 200,000. We're not sure. So, um, for based on the information that we do have, you know, these numbers that were coming up with their turning their assets, it's a little bit less than efficient here. Um, but I'm not completely sure. I mean, I'm not 100% relying on this ratio red flagging it just because I'm not quite using that the average assets. So, um, a part of life. So we did have a big increase in the asset base, but in terms of sales, you know, had some increase in sales. But not quite, you know, dramatic percentage wise. So is really that dramatic increase in assets that is causing this ratio to dip down, which I wouldn't be too too concerned about.
19. Inventory Turnover Ratio: All right, everybody, We're down to our last ah, last ratios. Let's run it. And then maybe just take a quick gander again just to sum up all these was this is inventory turnover ratio. So it's your cost of goods sold. Divide by your average inventory or we're just gonna take your and inventory. It sort of represents how quickly you're turning your inventory over. So I mean, that's obviously the name of it. Ah, the higher the number, the better. That means you're turning it over. You know, as much as you can and really been comparing it to the previous period. So again, we only have two years and skins to me. Cost of goods sold, divided by average inventory. Let's just go fetch those hoops. Didn't quite taking on a sec. There we go. So your cost of goods sold. We highlighted already is here divided by your average inventory, which we actually didn't highlight. But since we're mid formula, let's grab it. It's right here. And then we can just copy that now. So a couple things again, um, drastic. I mean, less than half. I mean, it's yeah, just slightly less than half the amount, but again, we never did calculate average inventory. And I'm just gonna highlight that now, Um and we do see, I mean, inventory is more than doubled this year, so Ah, one place to go would be in the tank and actually read the footnotes and see what they said about inventory. If there's a reason, Um, why, that was, You know, maybe, you know, they had a big product launch coming up. And I know they definitely launch products in September October, so they might have just been carrying a substantial inventory which later translated into a lot more sales. That's quite possible. And we also don't know what the inventory was here to start with. So ah, you know, can't 100% rely on this formula? Um, I would, at least and then the other part of that was cost of goods sold. Let's take a look. So their cost of goods sold do not go up too much drastically. But what did go up was their inventory self. My guess would be they were ramping up building up inventories for the holiday season, more so than here. So if anything based on this, I would consider it may be a good indicator. They're building up a solid inventory base because they plan on selling all that that inventory over the holiday season. So that said, guys, that there's our ratios, you know, we we put them together. So, um, I hope you found this course useful. And actually, let's go ahead. Let's end the city on actual do a final wrap up video for you.
20. Course Conclusion: Congratulations, everybody. You made it through the course. I'm really proud of you. There's a lot of content in there. We covered a lot of different things. If you feel the need to go back and maybe rewatch one of the two of the lectures and there's concepts you didn't get, you know, go back. We watch them and or just send me a question as well through the website. I'm happy to answer any questions. So now that you have all these financial announced, this ratio is ready to go. You have a better handle of excel. Get out there, encourage you to go ahead, download some other companies, financial statements and put your knowledge toe work. I really enjoyed teaching the course. I hope to see you in some of my other courses as well. Be sure to check them out. There's lots of great stuff that I've been producing over the last several years. That's it for me. I'm Chris Benjamin. Glad to be your instructor on the course. Have a great day