Calculation of Weighted Average Cost of Capital (WACC) for 2016, 2017

User Generated

Ivfvoyr

Business Finance

Description

Please calculate WACC for Starbucks based on the attached data . WACC calculation instructions including the formula as attached.

Please calculate WACC for Starbucks based on the attached data . WACC calculation instructions including the formula as attached.

Please calculate WACC for Starbucks based on the attached data . WACC calculation instructions including the formula as attached.

Unformatted Attachment Preview

Corporate Finance WACC estimation Calculation of Weighted Average Cost of Capital (WACC) The Cost of Capital. The WACC formula discussed below does not include Preferred Stock (PS). Should your company have PS, be sure to adjust the equation for it, and see the section in the chapter 13 on the Cost of Preferred Stock. The WACC formula that we use is: WACC = Ws *Rs + Wd*Rd*(1-T) or We need to know how to calculate: 1. The weights (Wd and Ws )– note that: Wd + Ws = 1; so you only have to calculate one of them). We need to calculate the weight of debt and the weight of equity (for the cost of debt, this simply means: what proportion of the firm’s financing is by debt?). we will use the formula: Wd = Book Value of Debt / [Market Value of Equity + Book Value of Debt] The book value of debt is calculated by adding up ALL of the debt on the balance sheet. This will typically be the sum of Notes Payable, Current Portion of LT Debt and Long-Term Debt (Interest-bearing debt). The market value of equity is the “Market Cap” and equals the number of (common) shares outstanding multiplied by the price/share. Note that the “timing” of this value should coincide with the book value of debt. For example, if you calculate the book value of debt as of 12/31/17, then the market cap should also be calculated for that date. Be very careful about using the reported Market Cap on Schlumberger-Private Yahoo.finance – it may not have the same “timing.” 2. Rd: the cost of debt. There may be more than one acceptable approach to calculate or estimate a company’s cost of debt. One relatively straightforward method is to discover the company’s debt rating. This can usually be found on the company’s 10K and doing a word search for ‘rating’ or ‘debt rating.’ If you can find the debt rating for your company then you can carry out the following steps. If you are not able to find a bond rating readily, you can register (for free) at Moody's to find company ratings. If you cannot find a bond rating for your company, you might try to estimate/guess what it is by considering your company’s beta and comparing the bond ratings for companies with similar betas. Once you have the actual bond rating or an estimate you can then find or estimate your company’s cost of debt by first going to Federal reserve St. Louis https://fred.stlouisfed.org/categories/32348 to find historical Corporate bond yields based on the credit rating of your company. For example, if the Aaa yield is 5.00%, the Aa1 yield is 5.15% and the Aa2 yield is 5.30%, you can see a pattern (equation): for every increase in risk (from Aaa to Aa1), there is a 0.15% increase in the yield. If your company has an A1 rating, then it is two steps “below” the Aa2 rating, so you should add approximately 0.30% more to the 5.30% for the A1 rating, giving you a cost of debt for your company of about 5.60%. Note that this approach assumes a linear equation for the cost of debt (which may not be strictly true). To make it easy for you, for this project, you can just assume Aaa yield is 5.0% with a 0.15% increase in yield for every increase in risk (e.g., from Aaa to Aa1, Aa1 to Aa2, and so on). 3. The corporate tax rate (T or Tc). You may want to calculate several/many historical effective tax rates for you company in this project. The effective tax rate is the actual taxes paid divided by earnings before taxes (on the income statement). You can calculate/consider these rates for the past 5-10 years and determine the effective tax rate you want to use in your pro forma. Then you adjust the rate to reflect the recent tax reform for future rate. 4. Rs (or REquity) the cost of common equity. Use the Security Market Line (SML) – this is why you learn how to calculate a company’s beta and also why Schlumberger-Private you learn how to find the appropriate risk-free rate and market-risk premium. The model is also called Capital Asset Pricing Model (CAPM). Schlumberger-Private You can use CAPM model to predict the cost of common stock. The equation runs as followings: Rs = RRF + (RPM)*β • β- find it from Yahoo Finance- Alternatively, you can also use historical stock returns to estimate the beta • RRF, please check the website or use 3.5%: https://www.federalreserve.gov/datadownload/Choose.aspx?rel=H15 • RPM, please use 6.5% for expected market premium for this project. Schlumberger-Private Year Div/Share LN(DIV) 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 0.71 0.73 0.76 0.79 0.81 0.83 0.86 0.90 0.93 0.96 1.00 -0.347 -0.308 -0.274 -0.235 -0.214 -0.184 -0.147 -0.105 -0.070 -0.041 0.000 Annual Div Growth Sales (in millions) Annual Sales Growth LnSales $ 2,924 3.9% $ 3,171 8.45% 3.5% $ 3,398 7.15% 4.0% $ 3,611 6.26% 2.1% $ 3,804 5.34% 3.1% $ 4,012 5.47% 3.7% $ 4,210 4.92% 4.3% $ 4,428 5.19% 3.5% $ 4,612 4.16% 3.0% $ 4,760 3.20% 4.2% $ 5,000 5.04% 7.98 8.06 8.13 8.19 8.24 8.30 8.35 8.40 8.44 8.47 8.52 Loglinear regression ( Dependent variable Log Sales revenue) SUMMARY OUTPUT Regression Statistics Multiple R R Square Adjusted R Square Standard Error Observations 0.99 0.99 0.99 0.02 11.00 ANOVA df Regression Residual Total SS 1.00 9.00 10.00 Coefficients Intercept X Variable 1 MS 0.30 0.00 0.30 Standard Error -96.56 3.88 0.05 0.00 0.30 0.00 t Stat -24.86 26.99 F 728.41 P-value 0.00 0.00 Significance F 0.00 Lower 95% Upper 95% Lower 95.0% -105.34 -87.77 -105.34 0.05 0.06 0.05 rate of the coefficient; then substract 1.00 5.35% = ANNUAL Growth RATE of GROWTH in Revenue be sure to look at the Excel function in the cell! Linear Regression for Dividend SUMMARY OUTPUT Regression Statistics Multiple R R Square Adjusted R Square Standard Error Observations 1.00 1.00 1.00 0.01 11.00 ANOVA df Regression Residual Total SS 1.00 9.00 10.00 Coefficients MS 0.13 0.00 0.13 Standard Error 0.13 0.00 t Stat F 4449.54 P-value Significance F 0.00 Lower 95% Upper 95% Lower 95.0% Intercept X Variable 1 -68.62 0.03 1.03 0.00 -66.88 66.70 0.00 0.00 -70.94 0.03 rate of the coefficient; then substract 1.00 3.46% = ANNUAL Growth RATE of GROWTH in Dividend be sure to look at the Excel function in the cell! -66.30 0.04 -70.94 0.03 Upper 95.0% -87.77 0.06 Upper 95.0% -66.30 0.04 Example Corporate Valuation and Financial Planning 9-2 Financial Planning at DaDa, Inc. The process used by DaDa to forecast the free cash flows from its operating plan is described in the sections below. Setting Up the Model to Forecast Operations We begin with DaDa's most recent financial statements and selected additional data. Figure 9-1 DaDa’s Most Recent Financial Statements (Millions, Except for Per Share Data) INCOME STATEMENTS BALANCE SHEETS 2016 2017 Assets Net sales $ 21,315 $ 22,386 Cash $ COGS (excl. depr.) 17,462 18,490 ST Investments Depreciation 981 1,011 Accounts receivable Income from Equity Investmentss - Inventories Other operating expenses 545 1,393 Other Current Assets EBIT $ 3,853 $ 3,896 Total CA $ Interest Income and Other, Net 426 666 Long Term Investments $ Interest expense 81 92 Equity and Cost Investments$ PP&E $ EBT $ 4,198 $ 4,317 Goodwill $ Intangible Asset $ Interest expense 100 120 Deferred LT Asset Charges Other L-T Assets Pre-tax earnings $ 4,198 $ 4,317 Total assets $ Taxes (40%) 1,379 1,433 NI before pref. div. $ 2,819 $ 2,884 Liabilities and equity Preferred div. 8 8 Accounts payable $ Net income $ 2,811 $ 2,876 Accruals Insurance Reserves Store Value and Card Liab Notes payable Other Data Total CL $ Common dividends $1,178 $1,450 Long Term Debt Other L-T Liabilities Addition to RE $1,633 $1,426 Total liabilities $ 2016 2,129 134 769 1,379 347 4,758 1,142 355 4,534 1,720 516 885 403 14,313 731 1,999 246 1,171 400 4,547 3,185 690 8,422 $ $ $ $ $ $ $ $ $ $ $ 2017 2,462 229 870 1,364 358 5,283 542 482 4,920 1,539 441 795 363 14,366 783 1,935 215 1,289 4,221 3,933 755 8,909 Tax rate Shares of common stock 33% 1,500 Earnings per share Dividends per share $1.87 $0.79 Price per share $40.00 33% Common stock 1,400 Additional Paid In Captial Retained earnings $2.05 Accum other Comprehensive Loss $1.04 Total common equity $ $ $27.00 Total liabs. & equity $ 2 41 5,950 (108) 5,884 $ 7 $ 14,313 $ 1 41 5,563 (156) 5,450 7 14,366 The figure below shows all the inputs required to project the financial statements for the scenario that has been selected. The scenario is named Status Quo because all operating ratios except the sales growth rate are assumed to remain unchanged. The initial sales growth rate was chosen by DaDa's managers based on the existing product lines. The growth rate declines over time until it eventually levels off at a sustainable rate. The other scenario is named Final because it is the set of inputs chosen by DaDa's management team. Section 1 shows the inputs required to estimate the items in an operating plan. For each of these inputs, Section 1 shows the industry averages, the actual values for the past two years for DaDa, and the forecasted values for the next five years. The managers assumed the inputs for future years (except the sales growth rate) would be equal to the inputs in the first projected year. DaDa's managers assume that sales will eventually level off at a sustaniable constant rate. Sections 2 and 3 show the data required to estimate the weighted average cost of capital. Section 4 shows the forecasted growth rate in dividends. Note: These inputs are linked throughout the model. If you want to change an input, do it here and not other places in the model. Figure 9-2 DaDa's Forecast: Inputs for the Selected Scenario Status Quo Industry DaDa Inputs Actual Actual 1. Operating Ratios 2017 2016 2017 Sales growth rate 5% 3% 5% COGS (excl. depr.) / Sales 76% 82% 83% Depreciation / Net PP&E 9% 111% 127% Other op. exp. / Sales 10% 3% 6% Cash / Sales 1% 10% 11% Acc. rec. / Sales 10% 4% 4% Inventory / Sales 19% 6% 6% Net PP&E / Sales 39% 4% 4% Other L-T Assets/Sales 2% 2% Other L-T Liabilities/Sales 3% 3% Acc. pay. / Sales 4% 3% 3% Accruals / Sales 7% 9% 9% Tax rate 40% 33% 33% 2. Capital Structure Actual Market Weights % Long-term debt 22% #REF! #REF! % Short-term debt 3% #REF! #REF! % Preferred stock 2% #REF! #REF! % Common stock 73% #REF! #REF! 3. Costs of Capital Rate on LT debt Rate on ST debt Rate on preferred stock (ignoring flotation costs) Cost of equity 4. Target Dividend Policy Actual Growth rate of dividends 3% 31.9% 2018 5.0% 76% 10% 9.5% 1% 10% 20% 45% 13% 13% 4% 6% 25% 28% 2% 3% 67% 9.0% 10.0% 8.0% 13.58% 3.5% DaDa Forecast 2019 2020 4.50% 4.00% 76% 76% 10% 10% 9.5% 9.5% 1% 1% 10% 10% 20% 20% 45% 45% 13% 13% 13% 13% 4% 4% 6% 6% 25% 25% Target Market Weights 28% 28% 2% 2% 3% 3% 67% 67% Forecast 9% 9% 10% 10% 8% 8% 14% 14% 3.5% 3.5% 2021 3.50% 76% 10% 9.5% 1% 10% 20% 45% 13% 13% 4% 6% 25% 2022 3.5% 76% 10% 9.5% 1% 10% 20% 45% 13% 13% 4% 6% 25% 28% 2% 3% 67% 28% 2% 3% 67% 9% 10% 8% 14% 9% 10% 8% 14% 3.5% 3.5% 9-3 Forecasting Operations The figure below shows the forecasted items for the operating plan. For convenience, we repeat the inputs of operating ratios. Section B1 shows the sales forecast. Each year's sales is equal to the previous year's sales multiplied by the forecasted sales growth rate. Section B2 shows the projections of operating assets and operating liabilities. The operating asset for a particular year is equal to the product of that asset's ratio in Section A1 and that particular year's projected sales. The operating liabilities are projected in a similar manner. Section B3 shows the projections of operating income. The COGS and other operating expenses are equal to the product of the ratio in Section A1 and that particular year's projected sales. Depreciation is equal to the product of the ratio in Section A1 and that particular year's projected net PP&E. EBIT is net sales minus COGS, depreciation, and other operating expenses. NOPAT is EBIT(1-T), where T is the tax rate. Section B3 shows the projections of operating income. The COGS and other operating expenses are equal to the product of the ratio in Section A1 and that particular year's projected sales. Depreciation is equal to the product of the ratio in Section A1 and that particular year's projected net PP&E. EBIT is net sales minus COGS, depreciation, and other operating expenses. NOPAT is EBIT(1-T), where T is the tax rate. Section B4 shows the projections of free cash flows. NWC is equal to operating CA (i.e., cash, accounts receivable, and inventories from Section B2) minus operating CL (i.e., accounts payable and accruals from Section 4). Total capital is equal to the sum of NWC and net Fixed Asset (NFA) (from Section B2). Section B5 shows the results of the operating plan. The first rows in Section B5 report the target WACC (calculated as shown in Chapter 13), the return on invested capital, and the growth rate in FCF. The continuing value, value of operations, and estimated intrinsic stock price are calculated using the FCF valuation model as present in Chapter 9. Note: Do not change inputs here because these inputs are linked to the ones in Figure 9-2. If you want to change inputs, do so in Figure 9-2. Figure 9-3 DaDa's Forecast of Operations for the Selected Scenario (Millions of Dollars, Except for Per Share Data) Status Quo Industry DaDa DaDa Panel A: Inputs Actual Actual Forecast A1. Operating Ratios 2017 2016 2017 2018 2019 2020 Sales growth rate 5% 3.2% 5.0% 5.0% 4.5% 4.0% COGS (excl. depr.) / Sales 76% 81.9% 82.6% 76.0% 76.0% 76.0% Depreciation / Net PP&E 9% 110.8% 127.1% 10.0% 10.0% 10.0% Other op. exp. / Sales 10% 2.6% 6.2% 9.5% 9.5% 9.5% Cash / Sales 1% 10.0% 11.0% 1.0% 1.0% 1.0% Acc. rec. / Sales 10% 3.6% 3.9% 10.0% 10.0% 10.0% Inventory / Sales 19% 6.5% 6.1% 20.0% 20.0% 20.0% Net PP&E / Sales 39% 4.2% 3.6% 45.0% 45.0% 45.0% Other L-T Assets/Sales 1.9% 1.6% 13.0% 13.0% 13.0% Other L-T Liabilities/Sales 3.2% 3.4% 12.5% 12.5% 12.5% Acc. pay. / Sales 4% 3.4% 3.5% 4.0% 4.0% 4.0% Accruals / Sales 7% 9.4% 8.6% 6.0% 6.0% 6.0% Tax rate 40% 32.9% 33.2% 25.0% 25.0% 25.0% Panel B: Results Actual Forecast B1. Sales Revenues 2017 2018 2019 2020 Net sales $22,386 $23,505 $24,563 $25,546 B2. Operating Assets and Operating Liabilities Cash $2,462 $235 $246 $255 Accounts receivable $870 $2,351 $2,456 $2,555 Inventories $1,364 $4,701 $4,913 $5,109 Net PP&E $795 $10,577 $11,053 $11,496 2021 3.5% 76.0% 10.0% 9.5% 1.0% 10.0% 20.0% 45.0% 13.0% 12.5% 4.0% 6.0% 25.0% 2022 3.5% 76.0% 10.0% 9.5% 1.0% 10.0% 20.0% 45.0% 13.0% 12.5% 4.0% 6.0% 25.0% 2021 $26,440 2022 $27,365 $264 $2,644 $5,288 $11,898 $274 $2,737 $5,473 $12,314 Other L-T Assets Other L-T Liabilities Accounts payable Accruals B3. Operating Income COGS (excl. depr.) Depreciation Other operating expenses EBIT Net operating profit after taxes NOPAT(i.e., EBIT (1-T)) B4. Free Cash Flows Change in Net Working Capital CapEx (investing in PP&E and other L-T Asset) FCF Below is an alternative way to calculate FCF Net operating working capital Total operating capital (i.e., NWC+NFA) FCF = NOPAT – Δ op capital B5. Estimated Intrinsic Value Target WACC Return on invested capital Growth in FCF Long-term FCF Growth Rate Continuing Value: $363 $755 $783 $1,935 $3,056 $2,938 $940 $1,410 $18,490 $1,011 $1,393 $3,896 $2,603 $17,864 $1,058 $2,233 $2,351 $1,763 $3,321 $3,193 $1,022 $1,533 $3,437 $3,305 $1,058 $1,586 $3,557 $3,421 $1,095 $1,642 $18,668 $1,105 $2,333.49 $2,456 $1,842 $19,415 $1,150 $2,427 $2,555 $1,916 $20,094 $1,190 $2,512 $2,644 $1,983 $20,797 $1,231 $2,600 $2,737 $2,052 $2,956 $13,533 −$13,668 $222 $1,719 $1,007 $206 $1,719 $1,140 $188 $1,708 $1,277 $194 $1,768 $1,321 $1,980 $3,138 $2,300 $4,936 $18,569 −$13,668 $5,158 $19,405 $1,007 $5,365 $20,181 $1,140 $5,552 $20,887 $1,277 $5,747 $21,618 $1,321 82.9% 11.4% 9.5% 11.4% 9.5% -107% 11.4% 9.5% 13.2% 11.4% 9.5% 12.0% 11.4% 9.5% 3.5% 3.5% $1,092 $229 $1,321 $3,933 #REF! #REF! $1,400 #REF! 433 881 2300 $3,193 $3,070 $983 $1,474 = $17,358 Present value of CV + Present value of FCF $10,127 −$9,035 Value of operations + S-T investments & L-T Securities Estimated total intrinsic value − All debt − Preferred stock Estimated intrinsic value of equity ÷ Number of shares Value of operations = $1,092 Estimated intrinsic stock price = Value of Operations: 9-4 Projecting DaDa's Financial Statements Projecting 1 Year of Financial Statements Figure 9-4, shown below, projects DaDa's financial statements for the upcoming year for the Status Quo scenario. Operating items are projected in the identical manner as previously projected for the operating plan. The preliminary short-term financial policy calls for no changes in notes payable, long-term bonds, preferred stock, and common stock, so their values from the previous year are carried over. The interest on notes payable and long-term bonds is based on the average amount of debt during the year, defined as the average of the beginning debt (i.e., the debt at the end of the previous year) and the ending debt. An identical process is applied to preferred dividends. The preliminary short-term financial policy calls for dividends to grow at the same rate as the long-term sustainable growth rate in earnings (which is the same as sales in the long-term). Section 3 in the figure below calculates the additional financing provided by spontaneous liabilities, external sources, and internal sources. The sum of these three sources of financing is the total amount of additional preliminary financing. Section 3 also calculates the total amount of additional assets required by the operating plan. The difference between the total additional financing and the total additional assets is defined as the financing deficit (if the difference is negative) or the financing surplus (if the difference is positive). If there is a financing deficit, DaDa will draw on a line of credit. DaDa assumes that the LOC will be accessed on the last day of the year, so the new line of credit (reflected in the end-of-year balance) will not accrue enough interest to matter. Therefore, the interest on the LOC will be equal to the balance at the beginning of the year (which is the same as the balance at the end of the previous year). If there is a financing surplus, DaDa will pay a special dividend. Note: Do not change inputs here because these inputs are linked to the ones in Figure 9-2. If you want to change inputs, do so in Figure 9-2. Figure 9-4 Projected Financial Statements (Millions of Dollars) Status Quo 1. Balance Sheets Most Recent 2017 Assets Cash $2,462.3 Accounts receivable 870.4 Inventories 1,364.0 Total current assets $4,696.7 Net PP&E 795.4 Other L-T Assets 362.8 Total assets (TA) $5,854.9 Liabilities and equity Input Basis for 2018 Forecast 1.00% × 2018 Sales 10.00% × 2018 Sales 20.00% × 2018 Sales 45.00% × 2018 Sales 13.00% × 2018 Sales Forecast 2018 $235.05 $2,350.53 $4,701.06 $7,286.64 $10,577.39 $3,055.69 $20,919.72 Accounts payable Accruals Notes payable Line of credit Total CL Other non-current Liabilities Long-term bonds Total liabilities Preferred stock Common stock Retained earnings Total common equity Total liabs. & equity 2. Income Statement Net sales COGS (excl. depr.) Depreciation Other operating expenses EBIT Less: Interest on notes Interest on bonds Interest on LOC Pre-tax earnings Taxes (40%) NI before pref. div. Preferred div. Net income Regular common dividends Special dividends Addition to RE $782.5 1,934.5 0.0 0.0 $2,717.0 $755.3 3,932.6 $7,404.9 #REF! 1.4 5,563.2 $5,564.6 #REF! Most Recent 2017 $22,386.0 18,490.0 1,011.0 $1,393.0 $1,492.0 20.0 100.0 0.0 $1,372.0 1,433.2 -$61.2 8.0 -$69.2 $1,450.0 $0.0 -$1,519.2 4.00% × 2018 Sales 6.00% × 2018 Sales Carry over from previous year Draw on LOC if financing deficit 13% × 2018 Sales Carry over from previous year Carry over from previous year Carry over from previous year Old RE + Add. to RE Check: TA − Total Liab. & Eq. = Input 105% 76.00% 10.00% 9.50% × × × × Basis for 2018 Forecast 2017 Sales 2018 Sales 2018 Net PP&E 2018 Sales 10.00% × Avg notes 9.00% × Avg bonds 11.50% × Beginning LOC 25.00% × Pretax earnings 8.00% × Avg pref. stock 104% × 2017 Dividend Pay if financing surplus Net income – Dividends 3. Elimination of the Financial Deficit or Surplus Increase in spontaneous liabilities (accounts payable and accruals) + Increase in notes payable, long-term bonds, preferred stock, and common stock + Net income minus regular common dividends Increase in financing − Increase in total assets Amount of deficit or surplus financing: If deficit in financing (negative), draw on line of credit Line of credit $940.21 $1,410.32 $0.00 #REF! #REF! $2,938.16 $3,932.60 #REF! #REF! $1.40 #REF! #REF! #REF! #REF! Forecast 2018 $23,505.30 $17,864.03 $1,057.74 $2,233.00 $2,350.53 $0.00 $353.93 $0.00 $1,996.60 $499.15 $1,497.45 #REF! #REF! $1,500.75 #REF! #REF! $1,816.39 #REF! #REF! #REF! $15,064.82 #REF! #REF! Note: If there is an initial balance on the on the LOC, the assumptio balance will not change until the last day of the year. Ther interest for the year is the based only on the beginning b Note: If there is a LOC in the previous year, then it is necessary to su Note: This is the planned increase in the retained earnings account. If surplus in financing (positive), pay special dividend Special dividend #REF! 9-4 Analysis and Revision of the Preliminary Plan Projected 5-Year Statements Projected Financial Statements (Millions of Dollars) Status Quo 1. Balance Sheets Actual 2017 Assets Cash $2,462.3 Accounts receivable 870.4 Inventories 1,364.0 Total current assets $4,696.7 Net PP&E 795.4 Other L-T Assets 362.8 Total assets (TA) $5,854.9 Liabilities and equity Accounts payable $782.5 Accruals 1,934.5 Notes payable 0.0 Line of credit 0.0 Total CL $2,717.0 Long-term bonds 3,932.6 Other non-current liabilities 755.3 Total liabilities $7,404.9 Preferred stock #REF! Common stock 1.4 Retained earnings 5,563.2 Total common equity $5,564.6 Total liabs. & equity #REF! Check: TA − Total Liab. & Eq. = 2. Income Statement Actual 2017 Net sales $22,386.0 COGS (excl. depr.) 18,490.0 Depreciation 1,011.0 Other operating expenses $1,393.0 EBIT $1,492.0 Less: Interest on notes 20.0 2018 2019 Forecast 2020 2021 2022 $235.1 2,350.5 4,701.1 $7,286.6 10,577.4 3,055.7 $20,919.7 $245.6 2,456.3 4,912.6 $7,614.5 11,053.4 3,193.2 $21,861.1 $255.5 2,554.6 5,109.1 $7,919.1 11,495.5 3,320.9 $22,735.5 $264.4 2,644.0 5,287.9 $8,196.3 11,897.8 3,437.2 $23,531.3 $273.7 2,736.5 5,473.0 $8,483.2 12,314.3 3,557.5 I added the line to show how you can include additional lines $24,354.9 $940.2 1,410.3 0.0 #REF! #REF! 3,932.6 2,938.2 #REF! #REF! 1.4 #REF! #REF! #REF! #REF! $982.5 1,473.8 0.0 #REF! #REF! 3,932.6 3,070.4 #REF! #REF! 1.4 #REF! #REF! #REF! #REF! $1,057.6 1,586.4 0.0 #REF! #REF! 3,932.6 3,305.0 #REF! #REF! 1.4 #REF! #REF! #REF! #REF! $1,094.6 1,641.9 0.0 #REF! #REF! 3,932.6 3,420.6 #REF! #REF! 1.4 #REF! #REF! #REF! #REF! 2018 $23,505.3 17,864.0 1,057.7 $2,233.0 $2,350.5 0.0 2019 $24,563.0 18,667.9 1,105.3 $2,333.5 $2,456.3 0.0 $1,021.8 1,532.7 0.0 #REF! #REF! 3,932.6 3,193.2 #REF! #REF! 1.4 #REF! #REF! #REF! #REF! Forecast 2020 $25,545.6 19,414.6 1,149.6 $2,426.8 $2,554.6 0.0 2021 $26,439.7 20,094.1 1,189.8 $2,511.8 $2,644.0 0.0 2022 $27,365.0 20,797.4 1,231.4 $2,599.7 $2,736.5 0.0 Interest on bonds Interest on LOC Pre-tax earnings Taxes (40%) NI before pref. div. Preferred div. Net income Regular common dividends Special dividends Addition to RE 100.0 0.0 $1,372.0 1,433.2 -$61.2 8.0 -$69.2 $1,450.0 $0.0 -$1,519.2 353.9 0.0 $1,996.6 499.1 $1,497.4 #REF! #REF! $1,500.8 #REF! #REF! 353.9 #REF! #REF! #REF! #REF! #REF! #REF! $1,553.3 #REF! #REF! 353.9 #REF! #REF! #REF! #REF! #REF! #REF! $1,607.6 #REF! #REF! 353.9 #REF! #REF! #REF! #REF! #REF! #REF! $1,663.9 #REF! #REF! 353.9 #REF! #REF! #REF! #REF! #REF! #REF! $1,722.1 #REF! #REF! 3. Incorporating the Financial Deficit or Surplus Increase in spontaneous liabilities (accounts payable and accruals) $1,816.4 $238.0 + Increase in notes payable, long-term bonds, preferred stock and common #REF! stock #REF! − Previous line of credit $0.0 #REF! + Net income minus regular common dividends #REF! #REF! Increase in financing #REF! #REF! − Increase in total assets $15,064.8 $941.4 Amount of deficit or surplus financing: #REF! #REF! Line of credit #REF! #REF! Special dividend #REF! #REF! $221.1 #REF! #REF! #REF! #REF! $874.4 #REF! #REF! #REF! $201.2 #REF! #REF! #REF! #REF! $795.7 #REF! #REF! #REF! $208.2 #REF! #REF! #REF! #REF! $823.6 #REF! #REF! #REF! Note: Note: Note: Do not change inputs here because these inputs are linked to the ones in Figure 9-2. If you want to change inputs, do so in Figure 9-2. Figure 9-5 (Status Quo Scenario) or Figure 9-6 (Final Scenario) Summary of Important Inputs and Key Results for Selected Scenario (Millions Except Percentages and Per Share Data) Status Quo Panel A: Inputs A1. Operating Ratios Sales growth rate COGS (excl. depr.) / Sales Inventory / Sales Net PP&E / Sales Panel B: Key Results B1. Operations Free cash flow Return on invested capital Industry Actual Actual 2017 2017 5.0% 5.0% 75.5% 82.6% 19.0% 6.1% 38.5% 3.6% Industry Actual Actual 2017 2017 NA $2,300 15.0% 82.9% DaDa 2018 5.0% 76.0% 20.0% 45.0% 2018 −$13,668 9.5% 2019 4.5% 76.0% 20.0% 45.0% DaDa 2019 $1,007 9.5% Forecast 2020 4.0% 76.0% 20.0% 45.0% 2021 3.5% 76.0% 20.0% 45.0% 2022 3.5% 76.0% 20.0% 45.0% Forecast 2020 $1,140 9.5% 2021 $1,277 9.5% 2022 $1,321 9.5% NOPAT/Sales 6.9% 11.6% Total op. capital / Sales 46.0% 14.0% Inventory turnover 5.0 14.3 Days sales outstanding 30.0 14.2 Fixed asset turnover 3.0 28.1 B2. Financing Total liabilities / TA 45.0% #REF! Net income / Sales 6.2% -0.3% Return on assets (ROA) 11.0% -1.2% Return on equity (ROE) 19.0% -1.2% Times interest earned 10.0 12.4 Line of credit NA $0 Payout ratio 35.0% -2094.0% Regular dividends/share NA $1.04 Special dividends/share NA $0.00 Earnings per share NA −$0.05 B3. Estimated intrinsic value 12/31/2017 Estimated value of operations = 12/31/2017 Estimated intrinsic stock price = 7.5% 79.0% 4.0 36.5 2.2 7.5% 79.0% 4.0 36.5 2.2 7.5% 79.0% 4.0 36.5 2.2 7.5% 79.0% 4.0 36.5 2.2 7.5% 79.0% 4.0 36.5 2.2 #REF! #REF! #REF! #REF! 6.6 #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! 1.5% 2.0% Long-term FCF Growth Rate 2.5% 3.0% $1,092 #REF! Two-Factor Scenario Analysis Operatining Expense Ratio #REF! 0.0% 8.0% 0.5% 1.0% #REF! #REF! #REF! #REF! #REF! #REF! 8.5% #REF! #REF! #REF! #REF! #REF! #REF! #REF! 9.0% #REF! #REF! #REF! #REF! #REF! #REF! #REF! 9.5% #REF! #REF! #REF! #REF! #REF! #REF! #REF! 10.0% #REF! #REF! #REF! #REF! #REF! #REF! #REF! 10.5% #REF! #REF! #REF! #REF! #REF! #REF! #REF! other places in the model. See Tab "Div&Revenue Growth Analysis" Actual Historical Financing Long-term debt Short-term debt Preferred stock Market value of equity = (Price x # shares) Total See the box to the right for calculations of the actual capital structures, based on market values, See Tab "Div&Revenue Growth Analysis" Percent long-term debt Percent short-term debt Percent preferred stock Percent market value of equity Total 2016 $3,185 $400 #REF! $60,000 #REF! #REF! #REF! #REF! #REF! #REF! 2017 $3,933 $0 #REF! $37,800 #REF! #REF! #REF! #REF! #REF! #REF! o change inputs, do so in Figure 9-2. Note: You can also treat these items as constant, i.e., not change with sale You can also treat these items as constant, i.e., not change with sale If there is an initial balance on the on the LOC, the assumption is that the balance will not change until the last day of the year. Therefore, the interest for the year is the based only on the beginning balance. If there is a LOC in the previous year, then it is necessary to subtract the previous year's line of credit. In other words, this is like paying off the old line of credit on the last day of the year and then drawing on a n This is the planned increase in the retained earnings account. I added the line to show how you can include additional lines If there is an initial balance on the on the LOC, the assumption is that the balance will not change until the last day of the year. Therefore, the interest for the year is the based only on the beginning balance. If there is a LOC in the previous year, then it is necessary to subtract the previous year's line of credit. In other words, this is like paying off the old line of credit on the last day of the year and then drawing on a new line of credit. o change inputs, do so in Figure 9-2. Long-term FCF Growth Rate 3.5% 4.0% 4.5% 5.0% 5.5% 6.0% #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF!
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Buddy here is the solution. I will be glad to ear from you. Thank you
There it is buddy as requested. Thank you

Starbucks Corporaation WACC Analysis
Name
Institution
Professor
Date

Starbucks Corporation WACC Analysis
WACC Formula = (E/V * Ke) + (D/V) * Kd * (1 – Tax rate)
E = Market Value of Equity
V = Total market value of equity & debt
Ke = Cost of Equity
D = Market Value of Debt
Kd = Cost of Debt
Tax Rate = Corporate Tax Rate
Market value of Equity (E)
Market Value of Equity = Number of shares outstanding x current price.
number of shares outstanding(millions)
current price per share(52 week high)

Market Value of Equity
$
Debts (D)
current portion of long term debt
long term debt

$

91,954.66

= 1349 * 68.16= 91954.7 million
649.8
6,149.10

market value of debt

6798.9 =649.8+6149.10= 6798.9 million

estimated fair value
$
357.00
$
511.00
$
255.00
$
526.00
$
839.00
$
509.00
$
417.00
$
507.00
$
3,921.00

fair value of debt

1,349
68.16

$

3,921.00 million

stated interest
2.000%
2.100%
2.100%
2.700%
3.850%
2.450%
4.300%
4.450%

COST OF EQUITY
Rf
Rm
Beta

We use the CAPM model to find the cost of equity.
Ke = Rf + (Rm – Rf) x Beta
EQUITY RISK PREMIUM (RM – RF)

6.25

Rf

Cost of Equity = Ke = Rf + (Rm – Rf) x Beta
Ke

8.21%

= 2.58% + 6.25% x 0.9

Cost of Equity = 8.21
COST OF DEBT

Issuance
2016 notes
2018 notes
2021 notes
2021 notes
2022 notes
2023 notes
2026 notes
2045 notes
Total Debt

Fair Value
357
511
255
526
839
509
417
507
3921

Interest Rate
Interest
2.000%
2.100%
2.100%
2.700%
3.850%
2.450%
4.300%
4.450%
Total Interest

EffectiveInterest Rate

Column1

Column2

7.14
10.731
5.355
14.202
32.3015
12.4705
17.931
22.5615
122.6925
3.13%

Column3

Column4

2017

2016

2015

arnings Before
Interest and Tax

$4,410,000

$4,279,900

$3,973,500

Interest Expense

$92,500

$81,300

$70,500

$4,317,500

$4,198,600

$3,903,000

$1,432,600

$1,379,700

$1,143,700

Earnings Before Tax

Income Tax

Tax rate
0.331812391
Mean For past 4 FY
Effective tax rate (Mean of past 4 FY)

0.328609537

32.5%

Summary Data Table
Market Value of Equity (million)
Market Value of Debt (million)
Cost of Equity
Cost of Debt
Tax rate

0.293031002

Linked Values
E
D
Ke
Kd
Tax Rate

91954.656
6798.9
8.21%
3.13%
32.48%

WACC Formula = E/V * Ke + D/V * Kd * (1 – Tax Rate)

WACC

7.79%

= (9194.656/(9194.656+6798.7)*8.21% + (6798.7/9194.656+6798.7) * 3.13% * (1-32.48%)

References
NASDAC.COM retrieved from https://www.nasdaq.com/symbol/sbux/financials?query=income-statement
Yahoo Finance retrieved from https://finance.yahoo.com/quote/SBUX/financials?p=SBUX

2.58
6.25
0.9
(yahoo finance)

Column5
2014
$3,223,800

$64,100

$3,159,700

$1,092,000

uery=income-statement

0.345602431

Starbucks Corporation WACC Analysis
WACC Formula = (E/V * Ke) + (D/V) * Kd * (1 – Tax rate)
E = Market Value of Equity
V = Total market value of equity & debt
Ke = Cost of Equity
D = Market Value of Debt
Kd = Cost of Debt
Tax Rate = Corporate Tax Rate
Market value of Equity (E)
Market Value of Equity = Number of shares outstanding x current price.
number of shares outstanding(millions)
current price per share(52 week high)

Market Value of Equity
$
Debts (D)
current portion of long term debt
long term debt

$

492.75

= 18 * 27= 492.75million
1,289
4,220.70

market value of debt

5509.2 =1289+4220= 5509.2 million

estimated fair value
$
782.50
$
$
$
$
$
$
$
$
782.50

fair value of debt

18
27.00

$

782.50 million

stated interest
4.000%

COST OF EQUITY
Rf
Rm
Beta

We use the CAPM model to find the cost of equity.
Ke = Rf + (Rm – Rf) x Beta
EQUITY RISK PREMIUM (RM – RF)

6.25

Rf

Cost of Equity = Ke = Rf + (Rm – Rf) x Beta
Ke

3.20%

Cost of Equity = 8.21

3.20%

= 1.64% + 6.25% x 0.1

COST OF DEBT

Issuance
2016 notes
2018 notes
2021 notes
2021 notes
2022 notes
2023 notes
2026 notes
2045 notes
Total Debt

Fair Value
Interest Rate
Interest
$
782.50
4.000%
$
$
$
$
$
$
$
782.5 Total Interest
EffectiveInterest Rate

Column1

Column2

Earnings Before Tax

Income Tax

$

4.00%

Column3
2017

31.3
0
0
0
0
0
0
0
31.3

2016

4,317 $

4,198

1,433

1,379

Tax rate
Mean For past 4 FY
Effective tax rate (Mean of past 4 FY)

0.332

0.3285
33.0%

Summary Data Table
Market Value of Equity (million)
Market Value of Debt (million)
Cost of Equity
Cost of Debt
Tax rate

Linked Values
E
D
Ke
Kd
Tax Rate

WACC Formula = E/V * Ke + D/V * Kd * (1 – Tax Rate)

WACC

2.72%

= (492.75/(492.75+5509.2)*3.2% + (5509.2/492.75+5509.2) * 4.0% * (1-33.03%)

References
Data from FCF VALUATION EXAMPLE
https://www.advfn.com/stock-market/AMEX/E%5CDAM/stock-price
https://www.bloomberg.com/quote/DA:IM

492.75
5509.2
3.20%
4.00%
33.03%

1.64
6.25
0.25
NASDQ finance)

Figure 9-1
DaDa’s Most Recent Financial Statements (Millions, Except for Per Share Data)
INCOME STATEMENTS

BALANCE SHEETS

2016
Net sales

$

21,315

2017 Assets
$

COGS (excl. depr.) 17,462
Depreciation

22,386 Cash

981

1,011 Accounts receivable

Other operating expenses
545
$

3,853

- Inventories
1,393 Other Current Assets

$

Interest Income and Other,
426 Net
Interest expense

81

3,896 Total CA

$

4,198

$

100
4,198

Taxes (40%)

1,379

NI before pref. $div. 2,819
Preferred div.
Net income

2,811

92 Equity and Cost Investments $

355

$

482

$

4,534

$

4,920

$

1,720

$

1,539

$

516

$

441

4,317 Goodwill

4,317 Total assets

33%

885

795

403

363

$

14,313

$

14,366

$

731

$

783

2,884 Liabilities and equity
8 Accounts payable

$

2,876 Accruals

1,999

$

$1,450 Long Term Debt
Other L-T Liabilities

Tax rate

358
542

Total CL

$1,633

1,364

347

5,283

Notes payable

Addition to
RE

1,379

$

Store Value and Card Liab

Common dividends $1,178

870

$

Insurance Reserves

Other Data

769

1,433
$

8
$

229

1,142

120 Deferred LT Asset Charges
$

$1,426 Total liabilities

$

33% Common ...


Anonymous
Just what I needed. Studypool is a lifesaver!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags