 Personal finance practice problem using Excel stock split versus stock dividend. 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. Information on the left, calculations on the right. We're getting a better understanding of what happens with stock splits and stock dividends by looking at the mock balance sheet of a corporation, focusing in on the equity side of things as we consider the split, as we consider the stock dividend. We want to see what's going to be happening with the equity side of things. We also, as investors, are going to be concerned with what will happen to the stock price and possibly also what will be happening with any dilution in our earnings percentages, which might not be so important if we have a very small amount of earning percent. Because remember, stocks are similar to kind of like voting in a republic, for example. We have the capacity to vote for, say, the board of directors oftentimes. But like one individual in a republic, if we hold a small amount of stocks, our voting power is quite small. However, if we own a significant amount of stocks, like 10% or something like that, we have significant voting power, which we're going to most likely be concerned with if the company is going to be distributing more shares. We want to know what's going to happen to our voting power. That's another question that would also often come up. The second tab is going to have some pre-formatted sales. You can work through the practice problem with less excel formatting. The third tab is going to be a blank tab where we're going to be doing 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 format the entire worksheet. I would do that by selecting the triangle up top, right-click in the selected area, go to the format cells, and then go to currency. Negative numbers bracketed and red, remove the dollar sign, remove the decimals. That's where I typically start. I'm not going to hit okay because I've already done this. I'm just going to X out of it. Then we do have a significant amount of data to enter on the left-hand side because we basically have the full balance sheet. This is common to do in practice if you work in an accounting office. It's really good practice just to enter the balance sheet because it makes you understand the balancing process and use formulas to enter things like total assets, total liabilities, and then how you're going to get the total equity in the liabilities and equity. It's really actually a good exercise to do and then make a skinny D column and then we're good to go. Just a quick recap on the balance sheet side of things. If we're looking at the company, assets represent what the company owns. Liabilities means they owe something to a third party like a bank, like a loan. Then the equity where we're focused, you could think of as, in essence, the net assets, assets minus liabilities from the accounting standpoint. We think of the assets as what the company has and then who has claimed to those assets, either third parties or the owner in liabilities. The equity then represents if there was only like one owner. For example, if this was a sole proprietor, your total equity would represent the value of the company for that one owner, which in a corporation, we have to break out to multiple owners. But unlike a partnership, which could have different ownership percents, we're going to break them out into standard units, which makes it a little bit more easy in many ways of shares. Now, you could have like preferred stock, which kind of muddies the water a bit, but we're basically thinking we're breaking them out into the common shares here. So if we think about our ownership in the company and the equity, this is how it breaks out in this example. We've got the number of shares, 100,000 that we're going to assume are outstanding. So that doesn't mean that 100,000 people own the company, but there's 100,000 shares out there we're going to start off with and some people could own, for example, multiple shares. Oftentimes, there's a par value. The par value isn't not the market value. It's an arbitrary kind of number that helps us to kind of value how many shares are out there. So if we use two as the par value and we could see, if we say that common stock is 200,000, we can easily then calculate the number of shares outstanding by taking the 200,000 divided by the par value, which is the number of shares. Whereas if you look at it as the issue price, then the issue price could change over time. So this is a way to get that common stock kind of a set value on the par value. And then the capital in excess of par is the dollar amount over and above the par value that was actually paid for the stock. So therefore these two amounts right here represent kind of the revenue generally that the company earned or the capital that was generated typically cash when they distributed the stock, mostly oftentimes from the initial public offering, where when people invested in the stock, they actually paid the company and then it was recorded with an increase in equity and an increase in cash, right? And then other times when stocks is traded, it's on the secondary market and you're not purchasing it from the company, but from other people and therefore there's not an impact on the balance sheet in that case. And then the retained earnings represents the earnings of the company that have accumulated over time calculated on the income statement which have rolled into the balance sheet here into the retained earnings which have not yet been distributed in the form of dividends, dividends typically representing the distribution of the earnings of the company back to the owner. So when you look at the equity then these two sides of things, the common stock and the capital are kind of like the investments from the owners when they purchase the stock, the owner putting money into the company basically generally and then the retained earnings is the earnings of the company that has been accumulated over time which has not yet been distributed to the owners in the form of dividends and that gives us our total equity and then we've got the total liabilities and equity here which is our balancing concept. Okay, so now like if you thought about this from a book value then you would think okay well if the total equity is this amount right here the total equity divided by the number of shares outstanding you've got your five something right or four ninety four book value kind of per share so you'd be breaking your calculations out based on the number of shareholders out there and then figure out in how many shares you have those that would be the concept of your calculation. Now the market price we're going to say is twelve dollars that is determined by the market right but the market is going to be reflecting of course how many shares are basically outstanding in part of the calculation. So let's assume first we've got a stock split situation so they decide they're going to do a stock split well what would that happen that would mean that all the shares that are outstanding if we owned any shares what we had before is now going to double in the number of shares what does that mean on the on the corporation side of things well that would mean the number of shares if there was one hundred thousand would suddenly be two hundred thousand and then the other side that would change is just the power value would then generally go to one and then the stock price we would assume because the stock market is independent from the company but we would assume once that the knowledge of that has been coming up because of the market that the stock price would change to half of that to six dollars. So let's recreate this just so we can kind of see what would happen just to see the balance sheets kind of in the same place in essence. Let's say this was a two four one stock split and I'm just going to recreate the balance sheet here and let's make this a little bit wider up top like right there I'm going to select these cells for the header home tab font group making this black and white and I'm just going to basically copy the whole thing over I'm going to say this equals the assets and I'm just going to put my cursor on this top item and I'm going to take the fill handle and go all the way down to the liabilities and equity now I don't have the formatting here I'd like the formatting so I'm going to take the whole thing again and I'm just going to go to the to the home tab format painter brush and put that right on the total assets so there we have it. Okay so I'm just going to recreate this I'm going to make this a little bit wider and then I'll go in and change just the equity just to get a feel for recreated so I'm just going to pull over I'm going to recalculate the subtotals as we pull this over and I'm going to format it a little bit differently I'm going to put the assets on the on the inside here so this is going to be equal to the thirty five thousand I'm going to copy that down to the fixed assets put an underline here font group underline pull that to the outer column equals the sum of the outer column that's going to be then the one three three five and then we've got the liabilities I'll put that on the inner column equals the two ninety eight enter put my cursor back on that fill handle drag it down I'm going to put an underline under the fifty font group underline total liabilities I'm going to pull to the outside here using the trustee SUM summit up for five or and then we've got the shares down below we're going to say we've got the shares are going to be equal to the one hundred thousand I'm going to put my cursor on that drag it down for the par value two and I'm going to calculate that right out here subcategory right up on the side as we did so one hundred times the two that's going to be a common stock two hundred thousand and then we've got the capital in excess of par which is just going to be one hundred thousand the retained earnings which is going to be the same at the six eighty seven let's put an underline here font group underline and then I'll sum this up equals the SUM of the equity and that's going to be the nine eight seven and then liabilities and equity is this number and this number equals the three forty eight plus this number here and enter so let's put it we can put the double underlines here I'm holding down control and here font group double underlines so there we have it and so let's select this whole thing I'm going to select this whole thing and make it border blue font group border bucket drop down there's the blue I typically use I'm going to go to the more colors and go to the standard and there's the blue okay so the only thing that's going to change here then is we're going to take these these items right here and I'm going to make that yellow and I'm just going to say what really happens on the company side of things well there's going to be two a stock split was this times two and then the power value we're going to drop down to one so now that's really the only change right we're basically in the same spot as we were for before but now we've got this two hundred thousand shares that are now outstanding so you can see the bottom line is if they sold the stock then you would have an impact because cash would have gone up and for example but here all they did was basically split the stock no real impact on the rest of the balance sheet what would be the stock price if we looked at the stock price calculation we would assume the market would take that into consideration but again it would be dependent on the market home tab font group black and white we would say so prior prior price was twelve and we would assume that the split two for one a two for one split would mean that the new price according to the market would most likely determine the new price to be the six percent or six dollars six dollars put an underline here font group let's put an underline and let's make this a bucket blue and border so that would be the general idea so if we for example had one stock that we invested in before the split and it was valued at twelve dollars now we would have two stocks at the six dollars meaning our investment should basically be in the same spot we can also calculate our percentage of ownership because remember our percent ownership will kind of measure how much control we have over what the corporation does I won't get into that right now but we should come up with basically the same percentage ownership as well but for now I want to compare it to like a stock dividend so let's make a skinny H column I'm going to put my cursor on the D we're going to go to the the format painter paintbrush it and go to the H and let's just recreate this again so I'm going to say this is the stock dividend dividend and it's a ten percent I'm going to say point one let's put the point one like over here point one or ten percent home tab number group percentify I'm going to make this a little bit larger so we could see what's happening I'm going to make these cells black and white again let's make them black and white and so this time I'm going to hide these items and I'm just going to recreate our balance sheet again so I'm going to put my cursor on skinny D go on over to G skinny D to G and then right click and hide those cells ok so we're just going to recreate this I'm going to say this equals the assets I'm going to put my cursor back on that fill handle all the way down so it copies that stuff over and then I want to copy the formatting to so I'm going to select this whole thing right here we're just going to select that whole thing and I'm going to go to the home tab format painter and format paint here so that looks good and then I will pull in the numbers so this is going to be equal to same stuff again we're just going to say this equals the 35,000 I'm going to put my cursor on that fill handle drag it down to the fixed assets put an underline under the 890 home tab font group underline sum it up in the outer column equals the SUM in the outer column liabilities are going to be equal to the 298 put my cursor back on it fill handle drag it down underline under the 50,000 home tab font group underline sum it up in the outer column equals the SUM shift 9 the 50 the 298,000 and then on the equity side of things our focus is down here this is going to be the shares this is our starting point which I'm just going to re-input and then we'll change it this equals the 100,000 times the 2 there's our starting point and we said the par the capital and excess of par and the retained earnings that's our starting point let's put some underline there and let's put a SUM here equals the S to the U to the M SUM and then we'll say this equals the liabilities plus the equity and so there we have it let's put a double underline there and there we're in balance just like my whole my practice here we're totally in balance and so I'm going to then say it's like by yoga work out or something it's totally in balance let's make this blue and blue and bordered home tab font group bordered and then blue and so then our focus is going to be down here in this area now it's going to change a little bit more significantly let's go to the font group and let's put some yellow here and let's do some sub-calculations and try to understand okay what's going to happen if we give out a 10% dividend so now we're giving a stock dividend not a cash dividend so we're actually giving out value we're giving out value therefore we would expect the retained earnings to go down because normally you give out a cash dividend and this goes down and then cash would go down right but now we're giving a stock dividend but it still has value so the retained earnings would go down but now the numbers of shares that are outstanding are going to go up so how's that going to work so let's try to figure this out we'll say let's make a skinny L column I'm going to go to the skinny column here which I think is an H so skinny I can't even tell what it is anymore you've got to lay off the diet here so skinny I can't even tell what you are so there's the skinny L this is going to be the number I'm going to say this is a number of new shares let's make this a little bit larger and so let's make this black and white we'll do a sub-calculation font group paint and make it black and white so we got number of shares outstanding so we've got starting starting shares was the 100,000 don't pull it from here because we're going to change this I'm going to pull it from here was the 100,000 shares and then we said there was a the dividend dividend percent let's say let's call it a stock dividend percent and that's going to be equal to I said the 10% up top let's make that a percentize number percentize so we can recognize font group let's put an underline and so that means we've got the number of new shares that are going out are going to be the 100,000 shares outstanding that we're holding on to some of these and then we've got times 10% and now there's going to be 10,000 new shares that are going to be issued out there so let's make this blue and bordered font group border and then blue okay so then the number of common stocks that are going to be out there so what are going to be the new new total common stocks that are out there selecting these two font group bucket make this black and white we're going to say that we've got number of shares outstanding before starting shares let's say are 100,000 and then the new shares are 10,000 so the number of shares outstanding after the stock dividend are going to be the new total total common stock which is equal to the sum of these two we've got the 110,000 let's make those blue and border we're going to say font group border and then blue on those so then we could say okay what's going to be the common stock value remember we break out the value between the kind of like the investment on the common stock and then the retained earnings component so we're going to say let's look at the common stock common stock value using the par value calculation it's going to be these two font group bucket it's going to be black and white so we're going to say this is going to be then number of common shares after the dividends which is equal to the 110,000 and then the par value which is still two dollars two dollars put an underline here font group and so then that's going to be the new let's say let's call this new common stock new that's going to be the new common stock value which is going to be this times this new common stock value and so let's put a blue and border that one border bucket and blue and so then so then we're going to have the capital in excess of par calculation so that's going to be this number here so we're going to say this is going to be the capital in excess of par let's make some black and white font group making this black and white so let's take the market price stock market price which is twelve dollars that's the value that we think we're giving out on the stock because that's the current market price minus the par value that we're accounting for up top which is equal to two dollars and so the difference is going to be this is called the difference is going to be equal to twelve minus two and then the number of shares number of new shares we determined to be up here ten thousand equals ten thousand so then let's put an underline font group underline that's going to give us then our increase in capital increase in capital in excess of par and that's going to be equal to the ten times ten thousand and the original balance original balance was the ten thousand so that means the capital in excess of par is now going to be so I'm going to say this is let's call it new new capital in excess of par new capital in excess of par is going to be equal to the sum of these two okay so then the retained earnings is the other side of the calculation let's make this blue and bordered so we're going to say bordered and blue and then we'll go up to the retained earnings calculation so let's go to the side here we're going to say let's make a skinny O I'm going to get the skinny column here font and paintbrush skinny O this is going to be the retained earnings and I'm going to make these three cells black and white font group making this black and white we're going to make this cell a little bit larger okay the starting retained earnings was equal to the amount here the six eighty seven and then we've got the change we're going to calculate the let's actually put this on the outside I'm going to say control X I'm going to call this change in retained earnings colon and that's going to be the number of new shares which is this number here number of new shares times the market price because that's the value that we're actually giving out twelve dollars not dollars but in shares that we're giving to people and so we're going to put an underline here font group underline and so then we're going to have the change in retained earnings let's just double click get rid of the colon which is going to be equal to the ten thousand times twelve one hundred and twenty decrease in retained earnings and that's going to give us our let's say new retained earnings which will be equal to the prior retained earnings six eighty seven thousand minus one hundred and twenty so there we have it let's put some indentations here we're going to go to alignment and dent here alignment and dent again let's put some border blue around this we're going to go to the borders bucket drop down and blue okay so I know that was a lot to kind of look at but let's see what actually happens to like the balance sheet then so on the balance sheet really you can think of it this way this might be easier to kind of you can say okay the retained earnings has to be going down by what we gave which was the twelve dollars times the twelve dollars times the ten thousand shares that we are giving out so it's got to go down by that one twenty that's why the retained earnings is now at the five sixty seven thousand so that's where we're at now if it was just a cash dividend we would have paid out cash of that amount that would be the other side because we're out of balance now but this time we have to we have to change something else in the equity section which is why we did these calculations up top so now the common stock calculation is calculated at this one hundred and ten shares that are now outstanding and the par value remains the same so we're still at the two hundred and twenty and the difference here has to go to the capital in excess of par which we now are at the two hundred thousand there and so that puts us back in balance that's the general idea now let's unhide some cells here I'm going to go from column C to column I right click the selected area and unhide those cells and so note when we looked at the stock split over here we had no adjustment to the retained earnings because although you're going to have more stocks if you own stocks they're going to double you didn't actually get any distribution from the company in value because basically we would expect that your stocks would double but the price would be cut in half and therefore we only have the adjustment here no adjustment to the retained earnings whereas when you have the stock dividend there is an impact on the retained earnings because of course the idea there is that you're getting something of value not cash in terms of a normal dividend but a stock dividend the stock that has value which we assume to be because it was trading on the market most likely at the twelve dollars so we have this adjustment to the retained earnings and the other side of the adjustment of course had to be in the equity side of things to basically remain a balance because we didn't actually get the cash so those are the differences in in in essence the calculation on the company side of things and when we're seeing it on our side of things when you're thinking stocks split that's why you're thinking okay no real underlying value change happen to the balance sheet here and you would suspect I'd be in a similar kind of scenario although again the market could change based on the stock split whether or not the market thinks it was a good idea or bad idea whether the price for example is in a more reasonable kind of kind of price level this would be kind of low you would think most of the time but that would be the general idea of the stock split whereas when you got the stock dividend you're receiving something of value in the form of dividends as opposed to cash something's being distributed from the company retained earnings going down on the company side of things