 Personal finance, Excel, practice, problem, earnings per share and price earnings ratio. Prepare to get financially fit by practicing personal finance. Here we are in our Excel worksheet if you don't have access to it, that's okay because we'll basically build this from a blank sheet. But if you do have access, we got three tabs down below example, practice, blank, example, answer key, let's look at it now. Information on the left, calculations on the right, we're imagining we're evaluating stocks possibly to be investing in them. Remember that a corporation is a separate legal entity breaking out the ownership into standardized units, those being stocks. We're typically thinking about stocks that are publicly traded, meaning the corporations are traded on the public exchanges. We're going to do common ratios here being the earnings per share and then the price earnings ratio calculations. The second tab, the practice tab will have some pre-formatted sales so you can work the practice problem with less Excel formatting. And then the third tab, the blank tab will do the Excel formatting. If you don't have any of this, that's okay. You can open up a blank sheet and just format the sales yourself. You're not going to have this item here, but that's okay. You just format the worksheet. I would do so by clicking the triangle, right clicking on the selected sheet and then go to the format sales. I usually start with currency, brackets and red and no dollar sign, no decimals. That's the starting point. I'm not going to hit okay, but instead X out because I already have it here. And then you can add your data on the left hand side and make a skinny C column and then we're good to start working in cell D1. So information on the left, we've got the company information, we've got the earnings. Now when we think about the earnings, we're calling it earnings because it's the earnings per share. And notice you kind of get confused on these calculations or the names that we often use for earnings, say net income, sales, net sales, gross sales. Some of these terms can be a little bit confusing. We're talking about basically the net income generally. So when we're thinking about the earnings per share calculation, we're typically thinking about the net income of the company and divided by the average outstanding shares of the company. So we're going to start thinking about it just by basically dividing by the shares of the company. So and we could get a little bit more complex on it, which we might do in some future calculations, taking the net income of the company minus the dividends to preferred shareholders. If there are any, there might not be any preferred shareholders because they would get paid first. And we're thinking about common shareholders divided by the average outstanding shares of the company. Note that this average is here because the net income number, the top number here is on the income statement, a timing statement. So it's showing the earnings of the company over a timeframe, typically let's say a year, for example. And then the number of shares outstanding is as of a point in time. So you could take the number of shares outstanding as of basically the end of the year. But if there has been changes in the number of shares, then you might try to get an average of the number of shares, which is not a perfect calculation. Because again, we've got two different kind of timeframes happening here. The top number represents income or net income that has been earned throughout the entire year. The bottom number is as of a point in time basically. If there was no change and the shares outstanding, which could be quite common for many stocks. If they're not issuing new stocks or doing splits or anything like that, then the average outstanding shares would be the same as the number at the end of the period. Okay, so this represents the net income. It would be on the financial statements of the company, on say the income statement. The shares of common stock, this would be something that would be on the financials to possibly the balance sheet. We're looking at possibly the equity section breaking up assets minus our liabilities equals the equity. The equity then allocated to the owners. The owners not being listed by people, but by shares, by units of shares. Many people can be owning multiple shares for example. So we need to know how many units of ownership are out there. And so that's going to be this number here. Now don't get thrown off by the large numbers as well because the ratios, the comparison, a ratio is a ratio. So it'll work the same if we had a large numbers or cut a bunch of zeros off of them, right? And then we've got the stock market price is going to be the 40. This is something determined by the market, by supply and demand. Based in part on the financial statements you would think because that would be driving people's supply and demand, but it's based on the market. Okay, so given that we're going to do the earnings per share calculation. So earnings per share. I'm going to make this a little bit larger here. Going from D making this larger. So here's the formula. You can think of it as the net income of the company, which is the earnings. And again that number, remember earnings is represented in the net income. So be aware of that revenue oftentimes is the top line of the income statement net income bottom line of the income statement earnings. And then we're not going to subtract any dividends. We're going to assume that there aren't any preferred dividends in this case. So I'm just going to take earnings and then divided by the outstanding shares. And again, we're not going to do the averaging here, which would mean if you had to average them, you might take the you'd have to figure out or find on the balance sheet or the financial statements for the end of the prior period. How many shares were outstanding and then find the shares outstanding at the end of the current period that you are looking at. Okay, so let's make the header black and white. I'm going to select these two tabs up top the cells home tab font group make this black and white. And then I'm going to pick up the earnings, which I'm just going to say is equal to the earnings. And this is going to equal the 6,500,000, which again we could find typically on the income statement. And this is going to equal the shares of common stock. So this is the number of units that are out there of ownership multiple people. One person could own multiple shares, for example, 2,900,000. And we're going to put an underline there. And then that's going to be our earnings per share. So this will be equal to the 6,500 divided by the 2,900,000. And let's add some decimals home tab number group adding some decibels. So note, you could get more complex if they had preferred stocks subtract not the preferred stock. You could do some different methods with the common stock. If there was a different number or amount of common stock at the beginning and end of the period possibly trying to take an average to get, you know, an average number. So that's going to give us our earnings per share of the two point or $2.24. So let's make this blue and bordered home tab font group. And I'll make this a border all borders and then blue with the bucket. And that's the blue, but I'm going to go down to more colors. If you don't have it standard, I usually pick that one right there. And OK, so there is that. Now that's something that we can, of course, use to compare to other other companies or stocks that we're thinking about investing in. So if this is, if this is the earnings, the total earnings of the company, we break it out into the standardized units. So one share then have the earnings of $2.24 would be the general idea. So that doesn't, however, mean that we got dividends of that about. You might say, well, if I, if they got earnings, if the company earned net income of $6,500,000. And each, that means each share earned $2.24. You might think first, well, I should get a dividend for that. But no, the company's going to decide whether or not they're going to issue the dividends or not. If they could issue the dividends for that, but they, or something, you know, but, but they may not. It depends on the board of directors and management. Or you might think that the stock should go up in value by that amount, which it may because, because the stock price is going to be dependent. Of course, on the earnings of the company to some degree, but that number will be dependent upon the market. So now we can say, okay, let's try to give a relationship between the earnings per share, the performance of the company on a per share per unit per standard component of unit basis to the market price, which is $40. So let's make a skinny F column over here. I'm going to hit the skinny C and do the paintbrush and make a skinny F. And we'll call this the price earnings ratio. And let's make this larger as well. Jeep will make that larger. Let's make black and white up top. We're going to go to the home tab font group, make this one black and then white. And so now we're just going to take the stock price, which we're saying is $40 per share. So each share that we purchase, and there's 2,900 of them out there. We're at $40. How do we know what that is? Because all the shares are the same and some shares are trading. So you would think that if people are currently trading all these things that are equal in value for whatever they're trading for $40, that would mean that all the shares would be worth $40. That's a little bit confusing or not exactly accurate in that if one person owned, for example, a large portion of the shares like Elon Musk owning Tesla stock or something like that, and they started to sell their shares, you would think the price would drop. But if nobody, just we own a few shares in our portfolio, sell a few shares, it's not going to have an impact on the market. You would think they would be worth the $40 because that's what they're currently selling for. So then we're going to be picking up the earnings per share. This is how much the company is making per share. That 2.24 is what we estimated. Let's go to the home tab and then numbers add some decimals. Let's put an underlying font group in underline. So that's going to give us our the price earnings ratio, which is going to be equal to $40 divided by the 2.24. We'll make this one home tab, numbers add some decimals. So we're saying that the revenue that's being generated is $2.24 per share. And we're paying $40, which is, you know, 17 times that amount of earnings on a per share basis. Now, whether or not that number is high or not is generally thought of as a relative kind of thing because we're trying to think how is this stock performing in comparison to other stocks. Now, you could think, of course, that the whole stock market is overvalued, right? We're in some kind of stock bubble or something like that. But generally, of course, we're trying to think what's the valuation of one company related to similar companies. So these numbers then could be compared to other stocks, typically stocks like in the same area or possibly to indexes, for example. So the other way you could think of this, of course, is if you had the $2.24, let's add a couple decimals. And then we multiply that 17 times 17 times the earnings. That's going to give us our price. So it's earning $2.24 per share. So if we bought that share, we would be paying $17.17 and 85 cents or 17.85 times the last yearly earnings at the $40 is what that's basically saying. So that's a common kind of ratio that we can use for comparisons. Let's make this blue and border here, home tab. Let's go to the font group, border and blue. And let's make this one a little skinnier. We can skinierize this one and delete this. Quick spell check on it. Review checking the spelling on it. Okay. So there we have it. We'll probably see, we will see those ratios multiple more times in the future. They're fundamental.