How to use Microsoft Excel in finance – Part 3

This time we will look a bit more in depth on how to use Microsoft Excel for financial calculations.

Green Excel icon with text about using Microsoft Excel in finance

What we will look at today is Financial statements and how to use them in order to understand the financial health of a company.

In particular we will look at:

Finance not accounting

The first thing to grasp is that we will look at the numbers and look at them through the eyes of people in finance.

They use the numbers differently than the people in accounting and we will show you how.

Balance sheet

In the last article we talked about this formula:

Assets = Liabilities + Equity

Everyone in finance is using this equation and not just in finance but also in accounting.

What the Balance sheet does is that it reflects the equation.

The Balance sheet is a snapshot of the Firm’s account balances at the last day of the reporting period.

Hypothetical Balance Sheet of ABC Corporation (Current assets, Non-current assets and Total assets).

Figure 1. Hypothetical Balance Sheet of ABC Corporation (Current assets, Non-current assets and Total assets).

Assets

The assets are divided into Current assets and Non-current assets where the Current portion is assets that can be turned into cash within 12 months.

Then per definition Cash is a Current asset. Then we Accounts receivable which is accounts that will be cash soon. Inventory is another Current asset. The whole point of inventory is so that you can sell it and get cash.

The Current assets are important not only in finance, but also in accounting, auditing and banking. It’s very important to see a business’ Current assets, because if they don’t have very many current assets perhaps they cannot pay their bills.

The Non-current portion are fixed assets that cannot be easily transformed into cash. These are your buildings, your trucks or patents or the long-term assets that actually define your business.

This is what you’ve invested in because you think you can make a profit from this.

In a financial statement you will see different periods. That is because you want to compare one year’s numbers to another.

 

Liabilities

We will also talk about the other side of the equation, which are the liabilities. These are the funds that the company have at its disposal.

The company either goes out and get debt (current), it borrows money long-term (bonds) or it issues equity to get its funds which means the cash it is going to use to buy its assets.

Current liabilities are liabilities that need to be reimbursed within a year – much like the current assets which are assets that can be converted into cash within a year.

Current liabilities are the bills the company need to pay within one year.

As you can see, within the current liabilities there are two items Accounts payable and Notes payable.

Accounts payable is when the company goes out and buys products that it has to pay for. Notes payable is when the company borrows money that it has to reimburse within a year.

The combined current liabilities and non-current liabilities represent debt on the balance sheet.

Hypothetical Balance Sheet of ABC Corporation (Current liabilities, Non-current liabilities, Total liabilities and Shareholders' equity).

Figure 2. Hypothetical Balance Sheet of ABC Corporation (Current liabilities, Non-current liabilities, Total liabilities and Shareholders’ equity).

Shareholders’ equity

In cell A19 you can see that we’ve written Common stock and paid-in surplus. What that means is that if the company issues common stock and they are being priced at $22 but were supposed only to be worth $20, the paid-in surplus is the $2 that the stockholders pay in order to own the company.

Retained earnings belong to the shareholders and they are to be paid back to the shareholders in the form of dividends, but sometimes they are not.

If they are not paid out to the shareholders they can be used within the company in the form of investment.

The way to account for such a situation is to label the item Retained earnings.

Adding it all up

Finally we add it all up. First we calculate total liabilities which is the sum between current and non-current liabilities.

Then we calculate total liabilities plus shareholders’ equity which is just what it sounds like.

Why is it called the Balance sheet?

That’s because there’s an equal sign in the formula Asset = Liabilities + Equity which means that the two sides have to balance each other.

So what I do in cell B24 (Figure 2) is that I add the total assets from Figure 1 in cell B18 and in cell C24 (Figure 2) I add the Total liabilities and Shareholders’ equity from cell B22:

Comparison between Total assets for 2016 (B24) and Total liabilities and Shareholders' equity for 2016 (C24).

Figure 3. Comparison between Total assets for 2016 (B24) and Total liabilities and Shareholders’ equity for 2016 (C24).

The result that we get in D24 is then TRUE.

 

Working capital

Remember that Current assets are assets that the firm easily can convert into cash and that the Current liabilities are the bills that the company needs to pay within 12 months.

If your current liabilities are greater than your current assets it means you’re in trouble and you need to find cash somehow.

The Net working capital is the term that is used and it is defined as Current assets – Current liabilities.

The Net working capital is defined as the difference between the Current assets and the Current liabilities.

Figure 4. The Net working capital is defined as the difference between the Current assets and the Current liabilities.

The Net working capital is the short-term capital that the firm has to work with.

We will use the Net working capital when we do our cash flow calculations and we will also use it in the next chapter when we do analysis of financial statements.

In accounting you will see that the term Net working capital is used but in finance the term Capital is used more broadly for all assets.

If we now have our Current assets and Current liabilities on different sheets like this:

Screenshot of Microsoft Excel showing Assets, Liabilities and Working capital on different sheets.

Figure 5. Screenshot of Microsoft Excel showing Assets, Liabilities and Working capital on different sheets.

If we now want to calculate the Net working capital we do it like this:

In the sheet Working capital we type an equal sign in cell D16.

Screenshot of Microsoft Excel showing how to calculate a Net working capital: Step 1.

Figure 6.Screenshot of Microsoft Excel showing how to calculate a Net working capital: Step 1.

We then click on the sheet Assets in Figure 5. and we click on Current assets (cell B13):

Screenshot of Excel showing how to activate cell B13 in the sheet called Assets.

Figure 7. Screenshot of Excel showing how to calculate a Net working capital: Step 2.

If we look in the formula bar in Figure 7 we see that we have now activated cell B13 in the sheet called Assets. The exclamation sign means that we are using a different sheet for our data.

We then type a “-“-sign (1) and click on the “liabilities”-sheet (2) in Figure 5:

Then we click in cell B16 and hit Enter and we are immediately brought back to the Working capital sheet.

Screenshot of Microsoft Excel showing how to calculate the Net working capital from different working sheets in the same Excel document.

Figure 8. Screenshot of Microsoft Excel showing how to calculate the Net working capital from different working sheets in the same Excel document.

What’s important to remember here is not to click on Working capital sheet but rather hit Enter (if you don’t hit Enter your formula will be ruined).

If we then go back to the Working capital sheet and hit the F2 key, this will appear:

Screenshot of Microsoft Excel showing how to calculate the Net working capital in cell D16.

Figure 9. Screenshot of Microsoft Excel showing how to calculate the Net working capital in cell D16.

Of course, different businesses have different values for their Net working capital, but in general, the number should be positive.

Liquidity

We will then turn our attention to Liquidity:

Different concepts of liquidity and how it can be used.

Figure 10. Different concepts of liquidity and how it can be used.

Liquidity is important because if you run out of it you’re in trouble.

If your working capital is getting too small then maybe you have to sell assets to get cash for the company.

Liquidity is defined as:

How quickly an asset can be converted into cash.

Furthermore, liquidity has two dimensions:

  1. Ease of conversion into cash.
  2. Loss of value because you have to sell your asset quickly.

There are highly liquid assets which can be sold quickly without loss of value. (This can be inventory or a short-term investment).

How liquid is cash? That is the most liquid.

How liquid is accounts receivable? You can quite easily convert accounts receivable into cash. In fact you can sell those assets to bank and get cash in return.

The we have illiquid assets which are assets that cannot be sold quickly without significant price reduction. Examples of this are machinery and buildings.

You can almost sell anything if you reduce the price enough.

On the Balance sheet the items are usually listed in decreasing liquidity so that the most liquid assets come first.

Another aspect of liquidity is that businesses that have it can go out and get a loan easily.

 

 

 

 

 

 

This article is part of a series. You can read Part 1 here and Part 2 here. This article is highly influenced by the excellent work of Michael Girvin (Excelisfun) on Youtube.

Popular articles:

Guide to Technical Analysis


Guide to Value Investing


Using Microsoft Excel in finance


Subscribe to RSS

Business Blogs - The Blog Index

Follow me on Blogarama