Excel Finance Class 09: Balance Sheet, Working Capital, Liquidity, Debt, Equity, Market Value


Welcome to Excel in
Finance video number 8. Hey, if you want to download
this workbook and follow along, click on the link
directly below this video. If you’re in the class, just
go to our class website. Hey, these videos, I’m going
to shoot them full screen. Usually I make
them kind of small. So you definitely, if
you’re watching this, want to hit the
Full Screen button. Hey, Chapter 2– Chapter 1, we talked
about introductory terms, the goal of finance,
and things like that. In this chapter,
we’re going to talk about financial statements,
different aspects of the balance sheet and
the income statement, but all in terms of what finance
people do with the numbers, not accounting people. So accounting people
see the numbers on the balance sheet
and the income statement and use the numbers differently
than finance people. And in particular, the
last couple of videos will talk about
calculating cash flows from financial statements. Accountants do accrual
accounting, which does not necessarily reflect cash flow. So we as finance people
need to calculate cash flow from financial statements. And we’ll talk about the
difference between average and marginal tax rates. All right, first, we’ll take
a look at the balance sheet. I’m going to click on
this tab right here. Balance sheet, last video,
we talked about assets equal liabilities plus equity. That is the fundamental
accounting equation. Not just accounting, finance. Everyone dealing with
businesses and numbers uses this equation here. The balance sheet
just reflects this. Assets on one side equals. And then there’s
two other things on the other side, some
liabilities and some equity. Now, I have in this
sheet here, if you point to these little red
comments, they have notes. Last chapter and in
chapters coming up, I’m going to have lots of PDFs. In this chapter, I just
don’t really have– I have one PDF that shows
a deductive proof for one of the formulas
we’re going to use. But otherwise, all the
notes are in this workbook. Now, this is a note,
for those of you who have done accounting. And actually, you can Right
Click that and say Show Hide, and then it will show. In this class, we will not
prepare financial statements. We will use them as a
source of information. Actually, that’s not quite true. We’re going to do really, really
basic financial statements with condensed numbers that
finance people would use. The financial statements
we see in this textbook will be condensed. And we’ll ignore such
things as proper titles, showing the proper expenses. When we have
interest and tax, it should be interest
expense, tax expense. We’ll list them lots of ways. But if you see interest
paid and tax paid, usually in accounting
you don’t want to list expenses like that. And in finance, we can go ahead
and list things like that. If you want to move the comment,
you can point to the edge and just click and drag. If you want to hide it, you
can Right Click Hide Comment. Now, assets, current assets,
these are assets like cash, accounts receivable. What’s accounts receivable? Accounts receivable is something
that will be cash soon. This is when Home Depot gives
the lumber to the contractor and the contractor pays
them 30 days later. So when you see accounts
receivable $465, or millions as it may be here– it looks like my screen
is not, there we go, in millions– that means
that number of dollars is owed to Home Depot. So accounts receivable is like
a holding tank until we get the cash from the customers. We’ve extended credit. So cash, accounts
receivable, inventory. These are all called or
classified as current assets, because these can be converted
into cash in a year or less, or the normal operating cycle. Now, think about this. Accounts receivable,
hopefully we’re going to collect it within
the next 30 days or 60 days or whatever it is. Inventory, the whole point of
having inventory in your store is so you could sell
it and get cash. So current assets. Now, this is very important
not only in finance, but accounting and auditing
and all sorts of banking. It’s very important to see
a business’s current assets. Because if they don’t have
very many current assets, perhaps they can’t
pay their bills. In addition, we will
talk about the other side of the equation– liabilities,
current liabilities. Well, if this is collect
cash in a year or less, this is pay cash
in a year or less. And that’s current liabilities. Current liabilities
are the bills that we have to pay
within one year or less, or the normal operating period. So this is cash out. This current assets are cash
in for all sorts of people– accountants, bankers,
financiers, internal managers. They always need to know current
assets, current liabilities. Because if this is the cash
out, this is the cash in, if cash out starts to get bigger
in the short-term than cash in, then you need to do something. You’re either in
trouble, or you need to do some financing like
going out and getting a loan or trying to collect
accounts receivable more quickly, something like that. All right, and then
on the asset side, we have fixed assets
or long-term assets. These are your building,
your trucks, your patents. The long term assets that
actually define your business and are the assets
that you’ve invested in because you think you
can make a profit from them. So UPS has a lot of brown
trucks because they’re in the delivery business. A manufacturing firm maybe
has a lot of buildings and has a lot of
equipment to manufacturer whatever it is
they’re manufacturing. Fixed assets
long-term– now, we’re going to see net fixed assets. That means whatever the original
historical cost of your asset– your machine, your truck–
minus the depreciation. And we’ll talk about
depreciation in just a bit. Then we have total assets. Now, many financial statements
show multiple periods. And the reason
why is because you want to see what is
the change, right? Cash was this. Cash went up. Accounts receivable was this. It went up. All right, so you want
to see the difference. And in this class,
we are going to– in this chapter, next chapter– we’ll be calculating
the difference. Now, let me ask you a question. Balance sheet is, it says up
here snapshot of the account balances on the last
day of the period. So really, if this is 2007 and
our ending year is December 31, this balance right here
is December 31, 2007. Now, what about this one? This is December 31, 2006. So what’s the difference
between the two? Well, it’s the ending
amount from 2007. We take that and
we subtract from it the very beginning amount
in the beginning of 2007. Well, the beginning
of 2007 is always whatever the balance was on
the last day of the last year. And for example, in
Chapter 2, we probably do this calculation end minus
begin 50 times in the homework. Some homework problems literally
have you do it 20 times. Because we’re going to be
analyzing the balance sheet. And we need to know
the change, right? An example of a change
that might not be so good– imagine if accounts receivable
went up, ballooned massively, much more than your sales. That could mean something
like you’re not collecting your accounts receivable. And that could lead to trouble. All right, assets–
current assets, fixed assets, and then total. Liabilities and equity,
these are the assets we buy. This is the source of funds. We either go out and get
debt, current liabilities, or long-term debt. Or we issue equity to
get our funds, which means our cash we’re going
to use to buy our assets. So let’s look here. Accounts payable, notes
payable, short-term, right? This is when we go out and we
owe people for some products or something like that. This is when you actually
go out and borrow money. But you have long-term
debt, short-term debt. On the balance sheet, you’ve
got to break it apart, whatever current
part must be listed in the current liabilities. Which means it’s owed
within one year or less. Long-term, both
of these are debt. I mean, all of this is debt. All right, and then
we have equity. The common stock–
sometimes you’ll just see equity when we have
our little mini balance sheets in this class. But other times you’ll
see common stock and paid in surplus. All that means is, common
stock, if the original stock was supposed to be priced at 10
bucks and it got sold for 12, the paid in surplus is the $2.00
above the 10 it was supposed to sell for. But that’s the amount
that stockholders pay to become owners. And then, retained
earnings, that’s the net income from the income
statement over the years that it’s accumulated. So you can do two things. And we’ll do this
in this chapter. Every time you make some
profit, you can either pay it out as dividends or
plow it back into the company, keep it in the equity
section, and then it’s available for whatever
assets you want to invest in. All right, so then
we add it all up. Total liabilities
plus the total equity equals liability
plus owner’s equity. Now, why is it called
the balance sheet here? Oh, because there’s an
equal sign right there. This has to equal this. So we go like this, right? Equals– and I’m
going to do 2007, that one, Tab, equals this. And I’m going to say,
is this equal sign, is that cell equal
to this cell, Tab? True. All right, so that’s why
it’s the balance sheet. All right, working capital. We were talking about current
assets and current liabilities. Let’s go up and talk about
current working capital. Oh, right, we mentioned
this earlier, right? If current liabilities are
getting too big, or even much bigger than current assets,
you’re kind of in trouble, right? Because if this is
current assets– things like cash
inventory, accounts receivable– it
means cash coming in. This is all the short-term
debts, meaning cash going out. Well, if cash going out is much
greater than the cash coming in, you’re in trouble. And so net working
capital is the term pretty much everyone uses,
accountants and financiers, for the difference
between these two. And in this chapter,
we will calculate it when we do our cash
flow calculations. And in next chapter when we do
financial statement analysis, it will be an important
ratio and measure. Net working capital is the
short-term capital of cash that the firm has to work with. It’s not always cash,
but it’s as-if, right? Because all of
this is supposedly going to be converted to cash
within one year, and this also. Capital is a term
that means assets. This term is often used
in economics and finance. So in accounting, you’ll
see net working capital. But in finance, you’ll see
capital referring to the assets all the time. In accounting, you see assets. But the one exception
is net working capital. All right, so here
in this cell, now I’m on this sheet working capital. And my balance sheet
is back over here. And I want to calculate
our net working capital here for at the end of 2006. So I’m going to do
a formula, and we’re going to use sheet references. Now, equal sign is the symbol
you put in to say, hey, Excel, I’m going to do a calculation. And to get some numbers
on a different sheet, you just gotta
type the equal sign and then click on that sheet. All right, here is the 2006. And again, balance sheet
is always the last day of whatever period. This one’s a year, by the way. It could be a month. Or even some companies
have everything automated, and they do it by the day. Nevertheless, current assets
minus current liabilities, all right? So I’m going to come down here. Let’s see, total, this is
2006 current assets total. Now, look up here. You can see the formula. Equals– and then,
balance sheet, that exclamation
point means this is on a different
sheet, and then B13. I’m going to type a minus. And watch what happens. Right there, you
can see that minus. And then I’m going to
click current liability total for 2006. Now, notice up in
the formula bar here, we can see our formula. It’s really just
two cell references. But they happen to be on the
sheet called balance sheet. Now, those little
apostrophes usually come around because if you have
spaces, it doesn’t like spaces. So it puts that
single apostrophe. But that exclamation point
is what tells the formula, this is a sheet name. All right, now, as soon
as you get it done, do not click back on the sheet. You can see this one,
we’re building it here. But that one’s white
because that’s where the formula is being created. Do not click back. Because it will
ruin your formula. What you do is, you hit Enter. Now I’m going to
click right here, and I’m going to hit the F2 key. There it is. There is our formula. OK, so that’s pretty good. Now, net working capital
is all over the place. Different businesses,
different industries have different numbers
that are good or bad. But in general, you
want it positive. Now, let’s go over
to a different sheet. I’m going to jump to the sheet– where is it? I can’t see it. I’m using my little arrows
over here to see where it– oh, there it is. It’s right there. Liquidity, I want to
come over to liquidity. Liquidity is how quickly an
asset can be converted to cash. All right, now,
liquidity is important just like working
capital is important. Because if you run out of
cash, you’re in trouble. The number one reason that
businesses big and small, not just mom and pop stores– people that run
small businesses– but all the way up
to gigantic banks. As we saw in the financial
crisis, people ran out of cash. So liquidity is very important. If your working capital
is getting too small, maybe you have to sell
assets so you can get cash. And in fact, that’s
what happened during the financial crisis. People tried to sell assets. But during a financial
crisis, hard to sell assets unless you’re going to
give a really low price. So let’s talk about liquidity. Liquidity, how quickly an asset
could be converted to cash. Just straight, simple–
how liquid is cash? It’s the most liquid. How about inventory or
accounts receivable? Those are pretty liquid. If you’re not in a
financial crisis, you could sell
those in emergency. In fact, you could sell
accounts receivable to banks, for example. All right. So those are examples of
assets that can quickly be converted to cash. What about a building? What about your truck? What about your
machines in the shop? Well, you probably don’t
want to sell those, right? Those are examples of
assets that are less liquid. All right, liquidity
has two dimensions. Ease of conversion
to cash– again, cash accounts receivable, easy. Your machines in your
shop or your buildings are less easy to
convert to cash. The other aspect of
liquidity is loss of value because you have
to sell it quickly. Because you can
pretty much, except for in a financial crisis,
you can pretty much sell anything quickly if you
reduce the price enough, right? So the building, it is hard
to sell real estate sometimes. It’s hard to sell
a machine quickly. But if you lower
the price enough, a fire sale, in essence,
you probably could sell it. So liquidity has two dimensions. You really, when you’re
measuring liquidity, you want to be able to not
only convert it to cash quickly but not lose a lot of value. Another aspect– we
mentioned selling assets. Another aspect of
liquidity is businesses that can go out and get a
loan quickly have access to liquidity because they
can get cash quickly, not just an asset sometimes. OK, highly liquid assets quickly
sold without significant loss, inventory. Short-term investments, right? So a lot of times,
you’ll see people invest in short-term bonds
or something like that. Illiquid assets cannot be sold
quickly without significant price reduction–
machine, building. Ah, but what about
the financial crisis? What happened with
the financial crisis, everyone had more
debt, too much debt. And in specific, it
was mortgage debt. Even more specifically,
it was things called collateralized debt
obligations, which was just a lot of mortgage debt. And when the financial
crisis hit and everyone knew that the housing
prices were going down and that debt on those
houses was no good, they couldn’t sell it. Before the financial crisis,
they had liquid assets. They could go out in
the financial markets and sell them. But during the financial
crisis, forget it. It was as if everything froze
up, everything was illiquid. Items on the balance
sheet are listed in order of decreasing liquidity. So if you go back and we
look at our balance sheet, right, oh, that’s
why cash is first and fixed assets are last. Liquidity– liquidity
is valuable. Firms can readily pay bills
and buy assets quickly. So we’ve mentioned
this one earlier. You’ve got to have enough
cash or working capital to pay your bills, right? But there’s another aspect. If you look around sometimes,
Microsoft over the years before– and other big companies– sometimes you see that
they have a lot of cash. And you’re thinking, why do they
have all that cash in the bank? Because cash in the bank doesn’t
earn a very big return, right? If you have a lot of cash, you
should go buy whatever assets. UPS is buying trucks. Google is buying up buildings
to put server computers in. So if someone has
a lot of cash, it could be that they’re saving up
to buy assets quickly or take over companies. Like, Microsoft, always
had a lot of cash because if they wanted
to buy a company. So liquidity is valuable
because you can pay your bills and buy assets quickly. Liquidity assets
such as cash tend to be less profitable
than illiquid assets such as buildings, trucks,
subdivisions of business, as we just mentioned, right? You probably don’t want
to have too much cash because you can’t
earn as much as if you invest in profitable assets. Now, sometimes it’s– like
right now, this is 2010. The financial
crisis 2007 to 2010, businesses have tons of cash. Because they’re not quite
sure what to invest in. And they’re nervous. Because during the crisis,
liquidity dried up, and they didn’t have anything. So they’re saving
lots of extra cash just in case things
get in trouble. All right, let’s do
a little bit of– I’m going to go over
to the sheet called building a balance sheet. In this chapter, we’re going
to have lots of homework. And let’s see how to do this. We’re going to be given
little small problems. These are not real
balance sheets. But they’re, in essence, a
summarized reduced balance sheet. We’re going to be giving
things like current assets, fixed assets,
current liabilities, and long-term debt. Notice they don’t give
us anything about equity. In this class and throughout
the whole textbook, we’re always going to have
to back in to numbers. All right, so total assets. Well, if we have our 250– I’ve already built this. These formulas are looking
up to these assumptions here. So I have current
assets and fixed assets. So we’ll do that. In the homework, I’m going
to give you templates. And then you just type
these in, and then you build all formulas here. Total assets, I’m going
to say equals SUM, and then highlight these
two cells right there. You can see right here,
the equal sign, it looks like the equal
sign is in that cell, but when you click
there, there’s nothing. So I’m going to
click on this column and drag it to make
it a little bit wider. Now, total liabilities,
again, I did the same thing. Looking up there. I’m going to Alt equal. That’s the keyboard shortcut
for Auto Sum, and Enter. But how in the world are we
going to calculate equity? Well, let’s see–
oh, guess what– the fundamental
accounting equation. If this is all the liabilities
and this is all the assets, remember, the
definition of equity is assets minus the total debt. So this is an example
of backing in. And some of the
backing into answers in this for homework
in this class will actually be quite tricky. We’ll have some examples
of that coming up. So there we go, Enter– 480. So now I’m going to
equals SUM because I want to add this up,
and triple check. And then, whoops, notice
I have a minus there. That will give me a minus sign. I meant to type an equal sign. All right, now, working
capital, always going to be current assets
minus current liabilities. Now, this is the working capital
at this particular moment in time. Because balance
sheets are always one day, a particular
moment in time. It is a camera taking a
picture of all the accounts. All right, current assets
minus current liabilities. Oh, look at that. They have a positive
working capital. All right, let’s go
over to debt and equity. We’re still talking
about the balance sheet. In finance, we have assets. Oh, that’s the use of the
funds on the other side of the equation. I’m going to jump over. We have debt and equity. Source of funds– now,
what’s the difference? This is a fixed claim contract. It’s a contractual claim. You must pay the
full amount back at some point in the
future and interest. It’s a contract. This is a residual claim. Guess what– when you go out
and invest in the stock market, does the company have to
pay you back by contract the full amount? So you bought a
stock for 10 bucks. Does it have to pay you back? No way. If it goes bankrupt
and there’s not enough money to even pay off
the creditors, you get nothing. So residual means leftover. Not only that, but dividends. Just here’s contract, right? It says you must pay
interest, 5% interest, right? You must pay it. It’s a contract. You don’t get paid,
you go to court. And you go into bankruptcy. But this, forget it. Dividends are only paid when
there’s something left over. Now, what is the upside? Why would people ever do
equity if it’s always residual? Because if it’s a good idea–
think about the early investors in Microsoft– if it’s a good idea, you can
make a lot of money, right? Interest expense is a cash out. And it is tax-deductible, wow. We’re going to see an example
of that just coming up here. But because it’s tax deductible,
when you pay interest, there’s cash interest. Let’s just say $25 going out. Because you get to put
it on your tax return and subtract it, it means you
will avoid paying some taxes, avoid if you hadn’t
incurred debt. So when you pay out
25 bucks, actually you get a little
benefit on your tax. So it’s not a total $25. Maybe it’s 23 net
that you will get. And we will see an
example of that. Tax-deductible, huge
advantage to debt. That is why over
the last quarter century companies have
loaded up on debt a lot, because there’s a benefit. Dividends cash out is
not tax-deductible. And we’ll see an example. We’ll compare
buying a new machine and using debt and equity. And we’ll see that there is a
small advantage of using debt. Debt also is paid off
first during bankruptcy. You get whatever is left over. All right, let’s go to
this next sheet here. Debt– I just want to
point out the word, well, the topic
of whether to use debt or equity to raise funds
is called capital structure. The term financial leverage is
used when the firm has debt. So leverage, it just means
like a big leverage thing. You can actually, if
you’re using debt wisely, you have a
tax-deductible advantage. And we’ll see an
example of that later. But leverage is
the word they use. Next chapter, in Chapter 3,
when we do financial ratios, we’ll have ratios
that will tell us how much leverage we’re using. The more debt as a percentage
of assets, the more leverage. And leverage can magnify
in gains and losses. More later on that. Market value versus book value. I think this is our last topic
in this video about the balance sheet. And then, the next video,
we’ll do income statement. Market value, that’s
the amount of cash we would get if you sold it. Now, market value, you
hear a lot about this. Market value for
financial assets like stocks, and debt
sometimes, right? You actually can
go out and see what the market value is every day. But most assets– all the
machines and the trucks and the buildings, and all
sorts of other assets– don’t really have a market
where you can go and see what the actual price is
if you sell it, right? So market value can
be slippery sometimes. People make estimates. But it’s only for
debt that have markets where we can go see a price that
market value is easy to get. Otherwise, forget it. You do not know for
sure until you sell it. You never know for
sure until you sell it. Book value, this is
what accountants have been doing for a long time. Because how in the world are
you going to get market value? So accountants say, forget it. Book value is more reliable. When I buy the thing– the business, the
inventory, the building, whatever it is– that receipt
told me how much it is. And that’s what I’m
going to record it. This is called the
historical cost principle. Required by GAAP, generally
accepted accounting principles. Ah, except some financial assets
are recorded as market value. And actually, there’s
a huge debate. Right before the
financial crisis, the FASB and other regulations
for accounting said that businesses had to record
many financial assets, particularly in banks, had
to record financial assets– debt and assets– at market value. But what happened during
the financial crisis is that many of their assets
they couldn’t sell anywhere, and they had no idea
what the price was. They had to write it down. And they had to do
a lot of guessing. So market value,
actually, although it’s a pretty good idea, it’s
being heavily debated. Because if we have bubbles,
like our housing bubble or the dot com
bubble in the ’90s, then there’s a problem
with recording things at market value. Actually, the FASB, the
generally accepted accounting principles before
the crisis said banks had to record
things at market value. And actually, it was suspended. And now it’s being debated
whether it’s a good idea or not. But in general, at least through
the history of accounting, accountants have recorded
things at historical cost. Book value of
assets often doesn’t reflect the firm’s
most valuable assets such as talented
employees and managers. More and more,
employees are really what can make a
particular entity worth whatever it’s worth. But there’s no asset
called employees. Customer lists,
reputation– not only that, but the actual, all the
things that they buy can change radically. The actual tangible– these
are all intangible assets. But tangible assets also
can change a lot also. That’s why if you look at
publicly traded companies, their market value,
if you look and see, all the stocks, whatever it’s
worth in the stock market can be radically different than
what’s on their balance sheet. Stockholders’ equity, market
value for share of stock is virtually always different
than the book value– and we just said that– since the goal of
financial management is to maximize the market
value of the stock. And again, we talked about
the problems with that. But again, in theory, that’s a
good value if that’s the goal. Thus, the financial manager is
interested in market value, not book value. And sometimes for a
financial manager, you can. You can go out and look
at the stock market and see what the value
of that stock is. And you know how many there are. So you can actually very
easily calculate market value and compare it to
the book value. Which, book value just
means the total asset value on your balance sheet. All right, let’s try
a little example here. We have a few homework problems
where we’re going to do this. We will just be
given the numbers. We’re not going to have to
go out and do research on it. Fixed assets book value, fixed
assets appraised market value. Now, that says appraised. But again, if it’s a
publicly traded company, you can easily get the stock
value at any particular moment and then the number of shares
outstanding and calculate that. Net working capital book
value, that’s all in our books. Net working capital
market value, that could be pretty slippery. But perhaps inventory increased
in value or went down. In this example, it went up. –long-term debt. We want to calculate what is
the book value of equity, what is the market value of equity. Well, here’s our book net
working capital right there. And the market equals
this 800, Enter. Net fixed assets, the book
value right there, Tab. And then this one right here. All right, now we
have book and market. Now, I’m going to
highlight these two cells and use the keyboard
shortcut Alt equals– Alt equals is Auto Sum. Notice it did both
of them at once. If you don’t believe it, hit
the F2 key, and you can see, sure enough, it got it right. All right, now let’s
come over here. They gave us debt. We’re going to have to
back into our equity again. The debt, I’m going
to go right there. Tab– oh, we’re going to assume
that the debt didn’t change. So I’m going to say
that one right there. And well, now what do we do? What’s the stockholders’ equity? Oh, well, we can back
into this again, right, just like we did in
our last example. There’s total assets. Actually, I better
put total assets. I probably could have
put just TA there. We’ll use that a lot next
chapter, total assets. Well, right here if this
is total assets, boom, and there’s our debt,
the definition of equity is residual. So equals this–
now, watch this. I’m going to do this as
a formula minus this. Now really, for some of us in
this class who are brand new to Excel, this is a
relative cell reference, as we mentioned in Chapter 00. This formula is really saying,
hey, go 1, 2, 3, 4 to my left and then one down. OK, and this one’s saying,
hey, look one above. I’m going to Control Enter. That puts the formula in the
cell and keeps it highlighted. And watch this. I can just copy this over. Why? I’m going to click
here and hit F2. Because this is still 1, 2,
3, 4 to my left and one below. And this is still
looking one above. And now we can check
this, Alt equals. OK, so that’s just
a little example of market value and book value. All right, next video, we will
talk about the income statement and things like depreciation
and accrual accounting, and then move our way
towards cash flows for finance calculations
from financial statements. All right, see you next video.