Data Table : Advancing a model

 

                                                         Data Table : Advancing a model

Link for Data table in excel

What is a data table

Once you have created a model, you may want to create tables showing how some of the results (outputs) of the model would look for a series of different values for one or more of the inputs to the model.  

A one-input data table

A one-input data table (also called one-variable data table) allows you to show the values of several dependent (output) variables for a range of values for  one independent (input) variable.

Independent variables

•       Independent variables are the inputs to a model.

•       They are not calculated in the model; the user has to provide them.

•       They are called independent because they can be changed independently, at the user’s choice.

•       In the worksheets, the cells for independent variables have only input numbers or text and
not any formula.             

Dependent variable

•       Any value calculated within the model is a dependent variable because its value depends on the values of other independent and dependent variables.

•       In the worksheets, any cell that has a formula in it represents a dependent variable.

A two-input data table

•       A two-input data table allows you to show the values of one dependent (output) variable for ranges of values for two independent (input) variables.        

•       Example

•       Number of units sold 100

•       Sales price/unit $12

•       Purchase price/unit $10

•       Income-tax(40%) $80

•       Q.1 : Build a revenue model. Calculate : Revenue, COGS,EBIT,PAT

Q.2:  Revenue and after tax income for different no. of sales units

Q.3 Revenue for different no. of sales units and different selling price per unit.

 

Risk-free rate

 

The risk-free rate is the rate of return of an investment with no risk of loss. Most often, either the current Treasury bill, or T-bill, rate or long-term government bond yield are used as the risk-free rate

Random walk theory

Random walk theory

 

—  Random walk theory is a financial model which assumes that the stock market moves in a completely unpredictable way. The hypothesis suggests that the future price of each stock is independent of its own historical movement and the price of other securities.

—  Random walk theory assumes that forms of stock analysis - both technical and fundamental - are unreliable.

—  The implication for traders is that it is impossible to outperform the overall market average other than by sheer chance.

 

Random walk vs Efficient Market Hypothesis (EMH)

—  Random walk theory has been likened to the efficient market hypothesis (EMH), as both theories agree it is impossible to outperform the market. However, EMH argues that this is because all of the available information will already be priced into the stock’s price, rather than that markets are disorganised in any way.

 

Basic Assumptions of the Random Walk Theory

1. The Random Walk Theory assumes that the price of each security in the stock market follows a random walk.
2. The Random Walk Theory also assumes that the movement in the price of one security is independent of the movement in the price of another security.

 

History

In 1964, American financial economist Paul Cootner published a book entitled “The Random Character of Stock Market Prices.” Considered a classic text in the field of financial economics, it inspired other works such as “A Random Walk Down Wall Street” by Burton Malkiel (another classic) and “Random Walks in Stock Market Prices” by Eugene Farma.

 

Implications of the Random Walk Theory

Since the Random Walk Theory posits that it is impossible to predict the movement of stock prices, it is also impossible for a stock market investor to outperform or “beat” the market in the long run. It implies that it is impossible for an investor to outperform the market without taking on large amounts of additional risk.

As such, the best strategy available to an investor is to invest in the market portfolio, i.e., a portfolio that bears a resemblance to the total stock market and whose price reflects perfectly the movement of the prices of every security in the market.

A flurry of recent performance studies reiterating the failure of most money managers to consistently outperform the overall market has indeed led to the creation of an ever-increasing number of passive index funds.

 Advantages of Random Walk Theory

·         It provides a cost-effective way of investing. That is an investment in ETFs.

·         In many situations market has not acted as predicted, which proves that stock prices are indeed random.

Disadvantages of the Random Walk Theory

·         Markets are not entirely efficient. Information asymmetry is there, and many insiders react much early than other investors due to the information edge.

·         In many cases, stock prices have shown trend year on year.

·         One lousy news affects a stock price for several days, even months.

 

A Non-Random Walk

In contrast to the Random Walk Theory is the contention of believers in technical analysis – those who think that future price movements can be predicted based on trends, patterns, and historical price action. The implication arising from this point of view is that traders with superior market analysis and trading skills can significantly outperform the overall market average.

Conclusion

So, who do you believe? If you believe in the Random Walk Theory, then you should just invest in a good ETF or mutual fund designed to mirror the performance of the S&P 500 Index and hope for an overall bull market.

If, on the other hand, you believe that price movements are not random, then you should be polishing your fundamental and/or technical analysis skills, confident that doing such work will pay off with superior profits through actively trading the market.

Efficient Markets Hypothesis (EMH)

 

Efficient Markets Hypothesis (EMH)

 

Introduction

The Efficient Markets Hypothesis (EMH) is an investment theory.

By Eugene Fama’s research as detailed in his 1970 book, “Efficient Capital Markets: A Review of Theory and Empirical Work.”

Fama put forth the basic idea that it is virtually impossible to consistently “beat the market” – to make investment returns that outperform the overall market average as reflected by major stock indexes such as the S&P 500 Index.

 

Assumptions of the Efficient Markets Hypothesis

—  The assumptions include the one idea critical to the validity of the efficient markets hypothesis: the belief that all information relevant to stock prices is freely and widely available, “universally shared” among all investors.

—  As there are always a large number of both buyers and sellers in the market, price movements always occur efficiently (i.e., in a timely, up-to-date manner). Thus, stocks are always trading at their current fair market value.

—  The major conclusion of the theory is that since stocks always trade at their fair market value, then it is virtually impossible to either buy undervalued stocks at a bargain or sell overvalued stocks for extra profits. Neither expert stock analysis nor carefully implemented market timing strategies can hope to average doing any better than the performance of the overall market.

 

Variations of the Efficient Markets Hypothesis

—  There are three variations of the hypothesis – the weak, semi-strong, and strong forms – which represent three different assumed levels of market efficiency.

—  The weak form suggests today’s stock prices reflect all the data of past prices and that no form of technical analysis can aid investors. 

—  The semi-strong form submits that because public information is part of a stock's current price, investors cannot utilize either technical or fundamental analysis, though information not available to the public can help investors.

—  The strong form version states that all information, public and not public, is completely accounted for in current stock prices, and no type of information can give an investor an advantage on the market.

 

Capital Asset Pricing Model (CAPM)

 

Capital Asset Pricing Model (CAPM)

CAPM In Excel

Introduction

}  The capital asset pricing model was developed by the financial economist (and later, Nobel laureate in economics) William Sharpe, set out in his 1970 book Portfolio Theory and Capital Markets. His model starts with the idea that individual investment contains two types of risk:

}  Systematic Risk – These are market risks—that is, general perils of investing—that cannot be diversified away. Interest rates, recessions, and wars are examples of systematic risks.

}  Unsystematic Risk – Also known as "specific risk," this risk relates to individual stocks. In more technical terms, it represents the component of a stock's return that is not correlated with general market moves

}  No matter how much you diversify your investments, some level of risk will always exist. So investors naturally seek a rate of return that compensates for that risk. The capital asset pricing model (CAPM) helps to calculate investment risk and what return on investment an investor should expect.

The CAPM can be calculated with the CAPM formula as follows:

ERi = Rf + βi(ERm-Rf)

}  ERi = Expected return of investment

}  Rf = Risk-free rate

}  βi = Beta of the investment

}  ERm = Expected return of the market

}  (ERm – Rf) = The market risk premium, which is calculated by subtracting the risk-free rate from the expected return of the investment account. 

The benefits of CAPM include the following:

}  Ease of use and understanding

}  Accounts for systematic risk

The limitations of CAPM include the following:

}  Experts believe it is too simplistic because it does not cover all of the risks that are involved with investing 

}  It does not correctly evaluate reasonable returns

}  Assumes that you can lend and borrow at a risk-free rate

}  Returns that are calculated evaluate past returns and might not accurately reflect future returns

 

Arbitrage Pricing Theory

 Introduction

}  Arbitrage pricing theory (APT) is an alternative to the capital asset pricing model (CAPM) for explaining returns of assets or portfolios.

}  It was developed by economist Stephen Ross in the 1970s. The arbitrage pricing theory is a lot more difficult to apply in practice because it requires a lot of data and complex statistical analysis.

 What Is APT?

APT is a multi-factor technical model based on the relationship between a financial asset's expected return and its risk. The model is designed to capture the sensitivity of the asset's returns to changes in certain macroeconomic variables. Investors and financial analysts can use these results to price securities.

 Three Underlying Assumptions of APT

}  Asset returns are explained by systematic factors.

}  Investors can build a portfolio of assets where specific risk is eliminated through diversification.

}  No arbitrage opportunity exists among well-diversified portfolios. If any arbitrage opportunities do exist, they will be exploited away by investors. (This how the theory got its name.)

Formula of Expected Return of a diversified portfolio as per APT

}  For a well-diversified portfolio, a basic formula describing arbitrage pricing theory can be written as the following:

}  ​E(Rp​)=Rf​+β1​*RP1​+β2​* RP2​+…+βn​*RPn

 where:

}  E(Rp​)=Expected return

}  Rf​=Risk-free return

}  βn​= β is the sensitivity of the asset or portfolio in relation to the specified factor

}  fn​= RP is the risk premium of the specified factor.

Factors affecting expected return

}  Unlike in the capital asset pricing model, the arbitrage pricing theory does not specify the factors. However, according to the research of Stephen Ross and Richard Roll, the most important factors are the following:

}  Change in inflation

}  Change in the level of industrial production

}  Shifts in risk premiums

}  Change in the shape of the term structure of interest rates

  KEY TAKEAWAYS

}  The CAPM lets investors quantify the expected return on investment given the risk, risk-free rate of return, expected market return, and the beta of an asset or portfolio.

}  The arbitrage pricing theory is an alternative to the CAPM that uses fewer assumptions and can be harder to implement than the CAPM.

}  While both are useful, many investors prefer to use the CAPM, a one-factor model, over the more complicated APT, which requires users to quantify multiple factors.