Excel portfolio data:

4.(46%) Given the portfolio data on Blackboard, 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 C (separated from question A and B): Using Dividend Valuation Model to determine the intrinsic values of selected firms, you would need to calculate dividend growth rate and required rate of return. To calculate the required rate of return, use CAPM. Risk free rate is assumed to be zero (very close to 0 now) and use weekly market S&P 500 (1/1/2011-11/30/2013) to obtain market return. Then determine the following firms' intrinsic values: Zero dividend growth: Yahoo, Google Constant dividend: Bank of America Constant Growth on dividend: IBM, Microsoft Question D: In your final portfolio with selected undervalued firms from question B, with 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) Shane Apoel Take Home Final Excel Data Hortense Page Layout Formulas Dura Review View Calor General Men Precoveringed Workbooks we were able to save tharges to one or more. Do you want to recoverem? Act Office to Cradle and to your heart in the nation - A ES X . 6% Coro CE SI B Yes NO Net 1 O SRCE . WA GER 19 USD 0 GOTTI TELEP o 0 ort HA 0359 TCP POE CHEST 0 TE FERLI 1991 FRIGE REL 120 1 SEHE BEERSE 1918 109 Canman Shares Malincome Stockholders Equity Dividende per Share_Price Chie Anne Outstanding | La Parent Day Dute - Flocales 15815 8.37 2010 o 11186 100 3123 1154 0 3419 4995 0 M 25823 935 43739 13200 22 2106 105 -34623 0 2141 10 1714 2211 58 23 10 170 901 13 113301 1985 1061 SN BA 10 3458 1790 110 . 1943 GIOR AP 0 ta IS 1 I. 919 112 IND 16, 1344 o 1 16 130 11 4140 11 1 124 24 1961 1991 130 11 10 16 130 131 218 11 15 18 33 ST 300 17 SE re Duta Year Theker Duta Durres Emael Company Name 2007 ALIM 2008 APPLE TODO LEN 20110010 DAME APPLIC 20 AVENTINE 2008 2017 FORO WORLD FOR MORE JON COLOR CO POROMOTORO 200 FOROWOCO 1211 2001 GERAL TRIKO 2001 GENERAL RICO TO . RICCO JO GRACICCO BUL 2011 MAKRO 200 CM GOPA MOTORSCO 2001 OSCO DUM GIOCO 2001 M GENERAL MOTORSCO 20 GENERAL OROSCO 2000 HEWLETT PARDCO 2010 HOW TO 0101011 PO HIWARDCO 1 HEWLETTACCO 20 HIWARDED M INDOOR 2010 www.WONG.COM IMRE OSCO MINT BUSES MAINS CORP 2004 CALORE > AGOR MAJA 2011 11 | 6 H 4 4 4 l 1 MACO OCTO 20 CHOCO TRO HE WHERE PSP LE PER 91 HERRE HD 0 o NE 18 HEN W TH 20 28 1 NE De 28 HERRE INIONS IL IN VER 01 TH INF 1991 HU HERE TE HT 1991 31 18 NO HIP PET 100 48 Hoe 10 TI SO 4581 11 1118 4 12 Shane Apoel Take Home Final Excel Data Hortense Page Layout Formulas Dura Review View Calor General Men Precoveringed Workbooks we were able to save tharges to one or more. Do you want to recoverem? Actiefoto Cristiat tut your own anyon A ES X . 6% Coro CE SMS I ! B Yes ON Act Cash Common Sures Outstanding 2263 Nance Lan) Stockholders Equity Parent 200 Dividende per Share Price Che Annual ray Dute-Fheal Dised SERE TE Herr HBC 145 11 ISSN 16 156631 10 W in 2 21 23 . o 0 15422 DO 17 BER HERE TEST HERE 1311 W 2 Han JO 15 816 MN -43650 EN 14 W SZERE SEHE TETE REST 12341 12182 2015 23 133.0 110.27 Duta Year Ticker Dula Difesi symbol Company Name 201 M GENERAL MOTORSCO 2001 M GENERAL MOSCO 101011 GERA MOTORO DIM GENEALORSO 11 2002 M GENERAL MOTORS 2010 2010 HEWLETT-BACUSCO TO1011 2001 HIWUTARDCO 2010 HOWARD 20111011 2011 HEWLETT PACKARDCO 20111011 2011 HEWLETT-HACERDCO 081111 2001 INTLBUS SCHRES.COM 200 2001 INT BUSINESS TOI INFLUSSON 2011 INTL BUSINESS WACHINES 2011 INTLUS WOHINES.COM 2008 2 OF AMERICA CORP WEMA CORP IULUI WAGOR ACOR WE OFFRAGON 700 MOSCOW MUT CHOCO 101010 MOSCO MICROSOFT.COM 2012 MICHOSOCO DORO 20 WO 1425 34333 SE 19 215 25 REAL LESER PER ETTE 1990 w WE 12 116 111 SEL! 588 08 33 104 TRE 1591 NS) 11444 RIOS 11 wat 521890 10 NE erer 13 SMS 13 LOT 90 30 Mih HT 04 101 30 10 4 NH 1885 1981 400 5878 32 HAL 841 WER THE NIT 18 H E WANI VE 10 o W! 110 1930 1292 1964 1561 102N ROMA 21 101 11 2011 200 W WIN WOO DOLM HOW 119 1115 1111 KONSE 0 0 IN 7 PE ROS LLORE NER O BOD GOD HU 9000 150 HER 141 0 981 wa 101 SH TRICOD 10 0 O * WEEN 16 11 Symbol Cash Comments Outstanding BLYD Net income Stockholders Egly Dividendeshare. Prehemu lonel Pay Dale Real WIT UE 1500.137 872 1564 SPST 0516 2 BILE ONE O HE NER N. 2164 RE CEO 700 you 18 SEEE ASI 10029 TETET IN 13 TU LOFTL 190.00 1 2014 1200 WE 334 1774 HOME 11 DEZI TETESO 1848 WE LE CE 111 Company Name ORAL MOTORSCO GENERAL MOTORSCO GENERAL MOTORS.CO LETSPREKORDCD WITH ACEROCO www.CO ARCHARDCO MONTECARO CO ISS MACHINES.COM IN MACHINES.COM INNESMES CON MEDENIS CORT IN MINE.COM WE OF AMERICA CORP SANOAMERICA LANE AMERICA GAME OF AMERICA WOMAN MICROSOFTCORE MICROSOFT MICROSOTCOM MOSCOW MCHCOTCOM 220 HERE 1061 13 104 25 ERE 111 MOET 43 14 1458 14 400 18 1951 1 CERET NE Data Year Ticker Data Dal Flocal INT 200012 2012 2011 GM 2013 M Joca 10001031 1010161 1911 16121911 2012 10021231 NOM 2009 ta 010331 2010 2011 11 10 2013 100111 300 C 1000 1010 . 2010 S 11 2011 2012 10010 20 MT 20090610 . 2016010 SA 2010 MM 0110010 2011 M 2010 300 W on 2009 0 20101711 2010 0 2011 2013 00 20120711 2012 2008121 2008 GOOG 2013 101011 2010 20111231 2011 000 2012 3012 000 DIE SS SHOP 120100 221 14 18 20341 310 0.64 111 TENDE ICE 18 151 14 2008 N SO 14 18 TE SOS DET M SI RE BEN 11 1111 1 VER IN EN 434 IT IND O 1 16 14 AHDOING WOO IN IEEE 221 1274 15647 16 2770 16 1048 WITH ONE 1150.000 1115 115 116 6226 WOTE 2008 WWOING GOOGLE GOOGLE GOOOLING GOOOLLIN GOOGLE INC AN SES HEIE 10 SERVER 13630 14 2011 10 Dista HO LITE 10000 10000 B WE I 500 w that 00 900 500 TELLIT EDOT TE VES 1 340 SON CLER LOGIN . 1 TY BE SEPSE ET ENT LET NOTE SEE . UHR w 2008 WEST RE WE SE WHE THE W E METE MICROSOFT WRITE 011 BLOOM NE WE 4175 de OST DORT GOSTI ET UP sos SON 23 Le TE 04 RE 15 VR SE NIME 31 14 TELE 100 MICT.COM MOTO MONACO OLAC NAME OF AMERICA CORP SOCOR MUSIC TULEM MACHINES.COM RE HE FE 38 4 30 TI STE RE 11 1 29 BE RESET LOW LIBE TELLO TE 1 SE 101 CLE VITE TOE LE 1 CENTE SHE we LEH 2.1 19 WWW DENOU ON TRE TI 10 THE HETTO TEST WIWITEARO CD Det tror Die LE On FI W ER ON SITE O To SOWIE SIRE MIX CHEVYT DOMINO COM RELI 2 31 SEE at thi Tot HR LESE DO > 0 4412 RE 2011 301 GM ONTS 1 . 11001 HOWRITT-PACKARDCD SIRAL MOSCO GIAO.CO GRATORIO GANGST. GENERAL MOTORS.CO Cominy Name woost LE La CORP 10 NE LOVE 301 100 w 1 114 Focal Pay Date.nl Stockholders Equity Dividende per Share Price Clee Annual Parent 16:41 | Natione Outstanding Common Shares Cash Data Bucal Dela Yeer Ticker 4.(46%) Given the portfolio data on Blackboard, 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 C (separated from question A and B): Using Dividend Valuation Model to determine the intrinsic values of selected firms, you would need to calculate dividend growth rate and required rate of return. To calculate the required rate of return, use CAPM. Risk free rate is assumed to be zero (very close to 0 now) and use weekly market S&P 500 (1/1/2011-11/30/2013) to obtain market return. Then determine the following firms' intrinsic values: Zero dividend growth: Yahoo, Google Constant dividend: Bank of America Constant Growth on dividend: IBM, Microsoft Question D: In your final portfolio with selected undervalued firms from question B, with 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) Shane Apoel Take Home Final Excel Data Hortense Page Layout Formulas Dura Review View Calor General Men Precoveringed Workbooks we were able to save tharges to one or more. Do you want to recoverem? Act Office to Cradle and to your heart in the nation - A ES X . 6% Coro CE SI B Yes NO Net 1 O SRCE . WA GER 19 USD 0 GOTTI TELEP o 0 ort HA 0359 TCP POE CHEST 0 TE FERLI 1991 FRIGE REL 120 1 SEHE BEERSE 1918 109 Canman Shares Malincome Stockholders Equity Dividende per Share_Price Chie Anne Outstanding | La Parent Day Dute - Flocales 15815 8.37 2010 o 11186 100 3123 1154 0 3419 4995 0 M 25823 935 43739 13200 22 2106 105 -34623 0 2141 10 1714 2211 58 23 10 170 901 13 113301 1985 1061 SN BA 10 3458 1790 110 . 1943 GIOR AP 0 ta IS 1 I. 919 112 IND 16, 1344 o 1 16 130 11 4140 11 1 124 24 1961 1991 130 11 10 16 130 131 218 11 15 18 33 ST 300 17 SE re Duta Year Theker Duta Durres Emael Company Name 2007 ALIM 2008 APPLE TODO LEN 20110010 DAME APPLIC 20 AVENTINE 2008 2017 FORO WORLD FOR MORE JON COLOR CO POROMOTORO 200 FOROWOCO 1211 2001 GERAL TRIKO 2001 GENERAL RICO TO . RICCO JO GRACICCO BUL 2011 MAKRO 200 CM GOPA MOTORSCO 2001 OSCO DUM GIOCO 2001 M GENERAL MOTORSCO 20 GENERAL OROSCO 2000 HEWLETT PARDCO 2010 HOW TO 0101011 PO HIWARDCO 1 HEWLETTACCO 20 HIWARDED M INDOOR 2010 www.WONG.COM IMRE OSCO MINT BUSES MAINS CORP 2004 CALORE > AGOR MAJA 2011 11 | 6 H 4 4 4 l 1 MACO OCTO 20 CHOCO TRO HE WHERE PSP LE PER 91 HERRE HD 0 o NE 18 HEN W TH 20 28 1 NE De 28 HERRE INIONS IL IN VER 01 TH INF 1991 HU HERE TE HT 1991 31 18 NO HIP PET 100 48 Hoe 10 TI SO 4581 11 1118 4 12 Shane Apoel Take Home Final Excel Data Hortense Page Layout Formulas Dura Review View Calor General Men Precoveringed Workbooks we were able to save tharges to one or more. Do you want to recoverem? Actiefoto Cristiat tut your own anyon A ES X . 6% Coro CE SMS I ! B Yes ON Act Cash Common Sures Outstanding 2263 Nance Lan) Stockholders Equity Parent 200 Dividende per Share Price Che Annual ray Dute-Fheal Dised SERE TE Herr HBC 145 11 ISSN 16 156631 10 W in 2 21 23 . o 0 15422 DO 17 BER HERE TEST HERE 1311 W 2 Han JO 15 816 MN -43650 EN 14 W SZERE SEHE TETE REST 12341 12182 2015 23 133.0 110.27 Duta Year Ticker Dula Difesi symbol Company Name 201 M GENERAL MOTORSCO 2001 M GENERAL MOSCO 101011 GERA MOTORO DIM GENEALORSO 11 2002 M GENERAL MOTORS 2010 2010 HEWLETT-BACUSCO TO1011 2001 HIWUTARDCO 2010 HOWARD 20111011 2011 HEWLETT PACKARDCO 20111011 2011 HEWLETT-HACERDCO 081111 2001 INTLBUS SCHRES.COM 200 2001 INT BUSINESS TOI INFLUSSON 2011 INTL BUSINESS WACHINES 2011 INTLUS WOHINES.COM 2008 2 OF AMERICA CORP WEMA CORP IULUI WAGOR ACOR WE OFFRAGON 700 MOSCOW MUT CHOCO 101010 MOSCO MICROSOFT.COM 2012 MICHOSOCO DORO 20 WO 1425 34333 SE 19 215 25 REAL LESER PER ETTE 1990 w WE 12 116 111 SEL! 588 08 33 104 TRE 1591 NS) 11444 RIOS 11 wat 521890 10 NE erer 13 SMS 13 LOT 90 30 Mih HT 04 101 30 10 4 NH 1885 1981 400 5878 32 HAL 841 WER THE NIT 18 H E WANI VE 10 o W! 110 1930 1292 1964 1561 102N ROMA 21 101 11 2011 200 W WIN WOO DOLM HOW 119 1115 1111 KONSE 0 0 IN 7 PE ROS LLORE NER O BOD GOD HU 9000 150 HER 141 0 981 wa 101 SH TRICOD 10 0 O * WEEN 16 11 Symbol Cash Comments Outstanding BLYD Net income Stockholders Egly Dividendeshare. Prehemu lonel Pay Dale Real WIT UE 1500.137 872 1564 SPST 0516 2 BILE ONE O HE NER N. 2164 RE CEO 700 you 18 SEEE ASI 10029 TETET IN 13 TU LOFTL 190.00 1 2014 1200 WE 334 1774 HOME 11 DEZI TETESO 1848 WE LE CE 111 Company Name ORAL MOTORSCO GENERAL MOTORSCO GENERAL MOTORS.CO LETSPREKORDCD WITH ACEROCO www.CO ARCHARDCO MONTECARO CO ISS MACHINES.COM IN MACHINES.COM INNESMES CON MEDENIS CORT IN MINE.COM WE OF AMERICA CORP SANOAMERICA LANE AMERICA GAME OF AMERICA WOMAN MICROSOFTCORE MICROSOFT MICROSOTCOM MOSCOW MCHCOTCOM 220 HERE 1061 13 104 25 ERE 111 MOET 43 14 1458 14 400 18 1951 1 CERET NE Data Year Ticker Data Dal Flocal INT 200012 2012 2011 GM 2013 M Joca 10001031 1010161 1911 16121911 2012 10021231 NOM 2009 ta 010331 2010 2011 11 10 2013 100111 300 C 1000 1010 . 2010 S 11 2011 2012 10010 20 MT 20090610 . 2016010 SA 2010 MM 0110010 2011 M 2010 300 W on 2009 0 20101711 2010 0 2011 2013 00 20120711 2012 2008121 2008 GOOG 2013 101011 2010 20111231 2011 000 2012 3012 000 DIE SS SHOP 120100 221 14 18 20341 310 0.64 111 TENDE ICE 18 151 14 2008 N SO 14 18 TE SOS DET M SI RE BEN 11 1111 1 VER IN EN 434 IT IND O 1 16 14 AHDOING WOO IN IEEE 221 1274 15647 16 2770 16 1048 WITH ONE 1150.000 1115 115 116 6226 WOTE 2008 WWOING GOOGLE GOOGLE GOOOLING GOOOLLIN GOOGLE INC AN SES HEIE 10 SERVER 13630 14 2011 10 Dista HO LITE 10000 10000 B WE I 500 w that 00 900 500 TELLIT EDOT TE VES 1 340 SON CLER LOGIN . 1 TY BE SEPSE ET ENT LET NOTE SEE . UHR w 2008 WEST RE WE SE WHE THE W E METE MICROSOFT WRITE 011 BLOOM NE WE 4175 de OST DORT GOSTI ET UP sos SON 23 Le TE 04 RE 15 VR SE NIME 31 14 TELE 100 MICT.COM MOTO MONACO OLAC NAME OF AMERICA CORP SOCOR MUSIC TULEM MACHINES.COM RE HE FE 38 4 30 TI STE RE 11 1 29 BE RESET LOW LIBE TELLO TE 1 SE 101 CLE VITE TOE LE 1 CENTE SHE we LEH 2.1 19 WWW DENOU ON TRE TI 10 THE HETTO TEST WIWITEARO CD Det tror Die LE On FI W ER ON SITE O To SOWIE SIRE MIX CHEVYT DOMINO COM RELI 2 31 SEE at thi Tot HR LESE DO > 0 4412 RE 2011 301 GM ONTS 1 . 11001 HOWRITT-PACKARDCD SIRAL MOSCO GIAO.CO GRATORIO GANGST. GENERAL MOTORS.CO Cominy Name woost LE La CORP 10 NE LOVE 301 100 w 1 114 Focal Pay Date.nl Stockholders Equity Dividende per Share Price Clee Annual Parent 16:41 | Natione Outstanding Common Shares Cash Data Bucal Dela Yeer Ticker