Project 1 Stock Evaluation and Market Efficiency
Submit your Excel Spreadsheet work on Canvas
Complete the project individually, though you can help each other with concepts and spreadsheet skills as long as you don’t do the work for each other.
Pick one stock publicly traded on the market other than Microsoft and use the template of Microsoft given to perform the following analysis on your stock.
Make sure your stock has available dividend history and financial information needed.
Also, avoid selecting financial or utility firms.
(You can use my template as reference, but you do have to pick your own stock and use the updated data information for your selected stock.)
Q1: obtain the past dividend distributions information and the systematic risk factor beta to estimate the discount rate (both type of information can be obtained from either Finance Yahoo! or MorningStar) Then use the Dividend Discount Model (DMM) to estimate the fundamental stock price with the following four assumptions, respectively:
1)Assuming that future annual dividends will remain the same as the current dividend.
2)Assuming that each year future annual dividends will grow at a rate consistent with the company’s earnings growth rate from financial analysis.
3)Assuming that each year future annual dividends will grow at an average rate of past dividend growth rates.
4)Assuming that, each year of the next five years, dividends will grow at a non-constant rate and then achieve stable annual growth after 5 years.
Compare the DDM estimated stock price under each assumption to the actual stock price on the market. Explain the differencebetween your projected stock price and the actual stock price under each assumption if there is any.
Q2: estimate the stock price using Residual Income Model (RIM) with the information of past annual earnings per share (EPS) of book value per share (BPS) of your stock.
Compare the RIM estimated stock price to the actual stock price on the market. Explain the differencebetween the two stock prices if there is any.
Q3: obtain historical earnings per share (EPS), price/earnings ratio (P/E), price/book value ratio (P/B), book value per share (BPS), price/sales ratio (P/B), price/cash flows (P/CF) and other key financial information from MorningStar. Estimate stock price and explain price differenceswith actual trading price by using price ratio analysis on
1)price/earnings ratio (P/E)
2)price/book value ratio (P/B)
3)price/sales ratio (P/B)
4)price/cash flows (P/CF)
Q4: get the past five year daily stock price of your stock, and perform a run test on weak-form market efficiency. What can you conclude from the run test results?
Q5: get the past five year daily stock price of your stock, and perform an autocorrelation test on weak-form market efficiency. Do the daily stock price appear to be autocorrelated?