I need the attached spreadsheet finished off with the following tabs: Horizontal Analysis, Vertical Analysis, and overall analysis. The work as already been done just the spreadsheet for formula verification the data must all be linked to the attached BS, Income statement and/or statement of cashflow. Please place your answer on the attached spreadsheet. new tabs should be created.

ACT 5140 - Accounting for Decision Makers Walmart Analysis Assignment Directions: Answer all the questions. Please submit your work in Word or PDF formats only. You can submit an Excel file to support calculations, but please \"cut and paste\" your solutions into the Word or PDF file. Be sure to show how you did your calculations. Also, please be sure to include your name at the top of the first page of your file. Part 1 Perform a vertical and horizontal analysis of Wal-Mart Stores, Inc.'s income statements and balance sheets as of January 31, 2015. In performing this analysis, consider any notable trends or changes that you observe that may provide useful information concerning its financial condition. Also use as many years' worth of statements as you feel necessary. You should write up your results in paragraph form. Part 2 Assess Wal-Mart, Stores Inc. concerning liquidity, solvency, profitability, and stock performance as of January 31, 2015. For each area, you should calculate the ratios we discussed in class and provide an analysis of the ratios calculated. I include historical stock price information and outstanding common share information below. Fiscal Year Ended Adjusted Closing Price Common Shares Outstanding (millions) 1/31/2015 $83.94 3,228 1/31/2014 $71.97 3,233 1/31/2013 $65.79 3,314 1/31/2012 $56.32 3,418 WAL MART STORES INC 10-K Income Statement (Amounts in millions except per share data) Fiscal Year Ended January 31, Revenues: Net sales Membership and other income 2015 2014 2013 2012 $482,229 3,422 $473,076 3,218 $465,604 3,047 $443,416 3,093 485,651 476,294 468,651 446,509 Costs and expenses: Cost of sales Operating, selling, general and administrative expenses 365,086 93,418 358,069 91,353 352,297 88,629 334,993 85,025 Operating income 27,147 26,872 27,725 26,491 Interest: Debt Capital leases Interest expense Interest income 2,161 300 2,461 (113) 2,072 263 2,335 (119) 1,977 272 2,249 (186) 2,034 286 2,320 (161) Interest, net 2,348 2,216 2,063 2,159 Income from continuing operations before income taxes 24,799 24,656 25,662 24,332 Provision for income taxes: Current Deferred 8,504 (519) 8,619 (514) 7,976 (18) 6,722 1,202 Total provision for income taxes 7,985 8,105 7,958 7,924 Income from continuing operations Income (Loss) from discontinued operations, net of tax 16,814 285 16,551 144 17,704 52 16,408 (21) Consolidated net income 17,099 16,695 17,756 16,387 (736) (673) (757) (688) $16,363 $16,022 $16,999 $15,699 Net income per common share: Basic income per common share from continuing operations to Walmart Basic income (loss) per common share from discontinued operations $5.01 $0.06 $4.87 $0.03 $5.03 $0.01 $4.55 ($0.01) Basic net income per common share attributable to Walmart $5.07 $4.90 $5.04 $4.54 Diluted income per common share from continuing operations to Walmart Diluted income (loss) per common share from discontinued operations $4.99 $0.06 $4.85 $0.03 $5.01 $0.01 $4.53 ($0.01) Diluted net income per common share $5.05 $4.88 $5.02 $4.52 Weighted-average number of common shares: Basic Diluted Dividends declared per common share 3,230 3,243 $1.92 3,269 3,283 $1.88 3,374 3,389 $1.59 3,460 3,474 $1.46 Consolidated net income attributable to noncontrolling interest Consolidated net income attributable to Walmart WAL MART STORES INC 10-K Balance Sheet (Amounts in millions except per share data) January 31, ASSETS Current assets: Cash and cash equivalents Receivables Inventories Prepaid expenses and other Current assets of discontinued operations 2015 2014 2013 2012 $9,135 6,778 45,141 2,224 0 $7,281 6,677 44,858 1,909 460 $7,781 6,768 43,803 1,551 37 $6,550 5,937 40,714 1,685 89 Total current assets $63,278 $61,185 $59,940 $54,975 Property and equipment, at cost Less accumulated depreciation Property and equipment, net 177,395 (63,115) 114,280 173,089 (57,725) 115,364 165,825 (51,896) 113,929 155,002 (45,399) 109,603 Property under capital lease Less accumulated amortization Property under capital lease, net 5,239 (2,864) 2,375 5,589 (3,046) 2,543 5,899 (3,147) 2,752 5,936 (3,215) 2,721 Goodwill Other assets and deferred charges 18,102 5,671 19,510 6,149 20,497 5,987 20,651 5,456 $203,706 $204,751 $203,105 $193,406 LIABILITIES AND SHAREHOLDERS EQUITY Current liabilities: Short-term borrowings Accounts payable Accrued liabilities Accrued income taxes Long-term debt due within one year Obligations under capital leases due within one year Current liabilities of discontinued operations $1,592 38,410 19,152 1,021 4,810 287 0 $7,670 37,415 18,793 966 4,103 309 89 $6,805 38,080 18,808 2,211 5,587 327 0 $4,047 36,608 18,154 1,164 1,975 326 26 Total current liabilities 65,272 69,345 71,818 62,300 Long-term debt Long-term obligations under capital leases Deferred income taxes and other Redeemable non-controlling interest Commitments and contingencies 41,086 2,606 8,805 0 0 41,771 2,788 8,017 1,491 0 38,394 3,023 7,613 519 0 44,070 3,009 7,862 404 0 Shareholders equity: Common stock Capital in excess of par value Retained earnings Accumulated other comprehensive income (loss) 323 2,462 85,777 (7,168) 323 2,362 76,566 (2,996) 332 3,620 72,978 (587) 342 3,692 68,691 (1,410) Total Walmart shareholders' equity 81,394 76,255 76,343 71,315 Noncontrolling interest 4,543 5,084 5,395 4,446 Total equity 85,937 81,339 81,738 75,761 $203,706 $204,751 $203,105 $193,406 Total assets Total liabilities and shareholders equity WAL MART STORES INC 10-K Statement of Cash Flows (Amounts in millions) Fiscal Year Ended January 31, Cash flows from operating activities: Consolidated net income (Income) Loss from discontinued operations, net of tax 2015 2014 2013 2012 $17,099 (285) $16,695 (144) $17,756 (52) $16,387 21 Income from continuing operations 16,814 16,551 17,704 16,408 Adjustments to reconcile income from continuing operations to net cash provided by operating activities Depreciation and amortization Deferred income taxes Other operating activities 9,173 (503) 785 8,870 (279) 938 8,478 (133) 602 8,106 1,050 468 Changes in certain assets and liabilities, net of effects of acquisitions: Increase in accounts receivable Increase in inventories Increase in accounts payable Increase in accrued liabilities (Decrease) Increase in accrued income taxes (569) (1,229) 2,678 1,249 166 (566) (1,667) 531 103 (1,224) (614) (2,759) 1,061 271 981 (796) (3,727) 2,687 30 29 Net cash provided by operating activities 28,564 23,257 25,591 24,255 Cash flows from investing activities: Payments for property and equipment Proceeds from disposal of property and equipment Proceeds from disposal of certain operations Other investing activities (12,174) 570 671 (192) (13,115) 727 0 (138) (12,898) 532 0 (271) (13,510) 580 0 (3,679) Net cash used in investing activities (11,125) (12,526) (12,637) (16,609) Cash flows from financing activities: Net change in short-term borrowings Proceeds from issuance of long-term debt Payment of long-term debt Dividends paid Purchase of Company stock Dividends paid to noncontrolling interest Purchase of noncontrolling interest Other financing activities (6,288) 5,174 (3,904) (6,185) (1,015) (600) (1,844) (409) 911 7,072 (4,968) (6,139) (6,683) (426) (296) (260) 2,754 211 (1,478) (5,361) (7,600) (282) (132) (58) 3,019 5,050 (4,584) (5,048) (6,298) (526) 0 (71) Net cash used in financing activities (15,071) (10,789) (11,946) (8,458) Effect of exchange rates on cash (514) (442) 223 (33) Net (decrease) increase in cash and cash equivalents Cash and cash equivalents at beginning of year 1,854 7,281 (500) 7,781 1,231 6,550 (845) 7,395 Cash and cash equivalents at end of year $9,135 $7,281 $7,781 $6,550 Supplemental disclosure of cash flow information Income tax paid Interest paid 8,169 2,433 8,641 2,362 7,304 2,262 5,889 2,346 WAL MART STORES INC Ratio Analysis FORMULA 1. Liquidity Ratios Current Ratio Working capital Acid-test ratio Inventory Turnover Days sales in inventory Accounts receivable turnover Days sales in receivables Free cash flow Average inventory Average A/R 2.Solvency Ratio Debt Equity Ratio Interest Coverage Long Term Liabilities 3. Profitability Ratios Asset Turnover Return on sales Gross margin % Return on assets Return on equity Average interest rate Average total assets Income tax rate Net of tax interest expense Adjusted net income Average equity 2015 Current Assets/Current liabilities Current Assets-Current Liabilities Quick Assets/Current liabilities Cost of Goods Sold/Average Inventory (Ending Inventory/Cost of Goods Sold)*365 0.97 -1994.00 0.24 8.11 45.13 Net Credit Sales/Average Accounts Receivable (Accounts Receivable/Net Credit Sales)*365 As per Annual Report (Beginning Inventory + Ending Inventory)/2 (Beginning Accounts Receivables+ Ending Accounts Receivables)/2 71.68 5.13 16400.00 44999.50 Total Liabilities/Total Equity Income before Interest and Taxes/Interest Expenses As per Balance Sheet 1.45 0.33 43692.00 Total Revenue/Total assets Operating Income/Net Sales Gross Margin/Sales Net Income/Average Total Assets Net Income/ Shareholders equity As per Annual Report 2.38 5.63% 24.83% 8.37% 21.01% 3.80% 204228.5 0 32.20% 2348.00 16363.00 78824.50 120590.5 0 Beginning Assets+Ending Assets)/2 As per Annual Report As per Balance Sheet As per Income Statement (Beginning Equity+Ending Equity)/2 (Beginning Total Liabilities+Ending Total Average total liabilities Liabilities)/2 4. Stock Performance Analysis 6727.50 Book value per common share Earnings per share (basic) Earnings per share (diluted) P/E Ratio Dividend yield Dividend payout Book value of equity Common shares outstanding Adjusted closing price Dividends per share 5. DuPont Analysis DuPont analysis Total Common Shareholder's Equity/ No. Of Common Shares A per Income Statement A per Income Statement Market Price per Share of Common Stock/ EPS Cash Dividend per Share/ Market Value per Share Dividend per Share/Earningss per Share Total Euqity of Common shareholders/ Number of Common Shares As per Annual Report 25.21 $5.07 $5.05 16.56 0.02 37.87% 25.20 3230.00 As per Information Provided As per Income Statement 83.94 1.92 Profit Margin*Total Assets Turnover* Financial Leverage 21.01% Ratio Analysis of Wal-Mart Stores Inc. Liquidity Ratios Current ratio The current ratio of the company is 0.97. Current ratio indicates the ability of the company to pay its short term obligations. Ideal current ratio is current ratio of 2. The current ratio is low and shows that company will find it difficult to pay its short term liabilities. Working Capital Working capital measures the overall health of the company. It is calculated by deducting current liabilities from current assets. The working capital of the company is negative at -$1,994 million. It indicates that the financial health of the company is not good. Acid-Test Ratio Acid test ratio of the company stood at 0.24. The ratio is low and shows that that the company will not be to meet its short term commitments from its quick assets. Inventory Turnover This ratio tells how effectively the company has managed its inventory. Inventory turnover ratio of the company is 8.11. The ratio for the company is good. Days Sales in Inventory Days sales in inventory measures the number of days the inventories are kept before being sold. The ratio is 5.13 and indicates that company sells its inventory quickly. Accounts Receivable Turnover It calculates the number of times the company has converted its accounts receivables in cash. The ratio of the company is 71.68 which show efficiency of the company in managing its accounts receivable. Days Sales in Receivables This ratio calculates number of days credit sales is outstanding. The ratio for the company stood at 5.13 in which is good. Free Cash Flow These are the funds that are available with the company for investment in their expansion and growth plans. The free cash flow for the company is $16,400 which is quiet high. Solvency Ratios Debt-to-Equity Ratio This ratio measures the percentage of capital the company is having financed from creditors and investors in comparison to its common shareholder's equity. The ratio for the company is 1.45 which indicates that the company has taken low debts. Interest Coverage The ratio of the company is 0.33 for the year 2015. The low ratio shows that the company is making less payment of interests. This ratio calculates the percentage amount of income that the company will use to pay its interest expenses. Profitability Ratios Assets Turnover This ratio measures how efficiently the company has used its assets in generating sales. The ratio for the company is 2.38. The ratio of the company is low and indicates an inefficient utilization of its assets in generating sales. Return on Sales This ratio calculates the percentage of operating income of the company in relation to its sales. The ratio for the company is 5.63% and it is low. Gross Margin It calculates the percentage of gross profit the company has earned over its sales. The ratio of the company is 24.83%. The gross margin of the company is good. Return on Assets This ratio for the company tells how efficiently the company has used its assets in generating profit. It is a profitability ratio that measures the net income produced by the assets during the year. The ratio of the company is 8.37%, which shows that the company is not managing its assets efficiently in generation of net income. Return on Equity This ratio for the company is 21.07% this ratio tells how much profit the company has earned from each dollar of shareholder's equity. The return on equity of the company is less. Book Value per Share It calculates the value of shares of the company in relation to the common shareholder's equty. The ratio of the company is 21.25 which cannot be considered as good.bad on its equity available to common shareholders. The book value per share stood at 25.21. Earnings per Share The earnings per share for 2015 is $5.07 per share. This ratio calculates the income earned per share of common stock. The ratio of the company is good.. Price Earnings The ratio for the year is 16.56. This ratio calculates the market price per share in relation to the earnings per share. Dividend Yield Dividend yield measures the dividend distributed to shareholders with relation to the market value of the share. The dividend yield of the company is too low at .02. Dividend Payout It measures the percentage of net income that the company has distributed in form of dividend to its shareholders. The ratio of the company stood at 37.87 which is high. WAL MART STORES INC Vertical Analysis of Income Statement (Amounts in millions except per share data) Fiscal Year Ended January 31, Revenues: Net sales Membership and other income Costs and expenses: Cost of sales Operating, selling, general and administrative expenses Operating income Interest: Debt Capital leases Interest expense Interest income Interest, net Income from continuing operations before income taxes Provision for income taxes: Current Deferred Total provision for income taxes Income from continuing operations 2015 Percent $4,82,229 99.30% 3,422 0.70% 4,85,651 100.00% 3,65,086 93,418 75.17% 19.24% 27,147 5.59% 2,161 300 2,461 -113 2,348 24,799 0.44% 0.06% 0.51% -0.02% 0.48% 5.11% 8,504 -519 7,985 16,814 1.75% -0.11% 1.64% 3.46% Income (Loss) from discontinued operations, net of tax Consolidated net income Consolidated net income attributable to non controlling interest 285 17,099 -736 0.06% 3.52% -0.15% Consolidated net income attributable to Walmart $16,363 3.37% WAL MART STORES INC Horizontal Analysis of Income Statement Fiscal Year Ended January 31, Revenues: Net sales Membership and other income Costs and expenses: Cost of sales Operating, selling, general and administrative expenses Operating income Interest: Debt Capital leases Interest expense Interest income Interest, net Income from continuing operations before income taxes Provision for income taxes: Current Deferred Total provision for income taxes Income from continuing operations Income (Loss) from discontinued operations, net of tax Consolidated net income Consolidated net income attributable to non controlling interest Consolidated net income attributable to Walmart 2015 Change Percent $4,82,229 $4,73,076 3,422 3,218 4,85,651 4,76,294 $9,153 $204 $9,357 1.93% 6.34% 1.96% 3,65,086 93,418 27,147 3,58,069 91,353 26,872 $7,017 $2,065 $275 1.96% 2.26% 1.02% 2,161 300 2,461 -113 2,348 24,799 2,072 263 2,335 -119 2,216 24,656 $89 $37 $126 $6 $132 $143 4.30% 14.07% 5.40% -5.04% 5.96% 0.58% 8,504 -519 7,985 16,814 285 17,099 8,619 -514 8,105 16,551 144 16,695 -$115 -$5 -$120 $263 $141 $404 -1.33% 0.97% -1.48% 1.59% 97.92% 2.42% -736 $16,363 -673 $16,022 -$63 $341 9.36% 2.13% WAL MART STORES INC Vertical Analysis of Balance Sheet (Amounts in millions except per share data) January 31, 2015 ASSETS 2014 Percent Current assets: Cash and cash equivalents Receivables Inventories Prepaid expenses and other Current assets of discontinued operations Total current assets Property and equipment, at cost Less accumulated depreciation Property and equipment, net Property under capital lease Less accumulated amortization Property under capital lease, net Goodwill Other assets and deferred charges Total assets LIABILITIES AND SHAREHOLDERS EQUITY Current liabilities: Short-term borrowings Accounts payable Accrued liabilities Accrued income taxes Long-term debt due within one year Obligations under capital leases due within one year Current liabilities of discontinued operations Total current liabilities Long-term debt Long-term obligations under capital leases Deferred income taxes and other Redeemable non-controlling interest Commitments and contingencies Shareholder's equity: Common stock Capital in excess of par value Retained earnings Accumulated other comprehensive income (loss) Total Walmart shareholders' equity Non controlling interest Total equity $9,135 4.48% 6,778 3.33% 45,141 22.16% 2,224 1.09% 0 0.00% $63,278 31.06% 1,77,395 87.08% -63,115 -30.98% 1,14,280 56.10% 5,239 2.57% -2,864 -1.41% 2,375 1.17% 18,102 8.89% 5,671 2.78% $2,03,706 100.00% $1,592 38,410 19,152 1,021 4,810 287 0 65,272 41,086 2,606 8,805 0 0 0.78% 18.86% 9.40% 0.50% 2.36% 0.14% 0.00% 32.04% 20.17% 1.28% 4.32% 0.00% 0.00% 323 2,462 85,777 -7,168 81,394 4,543 85,937 0.16% 1.21% 42.11% -3.52% 39.96% 2.23% 42.19% Total liabilities and shareholder's equity $2,03,706 100.00% WAL MART STORES INC Horizontal Analysis of Balance Sheet (Amounts in millions except per share data) January 31, ASSETS Current assets: Cash and cash equivalents Receivables Inventories Prepaid expenses and other Current assets of discontinued operations Total current assets Property and equipment, at cost Less accumulated depreciation Property and equipment, net Property under capital lease Less accumulated amortization Property under capital lease, net Goodwill Other assets and deferred charges Total assets LIABILITIES AND SHAREHOLDERS EQUITY Current liabilities: Short-term borrowings Accounts payable Accrued liabilities Accrued income taxes Long-term debt due within one year Obligations under capital leases due within one year Current liabilities of discontinued operations 2015 2014 Chang e $9,135 6,778 45,141 2,224 $7,281 6,677 44,858 1,909 $1,854 $101 $283 $315 0 $63,278 1,77,395 -63,115 1,14,280 5,239 -2,864 2,375 18,102 5,671 $2,03,70 6 460 $61,185 1,73,089 -57,725 1,15,364 5,589 -3,046 2,543 19,510 6,149 $2,04,75 1 -$460 $2,093 $4,306 -$5,390 -$1,084 -$350 $182 -$168 -$1,408 -$478 25.46% 1.51% 0.63% 16.50% 100.00 % 3.42% 2.49% 9.34% -0.94% -6.26% -5.98% -6.61% -7.22% -7.77% -$1,045 -0.51% $1,592 38,410 19,152 1,021 4,810 $7,670 37,415 18,793 966 4,103 -$6,078 $995 $359 $55 $707 -79.24% 2.66% 1.91% 5.69% 17.23% 287 309 -$22 0 89 -$89 -7.12% 100.00 % Percent Total current liabilities Long-term debt Long-term obligations under capital leases Deferred income taxes and other Redeemable non-controlling interest Commitments and contingencies Shareholder's equity: Common stock Capital in excess of par value Retained earnings Accumulated other comprehensive income (loss) Total Walmart shareholders' equity Non controlling interest Total equity Total liabilities and shareholder's equity 65,272 41,086 2,606 8,805 69,345 41,771 2,788 8,017 -$4,073 -$685 -$182 $788 0 0 1,491 0 -$1,491 $0 323 2,462 85,777 323 2,362 76,566 $0 $100 $9,211 -7,168 81,394 4,543 85,937 $2,03,70 6 -2,996 76,255 5,084 81,339 $2,04,75 1 -5.87% -1.64% -6.53% 9.83% 100.00 % 0.00% 4.23% 12.03% 139.25 -$4,172 % $5,139 6.74% -$541 -10.64% $4,598 5.65% -$1,045 -0.51% Vertical and Horizontal Analysis of Income Statement and Balance Sheet Income Statement The overall revenue of the company has increased by 9153 million or 1.96% during the year 2015. The net income of the company has increased by $404 million in the year 2015 which shows an increase of 2.42%. Net income for the company is $17,099 million for the year 2015 as compared to $16,695 million for the year 2014. It shows a positive growth in the income as well as revenue of the company. The company has seen an increase in the interest income during year 2015 up to the extent of 5.96%. The interest increased by $132 million during year 2015. The income tax expense for the company has declined by 1.48% irrespective of high income of the company. Balance Sheet Analysis of balance sheet reveals that the company's cash and cash equivalents have increased by $1,854 million during year 2015 which is an of 25.46% duo which the current assets of the company have increased. The company has also reduced its current liabilities. The company has made a great reduction in its short-term borrowings in the year 2015. It has paid major part of its short term borrowings. They have come down by $6,078 million or 79.24% The company has also paid off its long term liabilities, The long term liabilities of the company have decreased by $685 million. Despite of payment for short-term liabilities and long-term liabilities the company has been able to increase its cash; it indicates a big inflow of cash from issuing shares. During the year 2015 the company's common shareholders' equity has increased by 6.74%. It means the company has made a change in its capital structure by increasing its owners equity and by paying off short term and long term liabilities