Week 5 Project Company Stock and Cost of Capital Analysis In this assignment you will Perform a detailed stock price analysis on your company's stock, and Calculate and analyze the company's Weighted Average Cost of Capital (WACC) 1 Stock Price Analysis a Monthly Stock Price Analysis In Yahoo Finance go to the Historical Prices page and import into Excel the last 5 years of monthly stock prices (use the ending date as of the end of the previous month) For the below activities use the adjusting closed price ( Adj Close ) What is the current stock price What were the highest and lowest stock price values during this period For the remaining items in this section you will analyze the Year Over Year returns In your spreadsheet, calculate the Year Over Year (YoY) return for each month (Please submit this worksheet in Excel with your written paper ) The calculation for this is (month same month prior year) (same month prior year) What were the highest and lowest YoY returns during this period Calculate the mean or average of these YoY returns from your sample Calculate the standard deviation of the YoY returns (In Excel, use the STDEV function ) b Graphical View of Variability In Yahoo Finance go to the Basic Chart for your company Select the 5 year ( 5Y ) Range of the chart In the Compare line select the S P 500 and then press the Compare button Copy and paste this chart into this section of your paper Interpret your observations Does the general movement of your company's returns follow that of the S P 500 Is the company's stock price today higher than the company's stock price at the beginning of this 5 year period 2 Analysis of WACC a Calculation of WACC for your company You will need to identify each component of the WACC formula and calculate the overall WACC The WACC formula for a company that uses debt and equity is as follows WACC Debt Cost of Debt (1 Tax Rate) Equity Cost of Equity You should use Yahoo Finance and or the company's most recent 10K report to identify all financial statement inputs You can use the following guide for the inputs The specific financial statement data are found on the relevant financial statement Debt Long term Debt Short term Debt (on Yahoo this is called, Short Current Long Term Debt ) Equity Market Cap (This is on the Key Statistics page in Yahoo Finance) Debt Debt (Debt Equity) Equity 1 Debt Cost of Debt Interest Expense Debt Tax Rate Income Tax Expense Income Before Tax Cost of Equity Use the CAPM equation to calculate this Cost of Equity Risk free rate Beta (Market Risk Premium) Risk free rate look up the yield on 10 year US Treasury bonds Beta This is on the Key Statistics page in Yahoo Finance Market Risk Premium Assume 11 minus the risk free rate b Interpretation of WACC for Your Company Indicate what the WACC value you derived means for your company What role does the WACC play for company managers when they are evaluating new projects to undertake How would company managers and investors use the WACC for an overall company valuation analysis Bank of America https finance yahoo com quote BAC history p BAC https finance yahoo com quote BAC history period1 1333083600 period2 1493528400 interval 1mo filter history frequency 1mo WACC http investor bankofamerica com phoenix zhtml c 71595 p irol sec fbid NX MxixBMS8 hashlink 14228436 Valuation Measures Market Cap (intraday) 5 232 8B Enterprise Value 3 98 09B Trailing P E 15 59 Forward P E 1 10 91 PEG Ratio (5 yr expected) 1 1 23 Price Sales (ttm) 2 85 Price Book (mrq) 0 96 Enterprise Value Revenue 3 1 20 Enterprise Value EBITDA 6 N A Financial Highlights Fiscal Year Fiscal Year Ends Dec 31, 2016 Most Recent Quarter (mrq) Mar 31, 2017 Profitability Profit Margin 23 60 Operating Margin (ttm) 35 05 Management Effectiveness Return on Assets (ttm) 0 87 Return on Equity (ttm) 7 27 Income Statement Revenue (ttm) 81 72B Revenue Per Share (ttm) 8 00 Quarterly Revenue Growth (yoy) 15 70 Gross Profit (ttm) N A EBITDA N A Net Income Avi to Common (ttm) 17 56B Diluted EPS (ttm) 1 63 Quarterly Earnings Growth (yoy) 81 20 Balance Sheet Total Cash (mrq) 639 85B Total Cash Per Share (mrq) 60 84 Total Debt (mrq) 488 07B Total Debt Equity (mrq) N A Current Ratio (mrq) N A Book Value Per Share (mrq) 24 36 Cash Flow Statement Operating Cash Flow (ttm) N A Levered Free Cash Flow (ttm) N A Trading Information Stock Price History Beta 1 60 52 Week Change 3 60 12 S P500 52 Week Change 3 14 77 52 Week High 3 25 80 52 Week Low 3 12 05 50 Day Moving Average 3 23 53 200 Day Moving Average 3 21 99 Share Statistics Avg Vol (3 month) 3 98 56M Avg Vol (10 day) 3 106 48M Shares Outstanding 5 9 97B Float 10 51B Held by Insiders 1 0 06 Held by Institutions 1 63 20 Shares Short 3 107 78M Short Ratio 3 1 19 Short of Float 3 1 08 Shares Short (prior month) 3 103 45M Dividends Splits Forward Annual Dividend Rate 4 0 3 Forward Annual Dividend Yield 4 1 26 Trailing Annual Dividend Rate 3 0 25 Trailing Annual Dividend Yield 3 1 06 5 Year Average Dividend Yield 4 0 74 Payout Ratio 4 16 67 Dividend Date 3 Mar 31, 2017 Ex Dividend Date 4 Mar 1, 2017 Last Split Factor (new per old) 2 2 1 Last Split Date 3 Aug 30, 2004 Date Open High Low Close Adj Close Volume Apr 28, 2017 23 57 23 72 23 32 23 34 23 34 69,384,908 Apr 01, 2017 23 65 24 35 22 26 23 34 23 34 1,824,040,300 Mar 01, 2017 0 075 Dividend Mar 01, 2017 25 37 25 80 22 16 23 59 23 59 2,336,311,300 Feb 01, 2017 22 97 24 95 22 45 24 68 24 68 1,866,619,200 Jan 01, 2017 22 60 23 55 22 01 22 64 22 64 1,917,201,500 Dec 01, 2016 21 42 23 39 21 02 23 09 23 09 2,754,257,500 Nov 30, 2016 0 075 Dividend Nov 01, 2016 16 56 21 19 16 28 21 12 21 12 3,121,614,500 Oct 01, 2016 15 59 17 10 15 50 16 50 16 50 1,723,132,300 Sep 01, 2016 16 15 16 15 14 81 15 65 15 65 1,825,500,700 Aug 31, 2016 0 075 Dividend Aug 01, 2016 14 52 16 24 14 09 16 14 16 14 1,813,474,400 Jul 01, 2016 13 19 14 70 12 45 14 49 14 49 1,701,679,800 Jun 01, 2016 0 05 Dividend Jun 01, 2016 14 60 14 98 12 05 13 27 13 27 2,285,514,000 May 01, 2016 14 58 15 15 13 80 14 79 14 79 1,737,521,100 Apr 01, 2016 13 47 15 30 12 75 14 56 14 56 2,022,233,400 Mar 02, 2016 0 05 Dividend Mar 01, 2016 12 64 14 03 12 62 13 52 13 52 2,415,683,100 Feb 01, 2016 14 05 14 09 10 99 12 52 12 52 3,571,160,300 Jan 01, 2016 16 45 16 59 12 94 14 14 14 14 2,647,247,500 Dec 02, 2015 0 05 Dividend Dec 01, 2015 17 52 17 89 16 50 16 83 16 83 1,770,846,500 Nov 01, 2015 16 90 18 09 16 87 17 43 17 43 1,439,390,500 Oct 01, 2015 15 52 17 44 14 63 16 78 16 78 1,848,489,300 Sep 02, 2015 0 05 Dividend Sep 01, 2015 15 95 16 48 15 25 15 58 15 58 1,777,912,500 Aug 01, 2015 17 91 18 07 14 60 16 44 16 44 2,325,197,900 Jul 01, 2015 17 25 18 48 16 22 17 88 17 88 1,903,543,000 Jun 03, 2015 0 05 Dividend Jun 01, 2015 16 58 17 72 16 47 17 02 17 02 1,659,678,900 May 01, 2015 16 00 16 85 15 92 16 50 16 50 1,403,264,600 Apr 01, 2015 15 42 16 05 15 25 15 93 15 93 1,464,221,900 Mar 04, 2015 0 05 Dividend Mar 01, 2015 15 79 16 62 15 26 15 39 15 39 1,855,236,900 Feb 01, 2015 15 27 16 79 15 12 15 81 15 81 1,910,757,200 Jan 01, 2015 17 99 18 03 14 97 15 15 15 15 2,210,324,100 Dec 03, 2014 0 05 Dividend Dec 01, 2014 16 92 18 21 16 59 17 89 17 89 1,712,060,700 Nov 01, 2014 17 18 17 46 16 83 17 04 17 04 942,013,300 Oct 01, 2014 17 07 17 41 15 43 17 16 17 16 2,208,123,000 Sep 03, 2014 0 05 Dividend Sep 01, 2014 16 14 17 20 15 90 17 05 17 05 1,789,337,700 Aug 01, 2014 15 18 16 46 14 84 16 09 16 09 1,445,234,500 Jul 01, 2014 15 38 16 23 15 13 15 25 15 25 1,499,782,500 Jun 20, 2014 0 01 Dividend Jun 01, 2014 15 16 15 94 15 05 15 37 15 37 1,256,220,000 May 01, 2014 15 14 15 31 14 37 15 14 15 14 1,462,446,400 Apr 01, 2014 17 28 17 40 14 86 15 14 15 14 2,245,029,800 Mar 05, 2014 0 01 Dividend Mar 01, 2014 16 30 18 03 16 19 17 20 17 20 2,337,288,800 Feb 01, 2014 16 77 16 92 16 13 16 53 16 53 2,055,235,400 Jan 01, 2014 15 69 17 42 15 68 16 75 16 75 2,581,396,100 Dec 04, 2013 0 01 Dividend Dec 01, 2013 15 84 15 97 15 06 15 57 15 57 1,661,429,900 Nov 01, 2013 14 00 15 98 13 80 15 82 15 82 2,059,558,200 Oct 01, 2013 13 85 14 72 13 68 13 97 13 97 2,047,949,700 Sep 04, 2013 0 01 Dividend Sep 01, 2013 14 30 14 83 13 60 13 80 13 80 1,715,010,800 Aug 01, 2013 14 85 14 97 13 98 14 12 14 12 1,933,732,700 Jul 01, 2013 12 95 15 03 12 73 14 60 14 60 2,461,055,900 Jun 05, 2013 0 01 Dividend Jun 01, 2013 13 69 13 73 12 13 12 86 12 86 2,590,843,100 May 01, 2013 12 20 13 99 12 05 13 66 13 66 2,929,147,100 Apr 01, 2013 12 15 12 54 11 23 12 31 12 31 2,917,832,000 Mar 01, 2013 11 13 12 94 11 02 12 18 12 18 3,198,847,600 Feb 27, 2013 0 01 Dividend Feb 01, 2013 11 41 12 42 10 98 11 23 11 23 3,261,268,700 Jan 01, 2013 12 05 12 20 11 02 11 32 11 32 3,342,902,200 Dec 05, 2012 0 01 Dividend Dec 01, 2012 9 93 11 69 9 77 11 61 11 61 3,505,653,600 Nov 01, 2012 9 34 9 97 8 92 9 86 9 86 3,166,055,300 Oct 01, 2012 8 87 9 65 8 85 9 32 9 32 2,981,868,800 Sep 05, 2012 0 01 Dividend Sep 01, 2012 8 00 9 79 7 93 8 83 8 83 3,169,072,100 Aug 01, 2012 7 35 8 40 7 10 7 99 7 99 2,311,106,700 Jul 01, 2012 8 20 8 21 6 90 7 34 7 34 2,814,377,800 Jun 01, 2012 7 12 8 22 6 85 8 18 8 18 4,061,071,900 May 30, 2012 0 01 Dividend May 01, 2012 8 11 8 40 6 72 7 35 7 35 4,223,024,800 Apr 01, 2012 9 54 9 78 7 95 8 11 8 11 4,327,576,60

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 31, 2024

Week 5 Project - Company Stock and Cost of Capital Analysis In this assignment you will Perform a detailed stock price analysis on your company's

Week 5 Project - Company Stock and Cost of Capital Analysis In this assignment you will Perform a detailed stock price analysis on your company's stock, and Calculate and analyze the company's Weighted Average Cost of Capital (WACC) 1. Stock Price Analysis: a. Monthly Stock Price Analysis: In Yahoo! Finance go to the Historical Prices page and import into Excel the last 5 years of monthly stock prices (use the ending date as of the end of the previous month). For the below activities use the adjusting closed price ("Adj Close") What is the current stock price? What were the highest and lowest stock price values during this period? For the remaining items in this section you will analyze the Year-Over-Year returns. In your spreadsheet, calculate the Year-Over-Year (YoY) return for each month. (Please submit this worksheet in Excel with your written paper.) The calculation for this is: (month - same month prior year) / (same month prior year) What were the highest and lowest YoY returns during this period? Calculate the mean or average of these YoY returns from your sample. Calculate the standard deviation of the YoY returns. (In Excel, use the STDEV function.) b. Graphical View of Variability: In Yahoo! Finance go to the Basic Chart for your company. Select the 5 year ("5Y") Range of the chart. In the Compare line select the S&P 500 and then press the "Compare" button. Copy and paste this chart into this section of your paper. Interpret your observations. Does the general movement of your company's returns follow that of the S&P 500? Is the company's stock price today higher than the company's stock price at the beginning of this 5 year period? 2. Analysis of WACC a. Calculation of WACC for your company You will need to identify each component of the WACC formula and calculate the overall WACC. The WACC formula for a company that uses debt and equity is as follows: WACC = % Debt * Cost of Debt * (1 - Tax Rate) + % Equity * Cost of Equity You should use Yahoo! Finance and / or the company's most recent 10K report to identify all financial statement inputs. You can use the following guide for the inputs. The specific financial statement data are found on the relevant financial statement. Debt = Long-term Debt + Short-term Debt (on Yahoo! this is called, "Short/Current Long Term Debt") Equity = Market Cap. (This is on the Key Statistics page in Yahoo! Finance) % Debt = Debt / (Debt + Equity) % Equity = 1 - % Debt Cost of Debt = Interest Expense / Debt Tax Rate = Income Tax Expense / Income Before Tax Cost of Equity: Use the CAPM equation to calculate this Cost of Equity = Risk free rate + Beta * (Market Risk Premium) Risk free rate: look up the yield on 10 year US Treasury bonds Beta: This is on the Key Statistics page in Yahoo! Finance Market Risk Premium: Assume 11% minus the risk-free rate b. Interpretation of WACC for Your Company Indicate what the WACC value you derived means for your company. What role does the WACC play for company managers when they are evaluating new projects to undertake? How would company managers and investors use the WACC for an overall company valuation analysis?

Bank of America https://finance.yahoo.com/quote/BAC/history?p=BAC

https://finance.yahoo.com/quote/BAC/history?period1=1333083600&period2=1493528400&interval=1mo&filter=history&frequency=1mo

WACC = http://investor.bankofamerica.com/phoenix.zhtml?c=71595&p=irol-sec#fbid=NX_MxixBMS8/hashlink=14228436

Valuation Measures

Market Cap (intraday) ⁵	232.8B
Enterprise Value ³	98.09B
Trailing P/E	15.59
Forward P/E ¹	10.91
PEG Ratio (5 yr expected) ¹	1.23
Price/Sales (ttm)	2.85
Price/Book (mrq)	0.96
Enterprise Value/Revenue ³	1.20
Enterprise Value/EBITDA ⁶	N/A

Financial Highlights

Fiscal Year

Fiscal Year Ends	Dec 31, 2016
Most Recent Quarter (mrq)	Mar 31, 2017

Profitability

Profit Margin	23.60%
Operating Margin (ttm)	35.05%

Management Effectiveness

Return on Assets (ttm)	0.87%
Return on Equity (ttm)	7.27%

Income Statement

Revenue (ttm)	81.72B
Revenue Per Share (ttm)	8.00
Quarterly Revenue Growth (yoy)	15.70%
Gross Profit (ttm)	N/A
EBITDA	N/A
Net Income Avi to Common (ttm)	17.56B
Diluted EPS (ttm)	1.63
Quarterly Earnings Growth (yoy)	81.20%

Balance Sheet

Total Cash (mrq)	639.85B
Total Cash Per Share (mrq)	60.84
Total Debt (mrq)	488.07B
Total Debt/Equity (mrq)	N/A
Current Ratio (mrq)	N/A
Book Value Per Share (mrq)	24.36

Cash Flow Statement

Operating Cash Flow (ttm)	N/A
Levered Free Cash Flow (ttm)	N/A

Trading Information

Stock Price History

Beta	1.60
52-Week Change ³	60.12%
S&P500 52-Week Change ³	14.77%
52 Week High ³	25.80
52 Week Low ³	12.05
50-Day Moving Average ³	23.53
200-Day Moving Average ³	21.99

Share Statistics

Avg Vol (3 month) ³	98.56M
Avg Vol (10 day) ³	106.48M
Shares Outstanding ⁵	9.97B
Float	10.51B
% Held by Insiders ¹	0.06%
% Held by Institutions ¹	63.20%
Shares Short ³	107.78M
Short Ratio ³	1.19
Short % of Float ³	1.08%
Shares Short (prior month) ³	103.45M

Dividends & Splits

Forward Annual Dividend Rate ⁴	0.3
Forward Annual Dividend Yield ⁴	1.26%
Trailing Annual Dividend Rate ³	0.25
Trailing Annual Dividend Yield ³	1.06%
5 Year Average Dividend Yield ⁴	0.74
Payout Ratio ⁴	16.67%
Dividend Date ³	Mar 31, 2017
Ex-Dividend Date ⁴	Mar 1, 2017
Last Split Factor (new per old) ²	2/1
Last Split Date ³	Aug 30, 2004

Date

	Open	High	Low	Close	Adj Close*	Volume
Apr 28, 2017	23.57	23.72	23.32	23.34	23.34	69,384,908
Apr 01, 2017	23.65	24.35	22.26	23.34	23.34	1,824,040,300
Mar 01, 2017	0.075 Dividend
Mar 01, 2017	25.37	25.80	22.16	23.59	23.59	2,336,311,300
Feb 01, 2017	22.97	24.95	22.45	24.68	24.68	1,866,619,200
Jan 01, 2017	22.60	23.55	22.01	22.64	22.64	1,917,201,500
Dec 01, 2016	21.42	23.39	21.02	23.09	23.09	2,754,257,500
Nov 30, 2016	0.075 Dividend
Nov 01, 2016	16.56	21.19	16.28	21.12	21.12	3,121,614,500
Oct 01, 2016	15.59	17.10	15.50	16.50	16.50	1,723,132,300
Sep 01, 2016	16.15	16.15	14.81	15.65	15.65	1,825,500,700
Aug 31, 2016	0.075 Dividend
Aug 01, 2016	14.52	16.24	14.09	16.14	16.14	1,813,474,400
Jul 01, 2016	13.19	14.70	12.45	14.49	14.49	1,701,679,800
Jun 01, 2016	0.05 Dividend
Jun 01, 2016	14.60	14.98	12.05	13.27	13.27	2,285,514,000
May 01, 2016	14.58	15.15	13.80	14.79	14.79	1,737,521,100
Apr 01, 2016	13.47	15.30	12.75	14.56	14.56	2,022,233,400
Mar 02, 2016	0.05 Dividend
Mar 01, 2016	12.64	14.03	12.62	13.52	13.52	2,415,683,100
Feb 01, 2016	14.05	14.09	10.99	12.52	12.52	3,571,160,300
Jan 01, 2016	16.45	16.59	12.94	14.14	14.14	2,647,247,500
Dec 02, 2015	0.05 Dividend
Dec 01, 2015	17.52	17.89	16.50	16.83	16.83	1,770,846,500
Nov 01, 2015	16.90	18.09	16.87	17.43	17.43	1,439,390,500
Oct 01, 2015	15.52	17.44	14.63	16.78	16.78	1,848,489,300
Sep 02, 2015	0.05 Dividend
Sep 01, 2015	15.95	16.48	15.25	15.58	15.58	1,777,912,500
Aug 01, 2015	17.91	18.07	14.60	16.44	16.44	2,325,197,900
Jul 01, 2015	17.25	18.48	16.22	17.88	17.88	1,903,543,000
Jun 03, 2015	0.05 Dividend
Jun 01, 2015	16.58	17.72	16.47	17.02	17.02	1,659,678,900
May 01, 2015	16.00	16.85	15.92	16.50	16.50	1,403,264,600
Apr 01, 2015	15.42	16.05	15.25	15.93	15.93	1,464,221,900
Mar 04, 2015	0.05 Dividend
Mar 01, 2015	15.79	16.62	15.26	15.39	15.39	1,855,236,900
Feb 01, 2015	15.27	16.79	15.12	15.81	15.81	1,910,757,200
Jan 01, 2015	17.99	18.03	14.97	15.15	15.15	2,210,324,100
Dec 03, 2014	0.05 Dividend
Dec 01, 2014	16.92	18.21	16.59	17.89	17.89	1,712,060,700
Nov 01, 2014	17.18	17.46	16.83	17.04	17.04	942,013,300
Oct 01, 2014	17.07	17.41	15.43	17.16	17.16	2,208,123,000
Sep 03, 2014	0.05 Dividend
Sep 01, 2014	16.14	17.20	15.90	17.05	17.05	1,789,337,700
Aug 01, 2014	15.18	16.46	14.84	16.09	16.09	1,445,234,500
Jul 01, 2014	15.38	16.23	15.13	15.25	15.25	1,499,782,500
Jun 20, 2014	0.01 Dividend
Jun 01, 2014	15.16	15.94	15.05	15.37	15.37	1,256,220,000
May 01, 2014	15.14	15.31	14.37	15.14	15.14	1,462,446,400
Apr 01, 2014	17.28	17.40	14.86	15.14	15.14	2,245,029,800
Mar 05, 2014	0.01 Dividend
Mar 01, 2014	16.30	18.03	16.19	17.20	17.20	2,337,288,800
Feb 01, 2014	16.77	16.92	16.13	16.53	16.53	2,055,235,400
Jan 01, 2014	15.69	17.42	15.68	16.75	16.75	2,581,396,100
Dec 04, 2013	0.01 Dividend
Dec 01, 2013	15.84	15.97	15.06	15.57	15.57	1,661,429,900
Nov 01, 2013	14.00	15.98	13.80	15.82	15.82	2,059,558,200
Oct 01, 2013	13.85	14.72	13.68	13.97	13.97	2,047,949,700
Sep 04, 2013	0.01 Dividend
Sep 01, 2013	14.30	14.83	13.60	13.80	13.80	1,715,010,800
Aug 01, 2013	14.85	14.97	13.98	14.12	14.12	1,933,732,700
Jul 01, 2013	12.95	15.03	12.73	14.60	14.60	2,461,055,900
Jun 05, 2013	0.01 Dividend
Jun 01, 2013	13.69	13.73	12.13	12.86	12.86	2,590,843,100
May 01, 2013	12.20	13.99	12.05	13.66	13.66	2,929,147,100
Apr 01, 2013	12.15	12.54	11.23	12.31	12.31	2,917,832,000
Mar 01, 2013	11.13	12.94	11.02	12.18	12.18	3,198,847,600
Feb 27, 2013	0.01 Dividend
Feb 01, 2013	11.41	12.42	10.98	11.23	11.23	3,261,268,700
Jan 01, 2013	12.05	12.20	11.02	11.32	11.32	3,342,902,200
Dec 05, 2012	0.01 Dividend
Dec 01, 2012	9.93	11.69	9.77	11.61	11.61	3,505,653,600
Nov 01, 2012	9.34	9.97	8.92	9.86	9.86	3,166,055,300
Oct 01, 2012	8.87	9.65	8.85	9.32	9.32	2,981,868,800
Sep 05, 2012	0.01 Dividend
Sep 01, 2012	8.00	9.79	7.93	8.83	8.83	3,169,072,100
Aug 01, 2012	7.35	8.40	7.10	7.99	7.99	2,311,106,700
Jul 01, 2012	8.20	8.21	6.90	7.34	7.34	2,814,377,800
Jun 01, 2012	7.12	8.22	6.85	8.18	8.18	4,061,071,900
May 30, 2012	0.01 Dividend
May 01, 2012	8.11	8.40	6.72	7.35	7.35	4,223,024,800
Apr 01, 2012	9.54	9.78	7.95	8.11	8.11	4,327,576,60