I need formulas in excel: Thank you Problem 5: Finding the Weighted Average Cost of Capital...

I need formulas in excel: Thank you Problem 5: Finding the Weighted Average Cost of Capital
Use the information below to find Starbucks weighted average cost of capital.

Starbucks Dividend History				Inputs
Date	Dividends	Growth		re
2/7/2011	0.28	0.285714		rd
2/6/2012	0.36	0.236111		Tc
2/5/2013	0.445	0.235955		E
2/4/2014	0.55	0.527273		D
2/3/2015	0.84	0.011905		WACC
2/2/2016	0.85	0.235294
2/7/2017	1.05

Arithmetic g
Geometric g
Estimated 2018 Dividend
Cost of equity

Share Statistics
Current Share Price	$ 56.79
Number of Shares Outstanding	1.41	billion


E

Information from the Financial Statements (in thousands)
	2017	2016	2015
Income Before Tax	4,317,500	4,198,600	3,903,000
Income Tax Expense	1,432,600	1,379,700	1,143,700
Tax rate

	2017	2016	2015
Short/Current Long Term Debt	0	399,900	0
Long Term Debt	3,932,600	3,185,300	2,347,500


Interest Expense	92,500	81,300	70,500
Implied interest rate

Expert Solution

Starbucks Dividend History				Inputs
Date	Dividends	Growth		re
02-07-11	0.28	0.285714		rd
02-06-12	0.36	0.236111		Tc
02-05-13	0.445	0.235955		E
02-04-14	0.55	0.527273		D
02-03-15	0.84	0.011905		WACC
02-02-16	0.85	0.235294
02-07-17	1.05
				WACC	kd* wd+ ke*we
Arithmetic g	26%			WACC	17.97%
Geometric g	17%
Estimated 2018 Dividend	1.23
Cost of equity	18.77%

Share Statistics
Current Share Price	56.79			Total Equity		80,073,900,000
Number of Shares Outstanding	1.41	billion		Total Debt		3,932,600,000	(Since it is in thousands given)

				wd	0.05
E				we	0.95

Information from the Financial Statements (in thousands)				kd	0.02
	2017	2016	2015	ke	This we will determine from dividend discount model
Income Before Tax	4,317,500	4,198,600	3,903,000
Income Tax Expense	1,432,600	1,379,700	1,143,700	Cost of Equity = (Dividends for next year /Current Market Value of Stock )+ Growth Rate of Dividends
Tax rate	33%	33%	29%

	2017	2016	2015
Short/Current Long Term Debt	0	399,900	0
Long Term Debt	3,932,600	3,185,300	2,347,500


Interest Expense	92,500	81,300	70,500
Implied interest rate	2.4%	2.3%	3.0%

jeff jeffy answered 2 years ago

I need the formulas in excel: Thank you You're talking to a friend of yours who...

I need the formulas in excel: Thank you You're talking to a friend of yours who has student loans of $30,000. She has just graduated and now needs to pay them back with monthy payments at a 4.19% interest rate over the next 10 years. a. Create a loan amortization table for this loan. b. If she increases her monthly payment by $100, how much sooner will she have paid off the loan? loan balance number of payments interest rate...

I need formulas in excel: Thank you You're the owner of Arcata Scoop - the best...

I need formulas in excel: Thank you You're the owner of Arcata Scoop - the best ice cream shop on the planet. You're considering purchasing a new machine that will allow you to make chocolate dipped ice cream cones. The machine costs $42,000. Details: Depreciation: The machine will be depreciated using straight line depreciation over 7 years, but you think that you'll only use the machine for 5 years. At the end of the 5th year, you'll retire the machine...

I. Explain the meaning of capital structure, cost of capital, and weighted average cost of capital...

I. Explain the meaning of capital structure, cost of capital, and weighted average cost of capital (WACC). II. Describe how capital structure and cost of capital affect the way that a company is valued by investors. III. Utilize vocabulary and explanations suitable for a non-expert in finance to understand the communication.

The weighted average cost of capital is determined by _____ the weighted average cost of equity....

The weighted average cost of capital is determined by _____ the weighted average cost of equity. a. multiplying the weighted average aftertax cost of debt by b. adding the weighted average pretax cost of debt to c. adding the weighted average aftertax cost of debt to d. dividing the weighted average pretax cost of debt by e. dividing the weighted average aftertax cost of debt by

The weighted average cost of capital (WACC) is calculated as the weighted average of cost of...

The weighted average cost of capital (WACC) is calculated as the weighted average of cost of component capital, including debt, preferred stock and common equity. In general, debt is less expensive than equity because it is less risky to the investors. Some managers may intend to increase the usage of debt, therefore increase the weight on debt (Wd). Do you think by increasing the weight on debt (Wd) will reduce the WACC infinitely? What are the benefits and costs of...

You need to estimate the Weighted Average Cost of Capital (WACC) for your company. You have...

You need to estimate the Weighted Average Cost of Capital (WACC) for your company. You have the following balance sheet data as well as the information provided below Assets Current Assets $38,000,000 Net Plant, Property, and Equipment 101,000,000 Total Assets $139,000,000 Liabilities and Equity Accounts Payable $10,000,000 Accruals 9,000,000 Current Liabilities $19,000,000 Long-term Debt (40,000 bonds, $1,000 face value) $40,000,000 Total Liabilities $59,000,000 Common Stock (10,000,000 shares) $30,000,000 Retained Earnings ...

Which of the following is a problem associated with using the weighted average cost of capital?...

Which of the following is a problem associated with using the weighted average cost of capital? Select one: a. WACC will vary for the same business based on which method yiu use. b. it may be difficult to find a rusk- free rate that corresponds to the investment being analyzed. c. The calculation is not exact as at least one component is an estimate. d. All of these answers.

What is Weighted Average Cost of Capital?

Estimating Cost of Equity Capital and Weighted Average Cost of Capital

Estimating Cost of Equity Capital and Weighted Average Cost of Capital The December 31, 2015, partial financial statements taken from the annual report for AT&T Inc. (T ) follow. Consolidated Statements of Income Dollars in millions except per share amounts 2015 2014 Operating revenues Service $ 131,677 $ 118,437 Equipment 15,124 14,010 Total operating revenues 146,801 132,447 Operating expenses Equipment 19,268 18,946 Broadcast, programming and operations 11,996 4,075 Other cost of services (exclusive of depreciation and...

I need this in an excel file with formulas please: 1) Use an Excel spreadsheet to...

I need this in an excel file with formulas please: 1) Use an Excel spreadsheet to solve problem #31 (the PUTZ, Inc. project) for Chapter 10 in the textbook. 2) Conduct a sensitivity analysis that focuses on the sales price by increasing the price by 10% above the best estimate, and then by decreasing the price by 10% below the best estimate. 3) You must provide one spreadsheet for each of the three situations—the base case estimate, the best case,...

Question