Collateralized Debt Obligations

In the post-GFC world, Collateralized Debt Obligations (CDOs) have been talked about a lot. The reason for this is obvious – it was the frantic creation of the CDOs that begun to cause the collapse we saw in 2008.

It is important to note that while CDOs certainly played a role in the GFC, they were not the only cause of the crisis.

What is a CDO?

To start off simply, a CDO is simply a way of packaging debt that nobody wants to buy into a form that people are comfortable buying. A simple (yet not entirely accurate) example can be a broken wine glass. Nobody will want to buy a broken wine glass because, well, its broken. However, if you package it up in some nice packaging, perhaps a box, so that you cannot see that it is broken, people may actually buy it.

This is not really how a CDO works, but it is the general idea.

What do mortgages have to do with it?

A mortgage is, essentially, debt. In a technical sense, a mortgage is debt that is secured against a house. It includes, in the contracts, that should the borrower default on their repayments, the house may be seized to recover the losses of the bank.

Now, in general, people represent pretty high risk when it comes to loans. It’s why we must pay interest of up to 20% on credit cards, while banks will only pay us 1% interest on our savings. Big institutions are assumed to be much safer borrowers than individuals.

Banks, however, have an interest in selling mortgages to people. They earn fees on the mortgages they sell, and, if they sell mortgages to the right people, will make money over time as interest is paid on the loans. However, they cannot allow their exposure to mortgages grow too large. The way they deal with this is that they sell the mortgages on their books to others, who have a greater appetite for the risk.

However, a problem quickly develops with this approach. As banks frantically try to sell more and more mortgages, the people who can actually afford a mortgage quickly runs out. Historically, mortgages were only offered to people who had good credit scores, and who were clearly earning enough to pay the mortgages.

Of course, higher risk people could be charged higher interest rates on mortgages, but that presents a challenge in advertising, and even people who know very little about money and have bad credit scores will understand that high interest rates are never good.

The banks therefore, began to create programs wherein mortgages could be given for rates well below what they should be given for in the short term, and then raising those rates. However, they found that these mortgages were difficult to sell on to other  investors.

This is where the CDO comes in. The CDO packages the bad mortgages with the good  mortgages, and splits into tranches. These tranches can be sold as separate securities, and allows certain tranches to be almost guaranteed. This is how it works:

The top tranche receives payouts first, then the payouts will move down in the tranches. So, if the top tranche of the CDO makes up 70% of the investors, then up to 30% of mortgages in the CDO could default, and the top tranche would still get paid. Therefore, investors with lower risk appetites could buy the higher tranches of the CDO, while investors with higher risk appetites could buy the lower tranches. It would take a catastrophic collapse of the housing market to downgrade the rating of the top tranche.

This is essentially what happened during the Financial Crisis of 2008. However, it is important to note that CDOs were not the only cause of the GFC by any means, and that Credit Default Swaps contributed in a large way, and there were many other factors that caused the crash.

If you would like a full explanation of how the GFC happened, and how some people actually profited massively from it, check out the famous book and movie, the Big Short. The Amazon link for both are below.

The Big Short: Inside the Doomsday Machine (Book)

The Big Short (DVD)

Mergers and Acquisitions: How do they work?

One of the more exciting parts of the Finance space is M&A. This is where you may often find heavyweight investment banks battling it out to complete ever bigger deals. But what is the M&A, and how does a deal work?

We clearly have two parts to M&A – the mergers, and the acquisitions. We will more often see acquisitions, for reasons I will explain later, but the two are pretty similar transactions.

A merger involves two companies agreeing to merge, and become one company, often under a new name, or a name that is a combination of the two original names. An acquisition, on the other hand, involves one company (acquirer) purchasing another (target) company. The business of the target company is then absorbed into that of the acquirer. The end result in either case is two companies becoming one.

The first question to address then, is why such a transaction would be undertaken? For mergers, often they are undertaken to provide a wider range of business opportunities or market coverage, or to reduce redundant work by both companies in order to make the companies more profitable. An acquisition usually has many of the same goals, however one party is more aggressively pursuing the deal, and therefore becomes a purchaser.

How does one buy a company?

When you purchase a company, there are two options for payment – cash or stock. A cash payment is straightforward – a certain amount of cash is given for each share outstanding of the company you are purchasing. You effectively buy all of the shares available, and therefore own the company.

However, an alternative is to issue a certain number of your own shares for each outstanding target share. This effectively converts the shares of the target company into shares of the acquirer. The amount of shares issued is determined by the ratio between the stock prices of the companies, and the final purchase price.

These two approaches can also be combined – creating a cash/stock deal. Considerations for which way to go include borrowing that would be required – a Leveraged Buy Out will usually be performed using borrowed cash, which a company that has loan covenants restricting more borrowing may lean more toward a stock purchase.

The mechanics of how to pay for the company essentially comes down to a range of factors that are more complex than this article is trying to get at.

How much do you pay for a company?

Due to the scale of some of the transactions in the market, it is impossible to fully understand how they work unless you are one of the people working on the deal. However, there are some general ideas which can be understood. Note: This discussion is focused on publicly traded companies, not privately held companies.

When a deal is put in place, a purchase price must be determined. This is done in a number of ways, but the general idea is that you use the Enterprise Value of the company. This enterprise value the market value of equity, plus the market value of debt, less any cash.

EV = Market Cap + Debt – Cash

The reason for this, is that in order to own a company, you must own the stock. However, when you own the stock, you must now also pay back any debt that the company has, so this increases the purchase price of the company. However, any cash that the company has on its books will be transferred to the purchaser, and can be used the pay down that debt.

As is quite obvious, the amount that is offered by the acquirer is usually higher than the market cap of the business. This is known as a premium. There are several ways of determining the price that should be paid for a company, but the main ones will involve some kind of comps analysis or discounted cash flow analysis.

M&A Modelling

To determine whether a deal is worth going through with, and what price will make it work, an M&A model must be built.

The first step to the M&A model is to make assumptions about different costs or parts of the deal. Some major assumptions that must be made may include, but are not limited to:

  1. Current share price and number of shares outstanding of the buyer
  2. Current valuation information of the seller
  3. Expected purchase price and premium
  4. Portion of consideration arising from cash
  5. Portion of consideration arising from stock
  6. M&A transaction fees
  7. Financing fees from new equity/debt issues
  8. Expected interest rates on new debt

The next thing that must be built is a sources and uses of funds table. Essentially what this does is breaks down where money for the deal is coming from, and what it is being used for. Importantly, the sources of funds must always equal the uses of funds.

Accretion/Dilution Analysis

At the end of the day, the thing that shareholders will be interested in is whether they will earn more or less from their investment after the deal takes place. This is determined through the earnings per share. While the process for adjusting the balance sheet and income statements for the combined company can be quite complex, and definitely warrants its own blog post, the basic idea is that if EPS goes up, then the deal is accretive. However, if the EPS goes down, the deal is dilutive. A deal that is accretive is perferable for obvious reason.

While there are many, many technical details that affect M&A transactions, and many investment bankers make entire careers out of handling such deals, the basics of the deals almost always remain the same as outlined above. A good way to get comfortable with M&A analysis is to read different reports on M&A deals that are posted in the news, which will begin to lead to understanding of how the deals take place.

If you want to read more on the subject, go read the following book.

Investment Banking, Second Edition: Valuation, Leveraged Buyouts, and Mergers & Acquisitions

What are Options and how do they work?

When someone starts reading about investing, they will very quickly come across options. These sections will often talk about hedging, and use words like exercise price, strike price, maturity date, call and put. So what are options?

Like any financial security, an option is simply a contract between two parties. It gives the buyer the right, but not the obligation, to buy or sell a certain asset at a certain price, and on a certain date. There are three parts to this definition, so lets break down what they mean.

The first part is the right to either buy or sell an asset. This is where we get call and put options. Call options give us the right to buy an asset, while put options give us the right to sell an asset. It doesn’t get more complicated than that.

The second part of our definition is that there is a define asset that we get to buy or sell the asset for. This price is either called the exercise price or the strike price.

The final part of the definition is the date at which we can buy or sell the asset. This is the maturity date. At this date, we now have the option to buy or sell the asset if we wish to. European options can only be exercised on the maturity date, while American options can be exercised at any time up until the exercise date.

To get this option, we need to buy it. The price we pay for an option is called the premium. There are several factors that go into determining how much the option should cost. These include the interest rate environment, the volatility of the price of the underlying asset, the strike price of the option, and the time to maturity on the option. A popular method for pricing options is the Black-Scholes model. This was developed by economists in the 1970s, and is essentially a formula that you plug some information into, and out pops the price of the option.

The basic assumption is that the riskier the underlying asset, the higher the price of the option. This brings us to the idea of hedging.

Hedging is a process in which derivative instruments (such as options) can be used to protect an investor against adverse price movements. If we buy a certain stock, we want the price to move up. If we move down, however, we are going to lose money. So, what we can do is sell a call option, which profits when the price goes down, and if the trade moves against us, we have some of out losses earned back on the option.

So lets break down what the effects we can get from different options.

A call option gives us the ability to buy the underlying asset. We, of course, want to buy at the lowest price possible. So, if we buy a call option, we profit when the price goes up, and we make a loss when the price of the underlying asset goes down.

A put option is the opposite. We always want to sell an asset at the highest price possible, so if we buy a put option, we profit when the price of the asset goes down, and we make a loss when the price of the asset goes up.

In both of these cases, however, we are not obligated to buy or sell the underlying asset. So, if the price moves against the option, the most we will lose is our premium. This is why options are great hedging instruments.

When we are buying options, we have defined risk. The premium is also usually quite small compared to the price of the stock it is related to. If the most we lose is our premium, then our risk is limited to only that. The trade off, however, is that the smaller the premium, the more likely it is that we lose that premium.

In a future blog post, I will write about different strategies that can be implemented with Options in order to reflect different opinions about the markets.

If you would like to get a basic understanding of trading options, the following book is cheap and a good introduction to the markets.

Options Trading for Beginners: How to Get Started and Make Money with Stock Options (Options Trading, Stock Options, Options Trading Strategies)

Commerce Degree Topic: Interest Rate Determination

If you read any kind of financial news, you will invariably come across some news about interest rate predictions. This is quite an important part of finance for obvious reasons – interest rates can wildly change portfolio returns, debt repayments of companies, investment of companies and a whole range of other things.

But how do we determine interest rates? When we model investments, can we predict what the interest rate will be at a date in the future? Of course we can’t do this precisely, but there are a few tools that we can use to help us out.

First, however, we need to understand the context in which we are determining these interests rates.

Macroeconomic Context

The Reserve Bank adjusts its monetary policy in order to keep the inflation rate between 2 and 3 percent. However, one of the tools it can use to do this is the interest rates.

The interest rate is the cost of money, essentially, and the Reserve Bank can adjust its cost of money to influence the interest rates that are offered by consumer banks both in the short and in the long term. While it is not super important for our purposes to understand exactly how they do this, it is important to understand why.

The Reserve Bank may increase interest rates if:

  1. Inflation is above target range
  2. the is excessive growth in GDP
  3. There is a large deficit in the balance of payments
  4. rapid growth in credit and debt levels
  5. excessive downward pressure on FX markets

An increasing of interest rates makes money more expensive, and therefore reduces the amount of money in the economy. For this reason, it is called a tightening of monetary policy.

There are three main effect of the interest rates going up. The first is that eventually, long term interest rates will go up. With the rates on loans and credit cards going up, consumers will spend less which reduces inflation and the demand for imports. Finally, a higher interest rate environment may attract foreign investors, causing worldwide demand for the domestic currency and appreciating the domestic currency.

When we understand these effects, we can look at some of the more general effects of the interest rate change.

Liquidity Effect

When interest rates are increase, the Reserve Bank essentially sells its own securities into the market. This effectively takes money out of the market, and decreases liquidity. The opposite is true if the the interest rates are decreased.

Income Effect

When interest rates go up, and there is less money in the economy, people’s income will decrease. This slows their spending, and actually allows the interest rates to ease.

Inflation Effect

As people purchase less, the demand for loans slows, which means less money is needed in the economy. This reduces the rate of inflation, which is why the Reserve Bank increased interest rates in the first place.

 

When we are trying to predict interest rate movements, we can use different economic indicators. Some of these will lead changes in the business cycle, some will happen with the business cycle, and others will lag the changes in the business cycle. If we are able to use the indicators effectively, we may be able to predict the Reserve Bank’s intervention.

 

Loanable Funds Method

The loanable funds method if the preferred way by financial market analysts to predict interest rate movements, and is quite a simplistic model. We begin with two assumptions.

  1. As interest rates rise, the demand for funds falls
  2. As interest rates rise, the supply of funds increases

We also break the demand for funds into two sectors:

  1. Business demand for funds (B)
  2. Government demand for funds (G)

Therefore, our demand for loanable funds is equal to B + G

Our supply of loanable funds is also split into three sectors:

  1. Savings of the household sector (S)
  2. Changes in the money supply (M)
  3. Dishoarding (D)

Hoarding is the amount of savings held as currency. Dishoarding occurs when interest rates rise and more securities are purchased for the higher yield that is available.

When we plot the supply and demand curves for the loanable funds, we find that they are not independent, and will change. This is mainly due to dishoarding occurring as interest rates change.

Term Structure of Interest Rates

A yield curve that plots the yield on an identical security with different terms to maturity.

If the yield curve is upward sloping, it means that rates in the future are higher than current rates, while the opposite is true for a downward sloping yield curve. We may also encounter yield curves that change from upward sloping to downward sloping.

The fact that yield curves change over time tells us that monetary policy is not the only factor changing interest rates. So, we have three theories to try and explain the shape of the yield curve.

  1. Expectations Theory
  2. Market Segmentation Theory
  3. Liquidity Premium Theory

Expectations Theory

The expectations theory is relatively simple. It says that we forecast rates on a short term basis, and long term rates are an average of those short term forecasts.

For example, if we forecast the rates for one year ahead now, and in one year, then the two year interest rate starting now will be the average of the two. However, for this to work, we need to start with the following assumptions:

  1. Large number of investors with reasonably homogeneous expectations
  2. No transaction costs and no impediments to interest rates moving to their competitive equilibrium levels
  3. Investors aim to maximise returns and view all bonds as perfect subsitutes regardless of term to maturity

Segmented Market Theory

This directly challenges the third assumption of the expectations theory and says that bonds with different maturity levels are imperfect substitutes. It also rejects the assumption that investors aim to maximise returns and instead assumes tht investors aim to minimise their risk.

This then operates as you would expect, by increasing interest rates relative to where funds are being demanded. So, if the Reserve Bank buy a lots of short term securities and sell long term securities, they can increase the average term of bonds, and therefore create an upward sloping yield curve.

Liquidity Premium Theory

Assumes investors prefer short term instruments due to the liquidity, so they require compensation for longer term securities. This premium can be added into the expectations theory by adding a constant liquidity premium term. This will be more significant the further into the future we observe the yield curve. It can also change a curve from downward sloping from upward sloping.

Risk Structure of Interest Rates

This is a simple concept, and essentially says that for riskier bonds, investors require a risk premium  for holding them, meaning that the yield curve will be higher for riskier bonds.

 

Commerce Degree Topic: Microeconomics – International Trade

We know that a country will be able to consumer more if they trade with other countries, but how does this affect that consumers and producers at home?

In general, producers will export a good if the world price is greater than the domestic equilibrium price. Conversely, consumers will import goods if the world price is lower than the domestic equilibrium price.

Exporting

When a country exports, the price is higher for that good. This means that the producers get to charge more for the products, and that the consumers must now pay more. Therefore, we see the normal effect on surplus when the price goes up. Consumers lose surplus, and producers gain surplus.

However, there is a difference here. This time, instead of some producers and consumers being forced out of the market, the producers who would have been forced out just find international buyers. The domestic consumers are the only ones that lose out.

Additionally, because there is now more trade happening, the economy actually enjoys a total surplus increase.

Importing

When a country imports, the exact opposite is true. Consumers gain surplus both domestically and in international markets, while producers lose surplus. However, the economy enjoys a total surplus increase.

So on a whole, the economy benefits from international trade. However, there is always a group that is hurt. Usually this group, if it is powerful enough, will lobby the government to restrict the international trade that is happening.

There are two ways of restricting international trade by the domestic government.

  1. Tariffs
  2. Quotas

Both of these will increase the domestic price, and therefore benefit the consumers.

A tariff functions by increasing the domestic price by a certain amount. This drives demand down and supply up. It is essentially a tax on good produced abroad.

Consumers will lose surplus. The producers will gain surplus, and the government also gains tax revenue.

However, the consumers lose more than the producers and government gain. This is deadweight loss. Some of this is due to buying from domestic consumers at artificially high prices, and the other portion of this is from the reduction of consumption overall.

A quota limits the amount of imports. What this does, is shift the supply to the right.

When a nation hits the import quota, there will be an excess in demand. This demand will be equal to the previous number of imports less the import quota. The demand curve tells us how much domestic producers are willing to pay for these extra goods. This gives us a new market price.

When the price goes up, the supply goes up (this is given by the law of supply), and hence we have a market price.

Local producers benefit, as they are able to sell the extra units at a higher price. Producers lose surplus due to having to pay a higher price. However, the gains by domestic producers losses from domestic consumers don’t equal the surplus prior to the quota being implemented.

Some of this excess surplus goes to importing agents, who are able to buy good at the lower world price, and sell the domestically at the higher domestic price. This only, however, extends to the import quota units. The other surplus goes to unknown entities. It could be anyone that gains this surplus, which opens quota policy up to a lot of fraud.

Therefore, due to the effect being the same for local producers and consumers, tariffs are preferred to quotas, as it is known where the surplus goes to, and fraud is less likely when implementing policies.

Commerce Degree Topic: Origins of Australian Law

This blog post is a part of a series I will be posting that are part of what I am learning in my Commerce degree. This particular post if from the unit called ‘Business and the Law’.

Australia is deeply tied to England and its legal system. Therefore, the Australian legal system in many ways mirrors that of the English system.

Australia has, however, taken some ideas from the system of the United States. Particularly, this is the separation of powers that is written into the Constitution. We therefore have three arms of government – the legislative, the judiciary and the executive. The legislative is made up of the two houses of parliament, and their primary function is to make laws. The executive is made up of the government and the government departments that administer the law, and the judiciary is made up of the system of courts that both ensure the laws are followed, and check the constitutionality of the laws that are written by parliament.

However, the courts also have a very important function that comes from the English legal tradition. The courts make laws in the form of case law or common law. This is one of the most important roles that the courts have.

In this system, when a judge makes a decision, that decision becomes binding on all of the courts that sit below it in the hierarchy. This, in effect, makes a law. These laws that the courts make are not necessarily laws like forbidding insider trading, but relate more to how we determine if someone has broken a statute law, or how we determine if a contract exists between people.

These issues are far too complex to write down, and too nuanced for every case to fit into a certain prescribed set of requirements. Therefore, the laws that the courts come up with are a more fluid set of laws that outline general principles, guidelines and tests that can be made. It is then the job of the judges to ensure that their decisions are made in line with the relevant precedent that they are bound to.

Microeconomics: Government Intervention

Once we have established the idea of surplus, we can now begin looking at how governments might intervene in a market, and what effect that would have.

In each case, we want to define winners and losers. In short – if the surplus increases for a group, they are winners. If surplus decreases for a group, they are losers.

When a government intervenes in a market, they change either the way that surplus is distributed, or the total amount of surplus in the market. There are four basic ways that a government can intervene in a market.

  1. Price Ceiling
  2. Price Floor
  3. Taxation
  4. Subsidy

Price Ceiling

A price ceiling sets the maximum price that a good or service can have in the market. Consequently, it will always be below the equilibrium price (otherwise there would be no need for it.)

The effect of a price ceiling will be trapping the market price below the equilibrium price. Obviously, this means that producers earn less profit, and consumers get more surplus due to paying less for the goods. So in this case, we can say that the consumers are the winners and the producers are the losers.

However, the surplus doesn’t just get redistributed. Due to some producers having reservation prices above the price ceiling, some producers will be forced out of the market. This creates a region in which surplus is lost due to efficient trades not taking place. This region is called deadweight loss.

When there is deadweight loss present, the economy is worse off than it was before the intervention.

Price Floor

A price floor operates in a similar way to a price ceiling, except that it is a minimum price imposed by the government. A price floor will always occur above the equilibrium price.

This time, instead of producers exiting the market, consumers with lower reservation prices will exit the market. Producers are winners, and consumers are losers. Like a price ceiling, there is a deadweight loss due to the efficient trades that are not happening.

Taxation

The simple way to remember the effect of taxation is that the price gets forced up. Therefore, it operates the same as a price floor, but for one important difference.

When the price increases, the production cost for the producers goes up by the amount of the tax. Therefore, they do not experience the benefit that a price ceiling would.

By examining the price movement, we can see that the consumers will bear a fraction of the burden, while the producers also bear a fraction. The surplus lost by the producers and consumers is recognised by the government as tax revenue.

There is also the deadweight loss present as in the price floor.

Subsidy

Put simply, a subsidy is the opposite of a tax. However, the effects it has are not the same as a price ceiling.

The reason for this is that when a subsidy is offered, more producers will enter the market and supply will increase. This pushes the supply curve to the right. The subsidy effectively means that a producer can sell a product for price A, but enjoy the profits as if it sold the product for price B where B>A.

This decreases the price, but more trades will now be taking place. Therefore, both the consumers will benefit (from the lower price) and the producers will benefit (from the higher profits) and their surplus will increase. However, there is still a deadweight loss.

The deadweight loss this time occurs due to the fact that inefficient trades are happening. This is a burden on the economy.

A better alternative?

In all of these cases, there is a deadweight loss. Also, in each of these cases, there is a point to the government intervention.

  1. Price Ceiling: To lower the price and make goods more affordable.
  2. Price Floor: To benefit consumers and give inventive to enter a certain market.
  3. Taxation: To raise government revenue, and increase the price of a good to discourage market participants (for example, alcohol and tobacco taxes).
  4. Subsidy: To lower the prices for consumers and encourage producers to enter the market.

So, there must be a better alternative. In economics, at least, there is. To achieve the effects of the surplus changes, the party that ‘wins’ could do a ‘lump sum transfer’ to the party that ‘loses’ in the amount that the surplus changed the balance.

Economists even argue that as long as the lump sum transfer is less than the deadweight loss, it will occur because a burden on the economy is a burden on everyone.

There are, however the major flaw in this assumption is that markets are always perfectly competitive. The reality is that they are not. The costs for getting into the mining business are extreme, so businesses are not free to enter or exit the market whenever they please.

Human also don’t make decision based on purely economic reasons. An energy company may opt into a less profitable renewable sector because they are conscious about the environment, or a food producer may produce food in a more expensive way due the culture or religious values that they hold.

So, a lump sum transfer may never work. The competitive market is as much of a fiction as finance’s idea of an efficient market.

Microeconomics: Equilibrium Analysis

In Microeconomics, the graphs of supply and demand are a fundamental concept. In short, the supply curve shows how much of a good producers are willing to supply at a given price. The demand curve shows how much consumers are willing to purchase at a given price.

When the demand and supply curves cross, we have a point where the amount supplied and demanded is perfectly matched. In an ideal world, markets would find this price and remain level.

If the market price for a good is above this level, then producers will be willing to supply more than consumers are willing to buy. This gives us an excess of supply.

Conversely, if the price for a good is below the market price, the demand will overtake supply and we will have and excess of demand.

These excesses will mean that all market participants will have an incentive to be at the equilibrium price. If a consumer offers less than the equilibrium price, they will not find a seller. If a producers demands more than the equilibrium price, they will not find a buyer. In this situation, we say that the producers and consumers are both price takers.

Being a price taker means that if you don’t take the price, you will be forced out of the market.

Surplus

Following on from this idea, we can start developing an idea of what we actually gain from being at the market equilibrium. To get this, we need to determine what each side of the trade is hoping to gain.

The producer, obviously, is trying to sell their product for a higher price than what it cost for them to make it, thus making a profit. The consumer on the other hand, is trying to pay as low as possible, and thus as far below their reservation price as possible. We can think of the difference between the consumer reservation price and the market price in the same way we think of profit. We call it consumer surplus.

The surplus is therefore quite easy to visualise. It is the area between the relevant curve and the line of y = market price.

In general, the higher the total surplus, the better for the economy. At equilibrium, the surplus is at a maximum. At this point, we say that the market is ‘pareto efficient’. Essentially, this means that no change can be made without decreasing the surplus. Or, alternatively, nobody can be made better off without making someone else worse off.

The Invisible Hand

The Invisible Hand principle operate in the long term, and is quite simple.

We define the long term as the time horizon where there are no fixed costs, and all factors of production are variable. A firm can change all of its factors of production, or choose to exit a market.

If firms are making a positive profit, there is incentive for new firms to enter the market. As new firms enter the market, the supply increases and hence the equilibrium must decrease.

Therefore, we say that the invisible hand reduces the price in the long term. If our firms are price takers, they must take this new equilibrium price.

Over the long run, we therefore see that the supply is completely inelastic. That is, the curve is horizontal, and producers are willing to produce any amount for the same price. If there is more demand, in the long run more firms will enter the market to increase supply.

Conditional Formatting: How to make a dynamic spreadsheet in Excel

If you have downloaded and used enough excel templates, you have probably come across some spreadsheets that have the cells changing colour when different things happen.

Although this may seem extremely fancy, it doesn’t involve knowing how to code or really any super technical knowledge of Excel. There is a tool built right into Excel which allows you to do this. It lives in the Home tab of the ribbon, and is called Conditional Formatting. Here’s how it works:

You set up a series of rules for a cell. Linked with these rules, you set up a format that you want for the cell. For a simple, albeit not uncommon example, imagine setting up a cell that goes red when it contains a negative number, green when it contains a positive number, and stays white when the number is neither positive nor negative (or it equals zero.)

There are limitless ways you can use this tool, so instead of going through all of the different rules, let’s build an example.

Download the excel worksheets at the bottom of the blog post to see examples and try it for yourself.

We are going to build an example of a DCF sensitivity table, and we will highlight cells green if they are above the current share price, and red if they are below the current share price.

Note: The table is not representative of how an actual DCF Model would work. It just has numbers in it to experiment with conditional formatting. For information on how the DCF works, see THIS BLOG POST.

Setting this up is quite easy. All you have to do, is highlight the cells that contain the stock prices, and go to the conditional formatting menu.

You then click “Highlight Cell Rules > Less Than” and then select the cell that contains the current stock price. Then you choose the formatting. Excel has a handy preset format of a red fill with dark red text, and a green fill with dark green text. Choose the red one. You repeat this process for the greater than, choosing the green option this time, and you will see the result is that everything above the current stock price is green, and everything below the current stock price is red. Everything equal to the current stock price has no formatting.

We will do one more example. We are going to turn THIS list of stock prices into a list from which we can see a trend. This uses the same rules as before, except instead of using a set cell this time, what you do is reference the cell below. Remember to remove the anchoring for the reference cell.

Do this for the bottom left cell, and then copy and paste the format into the other cells. You do this by copying the cell using Ctrl-C, and you paste the formatting using Alt-E-S-T. Now we can see when a stock price moves up or down compared to the previous period.

Formatted Worksheet

Non-Formatted Worksheet