Previous work is attached. Need help with Excel calculations for Walmart's dividends.

Analyzing the Dividend Policies of Various Companies

Please recall the company that you selected for the Module 1 SLP. Please review the company?s dividends over the past three years. Then, answer the following questions in Word (except for the Excel portion specifically noted):

What has occurred with company?s dividend payout, dividend yield, and dividend per share over the past three years? Do you have any explanations for what has occurred?

How does your selected company?s dividend payout, dividend yield, and dividend per share compare to other companies in its industry? Has the company?s dividend strategy been similar to other companies in its industry?

You are now to use Excel and plot your selected company?s earnings and dividends over the past three years. Do you notice any patterns?

What is your estimate for your company?s dividend per share next year? Please justify why you made that decision.

Now locate a company that has reduced or eliminated its common stock cash dividend over the past year. Why did the company reduce or eliminate its dividend? What has happened to the company?s stock price over the year?

Running Head: WAL-MART FINANCIAL ANALYSIS Wal-Mart Financial Analysis Course Professor Institution Student Name Date 1 Wal-Mart Financial Analysis 2 Wal-Mart Financial Analysis A) Liquidity ratios: Current Ratio 2014 Current Assets Current Liabilities $ 61,185.00 $ 69,345.00 =0.88 2015 $ 63,278.00 $ 65,272.00 2016 =0.97 $ 60,239.00 $ 64,619.00 =0.93 =0.28 $ 15,770.00 $ 64,619.00 =0.24 Quick Ratio Cash + Accts. Rec. Current Liabilities $ 16,327.00 $ 69,345.00 =0.24 $ 18,137.00 $ 65,272.00 Liquidity ratios measure Wal-Mart's ability to pay the maturing debts i.e. maturing within one year. The current ratio increased from 2014 to 2015 and dropped slightly in 2016. Wal-Mart's average current ratio has been below the industry standard of 1.5 over the three year period. The quick ratio has been at a low of 0.24 - 0.28 over the three year period. The ratios are below the industry standard of 1.0 and SP500 of 0.5. Wal-Mart needs to improve on liquidity ratios. B) Asset Management ratios 2014 2015 2016 Inventory Turnover Cost of Goods Sold Inventory 365,086 44,858 =8.14 360,984 45,141 =8.00 360,512 44,469 =8.11 Total Assets Turnover Sales Total Assets 485651 204751 =2.37191 482130 203706 =2.366793 484604 199581 =2.428107 Fixed Assets Turnover Sales Fixed Assets 485651 143566 =3.382772 482130 140428 =3.43329 484604 139342 =3.477803 485651 6677 =72.73491 482130 6778 =71.1316 484604 5624 =86.16714 360 72.73491 =4.94948 360 71.1316 =5.061042 360 86.16714 =4.177927 Accounts Receivable Turnover Sales Accounts Receivable Accounts Receivable Turn-Days 360 Accts. Rec. Turnover Wal-Mart Financial Analysis 3 Inventory turnover measures how fast Wal-Mart turns inventory into sales within a year. The ratio was above 8.0 times 2014 recording the highest. Assets turnover measures effectiveness of assets to generate sales, while fixed assets turnover measures effectiveness of fixed assets to generate sales. Assets turnover and fixed assets turnover generally improved over the three years. The number of days sales are outstanding is 5 days in 2014, 5 days in 2015 and 4 days in 2016, which marks an improvement. C) Debt Management ratios 2014 2015 2016 EBITDA Coverage EBITDA Interest Payments Times-interest-earned 36,433 2,461 =14.80 33,640 2,548 =13.20 33,093 2,347 =14.10 EBIT Interest Payments 27,147 2,461 =11.03088 24,105 2,548 9.460361 23,201 2,347 =9.885386 122,312 203,706 =0.600434 119,035 199,581 =0.596425 Total debt to total assets Total debt Total Assets 128,496 204,751 =0.62757 2 EBITDA Coverage assesses a company's profitability in relation t the ability to pay interest on debt and other instruments. A bond investor would be interested in this ratio to assess id the company will be able to pay the interest at agreed period. EBITDA Coverage declines from 2014 to 2015 before shooting up again in 2016. EBITDA Coverage of 14 is strong and desirable when lending Wal-Mart of when purchasing debt securities since it is above the industry benchmark of 11.09. Closely related to EBITDA Coverage is Times-interest-earned which measures a company's ability to pay interest on pre-tax basis. Debt to total assets measure what proportion of assets is financed though debt. Wal-Mart's times interest earned declined between 2014 and 2015 and improved slightly in 2016 while debt to total assets declined over the years to 0.59 which is higher than the SP500 benchmark of 0.3. D) Profitability ratios 2014 Return on common equity Net Income Equity Return on total assets 27,147 76,255 =0.356002885 2015 24,105 81,394 =0.296152 2016 23,201 80,546 =0.288047 Wal-Mart Financial Analysis 4 Net Income Net Assets Basic earning power 27,147 76,255 =0.356002885 24,105 81,394 =0.296152 23,201 80,546 =0.288047 Net Income Total Assets Profit margin on sales Net Income Sales 27,147 204,751 =0.132585433 24,105 203,706 =0.118332 23,201 199,581 =0.116249 23,201 482,130 =0.05 27,147 476,294 =0.06 24,105 485,651 =0.05 Profitability ratios measure the company's profitability with respect to equity, net assets, total assets and sales. Return on Equity declined from 2014 to 2016. In investor in common stock would be interested in this ratio to assess earnings per share prospects. Return on assets, basic earnings power and profit margin are on the decline as well over the three years which sends a caution signal to investors of common stock. I would be careful before buying Wal-Mart since the company's profitability is on the decline. I would seek to establish the reasons behind the decline in ratios. E) Market Value ratios 2014 2015 2016 17.9 18.6 13.2 19 15 20.3 3.5 2.7 2.5 2.7 2.7 2.8 11 11.5 7.1 11.5 6.8 12.4 Price/Earnings Price of share/Earnings per share WMT S&P 500 Price/Book Price of share/ Book Value per share WMT S&P 500 Price/Cash Flow Price of share/ Cash flow per share WMT S&P 500 Wal-Mart's market value ratios dropped in 2015 and improved slightly in 2016. All the ratios are below the SP500 benchmark and for that reason I would opt not to buy WalMart's common stock since the price earnings ratio and price/cash flow ratios are quite weak compared to the SP500 benchmark. References Yahoo Finance. (2017, February 18). Wal-Mart Stores, Inc. (WMT). Retrieved February 19, 2017, from Yahoo Finance: https://finance.yahoo.com/quote/WMT?ltr=1 Wal-Mart Financial Analysis 5