(((( Please I need the answer only In Excel file with formula.)))))

Chapter 6 Problem 15
Using the financial statements provided below and any other information you find relevant, answer the following questions:
a.	What was the book value of Avon?s shareholders? equity from 2001 to 2003? What were Avon?s liabilities-to-assets and times-interest-earned ratios in these years? (Use Pretax Income plus Interest Expense as EBIT.) What do these figures suggest about Avon?s use of financial leverage? Consulting Table 6-5 in the text, what bond rating would Avon have in 2002 if the rating were based solely on the firm?s coverage ratio?
b.	What percentage decline in EBIT could Avon have suffered in each year before Avon would have been unable to make its interest payments out of operating income?
c.	Assuming a 35 percent corporate tax rate, and 2002 earnings before interest and taxes of $895 million, by how much did Avon?s $60 million interest expense reduce taxes?
d.	Answer question (a) and (b) again for 2002 assuming the company had borrowed an additional $3 billion in debt at 8 percent interest at the start of the year and distributed the proceeds to shareholders as a special dividend. You may ignore the effect of added interest expense on Avon?s balance sheet. Might shareholders benefit from such an increase in financial leverage? Explain.
e.	How would you assess Avon?s business risk? Setting aside the way the company is financed, how significant are the marketplace risks Avon faces; how uncertain are the company?s future operating cash flows? What does your assessment of Avon?s business risk suggest about the level of financial leverage the company can prudently support?
f.	How big a threat would it be to Avon if the company took on too much debt and had difficulty servicing it? How costly would financial distress be to Avon? Explain.
g.	Based on your analysis and any other considerations you think relevant, is Avon heavily or modestly indebted? Should the company acquire more debt, or shed existing debt? Why?
AVON PRODUCTS
1345 Avenue Of The Americas	ANNUAL INCOME STATEMENT
New York, NY 10105	(MILLIONS, EXCEPT PER SHARE)
Ticker: AVP
2001	2002	2003
Sales	5,957.8	6,228.3	6,876.0
Cost of Goods Sold	2,156.9	2,217.6	2,481.8
Gross Profit	3,800.9	4,010.7	4,394.2
Selling, General, & Administrative Exp.	2,889.5	2,979.6	3,213.6
Operating Income Before Deprec.	911.4	1,031.1	1,180.6
Depreciation,Depletion,& Amortization	109.0	124.8	123.5
Operating Profit	802.4	906.3	1,057.1
Interest Expense	71.1	59.7	49.0
Non-Operating Income/Expense	36.3	25.3	(0.3)
Special Items	(77.9)	(36.3)	(14.3)
Pretax Income	689.7	835.6	993.5
Total Income Taxes	240.3	292.3	318.9
Minority Interest	4.5	8.7	9.8
Income Before Extra. Items & Disc. Oper.	444.9	534.6	664.8
Extraordinary Items	(0.3)	0.0	0.0
Discontinued Operations	0.0	0.0	0.0
Adjusted Net Income	444.6	534.6	664.8
Earnings Per Share Basic -
Including Extra Items & Disc Op	0.9	1.1	1.4
Earnings Per Share Diluted -
Including Extra Items & Disc Op	0.9	1.1	1.4
EPS Basic from Operations	1.1	1.2	1.4
EPS Diluted from Operations	1.0	1.2	1.4
Dividends Per Share	0.4	0.4	0.4
Com Shares for Basic EPS	473.7	472.1	471.1
Com Shares for Diluted EPS	492.1	490.9	483.1
ANNUAL BALANCE SHEET
($ MILLIONS)
2001	2002	2003
ASSETS
Cash & Short-Term Investments	508.5	606.8	694.0
Net Receivables	519.5	555.4	599.8
Inventories	612.5	614.7	653.4
Other Current Assets	248.6	271.3	278.9
Total Current Assets	1,889.1	2,048.2	2,226.1
Gross Plant, Property & Equipment	1,552.4	1,548.4	1,728.9
Accumulated Depreciation	779.7	779.3	873.3
Net Plant, Property & Equipment	772.7	769.1	855.6
Intangibles	0.0	20.6	46.2
Deferred Charges	0.0	0.0	0.0
Other Assets	530.8	489.6	434.4
TOTAL ASSETS	3,192.6	3,327.5	3,562.3
LIABILITIES
Long Term Debt Due In One Year	1.2	3.1	4.4
Notes Payable	87.6	602.1	239.7
Accounts Payable	404.1	379.9	400.1
Taxes Payable	375.9	353.0	321.9
Other Current Liabilities	592.2	637.4	621.6
Total Current Liabilities	1,461.0	1,975.5	1,587.7
Long Term Debt	1,236.3	767.0	877.7
Deferred Taxes	30.6	35.4	50.6
Investment Tax Credit	0.0	0.0	0.0
Minority Interest	29.0	37.0	46.0
Other Liabilities	510.8	640.3	629.0
TOTAL LIABILITIES	3,267.7	3,455.2	3,191.0
EQUITY
Common Stock	89.1	89.6	90.3
Capital Surplus	938.0	1,019.5	1,188.4
Retained Earnings	899.9	943.9	1,473.0
Less: Treasury Stock	2,002.1	2,180.7	2,380.4
TOTAL EQUITY	(75.1)	(127.7)	371.3
TOTAL LIABILITIES & EQUITY	3,192.6	3,327.5	3,562.3

Chapter 6 Problem 14 a. What were HCA's liabilities-to-assets ratios and times-interest-earned ratios in the years 2005 through 2009? b. What percentage decline in EBIT could HCA have suffered each year between 2005 and 2009 before the company would have been unable to make interest payments out of operating earnings, where operating earnings is defined as EBIT? c. How volatile have HCA's cash flows been over the period 2005 - 2009? d. Calculate HCA's return on invested capital (ROIC) in the years 2005 - 2009. e. HCA is the largest private operator of health care facilities in the world with hundrd of facilities in over 20 states. In 2006, private equity buyers took the company private in a $31.6 billion acquisition. In broad terms how costly do you think financial distress would be to HCA if it began to appear the company might be having difficulty servicing its debt? Why? f. In late 2010 HCA announced an intended dividend recapitalization in which it would pay a $2 billion dividend to shareholders financed in large part by a $1.53 billion bond offering. At an interest rate of 6 percent, how would the added debt have affected HCA's times-interest-earned ratio in 2009? g. Please comment on HCA's capital structure. Is its 2009 debt level prudent? Is it smart to add another $1.53 billion to this total? Why, or why not? HCA INC ANNUAL INCOME STATEMENT ($ MILLIONS, EXCEPT PER SHARE) Sales Cost of Goods Sold Gross Profit Dec09 Dec08 Dec07 Dec06 $ 30,052 $ 28,374 $ 26,858 $ 25,477 24,826 24,023 22,480 21,448 5,226 4,351 4,378 4,029 Depreciation Operating Profit Interest Expense Non-Operating Income/Expense Pretax Income 1,425 3,801 1,987 188 2,002 1,416 2,935 2,021 256 1,170 1,426 2,952 2,215 661 1,398 1,391 2,638 955 179 1,862 627 321 268 229 316 208 625 201 673 $ 874 $ Total Income Taxes Minority Interest Net Income $ 1,054 $ 1,036 ANNUAL BALANCE SHEET ASSETS Cash & Equivalents Net Receivables Inventories Other Current Assets Total Current Assets Dec09 $ 312 $ 3,692 802 1,771 6,577 Dec08 465 $ 3,780 737 1,319 6,301 Dec07 393 $ 3,895 710 1,207 6,205 Dec06 634 3,705 669 1,070 6,078 Gross Plant, Property & Equipment Accumulated Depreciation Net Plant, Property & Equipment 24,669 13,242 11,427 23,714 12,185 11,529 22,579 11,137 11,442 21,907 10,238 11,669 Investments at Equity Other Investments Intangibles Deferred Charges Other Assets TOTAL ASSETS 853 1,166 2,577 418 1,113 24,131 842 1,422 2,580 458 1,148 24,280 688 1,669 2,629 539 853 24,025 679 1,886 2,601 614 148 23,675 846 1,460 2,007 4,313 404 1,370 224 1,912 3,910 308 1,370 190 1,981 3,849 293 1,415 1,868 3,576 Long Term Debt Deferred Taxes Minority Interest Other Liabilities TOTAL LIABILITIES 24,824 1,008 2,825 32,970 26,585 995 2,890 34,380 27,000 938 2,612 34,399 28,115 390 907 1,936 34,924 Preferred Stock Common Stock Capital Surplus Retained Earnings Common Equity 147 1 226 (9,213) (8,986) 155 1 165 (10,421) (10,255) 164 1 112 (10,651) (10,538) 125 1 (11,375) (11,374) TOTAL EQUITY TOTAL LIABILITIES & EQUITY (8,839) 24,131 $ (10,100) 24,280 $ (10,374) 24,025 $ (11,249) 23,675 LIABILITIES Long Term Debt Due In One Year Accounts Payable Taxes Payable Accrued Expenses Total Current Liabilities $ s 2005 through 2009? d 2009 before the here operating earnings es in over 20 states. In ms how costly do you think ervicing its debt? Why? billion dividend to nt, how would the added another $1.53 billion to MENT HARE) Dec05 $ 24,455 20,391 4,064 1,374 2,690 655 412 2,327 725 178 $ 1,424 ET Dec05 $ 336 3,332 616 931 5,215 20,818 9,439 11,379 627 2,134 2,626 85 159 22,225 586 1,484 1,825 3,895 9,889 830 828 1,920 17,362 4 4,859 4,863 $ 4,863 22,225 Chapter 6 Problem 15 Using the financial statements provided below and any other information you find relevant, answer the following questions: a. What was the book value of Avon's shareholders' equity from 2001 to 2003? What were Avon's liabilities-to-assets and times-inter Pretax Income plus Interest Expense as EBIT.) What do these figures suggest about Avon's use of financial leverage? Consulting T would Avon have in 2002 if the rating were based solely on the firm's coverage ratio? b. What percentage decline in EBIT could Avon have suffered in each year before Avon would have been unable to make its interest p c. Assuming a 35 percent corporate tax rate, and 2002 earnings before interest and taxes of $895 million, by how much did Avon's $60 taxes? d. Answer question (a) and (b) again for 2002 assuming the company had borrowed an additional $3 billion in debt at 8 percent interes the proceeds to shareholders as a special dividend. You may ignore the effect of added interest expense on Avon's balance sheet. M increase in financial leverage? Explain. e. How would you assess Avon's business risk? Setting aside the way the company is financed, how significant are the marketplace ri company's future operating cash flows? What does your assessment of Avon's business risk suggest about the level of financial lev support? f. How big a threat would it be to Avon if the company took on too much debt and had difficulty servicing it? How costly would finan g. Based on your analysis and any other considerations you think relevant, is Avon heavily or modestly indebted? Should the company debt? Why? AVON PRODUCTS 1345 Avenue Of The Americas New York, NY 10105 Ticker: AVP ANNUAL INCOME STATEMENT (MILLIONS, EXCEPT PER SHARE) 2001 2002 2003 Sales Cost of Goods Sold Gross Profit 5,957.8 2,156.9 3,800.9 6,228.3 2,217.6 4,010.7 6,876.0 2,481.8 4,394.2 Selling, General, & Administrative Exp. Operating Income Before Deprec. 2,889.5 911.4 2,979.6 1,031.1 3,213.6 1,180.6 Depreciation,Depletion,& Amortization Operating Profit 109.0 802.4 124.8 906.3 123.5 1,057.1 Interest Expense Non-Operating Income/Expense Special Items Pretax Income 71.1 36.3 (77.9) 689.7 59.7 25.3 (36.3) 835.6 49.0 (0.3) (14.3) 993.5 Total Income Taxes Minority Interest Income Before Extra. Items & Disc. Oper. 240.3 4.5 444.9 292.3 8.7 534.6 318.9 9.8 664.8 Extraordinary Items Discontinued Operations Adjusted Net Income (0.3) 0.0 444.6 0.0 0.0 534.6 0.0 0.0 664.8 Earnings Per Share Basic Including Extra Items & Disc Op 0.9 1.1 1.4 Earnings Per Share Diluted Including Extra Items & Disc Op 0.9 1.1 1.4 EPS Basic from Operations EPS Diluted from Operations Dividends Per Share 1.1 1.0 0.4 1.2 1.2 0.4 1.4 1.4 0.4 473.7 492.1 472.1 490.9 471.1 483.1 Com Shares for Basic EPS Com Shares for Diluted EPS ANNUAL BALANCE SHEET ($ MILLIONS) 2001 2002 2003 ASSETS Cash & Short-Term Investments Net Receivables Inventories Other Current Assets Total Current Assets 508.5 519.5 612.5 248.6 1,889.1 606.8 555.4 614.7 271.3 2,048.2 694.0 599.8 653.4 278.9 2,226.1 Gross Plant, Property & Equipment Accumulated Depreciation Net Plant, Property & Equipment Intangibles Deferred Charges Other Assets 1,552.4 779.7 772.7 0.0 0.0 530.8 1,548.4 779.3 769.1 20.6 0.0 489.6 1,728.9 873.3 855.6 46.2 0.0 434.4 TOTAL ASSETS 3,192.6 3,327.5 3,562.3 LIABILITIES Long Term Debt Due In One Year Notes Payable Accounts Payable Taxes Payable Other Current Liabilities Total Current Liabilities 1.2 87.6 404.1 375.9 592.2 1,461.0 3.1 602.1 379.9 353.0 637.4 1,975.5 4.4 239.7 400.1 321.9 621.6 1,587.7 Long Term Debt Deferred Taxes Investment Tax Credit Minority Interest Other Liabilities 1,236.3 30.6 0.0 29.0 510.8 767.0 35.4 0.0 37.0 640.3 877.7 50.6 0.0 46.0 629.0 TOTAL LIABILITIES 3,267.7 3,455.2 3,191.0 89.1 938.0 899.9 2,002.1 89.6 1,019.5 943.9 2,180.7 90.3 1,188.4 1,473.0 2,380.4 (75.1) (127.7) 371.3 EQUITY Common Stock Capital Surplus Retained Earnings Less: Treasury Stock TOTAL EQUITY TOTAL LIABILITIES & EQUITY 3,192.6 3,327.5 3,562.3 wer the following questions: liabilities-to-assets and times-interest-earned ratios in these years? (Use of financial leverage? Consulting Table 6-5 in the text, what bond rating e been unable to make its interest payments out of operating income? million, by how much did Avon's $60 million interest expense reduce $3 billion in debt at 8 percent interest at the start of the year and distributed expense on Avon's balance sheet. Might shareholders benefit from such an w significant are the marketplace risks Avon faces; how uncertain are the ggest about the level of financial leverage the company can prudently ervicing it? How costly would financial distress be to Avon? Explain. estly indebted? Should the company acquire more debt, or shed existing