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 prerequisite 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 prerequisite 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 (BRKB) and decided on a market proxy (S&P500) and a diversifying stock (Eli Lilly (LLY)).


Data
 I am analyzing data for 36 months ending June 2010 (You are doing 36 months ending January 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 TBill 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 BRKB, LLY, and the S&P500. Manually enter the dividend and split data.
 Download 3 month TBill 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 Jun2010.
 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


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


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.


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


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(5050 Portfolio), R(MVP), etc allows the user to navigate the spreadsheet.
Powerpoint Guide: The Market Model


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


