Apple Inc WACC Project
The company chosen is Apple Inc (AAPL). Must be over AAPL!!!
1. Please adhere to directions on the attachment
2. No plagiarism, APA format
3. Need within 12 hrs or less
4. Only MBA worthy work; If you can not submit this level of work, do not send a bid!
Regression spreadsheet is already completed, and it will be attached. This will have the beta that must be used attached in a word document.
For the bonds portions, please see the below info.
For your WACC project you will need to locate bond data. Morningstar does not seem to work. You will have to do research to find alternative sites, such as Yahoo finance or the below link.
https://finra-markets.morningstar.com/BondCenter/Default.jsp?part=3
For the beta selection process, I have decided to average the Yahoo Finance and my regression beta. My next step was to drop the Value Line beta. I decided to drop the value line beta because the data lacked in similarity compared to the other two betas. The Yahoo Finance beta for 5 years monthly was 1.22, and my regression beta for 5 years monthly was 1.27 (“Apple Inc. (AAPL) Stock Price, News, Quote & History”, 2021). The Value Line beta was 1.05, but it did not specify how it was calculated. The website did not allow for a specific time frame to be entered in which causes the data in this article to be inadequate for the five years that I selected. The average of the Yahoo Finance beta and my regression beta is 1.25.
“Apple Inc. (AAPL) Stock Price, News, Quote & History.” Yahoo! Finance, Yahoo!, 14 Apr. 2021, finance.yahoo.com/quote/AAPL?p=AAPL&.tsrc=fin-srch.
Regression
Spreadsheet Correct
| Date | S&P Adj Close | Market Return | Apple’s Adj Close | Apple’s Return | ||||
| 1 | 3,225.52 | -0.0016 | 76.600517 | 0.0540 | ||||
| 1 | 2/1/19 | 3,230.78 | 0.0286 | 72.675339 | 0.1021 | |||
| 1 | 1/1/19 | 3,140.98 | 0.0340 | 65.943619 | 0.0743 | |||
| 10/1/19 | 3,037.56 | 0.0204 | 61.381241 | 0.1107 | ||||
| 9/1/19 | 2,976.74 | 0.0172 | 55.264339 | 0.0770 | ||||
| 8/1/19 | 2,926.46 | – | 0.0181 | 51.311405 | -0.0202 | |||
| 7/1/19 | 2,980.38 | 0.0131 | 52.368397 | 0.0764 | ||||
| 6/1/19 | 2,941.76 | 0.0689 | 48.65168 | 0.1349 | ||||
| 5/1/19 | 2,752.06 | -0.06 | 58 | 42.869724 | -0.1276 | |||
| 4/1/19 | 2,945.83 | 0.0393 | 49.138439 | 0.0564 | ||||
| 3/1/19 | 2,834.40 | 0.0179 | 46.513416 | 0.1017 | ||||
| 2,784.49 | 0.0297 | 42.218506 | 0.0403 | |||||
| 2,704.10 | 0.0787 | 40.582428 | 0.0552 | |||||
| 1 | 2/1/18 | 2,506.85 | -0.0918 | 38.46114 | -0.1136 | |||
| 1 | 1/1/18 | 2,760.17 | 43.391079 | -0.1840 | ||||
| 10/1/18 | 2,711.74 | -0.0694 | 53.178249 | -0.0305 | ||||
| 9/1/18 | 2,913.98 | 0.0043 | 54.849937 | – | 0.0048 | |||
| 8/1/18 | 2,901.52 | 0.0303 | 55.115868 | 0.1962 | ||||
| 7/1/18 | 2,816.29 | 0.0360 | 46.07476 | 0.0280 | ||||
| 6/1/18 | 2,718.37 | 44.820538 | -0.0056 | |||||
| 5/1/18 | 2,705.27 | 0.0216 | 45.072868 | 0.1308 | ||||
| 4/1/18 | 2,648.05 | 0.0027 | 39.860565 | -0.0150 | ||||
| 3/1/18 | 2,640.87 | -0.0269 | 40.468391 | -0.0542 | ||||
| 2,713.83 | -0.0389 | 42.78793 | 0.0638 | |||||
| 2,823.81 | 0.0562 | 40.21999 | -0.0106 | |||||
| 1 | 2/1/17 | 2,673.61 | 0.0098 | 40.652374 | -0.0117 | |||
| 1 | 1/1/17 | 2,647.58 | 0.0281 | 41.133884 | 0.0166 | |||
| 10/1/17 | 2, | 57 | 0.0222 | 40.461285 | 0.0968 | |||
| 9/1/17 | 2,519.36 | 0.0193 | 36.890041 | -0.0566 | ||||
| 8/1/17 | 2,471.65 | 0.0005 | 39.101368 | 0.1027 | ||||
| 7/1/17 | 2,470.30 | 35.460644 | 0.0327 | |||||
| 6/1/17 | 2,423.41 | 34.337669 | – | 0.0533 | ||||
| 5/1/17 | 2,411.80 | 0.0116 | 36.27177 | 0.0634 | ||||
| 4/1/17 | 2,384.20 | 0.0091 | 34.108673 | -0.0001 | ||||
| 3/1/17 | 2,362.72 | -0.0004 | 34.111042 | 0.0532 | ||||
| 2,363.64 | 0.0372 | 32.386887 | 0.1289 | |||||
| 2,278.87 | 28.689308 | 0.0477 | ||||||
| 1 | 2/1/16 | 2,238.83 | 0.0182 | 27.381918 | ||||
| 1 | 1/1/16 | 2,198.81 | 0.0342 | 25.995438 | -0.0266 | |||
| 10/1/16 | 2,126.15 | -0.0194 | 26.705769 | |||||
| 9/1/16 | 2,168.27 | -0.0012 | 26. | 59 | 0.0713 | |||
| 8/1/16 | 2,170.95 | 24.821346 | ||||||
| 7/1/16 | 2,173.60 | 0.0356 | 24.379189 | 0.0901 | ||||
| 6/1/16 | 2,098.86 | 0.0009 | 22.364946 | -0.0368 | ||||
| 5/1/16 | 2,096.95 | 0.0153 | 23.220165 | 0.0653 | ||||
| 4/1/16 | 2,065.30 | 21.7971 | -0.1399 | |||||
| 3/1/16 | 2,059.74 | 0.0660 | 25.343138 | 0.1333 | ||||
| 1,932.23 | -0.0041 | 22.361717 | -0.0067 | |||||
| 1,940.24 | -0.0507 | 22.512041 | -0.0752 | |||||
| 1 | 2/1/15 | 2,043.94 | -0.0175 | 24.343718 | -0.1064 | |||
| 1 | 1/1/15 | 2,080.41 | 27.242897 | -0.0100 | ||||
| 10/1/15 | 2,079.36 | 0.0830 | 27.519243 | 0.0834 | ||||
| 9/1/15 | 1,920.03 | -0.0264 | 25.4006 | -0.0174 | ||||
| 8/1/15 | 1,972.18 | -0.0626 | 25.850096 | -0.0704 | ||||
| 7/1/15 | 2,103.84 | 0.0197 | 27.807886 | -0.0329 | ||||
| 6/1/15 | 2,063.11 | -0.0210 | 28.754684 | -0.0332 | ||||
| 5/1/15 | 2,107.39 | 0.0105 | 29.742304 | 0.0410 | ||||
| 4/1/15 | 2,085.51 | 0.0085 | 28.571152 | 0.0058 | ||||
| 3/1/15 | 2,067.89 | 28.406776 | -0.0275 | |||||
| 2,104.50 | 0.0000 | 29.211523 | ||||||
| 1,994.99 | 26.641926 | |||||||
| SUMMARY OUTPUT | ||||||||
| Regression Statistics | ||||||||
| Multiple R | 0.5710297107 | |||||||
| R Square | 0.3260749305 | |||||||
| Adjusted R Square | 0.3142516836 | |||||||
| Standard Error | 0.0627218905 | |||||||
| Observations | ||||||||
| ANOVA | ||||||||
| df | SS | MS | F | Significance F | ||||
| 0.1084973009 | 27.5791358376 | 0.0000023268 | ||||||
| Residual | 0.2242400265 | 0.0039340356 | ||||||
| Total | 0.3327373274 | |||||||
| Coefficients | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | ||
| Intercept | 0.009320153 | 0.0083840238 | 1.1116563214 | 0.2709528938 | -0.007468565 | 0.0261088711 | ||
| X Variable 1 | 1.2743861531 | 0.2426669977 | 5.2515841265 | 0.7884538721 | 1.7603184341 | |||
| The equation is highly significant with a level of 0.0000023685 | ||||||||
| A significance level of .10 or lower is acceptable | ||||||||
| Apple’s beta is 1.274 with a signficance level of 0.0000023685 |
Sheet1
| Input Data (Millions Except Per Share Data) | Source | |||||||||||||||||||||||||||||||||||||||||||||
| Tax rate | 39% | |||||||||||||||||||||||||||||||||||||||||||||
| Debt (D) | $2,119,560,000.00 | Morningstar Bonds + 10K Note 5 | ||||||||||||||||||||||||||||||||||||||||||||
| Number of common shares (n) | 728,100,000 | Google Finance | ||||||||||||||||||||||||||||||||||||||||||||
| Stock price per share (P) | $12.81 | Wall Street Journal | ||||||||||||||||||||||||||||||||||||||||||||
| Capital Structure (Millions Except Per Share Data) | ||||||||||||||||||||||||||||||||||||||||||||||
| Market value of equity (S = P ´ n) | $9,326,961,000.00 | |||||||||||||||||||||||||||||||||||||||||||||
| Total value (V = D + S) | $11,446,521,000.00 | |||||||||||||||||||||||||||||||||||||||||||||
| Percent financed with debt (wd = D/V) | 18.5% | |||||||||||||||||||||||||||||||||||||||||||||
| Percent financed with stock (ws = S/V) | 81.5% | |||||||||||||||||||||||||||||||||||||||||||||
| Cost of Capital | ||||||||||||||||||||||||||||||||||||||||||||||
| Cost of debt | 3.26% | Morningstar Bonds Weighted cost of debt | ||||||||||||||||||||||||||||||||||||||||||||
| Beta (b) | 1.14 | Beta from 5 years data | ||||||||||||||||||||||||||||||||||||||||||||
| Risk-free rate (rRF) | 2.87% | 30 Year Treasury Yield, WSJ, 4/5/13 | ||||||||||||||||||||||||||||||||||||||||||||
| Market risk premium (RPM) | 6.54% | |||||||||||||||||||||||||||||||||||||||||||||
| Cost of equity from CAPM (rs = rRF + b ´ RPM ) | 10.31% | |||||||||||||||||||||||||||||||||||||||||||||
| Cost of Equity from Dividend Growth Model | ||||||||||||||||||||||||||||||||||||||||||||||
| Future Dividend Growth Rate | 10.60% | |||||||||||||||||||||||||||||||||||||||||||||
| 2012 Dividend $ | 0.0345 | |||||||||||||||||||||||||||||||||||||||||||||
| Share Price $ (4/5/13) | $ 12.81 | |||||||||||||||||||||||||||||||||||||||||||||
| 10.90% | ||||||||||||||||||||||||||||||||||||||||||||||
| Cost of Equity from Bond Plus Markup | ||||||||||||||||||||||||||||||||||||||||||||||
| Risk Markup | 7.20% | |||||||||||||||||||||||||||||||||||||||||||||
| 10.46% | ||||||||||||||||||||||||||||||||||||||||||||||
| Average rs | 10.6% | |||||||||||||||||||||||||||||||||||||||||||||
| WACC | 8.97% |
Sheet2
Sheet3
Sheet1
| Name | Maturity Date | Amount $(Mil) | Par Value | Number Bonds | Price | YTM | YTM as a decimal | Market Value $(Mil) | Weight | Weighted YTM | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Southwest Air 1995-A 7.22% | 7/1/13 | 43.3 | 0 | 1000 | 43300 | 100 | 0.00 | 7.20 | 0.072 | 0.03 | 0.0022 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Southwest Air 1996-A 7.67% | 1/2/14 | 33.30 | 33300 | 1005.00 | 6.87 | 0.06 | 33.47 | 0.02 | 0.0016 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Southwest Airls 5.25% | 10/1/14 | 350.00 | 350000 | 1060.00 | 1.20 | 0.012 | 37 | 1.00 | 0.26 | 0.0032 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Southwest Air 1994-A 9.15% | 7/1/16 | 86.30 | 86300 | 1026.00 | 7.70 | 0.077 | 88.54 | 0.0048 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Southwest Airls 5.75% | 12/15/16 | 300.00 | 300000 | 1136.00 | 1.87 | 0.019 | 340.80 | 0.24 | 0.0045 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Southwest Airls 5.125% | 3/1/17 | 1092.00 | 2.57 | 0.026 | 327.60 | 0.23 | 0.0060 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Southwest Air 1995-A 7.64% | 1/1/18 | 49.00 | 49000 | 897.00 | 10.55 | 0.10 | 43.95 | 0.0033 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Southwest Air 1996-A 8% | 1/2/21 | 33.10 | 33100 | 894.00 | 10.03 | 0.100 | 29.59 | 0.0021 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Southwest Airls 7.375% | 3/1/27 | 111.00 | 111000 | 1210.00 | 5.20 | 0.052 | 134.31 | 0.0049 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Mv Bonds | 1412.56 | 3.261% | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Mv Leases | 707.00 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| MVd | 2119.56 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| common shares | 728100000 | MVe | 9326.96 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| preferred shares | MVPF | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Common Price @4/5 (WSJ) | 12.81 | MVf | 11446.53 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| preferred price | Ws | 0.81 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Wd | 0.19 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| wpf |
J-MRP:
This is the weighted average rd
J-MRP:
376 + 331 Leases info from Note 5 Non Current Lease Related Obligations
and Airport Construction Obligation
Southwest Airls 5.25%
Southwest Airls 5.125%
Southwest Airls 5.75%
Southwest Airls 7.375%
Southwest Air 1994-A 9.15%
Southwest Air 1995-A 7.64%
Southwest Air 1995-A 7.22%
Southwest Air 1996-A 7.67%
Southwest Air 1996-A 8%
Sheet2
Sheet3
WACC Project
In this project, you will find and discern the appropriate data to determine a realistic assessment of the weighted average cost of capital for a firm of your choosing.
You will need to search for data from several sources, use subjective judgment to determine which data to use or discard, use subjective judgment to determine which calculation gives a more acceptable estimate and make some simplifying assumptions.
The purpose of the projects is to show some of the sources of measurement errors in financial analysis, to introduce the diverse sources of publicly available financial information and to develop skill in analysis in situations where there are too much or too little data.
Keep the following in mind when choosing a company:
· Publicly traded
· No utilities
· No financial firms
· No all equity firms
· No firms with large amounts of convertible securities or warrants
Organization of the paper should be as follows:
I. Title page
II. Table of Contents
III. Pages showing equations with data and brief description
There is no page length given for this as it can vary greatly. This section is to be divided up based on the topics. In each section, you must show and explain the equations that are used. In addition, you are to draw any conclusions on the company you can from this data. Please note that detailed worksheets showing all of the calculations for this section are to be included in an appendix.
· Cost of Equity (Common Stock)
· Beta from Regression and two Betas from analysts
· Beta Chosen for CAPM and why
· Capital Assets Pricing Model (include how determined RF and[ RM or (RM – RF)]
· Discounted Cash Flow (DCF) (only if dividends – include how determined)
· Own-Bond-Yield-plus-Judgmental-Risk-Premium (include how determine risk premium)
· Cost of Preferred Stock
· Cost of Debt (make sure to include table that lists all bond issues with weighted average cost of debt)
· Market Value of Debt (will have calculated above, but will need to add any long-term leases from balance sheet to get total market value of debt)
· Market Value of Equity
· Market Value of Preferred Stock
· Value of Firm
· Firm’s Tax Rate (explain how determined)
· Weight for Equity
· Weight for Preferred Stock
· Weight for Debt
· WACC (Weighted Average Cost of Capital)
IV. Assumptions
Including but not limited to RF , RM, RPM (which =Rm – RF), growth rate of dividends. This page should have a brief description of how you came up with the estimates with spreadsheets, etc. to be put in the appendix.
V. Appendix
Appendix should include all relevant data including debt data from Morningstar, calculations of weighted average cost of debt, stock returns, betas from analysts, beta regression analysis, method/sourcing for RF and RM, growth rates for dividends, different methods to determine tax rates, etc. Detailed descriptions, tables of data and excel sheets etc will be in the appendix.
VI. References
HELPFUL EQUATIONS
WACC = [(wE) x RE] + [(wPF) x RPF] + [(wD) x RD x (1- TC)]
Where:
Weights
(wE) = % of common equity in capital structure
(wPF) = % of preferred stock in capital structure
(wD) = % of debt in capital structure
Component costs
RE = firm’s cost of equity
RPF = firm’s cost of preferred stock
RD = firm’s cost of debt
TC = firm’s corporate tax rate
HELPFUL INFORMATION
Useful information will be found in the video, the sample Excel sheets, or your text’s tool kit Excel sheets. For instance:
Bonds
· You enter the ticker symbol about half way down on the Morningstar bond page which you accessed by clicking on bonds on the first page.
· You will then see all the bonds for the company, in this case, Southwest Airlines.
· Copy and paste the information into an excel spreadsheet. You may adapt the bond spreadsheet given to you, or show your calculations in a new spreadsheet or show in another document
Calculating the Weighted Average Cost of Debt
1. Find the market value of each bond issue. To do this find the number of bonds and then multiply by the price of the bond (remember that bond prices are quoted in 100s, but are really 1000s).
2. If there is no bond price, assume $1000 par is the price.
3. For these bonds, the YTM =coupon rate
4. Calculate the total market value of bonds
5. Calculate the weights for each bond issue as market value of bond issue/ Total market value of debt. Make sure your weights sum to one.
6. For each bond issue, multiply the weight by that issue’s YTM.
7. Sum the weighted YTMs, and you now have the weighted average RD,
Calculating the Weights for the WACC
1. MV of bonds has already been calculated. To that you will add in the value of leases from the Balance Sheet
2. For Preferred Stock, find the number of preferred shares in the annual report and the prices in the WSJ Market Center
3. For common equity, find the price and number of shares in Yahoo Finance.
4. Can use example found in bottom half of bond worksheet or develop your own.
Calculating the Required Return on Preferred Stock
1. To Calculate RPF, we use the constant dividend model, ie. the perpetuity model.
2. RPF = Dividend/P0
3. Check in your company’s annual report to see if they have preferred stock and what the dividend is. Make sure you use the yearly dividend since we are calculating annual returns.
· Prices can be found in the WSJ Market Center:
http://online.wsj.com/mdc/public/page/2_3024-
HYPERLINK “http://online.wsj.com/mdc/public/page/2_3024-Preferreds.html” \t “_parent” Preferreds.html
Calculating the Required Return on Common Stock
CAPM
You must now decide what value you will use for RF and either RM or RPM where
· CAPM: RE = RRF + (RM – RRF)b
= RRF + (RPM)b.
Again, You must justify your choices for RF and either RM or RPM and provide citations in the body of your report and references at the end.
The Own-Bond-Yield-Plus-Judgmental-Risk-Premium Method
· RE = RD + Judgmental risk premium
· This judgmental-risk premium ( the CAPM equity risk premium, RPM
· Produces ballpark estimate of RE
· Useful check, particularly if Dividend Growth and CAPM are significantly different
· Again, you must justify your answer
Dividend Growth Model
· RE = {[D0* (1+g)] /P0 } + g
· The challenge here is to estimate the growth rate. There are several suggestions in your text as well as in earlier videos.
Corporate Tax Rate
· You may be able to find the corporate tax rate directly in the annual report
· Or you can choose to use the formula
· Taxes = Tax Rate * Earnings Before Taxes
· Since both Taxes and Earnings before Taxes are in the Balance Sheet, you can then calculate the tax rate
Reference
McDaniel, William (1997). The Cost of Capital Project Journal of Financial Education,
Fall 1997. p. 57-66.