Financial Ratio Analysis using Microsoft Excel | Ray Harkins | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Financial Ratio Analysis using Microsoft Excel

teacher avatar Ray Harkins, Senior manufacturing professional

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

17 Lessons (1h 49m)
    • 1. F 01 Introduction to the Course

      7:28
    • 2. F 02 Intro to Financial Ratios

      4:46
    • 3. F 03 Simple example

      5:34
    • 4. F 04 Intro to Profitability, Rev 2

      2:24
    • 5. F 05 Profitability Formulas

      8:28
    • 6. F 06 Profitability Analysis in Excel

      7:43
    • 7. F 07 Intro to Liquidity

      1:56
    • 8. F 08 Liquidity Formulas

      7:13
    • 9. F 09 Liquidity Analysis in Excel

      5:35
    • 10. F 10 Intro to Solvency

      2:57
    • 11. F 11 Solvency Formulas

      8:32
    • 12. F 12 Solvency Analysis in Excel, Rev 2

      11:38
    • 13. F 13 Intro to Efficiency

      3:03
    • 14. F 14 Efficiency Formulas

      12:28
    • 15. F 15 Efficiency Analysis in Excel

      10:00
    • 16. F 16 Operating Cycle Analysis in Excel

      5:43
    • 17. F 17 Conclusion to Course

      3:07
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

29

Students

--

Projects

About This Class

In this class, you will learn how to extract useful meaning and insights from a company's financial statements. After all, financial reporting is just a bunch of numbers if you can't find those valuable insights to make the best decisions for your organization.

Building on a basic knowledge of the four main financial statements - the balance sheet, income statement, cash flow statement, and statement of owners' equity - this class will show you how to pull specific data from those documents to derive informative ratios.

For instance, by dividing the "Cost of Goods Sold" found on an income statement by the "Average Inventory" found on a balance sheet, you can derive the "Inventory Turnover Ratio". This ratio tells you how many times per year your total inventory is sold to your customer in the form of products. A high ratio indicates an efficient use of your inventory. A low ratio indicates potential problems like obsolescence, slow moving inventory, over-buying, etc.

Calculating these financial ratios and using them to compare one company to another, or one year to the previous year, is a remarkably effective way to diagnosis the strengths and weaknesses of a company.

Lastly, you don't have to be a financial wizard to take this class or to use financial ratios in a useful way at your organization. The material in this class is accessible to someone with a basic understanding of accounting, yet what you learn will benefit you regardless of your title. Business owners, department managers, project engineers, administrators, personal investors, educators and a host of of career professionals will gain in this class skills that will help advance their careers.

Sign up today to take your business finance skills to the next level!!

Meet Your Teacher

Teacher Profile Image

Ray Harkins

Senior manufacturing professional

Teacher

Ray Harkins is a senior manufacturing professional with 25 years experience in manufacturing engineering, quality management, and business analysis.  During his career, he has toured hundreds of manufacturing facilities and worked with leading industry professionals throughout North America and Japan.  He is a senior member of the American Society of Quality, and holds their Quality Engineering, Quality Auditing and Calibration Technician certifications.  Ray has written extensively for national trade publications on the topics of quality engineering and career management.

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. F 01 Introduction to the Course: Hello, welcome to the Manufacturing Academy. My name is Ray Hopkins and this is the introductory video to the Skillshare class titled financial ratio analysis using Microsoft Excel. Thank you so much for stopping by. In my career as a manufacturing professional, I've worked as a quality manager and an engineer and a production supervisor and other roles, but I've never worked in finance. I'm not a CPA, I'm not a CFO, I'm not a C anything. I am more of an engineer and a project manager. And up until really a few years ago, I assumed that just like the accountants leave the engineering up to the engineers, that people like me, engineers and Quality Manager and stuff left the finances to the accounting and finance professionals. But I didn't realize until again recently how beneficial it is for a non-finance professionals to understand business finances, not to lead the organization that area, but to understand the data. They understand the financial strategies and how money flows through an organization. So that in your field of expertise, you can make the best decisions possible. And you can only understand what those decisions are if you understand the financial implications. If you're a professional like me, you care about your career, you care about your organization and the decisions you make. So this understanding business finance is another tool in your toolkit that will allow you to excel and to do a good job and advance yourself in your career and your organization. So what I'm offering you here in this class is an understanding of these so-called financial ratios. So if you have even mild understanding of business accounting, you might know that there are four main financial statements. The balance sheet, the income statement, the cashflow statement, and the statement of owner's equity. Now, I'm not expecting you to have a solid foundation in accounting to take this class, it would help. But the little bits that, you know, I think I walk you through pretty well. If not, you might be able to find a basics of business accounting class on Skillshare that would help you gain the understanding that you're looking for. Nonetheless, if you're, have some basic understanding of these four main financial statements, you'll know that there they describe the financial activities of an organization. A balance sheet, for instance, lists the assets, the liabilities, and the equities of an organization. So the, the problem is, even if you're familiar with these statements, is they're just a bunch of numbers. Unless you can extract useful insight from these statements, it's just going to be a lot of numbers. So what financial ratio analysis does is combines certain pieces of data off of these various statements to tell you something about the organization. A very simple example that's commonly heard, probably asset turnover or inventory turnover is a common term. This is a financial ratio when you know your costs of goods sold off your income statement. In other words, how much stuff you had to purchase in order to sell the products that you sold at your cost of goods sold. If you can take the cost of goods sold off of your income statement. Combine it with your average inventory that's found on your balance sheet. Combine these two together. Cogs, cost of goods sold divided by average inventory equals this. In this asset turnover, inventory turnover sometimes just called the turnover ratio. So why is that important? Because if you understand that, that all of this inventory you have in house is intended to manufacture all this product that you're selling? Well by dividing costs of goods sold by average inventory, you'll see how many times per year you turnover or sell through your inventory. The higher the number, gender, generally the better it is. It's means you're, you're making more efficient use of your inventory assets. So as you compare, for example, your inventory turnover ratio, year over year or from this company, that company, you start understanding your own organization's strengths and weaknesses and it helps you to make decisions about purchases, inventory, obsolescence, economic order quantities, you're all these different things. It helps you, if you're a manufacturing manager, purchasing manager, it helps you make better decisions about you, about your organization, go about doing your jobs. You're not going to get that unless you understand something about business finance. So, so this inventory turnover ratio is just one example. It's calling the efficiency ratio how efficiently you're utilizing your assets. But there are four, there's a total of four categories of financial ratios that I'm going to share with you and explain. The first is called profitability. Basically, how, how effectively are you turning revenue into bottom-line earnings? The next area wouldn't be illiquidity. Essentially, how how capable are you of meeting your short-term obligations with short-term assets? The next area would be solvency, which is really a measure of your organization's financial structure. And then lastly, as I already mentioned, would be efficiency. So these are areas that I'm going to address in this class. Don't feel intimidated by the numbers are the terms or the concepts. I'm going to walk you through these various concepts. I'm going to make it really accessible and I'm going to bring the formulas and the concepts into Microsoft Excel so you can use these formulas, practically speaking, on your desktop with your data, your organization, your side gig or business. So that's what I'm going to offer you in this class. By the time you're done, you're going to have a better understanding of how to use these financial documents, how to, how to analyze your organization, how to extract this data so that you can make better decisions on behalf of your organization. That's what I'm going to offer you here. So thank you so much for joining me for this. At the end, at the very end of this class, there is a kind of a final lecture and a project. So you can look to that final lecture first of all, to download and extract your the financial statements and the documents that I use is kind of case studies through his class. But then there's going to be a project at the end for you to put these newly found skills to work at and kind of practice what you just learned. So anyway, thank you again for checking out this class. I sincerely hope you sign up for, I promise you that the information I'm offering you here will benefit you in your career at your organization regardless of what you do. So again, thank you for joining me, sign up for the class. I'd love to hear from you, interact with you, and work with you as we continue on this learning journey. Thank you. 2. F 02 Intro to Financial Ratios: Okay, Well let's dig into these financial ratios and financial ratio analysis. So calculating and examining financial ratios, interpreting financial ratios is a key part of this broader topic, financial analysis. So financial ratios are calculated by comparing different accounts, or we could say different values that you find on the various financial statements. So financial ratios are used to compare a company's performance from one year to the next. It can also be used to compare a company's performance before and after a managerial decision, maybe a downsizing or maybe the issuance of more stock, basically an expansion of a company's equity or maybe some sort of restructuring. So before, after, you want to know what was the result of this big action that we took. They can also be used to compare one company to another. And they can be used for a lot of other things too. So financial ratios are used to compare different things. Even if you think about comparing a company from year over year. Even companies, it's the same company, same obviously Industry, same product, same all this other stuff. So what happened from one year to the next? Well, maybe their revenues were higher or lower. Their costs were slightly different. Some of their customers shifted around their product mix, change the way they pay, or again, the cost or the quantity of parts they needed. So even though one company from one year to the next is generally pretty similar, there's still enough differences that financial ratios kind of level the playing field and allow you to do a fair comparison. So financial ratios are generally grouped into four categories. Profitability, liquidity, solvency, and efficiency. And these are the four areas that we're going to examine more carefully in this section. So profitability measures an organization's ability to extract profits out of the revenues after all the costs are after all the expenses, whatever left is left over, the profits. So profitability compares those profits to other things like revenues and assets. Liquidity measures an organization's ability to pay off its short-term debt. It looks at the assets that are very quickly converted into cash and can be used to pay off its short-term loans, pay off its suppliers for supplies that it's purchasing and take care of all of its short-term obligations. Next, we have what are called solvency or leverage ratios. When we're talking about solvency, we're talking about the organization's ability to pay off all of its debts and even the word insolvent, you may have heard that before. Insolvent basically means you can't pay off your debts. So solvency measures and ability, the ability of an organization to pay off all of its debts will using all of the same inputs of equity, of debt, et cetera. The same ratios can be thought of as leverage ratios. These, when thought of as leverage ratios, measure an organization's financing of its assets. As we learned in the accounting section, all of a company's assets are financed either through debt or through equity. While the interplay between debt and equity is referred to as a company's leverage. So these leverage ratios or solvency ratios measure how a company goes about financing its assets. And then the fourth group is efficiency ratios. And this is a measure of how quickly an organization converts its short-term assets into revenue. We're talking about inventory here, we're talking about accounts receivable, accounts payable. These are short-term measures of not exactly cashflow, but short-term assets, how they flow through an organization, and how they're converted back into cash that the company has to use. So these are the four major categories of financial ratios, and we're gonna take a look at each one in detail next. 3. F 03 Simple example: Okay, As we already learned, financial ratios are tools that allow us to compare two different things. Sometimes it's two different companies, two different years for the same company, two different management strategies, whatever it is, we're comparing two different things. And financial ratios allow us to level the playing ground somewhat so we can do a fair comparison. Financial ratios are one important input, but just one input into comparing two very different things. So I was at the grocery store the other day and I bet you, you've used financial ratios just like me in comparing two different things. This caught my attention, this JIF, natural peanut butter caught my attention because it was on sale. So it was interesting to me that a smaller jar, this jar is 28 ounces, was on sale when the big jar, which normally is less expensive on a cost per ounce basis, was not on sale. So now I'm trying to think of, is the volume discount on a big jar better than the sale discount on a smaller jar. That's a, that's a tough question and it's a good question for financial ratios. So just like we would do with the balance sheet or income statement for a corporation, we can do financial ratios in our own personal finance on the, on the shopping trip here. So let's just do it real quick. The jar is 28 ounces, the smaller jar, and to get the sale price, I have to buy two of them. So 2 times 8 is 56 ounces. Now my total cost is $8. So I put in my $8 and I have a formula that divides cost by ounces comes out to $0.14 per ounce. Okay, that's good. So let's compare that to my bigger jar. Now let's just notice besides being a larger jar, this one's crunchy, that one's creamy. So I have to ask myself, is that an important difference to me as a consumer? Because again, financial ratios are helpful, but it's not the whole picture because these are two different styles. Maybe I hate creamy peanut butter. Well then this isn't going to help me at all. But let's just assume that I can factor that different style into my analysis as well. I want to look at this ratio. So now we have a 40 ounce jar at $6.79, and I come up with a $0.17 per ounce costs. So if if everything else were equal or if the other differences didn't matter to me, then I could clearly say that jeff peanut butter in the smaller jar on sale is a better deal than the non sale larger jar. So one thing I can ask myself in this analysis is, let's see here the sale ends here at some particular date at the end of the sale, how will this analysis look? Well, I can purchase just one jar then, which is 28 ounces, and the cost is going to be 479. Now look at this. My actual costs for the small jar is identical to the actual bigger jar. So now it's just a, now it's just you want more peanut butter on hand or less peanut butter on hand. You see what I mean? So the sale actually makes this a better deal. Otherwise, they're really the same deal. You're not getting a better deal by buying a larger jar. But you're only going to realize that if you're run the financial ratios. So here then I decide to expand my analysis not just to the natural Jeff, but the other brands too. There was a smokers natural skipping natural, and another skipping natural. So there are two different sizes, three different brands. Some are on sale, some aren't on sale. So by using a common financial ratio, in this case, cost per ounce, I can compare one brand in size and sales status to another very easily. So in this analysis right here, my best value for natural peanut butter. These are all natural, will be the skippy 15 ounce size that I have to buy tomb. It's a 15 ounce jar, but it's two for $4. So I get 30 ounces of peanut butter for four bucks. That's 13 cents per ounce. So in the big picture, I'm just trying to draw an analogy between shopping and financial analysis of an, of an organization is this has given me one very important comparison between these five very different products. But it's not the only comparison. Again size, you know, do I want this much peanut butter or this much peanut butter? What about ingredients or taste preferences? These all factor in the style, crunchy or creamy. All of these parameters factor into the decision I'm going to make. But given everything else being equal, which it's not. But if it were equal, then this is going to be my best value. So essentially, the financial ratio analysis provides a key input to your total financial analysis of an organization. 4. F 04 Intro to Profitability, Rev 2: So the first group of financial ratios we're going to examine refer to an organization's profitability. So profitability itself is a measure of how efficiently in organization turns its revenue or its sales into what we call margin and return. So we're going to use these terms a lot and you're going to hear them a lot in business finance. So let's make sure we understand what these mean. Margin is the portion of that revenue or that sales, that is profit. It's the amount of money leftover after the organization's expenses. Sometimes you'll hear a term, net margin. So a net margin is the portion of the total revenue leftover after all the expenses, all the inventory, all the purchases, all the payroll, et cetera. That's the amount of money that's left over, that's the profit. And we're going to dig into that and other types of margins here in just a minute. Return is a comparison of those profits to the original assets that were necessary to generate the profit. So maybe you could think in your own personal finances, maybe you have a retirement plan and Ira, something like that. So you put an initial amount of money or assets into the fund. So maybe you put $10 thousand into your IRA while the return is additional money that comes out as profits from that initial investment. So maybe you're earning 7% return or a 12 percent return, that percent return, a measure of the additional proceeds you receive your profits essentially as a percentage of that original investment. So if you had a $10 thousand investment and you had a 7% return, you would have an extra $700. So it's essential for the long-term sustainability of an organization that they remain profitable. So these profitability ratios that we're about to look into our measures of an organization's ability to generate this margin and return. 5. F 05 Profitability Formulas: Okay, The first set of ratios we're going to take a look at are what we call profitability ratios. So over here, I have the annual income statement for target. Then you found this in the extra material I added on to this lecture. And this is for the year that 12 month period ending in 2017, right at the beginning of 2017. And as you know from your counting, the income statement starts with what's called the topline, which is total revenue, and ends with the bottom line down here, which is net income. And then it shows the path from total revenue to net income. And these are all the various costs to the business. So the first Prof. the first profitability ratio we want to look at is called gross margin. So the formula, very simple. It is the gross profit divided by the total revenue. So as we see here, gross profit and total revenue. The only thing separating these two is this right here, the cost of revenue. Sometimes that's called the Cost of Goods Sold. You'll see that with manufacturing companies, sometimes it's the cost of sales, but it's the same idea. It's the cost required to generate the revenue. So in the case of target, that's going to include the labor, the cashier's, the people that stock the shelves. It's going to include obviously the products, the cost of the products, there's the clothing and the housewares in the kitchen stuff, you know, all that stuff costs them. So that's a cost. And then there's other things like overhead and certain depreciation and things like that go into this total cost of revenue. All what goes in there is always explained in the annual reports. As you dig in there, you can see specifically what costs are in here. But, so after the cost of revenue comes out, you're left with what's called the gross profit. So the gross margin is a ratio of the gross profit over the total revenue. So if we did a little formula here, would be 26, 23 divided by 69495. And then we could do the math there. But basically what this ratio does for us is tells us one how much money is left over after we do all of our expenses, pay all of our bills related to selling that product, that margin is available then to run the rest of the business. So effectively it would, it's sometimes called like the markup. So all of these costs are necessary for target, and then they mark up those costs and come up with their total revenue, total sales. So this is essentially the markup on the goods and labor they purchase. Okay? So the second profitability ratio is called operating margin. So what do we have in here? After we get to gross profit, this money is leftover and we start looking at SG&A sales general administration, that's a big one. And for them, now they don't have research and development and they don't have non recurring items in there either. And then they have this category called other. And of course you can look that up in their annual report, but relatively kinda small compared to SG&A. So SGNA, that's advertising, it's the cost of running the business itself, their headquarters, the you know, the payroll people, the financial people, all that stuff that's necessary to run a business, but doesn't have to do with the product itself, okay, so you've got to run your business if it was a drug company or an electronics company. Research and development is usually a pretty big number right here because again, that's necessary to develop new products that you can sell. Okay, so this has to do with the operation itself. So at the bottom, after they remove these, in this case, SG&A and some other, you're left with what's called operating income right here. So then operating margin is operating income divided by total revenue. And here we've got four, Let's see, 969, sorry for right, and so small 1609 for 95. And again, we can do the math there really quick. So the operating margin is operating income divided by total revenue after operating income. And sometimes this number right here is referred to as EBIT earnings before interest and taxes. Because if you look below here, what do you have? You have interests, you have texts is you have interests you have, you know, that's what they're talking about here. These other financing related tax related items, it's not really part of the operation. It's more interest and tax. It. It has to do with how the company is financed and then naturally how the, how the company is taxed as well. So after you pay all of the interests, all the taxes, whatever earnings or expenses you have related to those items. You come all the way down here and you have net income. A bottom line, this is the, the profits of the company, all expenses in, this is how much money is left over. So the net margin then becomes net income divided by total revenue. So in this case we have 2737 divided by 69495. Okay, so here I just did the math for you really quick. Here's our gross margin, 29.7%. And I just did the formula right here, operating margin 7.2%. And again, here's our formula. And then the net margin, the bottom line, 3.9%. So let's just do a little interpretation here. 29.7%. Again, it's the markup on the goods and labor and stuff they had to purchase in order to make the revenue. It's their markup on all of those costs. It's also the amount of money that's left over of their total revenues to run the rest of their business. So the rest of the operation is, at the end of all that there's $4.969 billion left. So that leaves us 7.2% return on their operation. And then the bottom line here, a 3.9%. That's really your net income. That's what investors are looking at in terms of just how well did the company do, 3.9% of its total revenue became profit or net income. So up here on the gross margin, if you wanted to improve your gross margin, you either have to increase sales or decrease your costs. That's the only way to increase your gross margin. In terms of operating margin. In order to improve your operating margin, you have to look in this area, of course, to reduce your costs, maybe of running your business front office sales commissions of that type of stuff, executive salaries, all of that stuff falls into here. So if you want to improve this relative to gross margins, this is the area you have to attack or this other area two. And then this bottom line, after those two areas, you pretty much have to address the financing of your company. Or there are certainly some techniques for reducing your taxes. So you have to address some of those items to improve this. Generally, executives are looking at all three of these together to determine where their money is actually going. 6. F 06 Profitability Analysis in Excel: Okay, now let's do some analysis on these financial ratios. I'm in Excel here and I just typed in the inputs to those profitability ratios that we just looked at, total revenue, gross profit, operating income, and net income. This of course came off over and income statement. And then down here, total assets and total equities that came off of our balance sheet, okay? And then I did year over year. So this is the these are the same numbers that you have in your attachments from earlier in the class. So I just type this into Excel so the mass a little bit easier. And I want to fill this in for you and just show you one very effective way to use financial ratios to analyze a business. Okay, so first of all, we can look this up ourselves, but to come up with total liabilities, it is simply the assets minus the equity. There we go. So there's our total liabilities and I can copy this over. That tells me something about the organization. But before we dig in the numbers, let's just make sure we can do the formula. So then we talked about gross margin and that was the gross profit divided by the total revenue, 29.7. We've seen that number before. And then our operating margin was the operating income divided by the total revenue 7.2. That looks right. And then our net margin, which equals the net income divided by the total revenue. And naturally we've seen that number two, so I can drag these over and fill in our past years as well. And now our two return on assets, return on equities. Now this is our net income relative to our investment in the business which we found on the balance sheet. So we have the return on assets, which is equal to the net income divided by the total assets right there. And then we have the net income divided by the total equity right there. And those numbers should look familiar to us as well. And then of course I'm just going to drag this over. So now we just filled in our spreadsheet and I'm going to attach this spreadsheet to this class so you can use it to analyze other companies as well. But let's just make some observations. First of all, you could graph this out to see what it looks like graphically. Let's just take some observations here. We'll look at total revenue first. So what's happened? The total revenue from 14, 15, 16, he knows it's going up a little bit, up a little bit more and then drops down. So that's kind of interesting. Up and then down. What happened to our gross profit? Kinda similar. Up slightly, up, slightly, and then down slyly, not too bad. Operating income more or less stayed in the same neighborhood. That's interesting. And then our net income, if we look at 2017, there's our net income. But relative to pass. The year before it's down. But then look at these two years, it's up a lot. That was a loss in parentheses means they lost money, but it's up substantially from 2014. So it looks like there was some bumpy years here. So let's take a look. Maybe there's some hints as to what may have been happening. So we have, this is, this has to do with our balance sheet right here. So if we look at total assets, what do we see here? Forty four billion, forty one, forty 37. So the total assets, that's the inventory, the furniture, the fixtures, the lamb, the stores. It's all of the the assets are the things that company owns that expects to generate business off of in the future. So the company, the total assets of the company are actually getting smaller. And the way it's getting smaller, I'd have to say this is a systematic shrinking or downsizing of the company. So they may be, and I don't know this, but target may have close him stores, so they don't have that investment is maybe they sold off the investment of the fixtures and the land and that type of stuff. Or they may have reduced the total value of the inventory in the stores. You know, the, the price they're paying, the amount of assets are carrying. Maybe they carry guns, they used to carry ten shirts now they only have six shirts or something like that. So they may have done it that way. So it's hard to say we'd have to dig into the notes to find that out, but I see a systematic shrinking of the organization. So then the question is, well, how are they financing those assets? So this I find very interesting as well. Let's look at liabilities first. Those are the loans, the money that they borrowed. So the money they borrowed went down from 20 billion to 26 and a half. So not even $2 billion, maybe 1 whatever that is, close to 2 billion dollars, it went down. But then the total equity, which is a smaller number, start with, went down from 16.2 to 10.5. So like $5.5 billion. So the total equity, which is a smaller number to start with, went down more than the total liabilities. Now we'll get into this in future lectures, but the fact that they ratio of liabilities to equities has gone up in recent years tells me, I would describe it as being more highly leveraged. So there's clearly a business decision here, a strategic decision to leverage the company more, or the percentage of liabilities to equity is higher. So then when we look down at our profitability ratios, what do we see her gross margin? Look how interesting it is. It's almost identical. It's hardly changed over the years. So the amount of money that went into the, or I should say, came out of the total revenues after the cost of sales is pretty much the same. That's interesting here. And then we take a look at operating margin, the cost of actually running the business. And that, you know, it's, let's see here, it's bounced around a little bit. It actually went up here in recent years. And then we look at the bottom line. You know, it's hard to say. It's clearly bounced around a little bit and as we already mentioned, there was a loss right there. So then down to return on assets and return on equity, That's really has to do with how well the investment into Target went. So the total return on assets invested, I would say the last two years have been very good relative to the previous two years. That would just be a quick analysis of mine. And then looking at return on equity, I would say absolutely, from the investor's standpoint, today's investors are much happier than certainly the 2015 investors and even the 2014 investors. So just a quick glance. And this is only one small piece of the puzzle, but this strategic downsizing of Target has paid off fairly well for its investors. 7. F 07 Intro to Liquidity: Now we're going to take a look at a family of financial ratios that are measures of what we call liquidity. This is a common financial term, but it's often misunderstood. So let's take a look here at what liquidity is. Liquidity measures an organization's ability to meet its short-term liabilities with its short-term assets. This sounds a little odd at first, but let's consider something. Long-term assets like land and machinery are generally used to generate revenues. You purchase an assembly line in a factory, and that assembly line you own for many, many years and it produces revenues. You're adding labor, adding material, but that piece of equipment or piece of land or building is generating revenues for your organization for many, many years it's a long term asset. So then short-term assets, such as cash and accounts receivable, are used to meet short-term needs, such as inventory and payroll, and the utility bills and things like this. So when short-term assets are sufficient to meet the short-term liabilities, it's a liquid organization, okay? But if those get out of whack, for instance, if you have to sell a piece of equipment or a piece of land to meet a short-term obligation like payroll. Now you're in trouble. So liquidity ratios are measures of an organization to stay in that balance of meeting its short-term obligations with its short-term assets. When that liquidity ratio gets out of whack, the company tends to be in trouble. 8. F 08 Liquidity Formulas: Okay, let's figure out how to calculate a couple of these liquidity ratios. And we're going to focus on the two most common, which are called the current and the quick ratio. So let's just make sure we understand what we're looking at here. I took a couple excerpts out of the balance sheet for the target company ending in January of 2017. And these are in the attachments for this class. And what I have up here is current assets and just a refresher on what current assets means. Those are assets that are expected to be converted into cash in less than 12 months. Okay? So obviously cash, you know, is, it is cash, so it's considered a current assets. There's other short-term investments. Here's the big one, inventory. So for Target, it's going to be the close and the food and the furniture and their stores. That's the inventory, other current assets, it's hard to say what that could be, different financing assets or something in that neighborhood. So, but the two main ones are cash and inventory. And then I took this other excerpt, and this is out of the liability section. So this is the total liabilities, okay? Current liabilities, accounts payable, that's what you owe to your suppliers, those clothing and furniture manufacturers, you owe them money because you purchased inventory on credit, short-term debt. So these could be bank loans or probably bank loans, current portion of long-term debt. So maybe a long-term debt like a mortgage. So you're paying off a building or a piece of property over 20, 30 years? Well, this is the portion, the current portion of long-term debt, so it's the magnitude o in the next 12 months. So again, this is current liabilities is less than 12 months also. And then you have other liabilities, okay? So then you end up with this total current liabilities. Now this is the long-term liability section, so we're not going to worry ourselves with this right now. So the first we're going to look at is what's called the current ratio. So remembering from our last video, liquidity has to do with an organization's ability to meet its short-term obligations with its short-term assets. Okay? So short-term and current is the same thing. So current ratio equals, it's a ratio of the current assets divided by the current liabilities. So the idea is that bowl of the current assets will become cash. All of the current liabilities will be paid off in the same timeframe. So in this case, our current assets, total current assets, a 11, 990. I'm dropping the zeros. That doesn't change our math. And the total current liabilities, 12, 700, eight. And I did the math off to the side for you, It's usually expressed in a two place decimal. So their current ratio is 0.94. So in other words, 94% of their liabilities that they're going to owe in the next 12 months. They already have in the form of current assets. So that's a really good ratio, a very informative measure of liquidity. Okay? So what is this quick ratio? Well, the quick ratio is a measure of liquidity as well, but it's an even more conservative ratio. So what we're gonna do, We're still looking at current liabilities on the denominator of our equation. But on the numerator or the top of our equation, we're just looking at cash. And why are we doing that? Well, because inventory, which again, that's the main category of current assets that we're eliminating from the quick ratio. Because inventory, one, you have to wait. Okay, so inventory could sit on the shelf before someone buys it. It could be devalued. Obviously, you go to Target, there's things on clearance, things on sale. So its value isn't as precisely known as cash. So the people at Target are very good at assessing the value of their inventory. But still, it takes longer to receive to turn it into cash. And its value isn't quite as precisely known. So the quick ratio eliminates the variability of inventory and just says, okay, cash, they list Cassie equivalence. So cash or cash equivalents divided by the current liabilities. So we have 25, 12, which we're getting from right here. And we're dividing it by the same total current liabilities right there, 12708. And this I did the math off to the side again, expressing it in a two place decimal point 2 0. So 20 percent of targets, total current liabilities they're holding in cash today. Is this a good current ratio or not? It, you can't say, okay, just like all of our other financial ratios, you have to use them to compare to other things. So one thing we could do is look at past years. We already know from reviewing our balance sheet for our profitability ratios, that target was going through some changes, some restructuring, probably reducing their inventory and a few other things. So we can use this number to compare year over year. We can also use it to compare before and after a major event, like a restructuring or downsizing or an acquisition. So it's, it can be used before and after an event to compare. And then the other things that these financial ratios are used is to compare from one company to another and usually it's within the same industry. So maybe we could compare Walmart to target and see how their current ratios and quick ratios compare. So stand-alone, it's hard to say they don't really mean a whole lot without the context of the industry, the competitors past years, et cetera. 9. F 09 Liquidity Analysis in Excel: Okay, Well let's do a little analysis of our current and quick ratios now that we understand how to calculate them. And what I've done here is I've set up a table with Target. And I want to do a little comparison with arguably its biggest competitor, which would be Walmart. So let's look in this section right here, 2017. So we already did these calculations. You've seen these numbers before. So it has a 0.9 for Target, has a 0.9 for current ratio and a point 2 0 quick ratio. So we can look in years past. We know that target went through some restructuring. So we can look at years past, so we can see if there's any patterns in their liquidity ratios. So as we look in years moving backwards from 2014 to 2017, you know, it looks like it bubbles around a little bit, but more or less stays in the same area, right around that 1 neighborhood for the current ratio. And it moves around a lot here with the quick ratio. Lot of this has to do with cash flow in and out of the business. I don't know if I can see any pattern here. But as I mentioned previously, it's financial ratios are excellent ways of comparing one organization to the other. So again, Target and Walmart clearly our competitors. Let's see what Walmart does over time with the same type of ratios. So for the current ratio for target, 0.91 to 1.96, Walmart's current ratio. Let's see here the low point 86 to point 97. So it's not a huge difference, but I could say that Walmart is running a lower current ratio on average. I think that would be a fair assessment. And then quick ratio, same way. So the ratio essentially of the cash to the current liabilities, they're running thinner there too, and more consistent too. So 0.1 to point 1, 4. So we'll say about 10 percent of their total, total current liabilities. They're holding in cash basically. So you can see that for a companies like these that have of, how do I say this? A high degree of ability to convert their current assets into cash. These are grocery stores in clothing stores, department stores, that's what they're doing. So their food turnaround quickly, close, and then if they want to turn them around more quickly, they can run sales and discounts and issue coupons. So these businesses just the nature of the business. It's, it's easy to turn their current assets into cash. So it's not unusual to see lower current and especially lower quick ratios. That's a very common thing to see in this type of business. So another thing we could do, we saw Target and Walmart. Well, what I did was we were going to be talking, have been talking about Intel a little bit. Let me zoom in this way. We could compare Intel to Target and Walmart. So what do we have here? These numbers I pulled out of the attachments that you have with this course. The current ratio, 1.75 for the year 2017 is we look over time, 1.73 to 2.45. Clearly, it's a whole tick above where either Target and Walmart are operating. Quick ratio. Similar to so they're running between 0.841.6 to a definitely a larger amount of cash on hand relative to their current liabilities. And just again, think about the business. Intel is selling their inventory to other major businesses, maybe Dell computer and Hewlett Packard and device manufacturers. So one, it's harder to turn over the inventory because you're selling directly to businesses. And there's a longer period of time because businesses have sometimes these net 30, net 60, 90 days, et cetera, to pay their bills. So in order for Intel and companies like Intel to operate smoothly, they have to have a larger amount of cash on hand. They have to have a stronger ability, you might say, to pay off their current liabilities with their current assets and their cash, of course. So these are again, they're good comparisons when you're looking between companies in the same industry, okay? No one realistically is comparing Intel to target. We can do it to understand how different companies operates. But if we wanted to make a good comparison out of Intel, we would look at AMD or some other chip manufacturer to really understand where Intel is within their industry. So these are excellent ratios for comparisons of companies within industries. And certainly year over year for the same company. 10. F 10 Intro to Solvency: The next group of financial ratios, we're gonna take a look at our referred to as solvency ratios. Sometimes they're referred to as leverage ratios, and it all depends on how you think about it. So essentially, solvency has to do with if an organization were to dissolve or just sell off all of its assets and pay off all of its debts. How much would be left over? In what category, okay, That's essentially what solvency refers to. Sometimes these same ratios are referred to as leverage ratios, which have to do with how much of your organization's assets are financed with debt, okay? They're the same measures, but they're just too different ways of looking at the same thing. So let's just dig into this a little bit here. So it's a measure of solvency ratios are a measure of how an organization finances its assets. Now you might know, hopefully you know from your accounting, that assets can be financed with either liabilities or equity. Those are your only two choices. All of your assets are financed with liabilities or equity. So liabilities are debts owed to other organizations like banks and suppliers. Banks often lend businesses long-term debt for the purchase of land and building for instances those are called mortgages. Suppliers lend shorter term dad to companies to purchase inventory. That's usually called accounts receivable, that type of stuff. So these are debts. Well, equity is the portion of the assets that are owned outright and so there are no liens, There are no deaths associated with that portion of the organization's assets. So you either own it outright or you owe a bank or somebody else for those assets. Okay. Pretty similar to like a home ownership model. You either own it yourself or the bank owns. Solvency ratios are then used to assess an organization's financial risk. And you could just think about this. And if an organization's assets has a million dollars in assets, but it owes the bank $900 thousand. Well, that's a, that's a highly leverage organization. Or in other words, the amount of debt to the amount of equity is very high. That would imply a higher financial risk. Then maybe accompany with a million dollars of assets that had $500 thousand in debt may be 5050 liabilities to equity. So anyway, it's a measure of financial risk. It's referred to as solvency, but it's also thought of as leverage. 11. F 11 Solvency Formulas: Okay, Now we're going to figure out how to calculate this family of financial ratios that we call solvency or leverage ratios. So what I've done for you over here on the left-hand side is I've pulled up the entire balance sheet for target for this year, right here ending in January of 2017. Now it's long and skinny and I just want to review this here to make sure we know what we're looking at with current assets. We talked about that under the liquidity section. And then we have long-term assets down here. So these are, this is 0, property and buildings, equipment and whatnot. And then we have all the way at the bottom, we have total assets. That's going to be an important number for us right here. Total assets. Then we move into the liability section. So we have all these accounts payable and all this other stuff that we called current assets. And then we have longer-term assets like debts and mortgages and things like this. And then we have a total liabilities, total debt, that's going to be an important number. And then down here, we have the equity section of the balance sheet, which tells us about the ownership of the company. So we have common stock and paid-in capital and retained earnings in all of this ends up with a total equity. So those three numbers are going to tell us a lot about how the company is leveraged and how the assets are financed. And let's just do a quick review before we move ahead and this was covered. If you took my basics of business accounting classic covered it or if you just took any accounting class, you know that assets. And that's all the assets, the building, the land, the baby close the frozen food, whatever target has for assets, its assets equals liabilities. That's all the debt, all the mortgages, all the accounts payable, et cetera, plus equity. And that's the ownership, common stock, retained earnings, It's just ownership. So all of the assets are financed through liabilities or through owner's equity. So this is called the fundamental accounting equation, okay? So solvency or leverage ratios has to do with this equation. And the how any given organization went about financing those assets. So the first is, they're all really straightforward, but the first is we would call this the debt ratio. So the debt ratio is a fraction where we take total liabilities divided by total assets. And if we look this up it, Here's our total liabilities or total debt, 26 for 78, and our total assets right here, 37, 431. And I already did the math for you. This comes out to 0.71. So what does that tell you? That tells you that 71% of our total assets, the buildings, the baby close, the frozen food, whatever we call assets, 71% of it is financed with debt. We owe money to banks and vendors and lending institutions, et cetera. That's how. That is how Target has financed their inventory. So the next one is very simple. Once you understand that debt ratio, because now it's simply equity divided by total assets. So it's the other half of the financing equation. So we look down here at our total equity, and it's 10, 1953. And then we look at our total assets up here, 37 for 31. Now I can do the math and I did. But pause for a moment, think what is that number going to equal? If we know from our fundamental accounting equation that all assets are financed either through liability or equity. So we know a 100 percent of our assets are financed. We already did the debt ratio, so we know it's 71 percent plus that's the number we're calculating. So we know and if you do the math here, you will calculate a 0.29 equity ratio. So the equity ratio, again, the portion of the assets that are owned outright by the owners, the shareholders. Basically for a publicly held company, these are stockholders in the company. So that is our equity ratio. So those two are pretty straightforward. And again, using the same numbers, it's just a different way to look at the same thing. So now you have your debt to equity ratio. So we're just using the same numbers, but we're calculating it in a different way. So our total liabilities, which we saw right here, 26 for 78, and our total equity 10, 1953. And if I do the math there, it comes out to a 2.42. And again, it's just a different way of looking at the same numbers. It's telling me that we owe to 0.42 times as much as we own, right? So a 2.42 debt to equity ratio, okay? And then the last financial ratio in this solvency or leveraged section, is actually called financial leverage. And again, solvency and leverage, it's using the same number. It just implies different things solvency has to do with the company went out of business today. Theoretically, who how much would they owe and what portion of it would get retained by the owners, this sort of thing. But generally speaking, companies aren't just shutting down abruptly. So how the capital or the money, the ownership of the assets is structured has to do with this, what we're calling financial leverage. Okay? So financial leverage, again, using all the same stuff, is a ratio of the total assets to the total equity. Here are assets 37 for 31. Divide it by our equity, which is 10, 1953. And then I'll do the math for you. It comes out to a 3.42. So financial leverage. Think about what we're talking about here. If our ownership doesn't change, we own some fixed portion of the company this year. We'll say, if our assets go up, this number goes up. Well, how do assets go up? If equity hasn't changed? Assets can only go up by borrowing more money. So the more money that you borrow to purchase assets relative to your ownership equals a higher financial leverage. So if my financial leverage goes from 3.42 and say next year it goes up to a four-point zero, while I am more highly leveraged. And it's, all of this is talking about how I've gone about financing my assets. Okay? So that is our solvency and leverage section. Just like all of our other financial ratios, they don't mean a whole lot in context, but there are important like debt ratio and financial leverage. These make the most sense when you're comparing them to something else. 12. F 12 Solvency Analysis in Excel, Rev 2: Okay, now let's do some analysis of our solvency ratios. So what I did up here, Here's target again. And here are the assets, liabilities, and equity over these four years right here. And then I calculate those same solvency ratios, debt ratio, equity ratio, debt-to-equity, and financial leverage. So let's just look at this and see what we can figure out here. By the way, I also charted. Sometimes looking at things graphically helps you see things that maybe you wouldn't necessarily see in the numbers. So what I just did was a line chart over time from 14 to 17 of the assets, liabilities, and equity. So let's just see what happens here. It's very easy to see this top line total assets. It's very interesting what's going on a target over these four years? The assets in 1444000000041.145 to the company is getting smaller. Now we'd have to dig into the annual reports and see what that's all about. There could be some leaning of inventory, but it looks like $7 billion. You know, I'm sure there's some invalid Torah, but it looks like some store closing something big and a systematic downsizing of the target organization. So as we know, all of our assets are financed either through liabilities, which is deaths we owe to banks and other companies, or equity, which is the portion of the company that the shareholders own. So how did we finance, how did the financing of these assets change over time? Liabilities, 28 billion, twenty eight point three, 26.4, so it dropped, we'll say 2-bit 1.81.9 billion so that liabilities are debts got smaller. Equity 16.2 to 10.9. So equity got a lot smaller relative to total liabilities. So I would say the majority of the downsizing of assets came through a downsizing of the total equity. If you think about it that way. And I think we'll be able to see that somewhat in R. Let's look at our equity ratio for instance. And these, naturally these two numbers, the debt ratio and your equity ratio, always add up to one because that's our, That's how we finance our assets. This looks like a little bit of a rounding error, but everything else adds up to one. So let's look at our equity ratio, 0.36.34.32.29. So the amount that the shareholders own is shrinking. So here if we look at our financial leverage over time, now again, that's total assets, divide it by total equity. We can see that we are becoming more, target is becoming more financially leveraged over time. They went from 2.74 to 3.42. So their leverage. Is increasing. So this is a sophisticated restructuring. And you can see that over here, the slope of our line, you can see the slope of our total assets is going down. Well, both of these lines are going down to our equities are going down and our, I'm sorry, our liabilities are going down and our equity is going down as well. But you can see that our total equity is dropping faster. So as the distance between these two lines, total liabilities and total equities gets larger, your financial leverage goes up. So just think about this as the portion which is equity of your total assets drops faster than the portion of your total assets, that is liabilities. This distance increases, your financial leverage goes up, your equity ratio goes down. So sometimes looking at these charts reveals again, something that you might not necessarily see in the numbers. So as we've done with our other financial ratios, we can just look at other companies and compare their financing structure as well. And I picked very different companies. I'm not trying to compare target to form factor, which is a smaller chip manufacturer, probably more like a competitor of small competitor to Intel. But I just want to show you how companies finance their growth or they're downsizing this sort of thing. So we can look at this company form factor again, chip manufacturer and interesting company as well. And I haven't dug into their annual report, but clearly, they're doing some restructuring and it's showing up in our solvency. Ratios are, as I mentioned before, sometimes they're called leverage ratios, but it shows up that they're doing something. So if I wanted to know what it was there doing, I have to read the annual report, But let's just take a look form factor 14, 15, 16, fairly steady assets, the size of their company was roughly the same. If I look at liabilities, there might have been some bouncing around a little bit here, and that's showed up and their equities, so there something in 15 caused their liabilities to spike and their equity to spike down. Same here, but then it seems like they brought it back. 2016, 2014 are almost identical. Okay. So let's look at our debt ratios. Very similar, equity ratios, very similar. So all of these financial leverage, debt-to-equity Verrier similar. So what happens in 17? Their total assets go up sharply. Their total liabilities go up sharply. Their total equities go up sharply. So first of all, this is a bigger company. They're experiencing substantial growth. Now. Is it there? Is it a planned growth? So for instance, did total assets go up because because they built a new factory and bought a bunch of new equipment. So is it longer-term assets or did it go up because their product went viral and they sold a lot and they have a lot of current assets, a lot of cash on hand. I'm not really sure we can dig into this, but by itself, it's hard to tell. But as I look down here, my guess is going to be that they are financing a larger company. In other words, they're pushing and promoting more growth. Well, why do I think that? Well, their liabilities have gone up sharply. Their total equity has gone up sharply. And again, I don't know this for sure, but if they sold, for instance, had a stock issue, sold more stock, and borrowed additional long-term assets to build another plant. That would be one scenario in which this would happen. They were financing long-term assets for growth. So as we look at our financial ratios and what do we see here just in terms of how did their financing change? One is their debt ratio went up from this pretty steady pace they had in years past. So they are borrowing more money on a percentage basis than they have in the past three years. Equity ratio, even though their total equity went up from 294 to 40, won, their equity ratio went down. And again, I'm guessing this is a planned change to their corporate structure. So naturally their debt to equity ratio went up substantially and their financial leverage went up substantially. And there's a certain sense and if you have a if you have a particular return on assets, we were talking about this back in the profitability section. You have a return on equity or return on assets. If you add more money to the pile, if you borrow money to invest in your assets and you get the same return on assets, as long as your return on assets is higher than the cost of borrowing, you're growing your company, you're making more money. So these are the types of decisions at CFOs and executives make. And then again over here we can just see graphically the assets, the equity and the liability. And these numbers are going up sharply here. So clearly form factors experiencing some substantial growth. Okay, Let's look at a completely different organization again. You know, so this is Pfizer, the major pharmaceutical company. And we'll just, you know, we're just looking here to see if we can identify any patterns. So total assets, interesting Pfizer over these years, gosh, I'm a little bit of growth from 15 to 16, but you're not on a percentage basis, very steady. Company size, their assets are very stable. So their liabilities now 96 to 10 to 10 to two, 112, and then down to 100. So that's an interesting pattern, up, up, up, down. Okay, what would happen to their equity? Again, assets are the same so that liabilities and equities are almost mirror images of each other. So equity was 71 down, down, and then went up again. That's interesting. And you can see the graph over here. Assets, very stable. Liabilities went up and up. Maybe they were trying to finance some growth. It's hard to say. But then sharply down again. Equity down, down, down, and then sharply up again. So we can see this expressed in our financial ratios. And I'm going to tune in on this line, financial leverage, the financial leverage in 2014.352. Again, borrowing more money with the same assets means your equity is going down, financial leverage is going up and up, and it returns to something very similar that they had in 2014. So we would have to dig into the annual reports again. But whenever you see major shifts like this in liabilities and equities without a lot of change in the assets. The, you know, there's some restructuring going on. Maybe stock buybacks, a transfer, major pay down of liabilities, long-term liabilities, some sort of financial restructuring to have such an abrupt change like that. So again, we'd have to dig into the reports, but it's interesting to see how companies, different types of companies finance their assets. 13. F 13 Intro to Efficiency: Now this fourth and last set of financial ratios that we're going to examine refer to a company's efficiency. Now, efficiency means a lot of things in the world, or in physics or in other areas. But in finance, it refers specifically to how quickly short-term assets flow in and out of an organization. Sometimes these are called activity ratios. There, they're really the same thing. Efficiency ratios, activity ratios, I think those are pretty much used interchangeably. So efficiency ratios can be easily converted into what are referred to as operating cycle days, okay? So an operating cycle is a crucial piece of business finance. It's the average length of time required for cash spent on short-term assets to be returned to cash. Again, sometimes you hear a term like turnover. It has to do with how quickly cash is returning to the company. So this is a simplified example, but, but a good example of a businesses operating cycle. So the cycle starts with cash. You have cash in the bank and you use it to purchase inventory. And that inventory, maybe you're purchasing raw materials, plastic resins or steel bar or some sort of chemical that you're using. These are raw materials, inventory. You use that inventory to make product. Well then that product, you then shipped to your customers. And then in return for your shipping that product, they pay you and you receive payment from those customers and then it goes back to cache. So it ends and starts with cash. And in-between. These key points between purchasing inventory and making product, you have what's called raw material inventory. So there's a number of days or a certainly an amount of money that represents raw material inventory between making a product and shipping a product, you have what's called work in process, inventory, which can be measured in days or dollars. And then between shipping a product and receiving a payment for it, you have what's called accounts receivable. It's the amount of money that your customers owe you for product that you sold them on credit. And again, accounts receivable can be measured in cash obviously, but it can be measured in days as well. And we're going to look at both of those interchangeable ideas, money and days. And starts with cash, ends with cash. And these efficiency ratios are measures of an organization's operating cycle. 14. F 14 Efficiency Formulas: Okay, Now we're going to take a look at a collection of financial ratios that measure the efficiency that an organization uses its resources. As you'll remember from the previous video, these ratios have to do with how fast cash used to purchase raw materials is being returned to the organization for the purchase of more raw material. So it has to go through that entire operating cycle of purchasing and making the raw material and storing the finished goods and shipping it and then collecting the payment and then it returns to cash again. So efficiency ratios are, are the measurements of those segments of the operating cycle. So we're gonna take a look at a couple common ones. There are many, many efficiency ratios. We're going to look at some of the most popular ones. And this one, inventory turnover. In short, people say inventory turns turnover turns. It's the same, same idea. And this is, I'll just give you the formula first. It is the cost of goods sold. Divide it by the average inventory. Okay? So typically you're not really getting average inventory. Now we're using the Intel documents that I included with your handouts. So let's just look at cogs for cost of goods sold. And we find that on the income statement down here. And one of the tricks is, and I'm glad Intel broke this out is sometimes there's some other stuff buried in COGS like depreciation and amortization. So we're just, we really just want to know about the inventory. Okay, so here's the COGS excluding inventory. And let's just say we're looking at the year 2014. So we slide over here and there's our number right there, $11.97 billion. Now we're just going to write 11.97. The racial come out the same way without all the zeros. And then we're going to divide it by average inventory. So let's take a look. Here's 2014 again. 2014. So this is the asset portion of the balance sheet. And at the end of the year 2014, they had 4.27 billion in inventory. Well, at the end of 13, that number was 4.17. Pretty similar. There's a lot of discussion about how to calculate average, but when you're just using a balance sheet, you just look at the year before and the year that you're taught, the end of the year you're talking about and it's 12 months previous and you just average those two numbers together. So we went from 4.1712 months later we have 4.27. So just for simplicity sake, we're just going to average these two numbers together. That equals 4.2 to 4.22. And then we're just gonna do the math. And I already did a Fourier off, off screen here and it comes to 2.83. Inventory turns. So that means in one year. In 12, 12 month period, we all love our inventory theoretically, or we'll just say on average, all of our inventory is sold and then sold again, and then 0.83 of it is sold. So 2.83 times we sell all the way through our existing inventory, our average inventory. So that's what that means there. And some people have a little, can kind of catch it more conceptually when they think about the average number of days in inventory. Now we're kinda remember that operating cycle we were talking about. So now let's kinda think along those lines. The number of days, on average, the inventory spent in house. So to do that, you just take 365 divided by your inventory turns. So in our case it's 365 divided by 2.83 and that equals a 128.5. So what does that mean? On average? Our inventory spent a 128.5 days in house before we sold it. Okay, let's take a look at another pair of financial ratios that addressed the efficiency of an organization. And the first one is called accounts receivable turnover. And you're going to see a very similar pattern as we saw with inventory turnover. And we're gonna do the same thing with average days collected. So let's just look at the formula first. So the average receivable turnover is the total revenue. Total revenue. Divide it by the average receivables. Now let's just remember what we're talking about here. So your accounts receivable is the money that your customers owe you. Many businesses, certainly Intel and most manufacturers and big companies, they're selling their products on credit to their large commercial customers and other businesses and stuff. So, so there's amount of money called accounts receivable that, that Intel doesn't have in their pocket. But they know that it's on the way. They are highly confident that this money is coming to them from other businesses or consumers. So that is your accounts receivable, that's an asset. Let's start with total revenue. We're going to look at 2014. So total revenue right there, 55, 55.87. And then average accounts receivable. Now I did the math for a year. So let's just keep in mind again what we're doing here, total accounts receivable. So 2014 we ended with 4.43, but 2013 ended lower. So we want the average accounts receivable. So I did the math for you there. So the average. Accounts receivable is 4.04 billion. So you just do the math here and you'll come up with 13.8. So 13.8 is our average accounts receivable turnover. Now what does that mean? That means that in a, in this case, 12 months in the 12 month period ending at the December 31st, 2014. The accounts receivable account was created and collected 13.8 times. So. And again, it might be easier to then Picture average days in accounts receivable. These two ratios are interchangeable. So to calculate the average number of days, money is held in accounts receivable. You take 365 and you divide it by the turnover. We'll just say accounts receivable turnover. So for us, 365 divided by 13.8 and that equals 26.4. So what does this mean? That means that the average account was paid 26.4 days. That is a fairly short collection period for your debt. You know, to be anything under 30, you're doing very well. So obviously, intel has a very good system setup to collect the debts that their customers owe them. Okay, and this third and last set of efficiency ratios that we're going to look at are the accounts payable turnover. And then it's, it's complimentary days in account payable. Now, the accounts payable turnover for soil, the formula, total supplier purchases, divide it by the average accounts payable. So we know from looking at our balance sheets that we can find accounts payable in our current liabilities. So this is Intel's excerpt out of their balance sheet. So for 2013, 2014, our accounts payable and this is the money that we owe our suppliers ending in 2013.972, ending in 2014, a slight drop to 2.75. So if we were talking about the year 2014, year that ended in 2014, to calculate the average accounts payable, we just average these two years together. So that's what I did right here, and that's 2.86. Now total supplier purchases is a little trickier because it's not probably not going to be listed on the income statement or any of the main financial statements. Maybe we could find it by looking in the annual reports, but just looking at the main statements, it's not going to be there. And I have seen, by the way, side note, I've seen people calculate Accounts Payable turnover using COGS, cost of goods sold right here. That is not correct. We're looking at just how quickly we pay off our suppliers. That that's really what we're getting at here with this efficiency ratio, COGS is going to include other stuff like the labor to produce apart, indirect labor, direct labor to right now we're just trying to understand the timeframe basically and the amount of money that's held up in this accounts payable period. So using COGS, you're going to bring in a lot of other numbers that don't belong in this calculation. Okay, So what I had to do just for the purpose of this tutorial is I just estimated looking at their inventory and looking at a few other things. I just estimated supplier purchases. So this these are not out of Intel's annual report. I just made these up. Okay. But I estimated them based on what I saw for their inventory. So so again, looking at 2014, I need total supplier purchases. So that's 14.714 and these are in billion dollars. So if I do the math here, I get an accounts payable turnover for the year of 2014, a five-point one. That's how I calculate it. Now, just like we saw with the other ratios, days in accounts payable is simply a ratio of 365 to the accounts payable turnover ratio. And I did the math off to the side. It equals 71 days. So what does the 55 0.1, what does that tell us? It to basically tells us in the period that we're talking about, which in this case is the year of 2014, we paid off our accounts payable account 5.1 times it filled up, and we paid it off, filled up, paid it off, et cetera, 5.1 times. So if you do something 5.1 times in a year, This just tells us that then our accounts payable days was then 71. It's just 365 days in the time period in the year divided by R ratio equals 71 days. So we interpret that as saying in 2014, it took intel an average of 71 days to pay off their suppliers. So what we're going to do when we look at the analysis side in Excel, is we're going to bring all of these ratios together and see how they interact with each other. We're not just going to compare it year over year, but we're going to see how they interact with each other to form what we're calling the operating cycle. 15. F 15 Efficiency Analysis in Excel: Okay, We're going to break up our analysis side into two sections. First, we're going to look at these various efficiency ratios over time for Intel and take a look at how we calculate these in Excel. So we have our data from our income statements, from our balance sheets, et cetera. And I've just consolidate this into one spreadsheet here. So wherever I have these white cells like these, these are just numbers I picked off of the financial statements. The supplier purchases is the exception, but you would find it on a financial statement of some sort annual report. Wherever you see this light orange color here, that's where we're doing some calculations. So we're going to look into those cells to see what we're doing. So just some armchair analysis here. It's interesting to see from 20132017, total revenue of Intel went up, up and up. So it's kind of indicative considering Intel is the predominant player in the microprocessor industry, the fact that they're growing in revenue so steadily, it's telling you something about the culture at where we live, the mobile devices and the Internet of Things, and the propagation of microprocessors and all sorts of things like refrigerators and dishwashers and things that didn't have those before. So it's just interesting from a cultural standpoint. So revenues went up and up $10 billion across these five-years. That's remarkable. So naturally, the cost of goods sold went up to, and it can bounce around a little bit here. A lot of it depends on product mix and many, many other factors, but there's a general trend. Upward from 13 to 17, it went up. Again, inventory, very natural that inventory would increase with revenue. As your products become more diverse, you need a greater number of different components. So anyway, that's not a surprise either. So then just a repeat of what we were doing in the equation section is, you'll see here that the average inventory for 2014 is simply an average of 13 and 14. And then as you see as I move over, That's the average of 14 and 15, 15 and 16, etc. So that is our average inventory and naturally goes up. Now here's our accounts receivable that we picked off the balance sheet. So the accounts receivable, the amount that customers owe us, that's going up to, again, that's natural, right? If revenues are going up and the great majority of your sales are on credit, which is something you would expect with Intel, your accounts receivable are going to go up as well. So naturally that's what we're seeing here. Accounts receivable are going up. We're interested in average accounts receivable. So just like I did in the equation section for 2014, I'm averaging 1314. I'm averaging 15, 14, and 15 here. And that's what I do all the way through. So 2017, it's an average of the end of year 2016 to the end of year 2017, okay. Accounts payable. This is right off of the liability section of the balance sheet. I just recorded that. And then I did average accounts payable in the same manner. And then my supplier purchases. Okay, so there's the raw data that we're inputting into our efficiency ratios. So let's take a look at our inventory turnover. We know that it is a ratio of our E4, which is our cost of goods sold divided by our average inventory. So that tells us, I mean, it's, it doesn't exactly work this way. But if we had a warehouse of inventory, we emptied it out and filled it up, emptied out, filled it up and emptied it out like 0.84 times. So, so it's like the number of times that we replenish fully our inventory in a particular year. So year over year analysis, you know, a fairly similar, right? 2.5 to 2.84. So similar. But if you think about it in terms of the year, it's a little bit magnified. So average days in inventory we already know is 365 divided by our inventory turns. You see a slight adjustment in inventory turns results in a much bigger change in our average days in inventory from a 128 to a 145. So that's interesting. In other words, don't be deceived by what appeared to be subtle changes. They could have a much larger impacts on the operation of the business. So here's our average days in inventory. So it looks like we're ranging from a 125 days to a 145 days in inventory. That gives you some sense of thinking about Intel. From the time they are purchasing raw materials. Well, that not the purchase by the time they receive raw materials to building the product and all those different work in process and testing and all that stuff to the pirate ships. It's stored in finished goods and then it ships out the door. That's a fairly long time. I mean, my gosh, a hundred and twenty-five hundred and forty five days. So months. The raw material spends in the warehouse in some form or another until it goes out the other side of the plant. Okay, so then we have our accounts receivable turnover and we know that that is a ratio way I can click on this equation. So E three, which is total revenue, divide it by 8, which is our average accounts receivable. So that tells us again, picture this account. These are the customers owe us this money and they are paying it off and charging more, paying it off, charging more 13.8 times in a year. So our accounts receivable formula 365, divide it by our accounts receivable turnover, 26 to 32. That is a really, really good. Average days in accounts receivable. Now, some companies, and maybe this is what Intel's doing. Some companies give their customers options. For instance, if you pay your total bill in less than 30 days, you get a 2% discount. If it's greater than 30 days, you pay the full price. If it's greater than 60 days, it's a plus 2% or something, something like that. So companies that are more focused or more reliant on a smooth cashflow will sometimes incentivize their customers to accelerate their payments back to them for their goods. Now I don't know that Intel's doing that. But what I do know is that is a very, for a company this size that is a very, very good average days in accounts receivable. Okay? And then the last piece of our puzzle is the accounts payable turnover. So what do we have here? 11, which is the average accounts payable. So that's the average amount of money that we owe our suppliers at any given time. And then it's divided by supplier purchases, how much we actually purchase there. So just like the other ratios are, accounts payable, turnover means we paid off all of our suppliers and charged all that money back 5.1 times or 4.2 times across the entire year doing the formula, then we see it is simply the average days in accounts payable is simply 365, divide it by the accounts payable turnover. And just like inventory and accounts receivable, accounts payable two is a form. It's one piece in the operating cycle formula. So what we see here, and again, this has nothing to do with Intel. I made up the supplier purchases. I don't know what the supply purchases are, but let's just pretend that these are correct. But maybe accompany would systematically stretch out their payments to increase their internal working capital. Okay, and we'll look at that formula in a second. But two, in other words, hold onto money longer. Maybe they're offering some other terms to their suppliers to allow them to take longer to pay their bill. Now, if this was, It could be it, this is not the case with Intel, but if you were seeing the average days in accounts payable go up and up and up, it might be indicative of some internal problem with cashflow. In other words, maybe they're having trouble turning over their inventory, are collecting their payments to generate money to pay their bills or something, you know, so generally speaking, increasing accounts payable isn't a good thing. That's a general statement. It might be a systematic way to increase your, or I should say, decrease your working capital or other words, increase your cash holdings. So anyway, this is just an example. So there is our analysis section. This is how we got there. The next we're going to look at operating cycle. We're going to bring this all together and understand what this means. 16. F 16 Operating Cycle Analysis in Excel: Okay, so here is where the efficiency ratios get very interesting. So the reason that we calculate a lot of these efficiency ratios that we're doing back over here is so that we can understand our organizations operating cycle. So when I did up here is I just copied and pasted forward from our, from all of our calculations. I just brought those forward. So these are the same numbers we were looking at before from 2014 to 2017, but then I group them and space them out a little bit here. So what do we have here? We have our an average accounts receivable. This is how much money is in our accounts receivable. So that's how much our customers owe us. And then we have our days. Same thing, inventory, money and days, accounts payable, money and days. Okay, now let's take a look at this diagram right here. You'll remember this from the introductory video to the efficiency ratios here. So we start out with cash and then we purchase inventory right here. We purchase the raw materials, the plastic, the steel, the aluminum, whatever we're buying, we buy it here. Then we store it. We make things out of it, and then we ship it. So this right here, I break it up for more raw materials work in process, but this is our inventory right there. Now, we can measure inventory as we know, either in money or in days, okay, but then what do we have here after we shipped the product, then we send an invoice, then we receive payment. And that is called our accounts receivable, which is again measured in money or days. Okay, So this is what we've been talking about, but now let's think about this here. Something else happens when we purchase inventory. I'm going to put it down here. We receive an invoice and then the accounts payable, either in days or money becomes our accounts payable period. So this line right here might be pay bill, right? That's what we got here. Okay? So this section right here represents our accounts payable. And again, we can measure that in days or we could measure it in actual money. Okay, so then what do we have here from the time? So we spend cash and we purchase inventory, then we sell product and we're waiting for our money back. Okay, well, but some of this money back is used to pay our suppliers for that inventory they would purchase way back here. So this right here, that right there is referred to as our working capital. Sometimes it's our operating working capital, but there's a few different definitions for working capital. But if you can just get a sense of it's the working capital for operation. If we wanted to calculate the total working capital in our operating cycle, we could simply add our inventory plus our accounts receivable and then subtract our accounts payable because we're still holding onto that money even though we owe to our suppliers. Okay? So this 5.4 billion for 2014 represents our operating cycles working capital, okay? Again, we can measure this in days also. So let's do the same formula. But now we're using days. So we take our average inventory days plus our average days accounts receivable and subtract our average day is accounts payable 84.1. So what we're saying is, is on average, it takes 84.1 days to get our money back from what we sent out. Now, inventory plus accounts receivable is much longer than 84.1 days, but some of that money we're holding onto before we pay our suppliers. So you might call it our net operating cycle or net working capital days. But it's the average number of days that our money is out and we're waiting for it to come back. So then naturally, we can paste this and show the trends of our operating cycle money and our operating cycle days across time and managerial decisions and market conditions, any number of things can affect this. But here is the end goal of calculating all those other efficiency ratios is to determine what is our operating cycle, working capital and our operating cycle in days. We may look at the amount of money that we have in the system or the amount of time it takes us to get our money back and make managerial decisions accordingly. Like I mentioned before, maybe incentivize our customers to praise pay sooner. Maybe negotiate terms with our suppliers to pay longer. Maybe we want to shorten those up. There's a lot of reasons why you'd want to make it longer or wanna make a shorter. But here's the, I'll say ultimate measurement of efficiency is our total operating cycle. 17. F 17 Conclusion to Course: Well, this is the end of the Skillshare class title financial ratio analysis using Microsoft Excel. Thank you so much for joining me for this class. I sincerely hope you learn some things and if challenge your thinking and you have some tools now that you can put into practice in your business or your organization. I'm so glad, so thankful for every student that joins me on this learning journey to gain some new skills, advance their careers, and increase their knowledge. So thank you very much. I do want to offer you one last piece of learning that may help you solidify your skills. So at the end of this class, there's a project section, class projects section. And in that section, I've included an Excel spreadsheet where I give you a small case study of a company and the numbers are largely fictional, but they based, it's based on something that I, that I saw in the marketplace, but it's largely a fictional case study. I give you all the big numbers, numbers that you might see on a company's financial documents like revenue and net earnings and accounts payable and accounts receivable, average inventory, things like this. So I'll give you all these data and then I have some financial ratios that I'm asking you to calculate yourself. All of the, everything you need to complete this case study spreadsheet is in the class. So this is a chance to kind of practice what you've just learned. So one tab of the spreadsheet is the blank form that you can fill out as part of your class project. The second tab, I've solved these for you. So if you want to check your answers or you want to see how I did it that's available for you to. So this is a resource for you. I strongly encourage you to practice this on that case study spreadsheet. Furthermore, take an opportunity now that you have a better handle on some of these ratios and some of the financial documents. Take a look at your favorite company, look up their financial statements. Maybe you're into a particular brand of tennis shoe or a particular smartphone or some organization in your community. Take a look at their financial statements. If they publish them online and see if you can extract the key pieces of data and form some of these financial ratios on your own practice is what's going to solidify these skills. So anyway, that is for you. Thank you again for joining me. If you know me from my other online courses, I love hearing from students and interacting with you. So if you have any questions or comments, please reach out to me through Skillshare. I tried to get back to everybody within 24, maybe 48 hours if I'm really busy, but I genuinely enjoy hearing from students. Please reach out to me with your questions and comments and thank you again for taking this class financial ratio analysis using Microsoft Excel best wishes to you in your career and your continuing education.