Equity Project

The data analysis that supports your group equity project is described in detail. Begin with a blank excel spreadsheet. If you have the spreadsheet proficiency required as a pre-requisite for Finance 300 the analysis should take you about an hour and many of the detailed explanations below will be unnecessary. If you do not have the pre-requisite level of proficiency then it will take you longer.

The Model

The theoretical model is mapped out in the flow chart. We can see that, to derive an abnormal return on investment, we need data to calculate the risk free rate of return, the market rate of return, and the rate of return on the investment.

You should understand the basic flow of the model before you begin.

Also, before I begin, my group has done its preliminary research on our assigned company (Berkshire Hathaway (BRK-B) and decided on a market proxy (S&P500) and a diversifying stock (Eli Lilly (LLY)).

Data Tab in the CAPM

Data

  • I am analyzing data for 36 months ending June 2010 (You are doing 36 months ending August 31, 2014) so I prepare my [Data] tab by specifying the months June 2010 through June 2007 down Column A and headers across Rows 1 and 2.
  • I will need to calculate a rate of return for each asset. This means I need Date, Open High, Low, Close, Dividend, and Split information for each equity issue. The index doesn't issue dividends or split, so that will be easier. The T-Bill rate is already in annual % format, so I need to adjust this so I can calculate monthly Yields.
  • Download to spreadhseet from Yahoo!Finance for BRK-B, LLY, and the S&P500. Manually enter the dividend and split data.
  • Download 3 month T-Bill data from FRED.
  • Note that monthly data for June is labelled June 1. If it bugs you, reformat the excel date from 6/1/2010 to Jun-2010.
  • Note that, consistent with the rules of best practise in our industry, downloaded data is coded Blue; Calculated data is coded black. Calculations can be changed, but do not mess with raw data.
  • Footnote the data source.

Powerpoint Guide: Data Collection

Data for BRK
Data for the Index and Risk Free Rate

Candlestick

  • Relay the date, open, high, low, close prices, and split data to the chart tab.
  • Now you can see exactly which prices need to be adjusted to accommmate splits.
  • Calculate the price based on the current definition of the share. This means that we adjust historical, not current, data. The 50:1 split in Berkshire Hathaway leaves us with historical prices around $80/share. We do not adjust current prices to get back to $4000/share.
  • The Candlestick chart is one of the built in Excel charts. But you are responsible for formatting the chart so that it looks professional. It makes no sense to label prices to two decimal places when the tick marks are $20 apart; it makes no sense to label every month when the labels then run into each other. Make sure that the graph is something that you would be willing to show a prospective employer as a sample of your work.

Powerpoint Guide: Graphing

High Low Close Chart

Returns

  • Rates of return are calculated into the [Returns] tab.
  • The rates of return on individual equities must be adjusted for dividends and splits. Hand calculate to double check your excel formula.
  • The index has neither dividends nor splits so this is easier.
  • The yield needs to be converted to % and to monthly.
  • Make sure that you verify your results before you procede to the next step.
  • Construct the 50/50 portfolio returns and the MVP portfolio returns
  • The returns on each of the four portfolios will then be regressed against our market proxy.
Rates of Return

Efficient Portfolio Frontier

  • This is Model 4.2 in Oltheten & Waspi 2012
Efficient Portfolio Frontier

The Market Model: Calculating Beta

  • This is Model 5.1 in Oltheten & Waspi 2012
  • Double check that you have all 36 observations
  • Format the Regression output so that your reader is impressed with your professionalism
  • You are estimating four betas.
    • the company you are analyzing
    • your diversifying company
    • a 50/50 portfolio
    • the minimum variance portfolio
  • Label the tabs properly. Sheet3, Sheet4, Sheet5 tell your users nothing. R(50-50 Portfolio), R(MVP), etc allows the user to navigate the spreadsheet.

Powerpoint Guide: The Market Model

Rates of Return

CAPM: the Security Market Line

  • This is Model 5.1 in Oltheten & Waspi 2012
  • Remember to format and label so that your graph looks professional.

Powerpoint Guide: CAPM

Rates of Return