 Personal finance practice problem using Excel. Dividends yield, earnings per share, PE ratio, and payout ratio calculations. 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, three tabs down below. Example, practice blank, example, answer key. Let's look at it now. On the left, the calculations will be on the right. We're looking at evaluating or using typical kind of ratio analysis to evaluate stocks, possibly to help us out with our investment decisions. The second tab, practice tab, pre-formatted worksheets. You can work through the practice problem with less Excel formatting. The third tab, the blank tab, we're going to do the Excel formatting. If you don't have any of this, that's okay because you could just open up a blank sheet and then lay down the baseline formatting. I would do so if this were a blank sheet by selecting the triangle up top. Right-click in the selected area, the entire thing. Format the cells. I usually go with the currency and negative numbers bracketed and red. No dollar sign, no decimals for the baseline start-out process here. I'm not going to hit okay because I already have this. I'm just going to close out up top. Then enter your data up on the left-hand side, formatting the cells as necessary. For example, adding some decimals here, make a skinny C column, and then we're good to go. So we got the price, market price. Remember that when we're talking about stocks, we usually talk about corporations that are separate legal entities breaking out their ownership into fixed or even units of stocks. We're usually talking about publicly traded companies, those which are traded on public exchanges, and the market price represents what the market through supply and demand on the exchanges have determined the price to be. How do we know that? Because other stocks are being traded at the same amount, and the stocks are all the same unit of ownership. You got the dividend per share, we're going to say is $4. Remember that the dividends represent the company distributing some of their earnings that they have been generating to the owners. The shareholders kind of like draws for a partnership or sole proprietor except for a partnership. Typically the partners can determine how much they're going to have as a draw with the corporations. We can't have one share basically trying to have a separate amount of dividends than other shares. They all have to be the same. Therefore, the dividend policy is set by the board of directors elected by the shareholders and management. So that's how much is being paid out. Remember that the dividends often have to be or the companies like them to be somewhat consistent, meaning they'd like them to be going up, not down, or at least be tied to possibly the earnings of the company. And then we've got the payout ratio. The payout ratio is going to tell us how much of the dividends are going to be paid out in the form of earnings. The payout ratio is something that we might basically estimate or in some dividend policies they may basically have in the dividend policy kind of a ratio that they're going to be using to be calculating the dividends. Okay, so let's first think about the annual dividend yield. So let's say the annual dividend yield. Now the dividends represent what's actually being paid out. Remember when we're investing in stocks we want to return on our investment which comes in two ways typically. We've got dividends which are them actually paying us money from the earnings of the corporation and we've got the increase in the value of the stock possibly because they may be paying less dividends. In that case keeping the money to put it into assets in order to increase the workings of the company which should increase the stock price if they do that efficiently. Let's make this a little bit wider. Call them D here and we're going to say this is going to be black and white on the header, selecting D1 to E1, going to the home tab, font group, bucket drop down. We're going to make this black and then with the lettering we're going to make that white, black and white. And so then we're going to say the dividends per share. Now this we're going to determine on a, we're looking at a yearly basis if they pay quarterly for example we're going to basically annualize it because typically when we think about the dividend yield we're typically thinking about annual time frames. Although you can look at other time frames as well, you just want to make sure that when you're using measurements of time that you are using the same time frames annual is the default typically. So I'm going to say this is equal to four dollars. And then we've got the market price which is determined by the market. We know that because the market is currently selling the stocks for that amount which we're going to say is the 120. So if we got a return of four dollars and the current market price is 120 if we were for example to buy it for 120 and expect the dividend yield at four we have our basically our return on the estimate, the yield, the investment, the yield. So I'm going to underline this and this is going to be the annual dividend yield. So this equals the four divided by the 120. We need to percentify that in order to recognize. So home tab number of group with percentify it. Let's add a couple of decimals. So there we have it. Now that dividend yield could be more important for people that of course are investing in types of stocks where they're trying to get a dividend possibly because they're in like retirement for example and they want a higher dividend. But we also that's not the only thing that we need to take into consider when comparing the stocks because we could also have the price of the stock go up in value. So let's go ahead and make this blue and border. This is a common ratio. We have to have an idea or concept of what it is to understand what's going on. Home tab font group. Let's make it border. Let's hit the bucket drop down and make it blue. If you don't have that blue, it's in the more colors standard. I'm going to use that blue right there. You don't have to use it. You could do whatever color you want. So let's go ahead as long as people can read it or you can read it. I don't even care if they can't read it. If you don't want people to read it, whatever. That's what I'm doing here. Earnings per share. So now we're going to have earnings per share. So this is another quite common calculation. Of course, let's go up top and say that we've got the font group bucket drop down. We're going to make that black and white. Now normally when you calculate the earnings per share from say the financial statements, you're really looking at generally the income statement, the performance statement. You're typically looking at the bottom line, the net income to see the performance of the company. And then you're trying to break out that performance of the company into standardized units, basically dividing it by the number of shares that are outstanding. There could be variants on that. You could say it's the average number of shares if there were changes in the shares. But that's the general idea, right? We're trying to take the earnings over a period of time here, for example, a year. And then we're trying to allocate those earnings, the performance to fixed units of ownership, those being the shares. But here we're going to do it a little bit differently because we've got this payout ratio, which we said is 0.6. So if we have that, we could take the dividends per share and help us back into the earnings per share so we can get an idea or a feel of the relationship for some of these ratios. So we'll do that and then I'll kind of prove it the way you might normally see it. So we're going to say we have then the dividends per share, which we said were $4, which we're saying are the annual dividends. And if the payout ratio, the payout ratio is 0.6, meaning they're paying 0.6 of the earnings in the form of dividends. If we know those two things, we can back into the earnings per share. So we're going to say our at the payout ratio is 0.6. I'm going to percentify it to recognize or destabilize it, home tab, number group. Let's add some decimals. Let's put some underline under it, font group and underline. And this is going to be the earnings per share. So we'll just take this is going to be equal to the four divided by the 0.6. And that's going to be seven about. Let's round. Let's put some decimalizing, decimalized. So there we have it. Now, how you might normally think of that is to say, okay, first we would calculate the earnings per share from the financial statements, net income divided by the total number of shares outstanding. For example, again, you might say net income minus preferred stock, divided by the weighted average. But the general idea is taking your net income, applying it to the number of outstanding shares, and then figure the dividends if you have the payout ratio. So let's just recalculate that so we can get a feel for it. I'm going to go up top, home tab, font group, put some borders and some blue around this one. I'm going to make a skinny F column. So I'm going to do that by taking the skinny C over here, home tab, clipboard, hitting the format painter, and then tapping down on the F to make a skinny F. So if we had the payout ratio, the payout ratio, when we think about how to calculate it, I'll make this a little bit larger. And then I'm going to make this black and white for the header. I'm going to go over here, home tab, font, group, bucket, drop down, black and white. So if we say normally the payout ratio would be the dividends per share, the dividends per share, which was here. Let's take this one and this is going to be equal to the four. And let's make this a little bit larger. And then we would take that and divide it by the earnings per share. So the earnings per share is here. So if you thought about how to calculate the payout ratio this way, you might back into this number, for example. So I'm going to make that yellow. And oftentimes if you're looking at these little calculations, we're using algebra, of course, you could type it out algebraically. But sometimes I think it's useful to write these out as like a table format in the way that you know it and then back into it instead of trying to think of different formulas, what you want to do, like many things when you're learning anything like music or something like that. You want to approach things from different ways and just see how the connections kind of fit together. So if you're using this calculation, I'm going to use my algebra to basically back into that, but I'm still going to write it down in the way I know it in like a table format. And then we've got the payout ratio. So that's going to be the payout ratio, which is going to be then, this is what we know, which is going to be this point six point six. Let's add some decimals home tab number group. Let's add some decimals or let's not percentifies it. Let's add just add decimals and undo that a couple of decimals. So then I know that if I take this divided by this, I should get to here and therefore I should be able to say that this is going to be equal to this divided by this. And hold on a second. It should be equal to this divided by this four divided by point six, add a couple decimals and go up top, add a couple decimals font group and underline. So I can kind of, and then I can kind of prove it to myself over here do it the way I know it this way times this, add a couple decimals and then multiply it out. This times this. So sometimes I'm sorry equals this divided by this. So I know I'm doing this a little bit kind of tediously, but sometimes I think this is a useful way to kind of to think through the connections of things. You could do it. You could write it down algebraically, of course, as well to help you to see how things are linked and then rework the algebra. When you look this stuff up in books, oftentimes it'll it'll feel like you need to remember different formulas, which are really just reworking the formulas, you know, and you just need to see them in a different format. So let's go to the home tab. So you just want to see the connections is what I'm saying. Let's do it one more time. I'm going to make all this stuff blue and make this stuff blue and bordered. So the other way, the other way you might see this is as if you're calculating, as if you're calculating the the dividends. So for example, I'll make a skinny J column go to the F. I'm going to go go to the paintbrush, make a skinny J. And if I was to calculate the dividends and let's say the dividends and what I know to calculate the dividends is the payout ratio and the earnings per share, then I can calculate the dividends. So I'm going to make this a little bit larger. I'm going to make these two black and white home tab font group. Let's make this black and white. So if I to calculate the dividends, I might first calculate the earnings per share, which I'm going to say is kind of like the unknown here again, the earnings per share. Let's make that yellow in my table. This would be the unknown. And then if I knew and then I would take that and I'd multiply at times the payout ratio, which is what we were given. The payout ratio is given here. And so let's make that here. Let's make a couple of decimals. Let's put an underline and that would give us the dividend, dividends, dividends per share, right? Dividends per share. And that was given to us at four, the four dollars. So we know that if I had this times this would equal this. And so therefore I can take this divided by this and we should get back to that 767 again. So home tab and let's go to the number group and some decimals and then I could check it by taking this number and then equals this number and multiplying them and see if I get the total that I should. And so we'll go over here and say this times this. So I'm just kind of reworking the algebra a couple of different ways. But I think it's really useful to kind of see it these different ways so that again, you know the connection. So if I knew how to calculate the dividends, if I had this stuff, I'll write it the way I would write it if I had that information and then back into the unknown if I'm just talking about like a three number formula and so you could do that algebraic however you want to do it. Okay, I'm going to then say this is going to be brackets. Let's make this stuff blue. This stuff blue and go to the bucket and make it blue and then let's make this black. So there is that and then we can calculate the price earnings ratio. So we're going to say this other common ratio price earnings ratio also known as the P E ratio. So fairly straightforward because it tells you right what to do right there. Let's go to the home tab font group and black and white. And so we've got the market price, which was here that equals 120. That's what it's selling for on the market a share on the market. And now we've calculated our earnings per share or they did they give us the earnings per share. We calculated the earnings per share here at the 667. So we're going to take that and pick that up here the 667. Notice there's rounding involved. So if I added more decimals, for example, just be aware that we're using the actual number even though we're rounding it or we can only see it rounded to 6.67. I'm going to then go up top and underline this. So that's going to give us our price earnings ratio. So now we're going to take the market price, which is typically going to be higher than the earnings per share divided by the earnings per share. And so we've got then 18 adding some decimals number group added a couple decimals were at the even 18. So just remember the dividend yield represents kind of the earnings that we are receiving that are actually being paid out in comparison to the market price. The earnings per share then is representing the actual earnings that we're getting that the company is earning on the income statement allocated on a per share basis. The earnings per share, you might think, well, if that's what they earned, that's what they should be paying us in the form of dividends. But that's not typically going to be the case because the company is going to roll over some of those earnings in order to generate more revenue, which will hopefully benefit the shareholders in the form at of increased prices because the company hopefully will be efficient in using those assets more so that we would to generate revenue. And then when we have the price, we often want to compare the price to the earnings. In other words, if the company is earning this 6.67 per share, you might say, well, that should then be the market price. But that's not typically going to be the market price because they're projecting future earnings into the future. Right. So how many times over is that earnings for the current time period per share divide into the actual market price what the stocks are selling for, 18 in this case, then we can use that 18 to kind of compare to other stocks. Also just note that this payout ratio might be something that you kind of determine that you might try to calculate to figure out how much of the earnings that the company's earnings say on a per share basis is being paid out to the stockholders or they might have in the form of dividends or they might have a policy in which they're basically paying out a portion of the earnings in the form of dividends depending on how the board of directors is conducting their dividend policy. And so obviously that can be a useful tool in calculating some of your calculations. And if you know something, you know, if you know a certain calculation you're trying to back into something, then oftentimes you don't need to memorize another formula like the earnings per share formula or the payout ratio or the calculation of the dividends. If you're using a payout ratio, you can basically use the formula, you know, and then plug in the unknowns and then use your algebra, of course, in whatever way you want to do it possibly in table format if there's only three numbers or writing it down algebraically to figure out the unknown. All right. So let's make this bottom one. Let's make this bordered and blue. And then I'm going to just review this to check the spelling dividends, dividends. And so good. That's not too bad. All right.