 Personal finance practice problem using Excel. Book value per share calculation, 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 analyzing a corporation possibly for investment opportunities, noting that a corporation's separate legal entity breaking out the ownership of it into equal units called stocks. Usually we're thinking about publicly traded companies, those trading on a public exchange. We're gonna be figuring out then the book value per share. The second tab is gonna have some pre-formatted sales so you can work the practice problem with less Excel formatting. The blank tab is gonna have blank sales so we can add the Excel formatting as we go. If you don't have any of this, that's okay. You can just open up a new worksheet. I would format the baseline formatting first before starting if I was doing a new worksheet by selecting the entire worksheet with the triangle. I usually right click on it and then go down to the format of the sales. Currency is what I usually use. Brackets negative and red. Brackets four negative and red. No dollars on and no decimals. And then I'm not gonna hit okay because I already have this, I'm just gonna X out of it. But then add your data on the left-hand side. This is just an image to show us the calculation so you don't really need that. And then we can make a skinny C column and then we're good to go with column D here. Okay, so we got the company information. Now remember this kind of stuff you would basically get from the financial statements of the company. And so we've got on the balance sheet, we would have the assets. This is where they stand as of a point in time. And so we might be doing this like at the end of the year for example but we could do it at any given time possibly quarterly or yearly. And then we've got the liabilities. That's what they owe to third parties. And then the difference between the two would be the equity we'll calculate here. You can see that on the balance sheet as well. We got the stock shares issued. So we're gonna say that the number of shares that have been issued are the 13 million shares. So the book value calculation will be calculated down below. We've got the shareholders equity minus the preferred stock. That would be if the company has preferred stock. We're not gonna deal with preferred stock here but note that the preferred stock is kind of an equity interest that acts kind of like more like a fixed income to some degree and we're focused on the common stock preferred stock having to be paid first. We would subtract out the preferred stock. So, and then we're gonna divide by the weighted average common shares outstanding. Now note, we're just gonna use the common shares outstanding that are given to us here. You might use the common shares outstanding at the point in time that you're making the calculation but just realize that you could also say, well, I wanna try to figure out the average shares over the timeframe that I am considering. So whether that be like a year or a quarter for example and if there was no change in the shares outstanding, no new shares were issued, no stock splits or buybacks or anything like that, then they might be the same at the beginning and end of the period. So we're gonna be using this number here as if it's basically the end of the period. Where could you find the beginning number and then the ending number? You could divide it by two. That would be the common method to use. You could find it at the balance sheet at the end of the last period and then the balance sheet at the end of the current period that you are in, see if there's a difference in the common shares outstanding and if you wanna take the weighted average, you could basically add them two up and divide it by two, for example. Okay, so let's take the book value per share. So let's figure this out. The book value per share. So I'm gonna say, I'm gonna make this a little bit wider, make that a little bit wider here. Let's make it black and white up top. So we'll select these two cells, go to the home tab, font group. I'm gonna hit the bucket dropdown, make that black and then the lettering white. And so first we'll just do the calculation, which is basically net assets or the total equity in the company, which would be assets minus liabilities. So for example, we'll just take the assets first, equals the assets. I'm gonna say equals, pull over the total assets. This would be found on the balance sheet, the 210 million and then equals the liabilities. This is what is owed to third parties. So we're gonna say this is gonna be equal to the 65 million. Let's put an underline here, font group and underline. The difference between the two, we could say is equity. Equity, you could also think of it as net assets. Net assets, which is a useful kind of idea because the equity represents what is owed to the owners or the net assets you can think about in essence as the value of the company. If the company say was to liquidate, for example, they would sell all the assets and get this amount in theory and then they would pay off all the third party liabilities, this amount in theory to get the difference, which can then be allocated to the owners if they were to liquidate or close up shop. That would be the general idea. But of course the assets might not be sold for that amount because you might have equipment stuff that's on there on depreciated cost as opposed to what you could sell it for at the current day because we don't know exactly what you could sell it for until you actually try to sell it on the market when you think you're talking about things like buildings and equipment, for example. So let's take a subtraction, this minus this, that's gonna be the equity or the net assets. You could find that number on the balance sheet as well, the total equity. And then we're gonna take the stocks outstanding. We're gonna say that there's no preferred stocks. So we'll just take the number of stocks outstanding. The stocks represent equal ownership in units of the company. So this does not mean that 13 million people own the company because not a bunch of people don't own one particular stock. You could have one person owning multiple stocks but we want to be able to break down the information on a per unit basis that are equal per unit basis. So this will be the book value, value per share. And we'll divide this out. This is gonna be equal to the 145 million divided by the 13 million. Let's put some decimals here. Home tab, a number group adding some decimals. And let's put an underline here. We're gonna go to the font group and underline. Let's put some blue borders around it. Font group, put some borders up top and hit the bucket drop down and make it blue. If you don't have that blue it's in the more color wheel standard it's right there. So that's the one I typically use. You could use whatever you would like. So note that that means that in theory then if we were basically to liquidate the company is one of the easiest way to kind of consider this I would then get the assets of this amount. We could imagine in cash if I was to sell everything and then pay off the liabilities of the 65 million giving us the net assets or the equity of 145 million. We would have that cash then that we could pay off to the owners of the company. And there's 13 million not owners but shares. Some owners might be owning multiple shares. So we broke it out into a per share basis and that would be a book value per share of the $11.15. So again multiple people might own multiple shares for example and so they would get whatever they would get at times that many shares. Now remember that that's not completely correct because we don't know what the assets are if you were actually to sell the company because they're on their at depreciated cost not at fair value. And note you might argue some people might argue well we should transfer to fair value and so on and you can make that argument and so on but note that even if we did we couldn't really accurately value some of the big assets like equipment and buildings because it's hard to know what those are valued at because they're unique, they're not like the stocks which are trading on an exchange. So that's gonna be an estimate no matter what we do really. And then we've got the liabilities and that's gonna give us the equity so that's gonna be the amount per share. Now that number note is based on the idea that we could just liquidate the company and basically give out the assets minus the liabilities but when we're investing in a company what we're really looking for is their earnings potential. We're looking to see how well they use their assets and how well they use their leverage and their liabilities which they generally took on to pick up the assets to generate revenue in the future. That's why we're investing. We're not investing in the company just so they can hold on to some assets that have the underlying value. We might not might be okay, we might like that but we would like them to be using those assets to basically generate revenue. So we can then take this comparison for example the book value per share and we would expect the market value to generally be higher than the book value per share because that's gonna be the market interpreting how well the company is gonna be able to use these assets, these net assets in order to generate more revenue in the future. If the market price was less than the book value per share note what that would invite potential investors to do. Like if the market price was only like $8 then somebody could say, hey let's go in there and just buy all the shares out. They could just buy all the shares of the company or a majority share to a takeover of the company and then just liquidate the company and they can make a profit because the company's worth even without revenue generation the 1150. So you would expect the market price to be something higher than the book value and then of course you want to be doing some relative comparisons to the relationship between for example the book value and the market price within particular industries because different industries are gonna have different spreads in terms of that difference. But that's the general idea. That's the calculation. Let's do a quick review. Did I spell anything wrong? No, it's perfect. It's perfect.