Excel Project 2 ? Estimation of Risk and Return

In order to understand the risk and return of the company you will estimate risk parameters and the cost of capital for the firm you have chosen for the Final Project.

Most publicly traded corporations are required to submit 10Q and 10K reports to the SEC detailing their financial operations over the previous quarter or year, respectively. Find the most recent 10K and 10Q and download the forms. Look on the Balance Sheet to find the book value of debt and the book value of equity. Find the section that lists a breakdown of company?s long-term debt.

1. Cost of Equity

In order to estimate the cost of equity, go to finance.yahoo.com or msn.money.com and find answers to the following questions:

What is the most recent stock price?

What is the market value of equity, or market capitalization?

How many shares of stock does the company have outstanding?

One measure of the risk or volatility of an individual stock is the standard deviation of the total return over several periods of time. Although the standard deviation is easy to compute, it does not take into account the extent to which the price of a given stock varies as a function of a market index, such as the S&P 500. As a result, we use another measure of risk ? beta.

Betas for individual stocks are determined by simple liner regression. The dependent variable is the total return for the stock and the independent variable is the total return for the stock market. We will use S&P 500 index as a measure of the total return for the stock market. The beta for the stock is the slope of the estimated regression equation (b1).

1. Run a regression of monthly returns on your firm's stock on monthly returns on a market index, using monthly data and 5 years of observations. What slope of the regression did you estimate and what does it tell you about the riskiness of the stock?

2. Using the beta that you have calculated, estimate the expected return on equity of the company. Go back to finance.yahoo.com and follow the ?Bonds? link. What is the yield on 3-month Treasury bills? Using a 7% market risk premium, what is the cost of equity for the company using CAPM?

CAPM: cost of equity r=risk free rate (rf) + ? * (rm? rf), where (rm? rf) is market risk premium

Use chapters 8 and 9 as references.

3. Go towww.reuters.comand find the list of competitors in the industry. Find the beta for each of these competitors, and then calculate the industry average beta. Using the industry average beta, what is the cost of equity? Does it matter if you use the beta for the company or the beta for the industry?

4. What are the book and market values of equity?

2. Cost of Debt

You now need to estimate default risk and cost of debt.

What is the most recent rating for the company?

What is the default spread and interest rate associated with this rating?

Go to Morningstar (or another financial website) and locate the information on bonds outstanding for your company. It can be a little tricky to navigate the website, but if you follow this link (I searched for AAPL), you can type in your company?s ticker symbol and see its bonds:

http://quicktake.morningstar.com/StockNet/bonds.aspx?Symbol=AAPL&Country=usa

Find the yield to maturity for each of the company?s bonds. What is the weighted average cost of debt for the company using the book value weights and the market value weights? Does it make the difference if you use the book value weights or market value weights?

Use chapters 8 and 9 as references.

3. Weighted Average Cost of Capital ? after tax WAAC

Calculate WACC for the company using market weights of debt and equity.

Do you think that the firm?s capital mix is fairly close to optimal or would you suggest any possible changes? Explain your thinking about this.

Use chapters 8, 9 and 19 as references.

Your groups will work on estimating WACC for the company you choose for Final Project

MCDONALDS CORP Consolidated Statement of Income In millions, except per share data REVENUES Sales by Company-operated restaurants Revenues from franchised restaurants Total revenues OPERATING COSTS AND EXPENSES Company-operated restaurant expenses Food & paper Payroll & employee benefits Occupancy & other operating expenses Franchised restaurants-occupancy expenses Selling, general & administrative expenses Other operating (income) expense, net Total operating costs and expenses Operating income Interest expense Nonoperating (income) expense, net Income before provision for income taxes Provision for income taxes Net income Earnings per common share-basic Earnings per common share-diluted Dividends declared per common share Weighted-average shares outstanding-basic Weighted-average shares outstanding-diluted Years ended December 31, 2015 2014 $ $ $ $ $ 16,488.30 $ 8,924.70 25,413.00 18,169.30 9,272.00 27,441.30 5,552.20 4,400.00 4,024.70 1,646.90 2,434.30 209.40 18,267.50 7,145.50 638.30 (48.50) 6,555.70 2,026.40 4,529.30 4.82 4.80 3.44 939.40 944.60 6,129.70 4,756.00 4,402.60 1,697.30 2,487.90 18.60 19,492.10 7,949.20 576.40 0.80 7,372.00 2,614.20 4,757.80 4.85 4.82 3.28 980.50 986.30 $ $ $ $ d December 31, 2013 $ $ $ $ $ 18,874.20 9,231.50 28,105.70 6,361.30 4,824.10 4,393.20 1,624.40 2,385.60 (247.20) 19,341.40 8,764.30 527.80 32.00 8,204.50 2,618.60 5,585.90 5.59 5.55 3.12 998.40 1,006.00 MCDONALDS CORP Consolidated Balance Sheet In millions, except per share data ASSETS Current assets Cash and equivalents Accounts and notes receivable Inventories, at cost, not in excess of market Prepaid expenses and other current assets Total current assets Investments in and advances to affiliates Goodwill Miscellaneous Total other assets Property and equipment, at cost Accumulated depreciation and amortization Net property and equipment Total assets LIABILITIES AND SHAREHOLDERS' EQUITY Current liabilities Accounts payable Income taxes Other taxes Accrued interest Accrued payroll and other liabilities Total current liabilities Long-term debt Other long-term liabilities Deferred income taxes Shareholders' equity Preferred stock, no par value; authorized - 165.0 million shares; issued - none Common stock, $.01 par value; authorized - 3.5 billion shares; issued - 1,660.6 million shares Additional paid-in capital Retained earnings Accumulated other comprehensive income Common stock in treasury, at cost; 753.8 and 697.7 million Total shareholders' equity Total liabilities and shareholders' equity 2015 2014 $ 7,685.50 $ 2,077.90 1,298.70 1,214.40 100.10 110.00 558.70 783.20 9,643.00 4,185.50 792.70 1,004.50 2,516.30 2,735.30 1,869.10 1,744.60 5,178.10 5,484.40 37,692.40 39,126.10 (14,574.80) (14,568.60) 23,117.60 24,557.50 $ 37,938.70 $ 34,227.40 $ 874.70 $ 860.10 154.80 166.80 309.00 330.00 233.10 233.70 1,378.80 1,157.30 2,950.40 2,747.90 24,122.10 14,935.70 2,074.00 2,065.90 1,704.30 1,624.50 0 0 16.60 16.60 6,533.40 6,239.10 44,594.50 43,294.50 (2,879.80) (1,519.70) (41,176.80) (35,177.10) 7,087.90 12,853.40 $ 37,938.70 $ 34,227.40 DOMINOS PIZZA INC Consolidated Statements of Income In thousands, except per share data Revenues: Domestic Company-owned stores Domestic franchise Supply chain International franchise Total revenues Cost of Sales: Domestic Company-owned stores Supply chain Total cost of sales Operating Margin General and Adninistrative Income from Operations Interest Income Interest Expense Income Before Provison of Income Taxes Provison for Income Taxes Net Income Earnings per Share Common Stock basic Common Stock diluted Dividend Declared per Share 2015 2014 2013 $396,916 272,808 1,383,161 163,643 2,216,528 $348,497 230,192 1,262,523 152,621 1,993,833 $337,414 212,369 1,118,873 133,567 1,802,223 299,294 1,234,103 1,533,397 683,131 277,692 405,439 313 (99,537) 306,215 113,426 $192,789 267,385 1,131,682 1,399,067 594,766 249,405 345,361 143 (86,881) 258,623 96,036 $162,587 256,596 996,653 1,253,249 548,974 235,163 313,811 160 (88,872) 225,099 82,114 $142,985 $3.58 $3.47 $1.24 $2.96 $2.86 $1.00 $2.58 $2.48 $0.80 DOMINOS PIZZA INC Consolidated Balance Sheets In thousands Assets Current Assets Cash and cash equivalents Restricted cash and cash equivalents Accounts receivable Inventories Prepaid expenses and other Advertising fund assets, restricted Deferred income taxes Asset held-for-sale Total current assets Property, Plant and Equipment, net Land and buildings Leasehold and other improvements Equipment Construction in progress Accumulated depreciation and amortization Property, plant and equipment, net Other Assets Investments in marketable securities Goodwill Capitalized software Other assets, net of accumulated amortization Deferred income taxes Total other assets Total assets Liabilities and Stockholder's Deficit Current Liabilities Current portion of long-term debt Accounts payable Accrued compensation Accrued interest Insurance reserves Dividends payable Advertising fund liabilities Other accrued liabilities Total current liabilities Long-term Liabilities Long-term debt, less current portion Insurance reserves Deferred income taxes Other accrued liabilities Total long-term liabilities Total liabilities 2015 2014 $133,449 180,940 131,582 36,861 20,646 99,159 0 0 602,637 $30,855 120,954 118,395 37,944 32,569 72,055 9,857 5,732 428,361 29,064 111,071 186,405 9,633 336,173 (204,283) 131,890 25,859 99,804 178,378 6,179 310,220 (196,174) 114,046 6,054 16,097 28,505 8,797 5,865 65,318 $799,845 4,586 16,297 20,562 10,006 2,475 53,926 $596,333 $59,333 106,927 32,999 20,459 17,597 557 99,159 38,952 375,983 $565 86,552 23,618 14,008 14,465 14,351 72,055 39,994 265,608 2,181,460 23,314 0 19,339 2,224,113 2,600,096 1,500,599 26,951 5,588 17,052 1,550,190 1,815,798 Commitment and Contingencies Stockholder's Deficit Common stock Additional paid-in capital Retained deficit Accumulated other comprehensive loss Total stockholders deficit Total liabilities and stockholders deficit 498 6,942 (1,804,143) (3,548) (1,800,251) $799,845 556 29,561 (1,246,921) (2,661) (1,219,465) $596,333 Performance Measures/Ratio McDonalds Domino's Pizza Performance Measures: Market Value Added (MVA) 196,166.68 5,543.98 8.49 42.04 (35,423.69) 7,752,706.49 Return on Capital (ROC) 0.20 1.61 Return on Assets (ROA) 12.55% 27.62% Return on Equity (ROE) 45.43% -12.77% Assets Turnover 0.70 3.18 Fixed Assets Turnover 1.07 18.03 148.73 41.00 2.45 8.90 Receivables Turnover 20.22 17.73 Average Collection period 18.05 20.58 Profit Margin 17.82% 8.70% Operating Profit Margin 28.12% 30.82% The Du Pont System 67.19% -0.12 Long Term Debt Ratio 0.64 2.73 Long Term Debt-Equity Ratio 3.40 -1.21 Total Debt Ratio 0.81 3.25 Times Interest Earned 11.27 -4.07 Cash Coverage Ratio 11.19 -4.07 NWC to Total Assets Ratio 0.18 0.28 Current Ratio 3.27 1.60 Quick Ratio 3.05 1.19 Cash Ratio 2.60 0.35 Market-to-book ratio Economic Value Added and Profitability Measures: EVA Measuring Efficiency - Asset Management or Turnover Measures: Inventory Turnover Average days in inventory Analyzing the return on Assets Measuring Financial Leverage - Long term Solvency Measures Measuring Liquidity - Short-Term Solvency or Liquidity Measures Growth measures: Payout Ratio 71.31% 34.49% Sustainable Growth 13.03% -8.36% Note: Please note that all the industry ratios and measures are not available. So some of the ratios and measur References: Industry Ratios. Retrieved from: http://csimarket.com McDonalds, Form 10-K. etrieved from: http://www.aboutmcdonalds.com/mcd/investors.html Domino's Pizza, Form 10-K. etrieved from: http://phx.corporate-ir.net/phoenix.zhtml?c=135383&p=irol-irhome Industry Average 8.33% 26.44% 1.105 42.7 8.55 25.83 14.13 7.69% 12.72% 0.2 23.84 1.16 0.45 34.39% o some of the ratios and measures are not available. ml?c=135383&p=irol-irhome and measures are not available.