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.

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:

• Financial statements
  • Balance sheet
  • Income statement
• The basic difference between accounting (or book) value and market value.
• The difference between accounting income and cash flow.
• How to determine a firm’s cash flow from its financial statements.
  • Calculate cash flow.
• The difference between average and marginal tax rates.
  • Calculate taxes

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.

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.

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:

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 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:

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.

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

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.

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:

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:

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.

A firm needs a positive working capital in order to pay its bills, but there is another aspect to it:

The reason why a lot of companies are keeping a lot of cash on their balance sheets is so that they can go out and buy other businesses quickly.

The last point is that you probably don’t want to have too much cash on your balance sheet because it doesn’t earn any return.

Building a balance sheet

We will then turn our attention to building a balance sheet:

The first thing that we want to look at is assets. What I do is that I type en equal sign in cell B10 and then I write SUM( and I highlight cells B7 and B8:

Then we do the same thing for the liabilities in cell E7 and E8:

The result in E10 will of course be $550.

How are we then going to calculate the equity? If you remember the fundamental accounting equation:

Assets = Liabilities + Equity

In other words:

Equity = Assets – Liabilities

So what we do is that we take the assets and we subtract the liabilities:

Then finally we can check that liabilities and equity equal assets, like so:

And the result in cell E12 is of course $1650.

Debt vs. equity

In finance we have Assets which are the use of the funds and on the other side of the equation we have Debt and Equity. Debt and Equity represent the source of funds:

If we look at the Source of funds we will see that there are two different items: Debt and Equity. What is the difference between the two?

The debt is a Fixed claim and is something that you must pay back to the lender at some point in the future + interest.

The Equity on the other hand is Residual claim which means that you as a company does not have to pay the holder back

If I go out and buy a stock of ABC Corp. in the stock market for $50, the company does not have to pay me back if the stock loses value.

If the company goes bankrupt and there isn’t enough money around to pay off the creditors I get nothing. Residual means “left over”.

Then we have dividends which are only paid out once there is something left over.

Why then would anybody like to do equity if it’s always residual?

It’s because of the upside. If you invest in a well run business with equity that is steadily growing then you can make a lot of money.

Interest expenses (cash out) are tax deductible. This means that when you are paying your taxes you will pay a little bit less if you have interest payments.

Let’s say that you are paying $2500 in interest then if you deduct the interest payments you will less than that (let’s say $2300).

On the other hand dividends (cash out) are not tax deductible which means that there is a slight advantage of using debt.

The creditors are also paid first during bankruptcy while if you have equity you will get whatever is left over.

Debt

We will now turn our attention to debt:

The question 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. If you are using debt wisely you can reap the benefits of having your debt tax deductible and put the capital to productive use.

The more debt you have, the higher your leverage.

Leverage can magnify both gains and losses.

Market value vs. Book value

This will be the last topic that we will cover about the Balance sheet:

Market value

The Market value is the amount that you would get if you sold your equity stake.

For financial assets like stocks and debt you can go out and see what the value is every day.

But for a lot of equity this is not really possible so we need to estimate the market value.

Otherwise you do not know for sure until you sell your asset.

Book value

For fixed assets like machines and trucks finding a market value becomes much more difficult.

That is why accountants have invented the Book Value.

When I buy the business outright, the machines, the buildings and the inventory, that receipt is telling me what it is and will be the sum that I’m going to record.

That is also called the Historical cost principle and it is required by the Generally Accepted Accounting Principles (GAAP).

Then the Book value of the company often does not take into account the company’s most valuable assets such as:

• Talented employees and managers
• Customer lists
• Reputation

These are all intangible assets, but it can be tangible assets also where the value of the assets can change radically from the price that you paid.

The Market value of an asset is almost always different from the Book value.

The goal of the financial management is to maximize the market value of the stock. At least in theory this is a good thing.

That means that the financial manager is more interested in the market value than he or she is of the book value.

We are given these numbers so we don’t have to research them:

Now we will calculate the Book value of the assets and we begin by adding cell B5 to the Book value in cell B16:

We then add the Book value of the Fixed assets to cell B17:

Then we do the same thing for the Market value. We begin by adding the Net working capital to cell C16:

We then continue the Market value of the Fixed assets (cell B4):

Finally we highlight cells B18 and C18 and use the keyboard shortcut Alt  + =.

What that does is that it calculates an auto sum of the numbers above it:

We can then see that we have different numbers for Book and Market Value.

Finally we do the same thing for liabilities.

First we assume that the Book and Market value for the Long-term debt is the same:

So what is then Shareholders’ equity?

Remember the fundamental accounting equation in Figure 16:

Assets = Debt + Equity

That means if we fill in Total assets from cells B19 and C19 into B27 and C27:

we can then calculate Shareholders’ equity by subtraction:

Then we do the same thing for the Market value in cell C26.

The final result is like this:

Income statement

In this section we will talk about the Income statement.

The Income statement is different from the Balance sheet in that it shows revenues, expenses and net income for the whole period.

On the other hand, what the Balance sheet showed us was valid just for a particular day.

The first thing that we are going to look at is the Revenue:

Total Revenue is the accounting term used for the Total sales of the business during the period.

Then we have Net sales which is the Amount earned by the business from delivering products or services.

You get this from taking the Total revenue and then subtracting any expenses.

Another thing that is important to recognize is that the company cannot record revenue before the product is delivered to the customer.

In accounting that is called accrual accounting.

The definition of accrual accounting is this:

As you can see there is both an Expense and Revenue meaning of the word.

If the business receives a bill and it isn’t due until 30 days later, they have to record the expense at the earlier date.

This is then an example of a hypothetical Income statement:

As you can see the statement is valid for the whole period ending on December 31, 2016.

For publicly traded stocks the reporting periods are either annual or by quarter.

In Figure 31 you will find a made up Income statement:

An Income statement is profit or loss for the whole period ( in this case the period is a whole year.)

We have our Total revenue and Cost of goods sold.

If you sell a widget for $100 and you paid $50 for it, you will record $100 as revenue and $50 for cost of goods sold.

If you look in cell C6 you will find an item called Depreciation and this is where accrual accounting comes in.

This is where the cash may be spent at at a different period from where it is received. We will look into when to book an expense like that.

It could come as cash, or it could come later as accounts receivables as we saw in the balance sheet.

Then we have Earnings before interest and tax which we get by subtracting Cost of goods sold and Depreciation from the Total revenue.

We calculate this by adding a formula. We first take Total revenue in cell C4 and subtract the sum between the Cost of goods sold in cell C5 and Depreciation in cell C6:

The result in cell C7 is then of course $1,928,000. We then do the same thing when we calculate the Taxable income by subtracting Interest paid (C8) from Earnings before interest and tax (C7):

The result in cell C9 is of course $1,856,000. Finally we are doing the same thing when we are calculating the Net income in cell C11 by subtracting taxes in cell C10 from Taxable income in cell C10:

The final Net income in cell C11 is then $1,536,00. The Net income (or the Earnings) can go in one of two places. Because technically they belong to the shareholders they can either be paid out in dividends or they can be plowed back into the business to buy more assets.

Generally if the company keeps a lot of retained earnings, it’s because they have good ideas of to make the company grow.

If we go back to the Balance sheet (Figure 8) and take a look at the item that is called Retained earnings, we now understand where that item comes from.

So in order to calculate how much money ABC Corp is keeping to plow back into the company we need to take the Dividend (cell C13) and subtract from the Net income (cell C11):

We will also consider the total number of shares outstanding. This means that at this particular date, the 31 December 2016, there were 210,000,000 shares outstanding.

Then we can calculate Earnings per share by dividing Net income (cell C11) x 1000 (because our numbers are divided by 1000 to begin with) by Shares outstanding (cell C15).

The formula looks like this:

The result in cell C16 will then be $7.31.

After that we continue with Dividend per share where we divide the Total dividend paid out in cell C13 (x 1000) with the total number of shares outstanding (cell C15):

The result in cell C17 will then be $0.31 which means that in this case the company keeps a lot of the earnings.

Depreciation

Then we come into the subject of depreciation:

In the following example we are using trucks for FedEx for $10 million with a Salvage value of 500,000 and an estimated time in use of 7 years:

The salvage value is what you would get out of your investment in a fire sale:

What this means is that if the cash goes out the first year, but that the Depreciation event is accounted for every year.

That means that there is no cash associated with it because it all went out the first year.

To give you an example of what I mean we first need to discuss the Matching principle:

This is part of the concept of accrual accounting that we discussed earlier in this chapter. What it means is basically that we need to add revenue and expenses in the right period so that they finally add up.

If we then look at Figure 39 again we see that we have a linear depreciation for our purchase of the assets over 7 years. What that means is that we assume that the trucks are going to last for 7 years.

But if we have the trucks they are going to generate revenue during that whole period and this we have to account for.

We do thisby matching the revenue that we are going to get from the trucks with the depreciation.

If we put this information into Excel it will look like this:

This is then the proper way of accounting for depreciation.

Another example of depreciation comes when we calculate Net capital spending or NCS:

What we are going to do is to calculate a Net cash flow from our accounting numbers.

The first thing that we need to look at is the begin number. The begin number we find in the Balance sheet item of Net fixed assets of the 31 December 2016 (cell B8).

Then we need to find the end number which is the Net fixed assets on the 31 December 2017 (cell B9).

That means that we have more cash at the end of the period than in the beginning (which is a good thing).

Then we need to look at the depreciation and because these items have already taken into account the depreciation we need to add it back.

The calculation therefore becomes B9 – B8 + B10. In Excel it looks like this:

The result in cell B12 is then of course $265,000.

Cash flow from accounting information

We previously discussed the concept of accrual accounting.

There is a fundamental problem with it and that is that it doesn’t consider cash flow.

That is what financial managers are interested in, cash.

We therefore have to take the financial numbers of the balance sheet and the income statement and convert them into cash:

On the balance sheet accrual accounting is for instance affecting Net fixed assets. Similarly, on the Income statement, Sales are recorded when they are earned and expenses are recorded when they are paid out:

The next figure is again the fundamental accounting equation:

In finance we think of the equation like this:

When FedEx buys trucks or Coca-Cola buys buildings they are acquiring assets which in other words is use of cash or funds. The reason why they buy these assets is because they think that they are going to make a profit from them.

Where are we then going to get the cash from? The cash is coming from either Debt or Equity which are the source of funds.

This equation, where we have Use of cash and Source of cash, will be our starting point when we calculate cash flow.

So the first question to answer is “What is cash flow?”

In finance people care about cash in and cash out.

Cash flow is not the same thing as Net earnings.

We will therefore have to derive cash flow information from the Balance sheet and the Income statement.

We will look at how cash is generated from utilizing assets and how it’s paid to those that finance the purchase.

What this means is that Cash flow from assets can either go to the Bondholders or the Stockholders.

In the next figure all the calculations we are going to do are summarized:

If we begin with the second box we see that it says Cash flow from assets and the way to calculate it is by taking the Operational cash flow and subtracting Net capital spending and Changes in net working capital.

In order to calculate the Operational cash flow we need to take the EBIT (Earnings Before Interest and Taxes) and add back the Depreciation minus Taxes.

To understand this we need to take another look at our Income statment (Figure 45). There we see that we have an item called EBIT and below that we have Interest. Where does the interest go? To the creditors. This means that EBIT should belong to the Operational cash flow.

In the end we need to subtract the Taxes from EBIT and add back the Depreciation, because it’s a non-cash item.

To calculate the Net capital spending we need to take the end value of the Net fixed assets then subtract the value of the Net fixed assets at the beginning of the period and finally add back the non-cash Depreciation on the Income statement. We do this because we’ve already taken this into account when we calculate the value of the assets.

Changes in Working capital is all change calculated by taking something at the end of the period minus the beginning. In this particular case we do it by taking the working capital for the end of the period minus working capital for the beginning. How do we calculate the working capital? We do it by subtracting current liabilities from current assets.

Then we have the Cash flow that go out to our creditors. The first thing that we do is that we identify the Interest paid and then we subtract the Net new borrowing. The Net new borrowing is always the change of the Long-term debt, calculated as the long-term debt at the end of the period minus the long-term debt at the beginning.

Of course the net new borrowing can be both positive or negative. If the company takes on more debt then the Net new borrowing is positive and we will say Interest paid minus the new debt that we’ve issued will be our Cash flow to creditors.

At last we have Cash flow to shareholders which is similar to Cash flow to creditors. We begin by taking the dividends paid and then subtracting the Net new equity that we’ve raised.

The way that we calculate the Net new equity is that we begin by looking at value of the common stock and paid-in surplus at the end of the period minus the common stock and paid-in surplus at the beginning of the period.

Then we have defined all the cash flow calculations that we are going to do.

Income statement, Balance sheet and Cash flow problem

We are then going to take on a comprehensive problem. We will call it Exercise 1. In Figure 49 we have a lot of information that we are going to use:

We have information about the corporation, the dates and the tax rates and for a number of accounts we have a begin of a period and an end of the same period.

What we are going to do is to calculate an income statement, a balance sheet and then a cash flow.

This is then the income statement that we are going to fill out:

The first thing that we notice in the exercise is that the income statement is for the year 2017. That means that we only need to take the values for 2017. In cell C28 we therefore type =C15, like so:

Next we take the Cost of goods sold for the year 2017 which we find in cell C16:

Then the Depreciation for the year 2017 is found in cell C17:

Then we want to calculate the earnings before interest and tax and the way we do that is to take our Net sales and then subtract the Cost of goods sold and the Depreciation. In Excel it looks like this:

The result in cell C31 is then of course $930.

Then we want to find the interest which is given in cell C18:

Then we want to calculate the Taxable income. How do we do that?

We take our earnings before interest and tax (C31) minus the interest (C32), like so:

Then we are going to calculate the taxes. How much are we to pay in taxes?

To calculate that we first have to take the taxable income (C33) and multiply with the Tax rate (B5):

If we then hit enter we will get two decimals like this in cell C34:

What this then means is that we usually have to round to the nearest dollar (but in this case we don’t have to). So how do we do that?

In the same way that we can use the sum function for summing or the average function for calculating averages, there is a round function that we can use. It is used like this:

What do we do in Figure 59? We are rounding to the nearest dollar.

Finally to calculate the Net income we take the taxable income in cell C33 minus the taxes in cell C34, like so:

Then we have dividends which is given in cell C19:

Now the dividends can go in either of two ways. Either it goes out of the company to the shareholders or its kept within.

If it’s kept within the company, the dividends are to be used for purchase of new productive assets. Thus they are added to the retained earnings or subtracted from the net income, like so:

We then want to build our balance sheet.

First, we look at the current assets for 2016:

 

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.

How to use Microsoft Excel as a finance tool – part 2

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

How can I use Excel to get the best out of my data?

Chances are that you are going to have to look into the data that you have entered again in a year or two years.

So how do I organize a spreadsheet so that I can see what I’ve done when I come back?

Excel is a very powerful tool once you know how to use it.

The secret is called labeling and with that I mean to properly label all the cells in order to easily be able to go back and change.

But to start off this chapter I will discuss  some financial metrics and why they are important.

So why is it an exciting time to study finance?

The reason is that we had a financial crisis in 2009 and still to this day we are seeing the repercussions of that meltdown.

We all got into a lot of trouble and the financial institutions that we depend on for our daily lives lost a lot of money.

Some of the questions that people are asking themselves in the aftermath of the financial crisis are:

1. Can markets really allocate resources efficiently?
2. Are markets efficient?
3. Are people always acting rationallly?

These are questions that people in finance and economics assumed they had the answer to for years, but now they are getting more cautious.

What I will be talking about in this chapter is:

1. The reason why the corporation is an efficient business form.
2. The structure of a corporation.
3. The fundamental accounting equation: Assets = Liabilities + Equity.
4. Define what finance is.
5. The goal of financial management.
6. Why we are studying finance.
7. What the key questions in finance are.
8. Define financial markets (primary and sceondary) and why they are important.
9. What is the importance of cash flow.

Forms of business

There are many forms of business, but the most common is Sole Proprietorship where just one person owns the company.

This particular form is easy to start and the least regulated kind of business there is.

Another advantage is that there is single taxation. You only get taxed on whatever your income is.

On the downside, it is quite difficult to raise funding.

Another thing is that you as a private person have unlimited liability. This means that if you get sued you can not only all the assets in the company, but also your personal belongings.

Finally, it is pretty difficult to sell your ownership compared to if you, for example, own a corporation. If you have shares in a corporation, you can just sell them on the market.

When many own the company it is called a Partnership or a Corporation.

A General Partnership means that you invest and work for the company whereas a limited Partnership means that you just invest.

A Partnership is easy to start and it is more regulated than a Sole Proprietorship.

If you have a Partnership it is somewhat difficult to raise money.

There is also the same problem as with a Sole Proprietorship – there is unlimited liability. So if you get sued, the court can take all your Partnership and personal assets.

It’s difficult to sell ownership if you want to.

But on the positive side: In a Partnership there is Single Taxation just as in Sole Proprietorship.

A Corporation is when instead of having a person owning the business there is a separate legal “person” who owns it.

On the negative side with this form of ownership:

• There is a lot of paperwork that you need to file which means that it is somewhat hard to start a Corporation.
• It is often more regulated than a Partnership or a Sole Proprietorship.
• You have to pay taxes on both income and dividends. This is called Double Taxation. A dividend is when a company pays out cash to the stockholder.

On the positive side with a Corporation:

• It is reasonably easy to raise funds.
• There is Limited Liability which means that if someone sues the Corporation the court can come after the Corporation’s assets but it can not come after your personal assets.
• It’s reasonably easy to sell shares of a Corporation in the market.

Regarding the last point, there are two kinds of financial markets:

• Primary markets, and
• Secondary markets

If you buy shares in the Primary market, the shares are issued by the corporation and sold to you.

This means that the shares (or securities) are issued by the Corporation.

Now, if you own the shares, you can go and sells them to whoever you want and you do that in the secondary market.

This means that after the sale in the Primary market, you can buy or sell shares, debt or equity to your liking in the secondary market.

Why is a Corporation a better form a better form of ownership than a Sole Proprietorship or a Partnership?

It’s because of the limited liability of a Corporation. If you get sued, the courts will only take assets belonging to the Corporation and not your car.

The main reason why a Corporation is the better alternative, however, is that it allows you to finance your idea far easier.

In other words it is pretty easy to get funds (equity or debt).

So if I were to summarize why a Corporation is the better alternative:

1. It’s easy to raise cash.
2. There is  a limited liability for debt.
3. It is easy to transfer ownership.
4. A Corporation has unlimited life which means that if the owner dies the business still lives on.

Structure of Corporation

Because this article is about corporate finance, I will now get into the structure of a corporation (Figure 1.)

At the top of the Corporation there are the Shareholders. When you own a stock of Corporation, you are the owner of that Corporation and owners vote and bring in a Board of Directors.

The Board of Directors then hire the managers. The managers are then working inside the company and running the company.

Finally, it is the managers who employ the employees who work in the company.

The role of the Financial Director

Because this is a finance class we will of course discuss the role of finance inside a corporation (Figure 2.)

Above the horizontal line in Figure 2. is the Board Of Directors. Below the line is inside the corporation.

The role of the Chief Financial Officer is then to supervise the corporation’s financial activities.

To his/her help, he/she has these people to help:

On top there is the CFO or Chief Financial Officer.

Below there is the Treasurer and below him/her there are different people like Cash Manager who supervise the cash flows of the company.

The Credit Manager who look into questions like if the company is going to extend credit to certain customers.

Capital Expenditures is about what kind of projects or machinery the business might engage in. They do that by using cash flow analysis.

Financial Planning is about figuring out the company ‘s needs for issuing debt or stock to raise cash.

Then there is the Controller under whom there’s the accounting part of the Corporation.

The Fundamental Accounting Equation

The equation goes like this:

Asset = Liability + Equity

The equation is the fundamental part of Accounting and as such it has been around for more than 600 years.

What does it mean?

Assets

1./ If you have an asset – in the example I use a house – worth $200,000 and your loan on the house is $150,000 (liability), then you have $50,000 left in equity.

Imagine that a company buys trucks for its operations, buying other businesses, real estate or even inventory: Those are all assets.

What this means is that if you have a good new idea you can either pay for it yourself, you can borrow the money or use some combination of the two.

2./ The idea behind buying an asset is that you will make money in the end.

If for instance we use the example of a company buying delivery trucks – then the company needs to pay for it in order to get cash back in return.

The GAP definition of an asset is that it will provide a probable future economic benefit to the owner.

Liabilities

A liability is a promise to pay back the loan plus interest on that loan.

If the Company defaults on its loan, the house will go to the bank. That is a contractual obligation.

If you default on the loan, the bank gets paid first. That is also true in business. The person or the entity that has loaned the money gets paid first.

Equity

In the example above, there is $50,000 in equity.

If the bank is only able to get $100,000 for the house – that doesn’t cover the debt, but it’s all they get – you will get nothing.

If anything is left over, you will get it.

The way to think about it is whatever is left over after you pay all the Bankers.

Finance

The definition of finance is as follows:

What this means is:

• What should we invest in?
• Should we use cash (equity) or should we incur debt?
• The future is unknown which makes finance difficult.

The third point comes from the fact that finance is all about the future and since the future is unknown finance is difficult.

What we do in finance is that we are looking into the future and doing lots of estimates to decide what to do.

Goal of Corporate Financial Manager

The goal of financial management is to maximize the current value per share of existing stock (market value of equity).

Theoretically this is a good goal because the owners own the company and the financial manager works for the owners.

However, there are a few problems and let’s look at a few of them:

1./ The Agency Problem with Corporations

This is what the Agency Problem means:

The shareholders own the company and are what is called “principal”.

The managers run the business and are what is called “agents”.

According to the definition an “agent” is working for somebody and in this case for the shareholders or the “principal”.

The agent is supposed to act in the best interest of the principal.

But because the agent is inside the company the agent has custody of the assets.

Managers do not always act ethically or legally.

Question: If the principal is not watching the agent 100% of the time how can the principal make sure that the agent is always acting in their best interest?

Answer:

1. Pay managers based on stock value of the company.
2. External auditors of the financial situation of the company to the Board of Directors. The role of the auditors is to control the financial information of the company so that it comes out to its owners, regulators and potential investors. If the auditors have no direct interest in the company, it makes the situation slightly better.
3. Control over assets and accounting. This means that before the management even gets hold of any books they are supposed to be managed properly by somebody associated with principal.
4. Make management personally responsible for the financial statements.
5. Regulation as for an insurance. If you come in a buy a car insurance, the insurance is obliged by law to hold money aside if there’s an accident.

2./ Financial-, Accounting- and Management-Gurus invent ways to circumvent laws that protect the owners.

There are many examples in financial history of companies having gone out and borrowed money in the market.

This money has then been accounted for as debt on the balance sheet just as it should be (a liability).

But then they bought the debt back and recorded the debt as an asset on the balance sheet instead.

This is fraud and illegal.

Another example is insurance companies that invented policies that circumvented the regulations and the law.

This was also illegal.

3./ Financial markets are efficient.

The definition of finance depends on financial markets being efficient.

What that means is that the assets are accurately priced in the market.

Obviously, we all know that this is not always true, but as a general rule it should hold.

However, there have been two major bubbles over the past 20 years:

1. The dot com-bubble of the late nineties.
2. The housing bubble between 2003 and 2007.

When you have a bubble, the market is telling you that the companies, or more broadly, the assets involved are worth a lot of money, but they are not.

What can happen then is that the bubble pops and loses all its inflated value at once.

This way a lot of people can lose a lot of money quickly.

If there is a manager inside a company and he/she is trying to maximize the value of the company, but the market value is not fair, the goal itself cannot be achieved.

That means that everyone is left guessing what the market value of the company is.

Here’s an example of a house in 2003 to 2007:

The market was telling the participants that houses worth more and more during the bubble years, but they were not as we could see when the bubble popped in 2007.

The process can be summarized like so:

1. The markets said that house prices were worth a lot. This was a price signal to buy houses as an investment.
2. The banks, who make loans for a living, the individual, who buy houses, and the contractor, who build the houses, all reacted to that price signal.
3. Market was incorrectly giving people the signal to buy.

Why should we study finance?

There are of course several reasons why you would want to study finance.

First of all we have the Personal side:

• Student loans
• Credit cards
• Investments
• Retirement savings
• Banking

What are the careers that you can have in finance?

• Marketing (budgets, analyze market plan)
• Accounting and finance (have a lot in common)
• Management (investing, what projects are best given current circumstances, job performance)
• Personal finance

Other areas of finance

In this class we are going to study corporate finance, but there are other areas of finance as well:

a. Investing

• Stockbroker (where you buy and sell stock for customers)
• Portfolio manager (where you buy and sell stock for a mutual or index fund)
• Security analyst (where you do all the research and pick the individual stocks)
• Bond trader (where you buy and sell bonds for customers)

b. Financial institutions

• Banks
• Insurance

c. International finance

What questions to ask?

1. Capital budgeting: What long-term investments or assets do we buy? This includes equipment, buildings and investments.
2. Capital structure: Are you going finance your investments with debt, equity or profits? What mixture of those are you going to use?
3. Working capital: This is the nuts and bolts of running the company. The Working capital is defined as Current assets – Current liabilities and by definition this is short-term. How do we collect money from our customers to pay our bills? Concerned with short-term assets and liabilities.

Cash flow

In finance cash flow is everything.

This is an example of how cash can flow through a corporation:

In the figure it says A. The Firm issues securities. That can be the company having an initial public offering or an IPO.

Then people in financial markets decide to buy some stock so the cash goes from right to left and into the business.

Then we have B. Firm invest in assets which can be that the company buys for example machines or buildings.

Why is the company buying the assets? They of course do it to get a return on their investment.

Then we have C. Cash flow from Firm’s assets. This means that the company has earned a return on its investments and now the cash is flowing in three different directions:

1. Back to the financial markets
2. D. It can go to the government or other stakeholders in the form of taxes.
3. E. The cash can be reinvested in the company.

The take home message here is that in finance what matters is Cash flow and not accounting numbers.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The first part in using Microsoft Excel as a finance tool can be found here and part 3 can be found here.

This article is based on the excellent work of the man behind the Youtube-channel Excelisfun.

 

 

How to use Microsoft Excel as a finance tool

Knowing Microsoft Excel is great.

Knowing finance is even greater.

But when you can do BOTH?

That’s when you call yourself Superman!

…because there’s nothing stopping you!

Today I am going to talk to you about Microsoft Excel so that you feel like you have Excel superpowers.

I am going to give you insanely actionable Excel tools to use for corporate and personal finance.

To kick things off I am going to start with an introduction of Excel.

Like so:

1. Introduction to Microsoft Excel

Look:

Most people think that Excel is overly complicated and would like some guidance on how to use it properly.

So it definitely pays off to go back to the old definition of what Excel actually does. Excel essentially does three things:

a. Calculations, which include things like adding a column or calculating a total monthly loan payment. We can add some numbers, or find a minimum of some values or simply subtract numbers from each other.

b. Data analysis. That’s when you look at raw data, you organize it in some way and then come to a conclusion. A very simple example of this is illustrated in the figure below (1). If we for example have many interest rates and we would like to find the lowest we can do that with Excel.

c. Excel also stores raw data, but that is out of the scope for this article.

Columns and rows

Excel is a two-dimensional grid where you have different columns horizontally and rows vertically.

Cells

The intersection between a column and a row is called a cell. If we for instance highlight the green cell to the right of the numbers and below the Add-cell, you can see that cell is called I3.

That is the heart of Excel because as you will see in this series you can reference a specific cell to make formulas.

Range of cells

A range of cells is when you choose a number of cells in order to make a calculation. In the example above we can highlight F3, G3 and H3 to select this range.

Worksheet

What about all the cells?

That is called a worksheet.

Sheet tab

A sheet tab is shown below:

What you can do here is to double click on it and change the name of the sheet.

CTRL + PgUp/CTRL + PgDn

To change between the different worksheets you can use the shortcuts CTRL + PgUp or CTRL + PgDn which is very useful.

Ribbons

When I use Excel I always have my Ribbons open so that I have easy access to the functions.

It looks like this:

Now, if you want to close the ribbons for whatever reason you can just right click on your mouse and select the “Minimize the ribbon”-option when you hover over the Home tab.

Quick Access Toolbar

We then have the Quick Access Toolbar which looks like this:

What the Undo button does is that it undo:s the last action and the Redo button undo:s the Undo.

If you have a particular task that you like and use often, you can put it in the Quick Access Toolbar by right clicking your mouse and scroll down to the “Add to quick access toolbar” option.

Scroll bars

Sometimes in this article series I am going to show you large tables. The way to navigate up and down in these is by clicking on the scroll arrows or pulling the scroll box.

Formulas and functions

There will be separate articles on this concept, but just to give you foretaste I will show you how it works:

Let’s say that I want to subtract 85 from 98. How do I go about doing this?

In order to do that you’ve got to know how to tell Excel that what it wants is a formula in I9.

If you just type an equal sign in I9, you’ve just to Excel that what is coming is a formula.

What you do next is that click on G9 for the first number.

Then you type a minus sign on your keyboard and click on H9 for the second number.

When you then hit Return you will get your answer in I9.

2.  Formulas and functions

In this chapter we are going to go a little bit deeper into the formulas and functions that we discussed last time.

First of all let’s repeat what I talked about in the last chapter. The way to tell Excel that you want to input a formula is by typing an equal sign (=).

An illustration:

For the sake of argument, let’s say that we have an interest rate of 8.4 percent (C3) and this has to be paid twice a year.

To figure out how much you have to pay each time we then have to divide the number in C3 with 2, which is the number in C5.

Instead of taking each cell the way we described above, many people type the numbers directly. This of course gives the same result, like so:

The problem comes if we want to update the rate. Then the C5 is updated automatically whereas C6 stays the same. You can see what I mean in this picture:

There are three reasons for why you want to update your data dynamically like that:

1. It’s faster to change the numbers directly in the cells.
2. If you’ve labeled your cells properly, you clearly see what the individual numbers are.
3. If there is a spreadsheet filled with direct edits and they are not labeled properly it’s difficult to edit.

To calculate the sum of different numbers, there’s a nifty shortcut to use: Alt + =

When you type the keyboard shortcut, Excel will guess which numbers you want to add together and there will be “marching ants” around these.

When you are done selecting the numbers you want, just hit ENTER and the formula will automatically update.

What you don’t want to do is to type =sum and then click on D2, D3, D4 and D5. The reason for this is that a range (as we have in Figure 8) is dynamic. This means that you can insert or remove numbers to your liking, whereas if you insert numbers into a formula with only clicked numbers, the final sum will not update.

We are going to look at a few more formulas and we will begin with an average or a mean:

What I do is that I type in the start of the formula and Excel then give you suggestions of what to choose based on what you have typed. Then you can select the corresponding correct formula and press the Tab key to select. As always Excel then guesses which numbers you want to choose.

We then want to calculate a loan function.

What I want is my yearly interest payment. First of all I click in C5 to activate the cell. Then I click the fx in the upper center of Figure 10. Then I begin to type “Loan payment” in the dialog box where it says “Search for a function”.

You will then get a list of a number of different functions that you can use, like so:

Then you have to read the description to figure out exactly what the formula does. In my case the description of PMT seems fine so I click OK.

I then get this dialog box:

Here it says that I’ve come to the Function Arguments box.

What is then an argument?

It’s a mathematical term of the individual parameters within a formula. In my example there are three different arguments that I want to insert and they are highlighted in bold.

The first argument being the rate so I go back and click on C3 to insert the rate.

The next argument is the number of periods and because I have annual rates in my example, I simply click on C4 for the number of years.

The last argument is the Present Value which in our case is how much the loan is worth right now. So I click C2, like so:

What you see to the right of the dialog box in Figure 13 are the actual numbers of the cells that I’ve entered. Down below you will see the unformatted result of the formula.

Down to the left I get the formatted result which has parentheses. These are financial numbers so parentheses mean that the number is negative. The reason for this is that if you have borrowed that amount of dollars at that rate you will get a negative number because the money will come out of your wallet.

One other thing that I will talk about is screen tips. When you have done these numbers for a while you will be fluent. Once you are fluent, you can just type =PMT( and you will see that the formula pops up in the screen:

These screen tips allow you to see which value to enter next. You can between the different values by typing a comma on your keyboard.

3. Math Operators and Orders of Operation

I now want to turn your attention math operators and orders of operation. In this part of the article I will talk about all operators below:

In Figure 14 you can see how you can type the different operators.

Let’s see some math examples:

Adding

If you want to add two numbers together the easiest way is to type Alt + = in the result cell. In my example that is B5. That way you will get a formula of =SUM(B3:B4) and if you hit enter Excel will calculate that addition for you, like so:

Subtracting

To subtract a number from another I type an equal sign in C5 and then I use the mouse to click in C3 (or the up arrow twice on my keyboard). Then I type a minus sign on the number pad and click in C4:

The result is then shown in C5.

Multiplication

There are two ways to multiply numbers in Excel. The most common is to type an equal sign and then click the individual numbers, like so:

You can also use the PRODUCT function, like so:

Division

To divide two numbers we first type an equal sign and then click on the individual numbers. In this case E3 is called the numerator and E4 is called the denominator:

If the numerator is greater than the denominator, the result will be a number that is bigger than 1 (E5):

Similarly, if the nominator is less than the denominator then the resulting number will be less than 1 (F5):

 Exponent

If I want to take the number in cell G3, which in my case is 5, squared, I first have to type an equal sign. Then I have to click in G3 to get the number in G3. Then I have to type Shift + 6 to get to the caret symbol. Then I can click in G4:

That means that the base is 5 and the exponent is 2. The result is then shown in cell G5, like so:

Logical formulas

Are two numbers equal?

I will now show you how to use logical formulas. If I want to check if two numbers are equal, I click the first cell and then the second:

It may come as a surprise, but when I hit Enter now I see that I get the Boolean value “False” in B5.

Why is that?

It comes from that Excel does not always show you everything. In this case if I click in cell B3, you can see that the value is not 5, but really 5.02:

How can that be?

This comes from the number formatting in Microsoft Excel. If you look up in the formula bar you see that the number in B3 is actually 5.02 and not 5.

How do you go about changing this?

The answer is that you change the number of displayed decimals simply by clicking the Increase Decimals or Decrease Decimals box like so:

Is one number greater than another?

I then want to check if a number is greater than another number.

Is the value in C3 greater than the value in C4? I do that with a greater than symbol.

And of course if I hit Enter:

I get the Boolean value TRUE in cell C5.

Order of operations

The next thing that I will talk about is the order of operations. In particular I will focus on the question if 2+2*4^2 is 256 or 34:

If I calculate from left to right, the first calculation is 2 + 2 which is 4. Then the next is times 4 which would make 16. If I then take that number squared I would end up with 256.

But that is not the way calculations work. The parenthesis always come first followed by exponents, multiply and divide follow and addition and subtraction come last.

If we then type in the formula in the example into Excel we will see this:

So Excel knows in which order to do the calculations and it does it automatically.

The correct way of calculating is first to look for any parenthesis, but in this case I don’t have any. Then I see that I have look at exponents where I have 4 raised to the second. This turns out to be 16. I then have to multiply that number with 2 which gives 32. Finally I have to add 32 with 2 which gives a result of 34.

There is a neat acronym to memorize the order of operations:

Please Excuse My Dear Aunt Sally.

If, on the other hand, I want to have 256 as a result I have to put parenthesis around the number, like so:

If I hit enter I will get the result 256 in cell C9.

Evaluation of formulas

In this section I want to show you how to evaluate a formula.

What I do is that I go up to the formulas ribbon where I first click “Formulas” and then “Evaluate formula” as shown in the picture:

When I click on “Evaluate Formula” I get a dialog box, like so:

Then when I click on “Evaluate” Microsoft Excel will calculate the underlined value for me. This comes in handy when you want to check your formula and you don’t know the order of operations.

4. Number formatting

The next thing that I will talk about is number formatting. The reason for me to do this is there are a lot of people who get it wrong and that we want to avoid.

If I for instance want to change a naked number to a number with a comma and two decimals I first highlight the numbers, like this:

If I then click “Ctrl” and “1” at the same time I open the Format Cells dialog box. Now if I click on “Number” I will get this:

Here I can decide how many decimals I want to show. I this case I go for zero and I click the box that says “Use 1000 Separator (,)”:

Next thing that we want to look at are the numbers in A3 to A5. They all say $67.35 but if I look closer I see that what it really says is $67.351383489, like so:

If I want to get rid of the number formatting in A3 to A5, I type Ctrl + Shift + ~ and I get get this result:

If I then jump to Currency in column D, you can see that by pressing Ctrl + Shift + 4, I get the currency formatting that I want:

If you then check with Ctrl + 1, you can see that the Format Cells dialog box has indeed chosen Currency.

What is the difference between Currency and Accounting?

If I highlight cells E3 to E5 and click on Accounting in the format drop down-menu, like so:

What happens when you click Accounting is that all the numbers that you are working with will align so that you can compare them directly.

I will be talking about loans and cash flow later on where you will have to do math on those dates.

Let’s say thatI have a loan that is due on a specific date and today is another date and I want to know how many days the loan is behind:

In the cells there are dates written, but behind the scenes Excel consider these as numbers. That means that there is an actual number there where Excel can do calculations.

You can check which actual numbers Excel uses by changing the formatting drop down to Text. First I highlight cells F7 and F8 and then change to Text:

You can then see that Excel use the numbers 42675 and 42712. If you want to know the difference between those you can write a formula:

First of all I’m typing an equal sign, then I click on the later date (F8), I click on the earlier date (F7) and hit Enter.

The result is 37, like so:

If you get a date as a result, it’s because the formatting is set to date. Just change to General (Ctrl + Shift + ~) and you will see the number.

The keyboard shortcut for today’s date is Ctrl + ; so if I for instance would like to add today’s date in cell F3, I first activate the cell by clicking in it and then type Ctrl + ; like so:

If I then want to increment my dates I click in the bottom right corner of cell F3 to get the crosshairs. Then I can simply drag it down:

If I then click on the Fill options handle I can choose to increment months or years instead of days. That is going to be handy when we want to analyze cash flows.

If we now want to know which numbers Excel use behind the scenes, you can highlight the dates that I just used (F3 to F5) and click General:

In Figure 49 I went from a date to a number so let’s do the opposite instead. I cells G3 and H3 I have written 1 and 2:

How do I make these numbers into dates? I type Ctrl + 1 to bring up the Format Cells dialog box. Here I choose the option “Date” and the format 3/14/01:

The result then becomes 1/1/00 where 00 is short for 1900:

As a sidenote I will now discuss how dates and text are differently treated by Microsoft Excel:

By default, Excel align numbers to the right and text to the left. From Figure 53 it is obvious that our dates are aligned to the left. That means that I have made something wrong in the formatting:

What has happened here is that we have started the date in cell C3 with an apostrophe and in C4 I’ve formatted the date as “Text” in the number formatting.

The reverse of converting dates to numbers can also be achieved. I have some numbers in cells I3 to I5:

Then I go to the number formatting drop down menu and choose “Short Date”:

Then I click and I get these dates out:

You can also find the maturity date of a loan that is issued on a specific date:

In this case the Loan Issue Date is in cell C3 and the Loan Length In Days is in cell C4. When I then type =C3+C4 I get the maturity date in cell C5.

5. Percent Number Format

In this section I will talk about Percent Number Format, Percent and Percent Change.

One of the biggest mistakes made in Excel with the Percent Button:

If I then type 0.02 in cell B2 and I click the Percent Button above I get the following:

The 0.02 is then transformed into 2%. On the other hand if I click the Percent Button with the value 2 highlighted in cell C2, I get the following:

That is an example of Number First, Then Format. The other way to do it is by first formatting and then typing. Here I highlight the cells B3 and C3 before I click the Percent Button:

If I then type the number 3 in cell B3 I get the result that I want (which in my case is 3%).

But when I type 0.03 in cell C3 I also get 3.00% so it doesn’t matter if you use 3 or 0.03:

I don’t know why this has been programmed this way, but it’s the way Microsoft Excel works.

Sometimes you have just one interest rate and then you can just type for example 3% in cell B4:

You will then get the result 3.00% when you hit enter.

Then I am going to talk about Percent and Percent Change. A Percentage is calculated as a Part of the Total. In Figure 66 I am comparing the Cost Of Goods Sold to the Total Revenue:

The result in cell B9 is of course the following:

Then we have the concept of Percent Change. This is calculated as (End – Beginning)/Beginning:

The result is of course as follows:

Conclusion:

Microsoft Excel is a very powerful tool for doing calculations and data analysis. It is very often used in finance and we will dive deeper into the topic in later articles.

Today I have only gone through the basics of Microsoft Excel to whet your appetite.

I hope you come back for more.

(This article is highly inspired by the great work of ExcelIsFun on Youtube. You can find part 2 of this series here.)

If you liked this post and/or have comments please leave comment below.