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.