 Personal finance practice problem using Excel. Calculating dividend payments. 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 being an answer key. Let's take a look at it now. We've got the information on the left. We're gonna do our calculations on the right, calculating our quarterly dividends and our yearly dividends in a couple different ways. The second tab, having a pre-formatted worksheet so we can work through the practice problem with less Excel formatting. The third tab, basically a blank tab so we can practice the Excel formatting. If you don't have any of this, you could just open a blank worksheet, lay down the baseline formatting which would look something like this. I would select the entire worksheet if I had a blank worksheet with the triangle. Right click on the worksheet, format those cells. I usually lay down the baseline of the currency, currency I usually do, and then brackets and red and no dollar sign, no decimals. I'm not gonna hit okay but just close it out. That's usually the starting point for me. Add the data on the left-hand side, format the cells as needed including decimals for these cells, make a skinny C column and then we're good to go. So we've got the dividends. We're gonna assume or imagine that we have dividends that are gonna be paid out quarterly, remembering that dividends represent a corporation that's a separate legal entity paying the revenue generally out in the form of dividends to the stockholders, those being similar to say, draws for sole proprietorship or partnership except for the fact that they have to be the same for all the people, all the stocks out there and therefore one individual stock cannot determine the dividends or get a different dividend than other stocks so it has to be determined by the board of directors and the management. And oftentimes they're given out quarterly, not all the time, so different companies might have different ways that they're gonna be giving out dividends and so we're gonna imagine here that dividends are quarterly, you've got the Q1 dividends on a per share basis, 33 cents, Q2 on a per share basis, Q3 in a per share basis and Q4. Now remember, you might be looking at dividends from a company to try to predict what they're gonna do in the future. Sometimes they might have some kind of stable strategy where they might be using lesser dividends in the front of the year because maybe they make more money in the end of the year or something like that or they might just be increasing the dividends and next year we could expect the dividends to be 61 cents hopefully in the following year. But here we're just gonna say, okay, well if I have 440 shares, what are my dividends on a quarterly basis and how much dividends would I be getting for the year? And we'll do a couple of different ways we could do this calculation that could make it a little bit easier and it'll be practice for us to work with our Excel worksheets here. So we're gonna say, okay, let's do it quarter by quarter first, so I'm gonna say Q1, Q1 dividends, dividends and let's make column D a little bit wider here and we'll make the headers for column D and E, so D1 to E1, gonna go to the home tab, gonna go to the font group, I usually make those black and white, we'll make those black and the lettering we'll make that white for our headers and we're gonna say the number of shares we have, I'm gonna say equal pulling this from our data set, shares owned, I'm gonna say equals in E1 or is this E2 equals scrolling down to the 440 and B7 and enter, so now we can change this number down here of course and it would change automatically, I'm gonna undo that, our data up top so we can have a dynamic worksheet. This is gonna be our Q1 dividends per share, I'm just gonna say equals point to that Q1 dividend per share, this needs to be a little bit wider, it looks like to me, so I'm just gonna take column D and widen it up, widen it up and so we're gonna say this then equals in E3 equals the 33.33, it says zero there because I gotta format that cell, gotta add some decimals to it, gotta decimalize it, so we're gonna go to the home tab, number group, no that's not a word but we're gonna make it a word in case you're wondering, people get upset when I say stuff that aren't actual words but there's not enough words for stuff these days, font group, I'm gonna underline this and so we're gonna then go down here and then we're gonna say this is the Q1 dividend, Q1 dividend, multiplying this out, we're just gonna say all right, if I have equal 440 shares and they're giving out 33 cents per share, that's gonna give us 145, we can add a couple decimals just in case, number group, decimalize 145.20, let's do the same thing for the other three quarters and then we'll see how we can do the calculation for the year a little bit more easily adding them all together, so let's do the blue, I'm gonna make this blue and bordered because that's our custom to do and so I'm gonna go up top and say this is gonna be the bucket dropdown, there's the blue I need but if you don't have that, it's in the more colors, more colors. We need more colors, standard wheel, I'm gonna pick that one right there, why? Because that's just what I do and my ways have been set down and they're not changing unless I find, okay, I'm gonna make a skinny F column over here now I'll do the same thing, so I could go here and let's take the, and it might be better, it might be easier actually to go underneath, let's do it underneath here, that might be better. So let's say this is gonna be Q2, I'm changing up the formatting, Q2 dividends on the fly as I'm flying, I'm doing this and you didn't even know I could fly, I could fly and change the formatting. We're gonna go to the home tab, fought group, we're gonna go to the bucket dropdown, make this black and white, black and white and then we're gonna go Q2, once again, we've got the shares and that's still gonna be the 440 and no change on Q2 dividend per share, I'm just gonna say equals the Q2 dividend per share equals the 33 cents. Now notice we could do something fancy up here and just like copy the formatting here since it's the same kind of thing instead of reformatting it, let's do the paint brush to do that, we're saying I could see, I see I could do that, that makes things easier. Home tab, font, let's go to the paint brush painter and just paint brush that right there, I'll just put it in the top cell, boom, format done for us automatically, I'm just gonna put in Q2 dividend, it's trying to help me but it's not, so I'm just gonna hit the space bar two times because I don't want that per share thing, this is the total dividend for Q2 quarter two, this is gonna be equal to the 440 times 33 cents and there we have it, let's do it for Q3, Q number three that stands for quarter three dividend calculation, once again I could just format the whole thing off the bat right off the bat, that means like when I'm starting out off the bat, home tab, clipboard, let's hit the paint brush right there and just paint brush the whole thing just as the ball is going off the bat and so this is gonna be the shares owned once again, shares owned is gonna be equal to the 440 and then now we're looking at Q3 per share, Q3 dividend per share has now going up to the 58 cents per share, so that means my Q3 dividends is gonna be equal to, I put an S that time, I hadn't done that before but that's okay, we're gonna say this times this, so now we're up to 255 dividends, we're hoping that trend stays the way it's going and so let's do one more Q4 dividends, let's do our format painting, I'm just gonna format paint this cause it's gonna look like the same bit, same bit, home tab, clipboard, paint brushy, put that paint brush right there, boom, one brush stroke and the whole fence is painted. It's amazing, equals we're gonna say this is the shares again, equaling the 440 and now we're gonna say this equals the dividends for Q4 dividend per share, notice I can also just like copy this to the right so I can put my cursor on the fill handle, drag it to the right and there it is I can now, what that did is it messed up the formatting though, it messed up the formatting, I could get a little fancy there, I could right click and copy it and then right click and paste it special but then I just want to paste the formula, the formula, not the formatting and then okay so it'll get the formatting, not mess up the formatting just to play with some different Excel stuff and so this is gonna be Q4 dividends which is gonna be equal to the 440 times the 61 so there we have Q4 dividends, now if I was to add them all up and just say dividends for the year it's gonna be equal to Q1 dividends plus Q2 dividends plus Q3 dividends plus Q4 dividends and there's the dividends from the year number and we can add some decimals let's make it blue and bordered, home tab, font group, bordered, blue now we could do that a little bit more quickly we could say let's see if we can do that in a table without having all these separate tables let's try to do it again a different way we're gonna, I'm gonna make a skinny C column over to the skinny F using the format painter, home tab, clipboard, paint brush, format paint, column F this is gonna be the dividends per year let's just say for the year and I'll make this a little bit whiter here I'm gonna make from column G1 to I1 and make that black and white with the font group bucket drop down, black and white and so I'm just gonna say this time I'm just gonna add up all the quarters dividends so this is gonna be the quarterly dividends per share brackets and I'll just add them all up so I'm just gonna say this equals notice I put a colon here so we're gonna have a sub line down below indicated by the colon, I'll also indent it and I'll put a sub column just to practice our formatting this will be equal to the Q1 dividends and I could just copy that down to Q4 putting my cursor on the fill handle dragging down to Q4 and then I could drag it to the right and that'll actually pull in the numbers but they're not formatted so now I'm gonna select these cells to add some decimals, home tab, number group decimalizing them twice, double decimalized and then we're gonna go down here and say home tab, font group, underline and we'll say this now is gonna be, I'll just copy this I could say total dividends but I'll just say copy, paste, double click get rid of the colon because now this is the total of my subcategory I'm gonna select these five go to the home tab, alignment indent and then select this one again indent again, home tab, alignment indent again we'll sum these up in the outer column now using our trusty sum function the most famous function and deservedly so of Excel it's amazing so it's just so useful that you don't actually recognize the amazingness of it it's like the faucet in your house you're like hot water take advantage of it but it's just there, it's just amazing the sum, what do you talk, just do the problem okay, we're gonna finish this up we're gonna say this is the shares owned this is gonna be equal to the 440 let's put an underline there we're gonna go to the home tab, font group, underline and this is gonna be the dividends for the year and we'll just multiply that out we're gonna say this is the 1.85 times the 440 so that's a quicker way to get to just that year end dividend, although we don't have the breakout of the Q by Q, the quarter by quarter breakout in that format you could of course make another table we might format it in a table let's just do it one more time let's put some borders and blue around this one border and blue you might say something like let's say that let's make a skinny F or skinny J, we're gonna go from the F home tab and say F and then you might make a table looking something like this you might say this is the dividends for the year dividends for the year and then I'm gonna make this a little bit wider and then let's do it this way you could say this is gonna be Q1, Q2, Q3 Q3, Q4 and then the total right we could do something like this which would make sense and we can say that we're gonna say this is going to be font group brackets black and white let's put some center in around these ones alignment and center and then I'm just gonna say all right this is the dividends per share which is gonna be equal in Q1 we're gonna be the 33 tab Q2 I'm gonna format it shortly 33 tab Q3 equals the 58 cents tab and Q4 equals the 61 cents tab let's format all of these holding shift scroll into the left home tab number adding some decimals I could sum up the total on the year then equals the trusty sum function trusty sum enter and then we're gonna have the shares the number of shares now if we have the same shares all the way across for the full year then this will be easy to do I'm gonna put my 440 I could say F4 on the keyboard making an absolute so I can copy it across so I can copy this across putting my cursor on the fill handle copy it across it's not moving that reference so I'm just picking that up another way you could do that I often do it is just doing it this way equals the one before it and then copy it across so you can use that and so then I'm gonna copy it all the way across to the total notice that then you could do your calculations so you could say total dividends which would be equal to the 33 cents times the 440 in Q1 and I can copy that across putting my cursor on the fill handle dragging to the right so there we have that and we should come up to our same quarterly dividends as well as the yearly dividend now notice this yearly dividend calculation will only work this way if you didn't buy or sell any shares during the year if you did then you'd have to sum up the dividends and you could also double check this number by summing it up again maybe let's do it over here I'll sum it up again just summing up the four quarters so now I've got to double check this way and this way if any of these shares change in the quarters then I can't do this multiplication it won't work because now we have a difference we're gonna have to do the quarter by quarter a breakout kind of calculation then now let's do it one more format let's make this blue and bordered font group blue and bordered notice that you might kind of visualize this in a vertical fashion so I could go let's make this Q J format paint that over here and you might say that you want the dividends dividends let's just say call it the dividends and then I'm gonna say that we're gonna have the quarters this way so I'm gonna say Q 1 Q 2 Q 3 and Q 4 and then the total and so let's put an underline under these two underline here home tab font group underline those please so then we've got the dividends and then we're gonna say now notice you don't have as much room on the headers when you do it when you kind of do it this way so you gotta abbreviate the headers to some degree possibly or you can use multiple cells so I could say dividends per share I could say like div per share which is a little bit shorter or I might want two cells which I could use the home tab alignment and wrap although I don't really like that unless I'm gonna make it into a table because it makes everything else to the left fat when I just want that one to be the fat one so I'm gonna say let's undo that another way you could do it is I could take these here I'm gonna give it some space by dragging them down or I could say control X or right click and cut and bring them down here and then I'm gonna call it div let's say per share let's do it that way and so now I've got two I'm telling it when I want it to be broken out into two shares which will both be the header because I'll make them black and white and then we could say shares owned shares owned notice I did the same thing it's on two two rows and then this will be the total total and just call it div div for short you could type it out probably have room there and then I'm gonna select this whole thing make that black and white home tab font group making it black and white and let's just choose these ones and center that alignment and center and so now we can pick these up and I'm gonna say the dividends per share and I could just pick these up by saying equals and this way I can just copy them down because they're all in a vertical fashion so I could just say let's pick that one up and then just copy it down fill handle let's format pane it home tab number group adding some decimals let's total it up equals the trusty sum function adding some decimals number group couple decimals there and then the shares owned are gonna be equal to and once again it's gonna be about 440 which I could then say F4 on the keyboard making it absolute so it doesn't copy down dollar sign before the well it will copy down or it won't change the cell when I copy it down dollar sign before the B and seven you only need a mixed reference but an absolute one works and then I can copy that down like so or down to here actually or I could say let's just delete these ones and say I'm just gonna say equals the one above it therefore I don't need an absolute reference and so that kind of works too it's a little thing that'll work and then we're gonna say all right so now let's multiply them out this equals this times this in our vertical file table and this is nice because now I can just double click here instead of fill handling down I could just double click and boom and it sums them up that way and then I could I could go one more down I could do it this multiplying it out that way or I could sum it up for a double check of this way and I should get to the 814 either way we could add some decimals here maybe just in case we want a little bit more detail we could put an underline under like these for example and so and this is like my check figure check the check figure let's make a check figure here so great practice for just practicing tables fairly simple tables but you want to be able to visualize how you can construct your data if you can construct your data in different ways then you have a better grasp of the data and you can work with other people who will inevitably construct the data in weird and unusual ways because other people's minds work funny my mind's the only one that works right other people's minds they make stuff all weird but then you gotta figure it out because that's like and that's fine as long as it works right I mean home tab font group we're gonna put some brackets around this let's put some blue let's let's do a spell check see how many words I didn't spell right div and that's an abbreviation Excel that's not wrong that's not wrong I'm just gonna ignore that or change it did I change it okay so there we go