Please Submit with Excel Format. ALL DATA IS FROM YAHOO FINANCE!!!!!

with 5 years and 10 firms ranked by PE in 2008, you are required to perform stock valuation methods to analyze individual stock and Capital Asset Pricing Model to determine your final portfolio performance.

With five years of financial fundamentals for each firm, you will need to first determine the predicted value of each firm:

Question A:

Apply P/E ratio and P/Book value ratio analysis to predict the expected values for 10 firms and identify firms which are undervalued and which are overvalued. (Hint: Use P/E and P/B average ratio and growth rates of earnings and book value)

Question B:
With both methods used above, determine and screen over the final selected 7 firms into your portfolio. The final firms are determined BOTH undervalued by both P/E and P/B methods. 

Question D

In your final portfolio with selected undervalued firms from question B, with a required rate of return and beta calculated for each firm (regressed on market return by using weekly data from 1/1/2011-11/30/2013), please find the portfolio risk(beta, assume equally weighted) and portfolio required return. Note: (Calculating Beta by using regression is optional. If you don’t know how to use the regression to find firm’s beta, you can use yahoo finance or google finance to get it)