 Personal finance practice problem using Excel. Estimated future dividends and earnings per share. 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, in essence, answer key. Let's look at it now. Information on the left. Calculations on the right. We're looking at common stock, trying to predict what's gonna happen in the future from trends in the past, possibly to help us out with investing types of decisions. The second tab, practice tab, will have some pre-formatted sales so you could work the practice problem with less Excel formatting. The third tab, the blank tab, we will be dealing with the Excel formatting. If you don't have anything, you could just open up a blank sheet. I would select the entire sheet by selecting the triangle possibly. If you had a blank sheet, right click on the selected area format so you can lay down the baseline formatting which I usually go with the currency and then the brackets and red for negative numbers, no dollar sign and no decimal. I'm not gonna hit okay because I already have this. I'm just gonna X out up top. Then you can add your data on the left-hand side adding information as needed, formatting sales as needed. For example, putting decimals here and a percentization there and then we're good to go. So the information on the left, we've got the earnings per share and we're looking at past data for which it's gonna say X1, X2, X3, X4 and so on that we have calculated in past data to try to help us out with what we think is gonna be the trend going forward. We got the earnings per share, a constant rate of growth assumed to continue, meaning when we're trying to figure out what's gonna happen in the future, we have to make some assumptions, note that we can make similar assumptions to other investments, but it's easier with things like fixed income type things like bonds because if we're trying to value bonds, we can think about what's gonna be the future income of the bonds and that will typically be defined of course in the terms of the bonds in formal interest payments, typically semi-annual for example and a maturity payment with the stocks. The value that we're gonna be receiving will be possibly an increase in the valuation of the stock itself and the dividends that might be paid out to us in the future so those are the things that we can try to predict what's gonna happen in the future to try to value the stocks at this point in time. So the dividend percent of earnings we're gonna say is 30%. Now note that the dividend policy of a corporation could differ depending on the corporation. The dividend policy represents the company taking the earnings they have and distributing it to the owners which are the shareholders. The shareholders cannot just take draws as they do for like a partnership for example because all the shares have to represent the same amount that's being distributed therefore the dividend policy will be determined by the board of directors and the management. So they might determine that dividend policy typically they're gonna want dividends to go up and not typically go down or at least not go down without any knowing happen because that could be a bad sign to the market. So they might have a policy however that they tie the dividends to the earnings and that could be a typical kind of strategy because then you can have the dividends going up and down in relation to the earnings. So we'll make an assumption here that we're gonna say the dividend percent of earnings is the 30%. So given that we might look at past data and try to get some idea about the valuation of the stock by looking at trends and the earnings per share after calculating the earnings per share we might then try to say well let's take the difference how much is the earnings per share going up by and see if we can extend that trend into the future. So for example in 2022 I can say this is gonna be the 1380 minus the 12. This is a typical running balance type of calculation. I'm gonna add a couple of decimals home tab, number group add a couple of decimals do it again. We're gonna say okay 2000 X3 the 1580 sevens where we are now minus where we were before and we could of course copy this down. I'm gonna add some decimals which I'll do in a second but just to see the trend clearly and X4 we've got 1825 minus the 1587 add some decimals and then of course in X5 we've got the 2099 that we're at minus the 1825 adding some decimals there it is. Now this is a really useful kind of running balance analysis we see this often in Excel and the easiest way to do that is you could do the first calculation I'm gonna delete this to show it and then you can just copy that down by putting your cursor on the fill handle dragging down and it'll do that running balance calculation. So there is that so we could see this difference that is applying out here and that's useful and then we can also say okay what is the percentage increase that is happening here? So now we can say okay if it went from 12 to 1380 we got a 1.8% increase but possibly I wanna look at the percent increase because that could be something I can use for example to compare to other stocks to see what their percentage increase is even if they have a different earnings per share. To do that we're gonna take the difference divided by the prior balance so remember it's the prior balance. So this is gonna be the 1.8 divided by the 12. So I'm gonna add some decimal or percentize it home tab font group we need to recognize by percentifies add some decimals possibly but it's even. So then we're gonna do that same thing here this equals the change that happened from X2 to X3 divided by the prior period this is the percentage increase very again a very common kind of metric when you're trying to think about the increase in things in things like job performance for example if you were to measure baseball players increases and so on you would use a similar kind of calculation so it's really useful to have and be able to calculate with and so we've got the same percent increase it's gonna come out nice and even because that's how we set it up. We got the 2.38 divided by the prior period 1587 and once again that comes out nice and even and then one more time we got the change from X4 to X5 the 2.74 divided by the 1825 on X4 and percentifies and decimals it. Okay now we can copy that down it's a little bit more tricky so let's do that the easy way I'm gonna delete these what you're gonna delete yeah I'm gonna delete it but then we'll do it the easy way double click on here and so now when we copy this down we want this cell in C5 to move down and we want this cell over here to move down so that's an easy formula we should just be able to copy that down like so and double click I thought we needed an absolute reference but no we're good all the way down so there's a 15% change all the way down now that's a very consistent earnings trend that we can then assume is gonna happen as we go out into the future it might not be that consistent we might have to basically try to see what the data points are basically with this trend and then see what the most likely data point would be try to draw a line through the dot pattern to see what the most likely pattern would be if we were to draw a straight line through it for example so that's what we'll do here so now let's take about the earnings per share calculation the estimated earnings per share calculation for the following year the next year and we could do a quick calculation let's put let's make our E a skinny column I'm gonna skinner skinny up E and then we're gonna say this is the estimated earnings per share and we'll do it with like a table kind of format so we'll open this up and let's say this is for X let's say this is for X six X six what do we expect to happen? Let's make this black and white I'm gonna select from F one H one make that our header home tab font group drop down on the bucket black and the lettering is white so we've got the earnings per share so let's take the earnings per share for X five which I'm gonna put in the outer column here I'm gonna say that was equal to the 20 99 that's where we left off and we'll make that a decimalized number adding some decimals and then we'll make the subcategory which will be the increase so the increase percent let's call it brackets it's gonna be the earnings per share estimated growth rate let's say I'm gonna make this a little bit larger I'm gonna put this in the inner column this is of course gonna be that 15% that we're estimating that's exact here but again even if it wasn't exact we could try to draw a trend line which is what we think is the most likely outcome and then home tab number percentifies that and we're gonna add one to it plus one plus one to get it to 115% so I'm gonna underline that and then we're gonna say that this is going to be the earnings per share the estimated earnings per share for X six say X six I'm sorry this is gonna be the increase percent which is gonna be equal to the sum of the 15 plus the one or 115 the one could be a 100 number let's make that a percent so 115 or 1.15 and we're gonna underline that and then we've got this will be the estimated earnings per share X six it's a little X, X six and that's gonna be equal to the 20.99 outer column times the 115 so that's gonna be 24 adding some decimals home tab font group couple decimals 24, 14 or in other words you could do it this way right you could say it would be the 20.99 adding some decimals times 0.15 making that a percent that means the increase would be this times this that would be the three adding some decimals 0.15 and then add the original number so this plus this would be the 24 so you can calculate the increase hold on you can calculate the increase that way and then get to that or you could say it's gonna be 15% plus the original 100% or one and that gives you 115% which is a little bit faster of a calculation there I'm going to undo let's format paint over all this to get the sales back let's do some formatting here I'm gonna select let's select these three and indent them home tab number alignment indent and then indent again hold on indent again and then I'm gonna make this all blue I'll select the whole thing home tab font group bucket drop down that's the blue I want I'm gonna find it in the more colors standard and there's the blue right there okay and then font group brackets and then we'll choose these items too let's make that bracketed and blue because we entered data there and so there we have it now if we've got the earnings per share going forward we can of course also calculate the dividends so estimated dividends for X6 let's make this blue and or black and white we'll hit the bucket black and white so we've got the earnings per share calculation gonna make this a little bit larger so we can see it that's the 2414 we'll make that numbers and decimals and then we said the dividend dividend percent of earnings per share is 30% and again, we're gonna assume that's the case because it might be actually the policy of the company to do something like that to try to tie their dividends to the earnings per share we might see a similar trend in how they're calculating their dividends as the earnings go up they possibly are have a trend of increasing their dividends to the estimated as estimated dividends then is the 2414 times the 30% adding some decimals and we can go then home tab, add a couple decimals there we have it let's make this blue and bordered so we'll make this border blue and so there we have it now you also might try to do this in like a trend so you can see not just x6 but going forward so if I make like a skinny let's make a skinny eye selecting this and go into the skinny eye so we might have like a running type of balance where we're gonna say that we have let's say we have like the period and then we've got the earnings per share and then this would be estimated earnings per share and then the dividends dividends and so the period I'm gonna say is we've got x6, x7, right x8, x9 and we can continue out as far as we wanna go based on these assumptions let's make this black and white font group and make this black and white and so we can make this j a little bit thinner let's make these centered too alignment and center them so in x6 the earnings per share we could say okay the earnings per share is simply going to be the prior earnings per share I'll pull that from here the 20.99 times what we determined to be the one plus what we determined the change to be that we're gonna go for so brackets one plus the 15% and so brackets and enter so we get the 24 let's go up top and then say add some decibels so we get the 24.14 so we just basically did this calculation again and from there going forward we could say okay it's gonna go up by 15% again so we're gonna take the prior number x6 if we assume this trend is gonna keep going forward and we can multiply it times I'm gonna pick up the 15% over here plus one I gotta put that in brackets though brackets hold on a second well I know I know xo 15 plus one because of order of operation so I'm gonna take that times 15% plus the original or times 115% I wanna make that 15% absolute so I can copy it down and it doesn't move down I want this one not to be absolute because I do want it to move down as we pull it down so I'm gonna say F1 the keyboard for this one out here dd8 and you only need a mixed reference but an absolute one works enter and I'm gonna go home tab add some decibels put my cursor on the fill handle and drag that down so now you've got you know you can get like a trend analysis going forward and we said the dividends are always just gonna be 30% of whatever the earnings per share is that's our predictions so I could take the earnings per share times the dividend let's pull it from our data over here and take F4 and the keyboard because this is outside my data set I don't want it to move down I do want cave two to move down so all you need is a mixed reference but an absolute one works I'm gonna hit okay put my cursor back on it home tab I'm sorry numbers add some decimals there's the 724 then I'm gonna put my cursor on the fill handle double click and that'll just drag it on down so you can see a trend going forward let's center these let's go to the home tab alignment and center those so and we can obviously extend that trend down as far as we think would be applicable for our usefulness and as far as we think the trend might be applicable to think about going forward so I'm gonna go to the brackets and make that blue let's do a quick review of the spelling looks good