USA: +1(669)289-8683 
Sign in
To get started:
- Review the attached PPT and WORD files on analysis of financial data.
- Open the attached Excel document. Rename and save the document to your computer.
The Balance Sheet and Income Statement for Wal-Mart, Inc. are provided in the Walmart excel file, along with the worksheet you will complete. On the Ratio Analysis worksheet, complete a ratio analysis of Walmart based on the five categories of analysis discussed in the PPT lesson. In EXCEL format, calculate at least two ratios from each category of Liquidity, Activity, Debt, Profitability, and Market, using the figures in the Jan 2011 and Jan 2012 columns. This is a ratio calculation assignment- no written analysis. For the Market/Book and Price/Book Ratios, you will need to find the current stock price for WalMart online. If you do not have MS Excel, this assignment can be done in OpenOffice.
Analyzing Financial Data
For the remainder of the lesson, you will be concentrating on learning some basic skills in financial calculations. It is important in finance to have an understanding of financial statements and how they are used to interpret the financial performance of corporations. We will review the various financial statements, and discuss how ratios are used to examine the different areas of a company’s financial operations. You will also learn about cash flow and the importance of managing the company’s cash operations to the overall financial planning.
Financial Statements and Analysis
In finance, it is key to have an understanding of financial statements from the perspective of being able to read them and knowing what goes where. Being able to compile them is, well, better left to the accountants. You will learn about that in another course! For our purposes, it will be enough for you to recognize the parts of each statement and be able to use them for analysis.
Four Key Financial Statements
Each corporation is required to file with the Securities and Exchange Commission (SEC) four key financial statements:
1. Income statement: provides a financial summary of the company’s operating results over a specified period of time.
2. Balance sheet: provides a summary statement of the company’s financial position at a given point in time.
3. Statement of Stockholders’ Equity: shows all the equity account transactions in a given year.
4. Statement of Cash Flows: summary of cash flows of a given period of time.
These financial statements are also used in order to do ratio analysis to examine the performance of a firm.
Using Financial Ratios
There are several reasons for doing ratio analysis. For example, if you are considering two investments between two companies, one that is a large company with a net income of $2 million in the previous year and the other a smaller startup with a net income of $200K; how do you decide which is the better investment?
It is not necessarily the larger company with the larger net income. There may be other factors that are not apparent without analysis of the financial status of each of the companies.
There are five general areas that you would want to explore. These are liquidity, activity, debt, profitability, and market ratios.
Liquidity Ratios
The first area to explore when beginning your analysis is the company’s liquidity. Liquidity is the company’s ability to pay its short-term bills. If a firm is having liquidity problems, it can be an indicator of cash flow problems that can signal larger internal difficulties. There are two primary measures of liquidity: the current ratio and the quick or acid-test ratio.
We will use the example of Pampered Pet’s balance sheet and income statement as we proceed through the following examples.
Current ratio
Current assets/Current liabilities
$72,000/$69,000 = 1.04
Quick ratio
Current assets-inventory/Current liabilities
$72,000-45,500/$69,000 =0.38
When looking at ratios, you should always take them as a comparison either with an industry benchmark or by doing a trend analysis over time. You would never want to look at a ratio as an isolated indicator. For the current ratio, the normal acceptable ratio is 2.0 and for the quick it is 1.0, but this can vary depending on the industry. By looking at our example, we can see that Pampered Pets does not have enough in assets to cover current liabilities by 2, in fact, it just barely has enough to cover its current liabilities. This could be a sign to worry. We will need to reserve judgment, however, until we complete our analysis.
Critical thought question: Why would the quick ratio be a truer measure of a firm’s ability to pay short term debts?
Activity Ratios
The second area of analysis is activity. Activity is a measure of how quickly the firm’s current assets are converted into cash. For example, inventory turnover is a measure of how quickly inventory is sold off or turned into cash. There are three other measures we will cover as well, and they are average collection period, and total asset turnover.
|
Inventory Turnover |
COGS /Average Inventory |
$106,000/ $45,500=2.33 (avg. already calculated) |
|
Avg. Collection Period |
Accts receivable/ (Annual sales/365) |
$25,000/(160,000/365) |
|
Total Asset Turnover |
Sales/ Total Assets |
$160,000/ $150,000=1.07 |
· Inventory turnover: measures the liquidity of the firm’s inventory. Useful only when compared to industry averages or past inventory turnovers
· Average collection period: useful when evaluating a firm’s credit policies
· Total asset turnover: indicates how efficiently the firm used its assets to generate sales during the year. The higher the better.
The inventory turnover for Pampered Pets could indicate a problem. The store may want to consider branching out into something less specialized such as fish that would generate more cash flow. The average collection period must be looked at in context with the collection policies of the store. If the credit policy for vendors is 30 days, then the company is not doing a good job of collecting receivables. However, if it is 60 days, then this ratio is fine. The total asset turnover indicates that the company is using its overall assets efficiently to generate sales.
Debt Ratio
s
The next area to consider is debt. You should be concerned with whether the firm is able to meet long-term financial obligations. There are two basic categories of debt measures. First, you want to know the degree to which the company is in debt with relation to other items on the balance sheet. Then you want to know about its ability to pay the interest on those debts.
| Debt Ratio |
Total liabilities/ Total assets |
$91,950/ $150,000=61.3% |
|
Times Interest Earned Ratio |
EBIT/ Interest expense |
$17,000/ 6,100=2.79 |
Although Pampered Pets is doing well to stay away from financing too much with debt, the loans they do have are generating quite a bit of interest. The earnings or profits before paying the interest expense on debt should, by rule of thumb, be three times the interest owed at a minimum. So, you can see here that Pampered Pets is not quite at that mark since its ratio is 2.79. They should consider raising more capital to expand operations into another product line that will increase profits and cash flow.
Profitability Ratios
The following ratios are used to measure how well management is utilizing company resources to earn a return on the funds invested by various groups.
Gross Profit Margin
(Sales-COGS)/ Sales
($160,000-106,000)/ $160,000 =33.76%
Operating Profit Margin
EBIT or Operating Income/
Sales
$17,000/160,000=10.63%
Net Profit Margin
Net Income/Sales
$6,540/160,000=4.08%
Earnings per Share
Net Income/Shares Outstanding
$6,540/3,000=$2.18
Return on Total Assets
Net Income/ Total Assets
$6,540/150,000=4.36%
Return on Equity
Net Income/Shareholder Equity
$6,540/31,500=20.76%
Despite the liquidity problems identified earlier, which could be explained by a small business, Pampered Pets seems to be doing quite well from a profitability standpoint. For a small business owner, these ratios are indicative of stability. It seems that the company is supporting growth through equity instead of debt as well as indicated by the ROE ratio.
Market Ratios
The last area is the market ratios. This set measures how well the firm is doing in terms of the stock price and risk and return. The first ratio in this set is the Price/Earnings or PE Ratio. This ratio gives an idea of the owner’s opinion of the share value. This ratio indicates a degree of confidence from investors in the company. The other is the Market/Book or M/B Ratio which assesses investor’s perspective on a company’s performance.
|
Price Earnings Ratio |
Market price per share common stock/ EPS |
$25/$2.18 = 11.47 |
|
Market/Book Ratio |
Market price per share common stock/ (Shareholder Equity/shares outstanding) |
$25/($31,500/3000) =2.28 |
Generally speaking, Pampered Pets is in good shape. According to the PE Ratio, the company’s stock was selling for $25 per share and investors were paying $11.47 for every $1.00 of earnings. On the books, investors are paying $2.38 for each $1.00 of book value. This is a good indicator because investors are willing to pay more than book value for the stock, so the company is being viewed favorably by investors.
A Complete Ratio Analysis
So, what have we learned? The five measures of performance in a ratio analysis are liquidity, activity, debt, profitability, and market. Remember that just calculating any of these ratios on their own will do you no good, as we learned when we started off calculating the liquidity ratios on Pampered Pets. You must compare your findings with that of the industry and with previous calculations from previous years to get an idea of how the company is truly performing. Then, and only then, will you have done a complete ratio analysis.
Summary
This has been a lesson full of key concepts. You began by reviewing some accounting principles with a recap of the important financial statements and how they are used to perform ratio analysis for a company. You learned about the five areas of ratio analysis: liquidity, activity, debt, profitability, and market ratios. Each area tells us a little something about the performance of a company. We worked through the calculations of several different ratios in each category by looking at a company called Pampered Pets.
Balance Sheet
Sample Accounts
Cash Accounts Payable
Accounts Receivable Notes Payable
Inventories Accruals
Total Current Assets Total Current Liabilities
Land and Buildings Preferred Stock
Machinery & Equipment Common Stock
Vehicles Retained Earnings
Income Statement
Sample Accounts
Sales
COGS
Operating Expenses
Selling Expenses
Interest Expense
Earnings before Interest & Taxes (EBIT)
Net Income
Analyzing Financial Data
Ratio Analysis
Lesson Components
Four Key Financial Statements
Ratio Overview
Ratio Categories
Liquidity
Activity
Debt
Profitability
Market
Practice
Four Key Financial Statements
Each corporation is required to file with the Securities and Exchange Commission (SEC) four key financial statements:
Income statement:
provides a financial summary of the company’s operating results over a specified period of time.
Balance sheet:
provides a summary statement of the company’s financial position at a given point in time.
Statement of Stockholders’ Equity:
shows all the equity account transactions in a given year.
Statement of Cash Flows:
summary of cash flows of a given period of time.
*
Learning Statements
These financial statements are also used in order to do ratio analysis to examine the performance of a firm.
Income Statement
Sample Accounts
Sales
COGS
Operating Expenses
Selling Expenses
Interest Expense
Earnings before Interest & Taxes (EBIT)
Net Income
Balance Sheet
Sample Accounts
Cash Accounts Payable
Accounts Receivable Notes Payable
Inventories Accruals
Total Current Assets Total Current Liability
Land and Buildings Preferred Stock
Machinery & Equipment Common Stock
Vehicles Retained Earnings
Ratio Overview
Many internal and external stakeh0lders use ratios
A ratio is not enough information on its own.
Several limitations exist to ratio analysis
Seasonality
Inflation
Summarization
Asset valuation
Five Categories of Ratios
Liquidity
Activity
Debt
Profitability
Market
Liquidity Ratios
Liquidity is the company’s ability to pay its short-term bills
Current ratio
Current assets/Current liabilities $72,000/$69,000 = ???
Quick ratio
Current assets-inventory/Current liabilities $72,000-45,500/$69,000 =???
Activity Ratios
Activity is a measure of how quickly the firm’s current assets are converted into cash.
Inventory Turnover
COGS /Avg Inventory $106,000/ $45,500=???
Average Collection Period
Accts rec/(Annual sales/365) $25,000/(160,000/365)=???
Total Asset Turnover
Sales/ Total Assets $160,000 $150,000=???
Debt Ratios
Debt Ratio
Total liabilities/ Total assets $91,950/ $150,000 =???
Times Interest Earned Ratio
EBIT/ Interest expense $17,000/ 6,100 =???
Used to determine whether the firm is able to meet long-term financial obligations.
Profitability Ratios
Gross Profit Margin
(Sales-COGS)/ Sales ($160,000-106,000)/$160,000 =??
Operating Profit Margin
EBIT or Operating Income/Sales $17,000/160,000=??
Net Profit Margin
Net Income/Sales $6,540/160,000=??
Earnings per Share
Net Income/Shares Outstanding $6,540/3,000=??
Return on Total Assets
Net Income/ Total Asset $6,540/150,000=??
Return on Equity
Net Income/Shareholder Equity $6,540/31,500=??
Measure how well management is utilizing company resources to earn a return on the funds invested by various groups
Market Ratios
Price Earnings Ratio
Market price per share common stock/ EPS
$25/$2.18 = ???
Market/Book Ratio
Market price per share common stock/(Shareholder Equity/shares outstanding)
$25/($31,500/3000) =???
This set measures how well the firm is doing in terms of the stock price and risk and return
Check Your Understanding
Which of the following measure a company’s liquidity without considering inventory?
a. Current ratio
b. Rapid test ratio
c. Quick ratio
d. Inventory ratio
Walmart Income Statement
| Wal-Mart Stores Inc., | C | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| USD $ in millions | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 12 months ended | Jan 31, | 201 | Jan 31, | 2011 | Jan 31, 2010 | Jan 31, 2009 | Jan 31, 2008 | Jan 31, 2007 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Net sales | 443,854 | 418,952 | 405,046 | 401,244 | 374,5 | 26 | 344,9 | 92 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Cost of sales | -335,127 | – | 315 | -304,657 | -306,158 | -286,515 | -264,152 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Gross profit | 108,727 | 103,665 | 100,3 | 89 | 95,086 | 88,011 | 80,840 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Membership and other income | 3,096 | 2,897 | 3,168 | 4,363 | 4,273 | 3,658 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Operating, selling, general and administrative expenses | -85,265 | -81,020 | -79 | -76,651 | -70 | -64,001 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Operating income | 26,558 | 25,542 | 23,950 | 22,798 | 21,996 | 20,497 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Interest (debt and capital leases) | -2,322 | -2,205 | -2,065 | -2,184 | -2,103 | -1,809 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Interest income | 162 | 181 | 284 | 305 | 280 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Interest, net | – | 2,160 | -2,004 | -1,884 | -1,900 | -1,798 | -1,529 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Income from continuing operations | 24,398 | 23,538 | 22,066 | 20,898 | 20,198 | 18,968 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Provision for income taxes | -7,944 | -7,579 | -7,139 | -7,145 | -6,908 | -6,365 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 16,454 | 15,959 | 14,927 | 13,753 | 13,290 | 12,603 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Income (loss) from discontinued operations, net of tax | -67 | 1,034 | 146 | -153 | -894 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Consolidated net income | 16,387 | 16,993 | 14,848 | 13,899 | 13,137 | 11,709 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Consolidated net income attributable to noncontrolling interest | -688 | -604 | -513 | -499 | -406 | -425 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Consolidated net income attributable to Walmart | 15,699 | 16,389 | 14,335 | 13,400 | 12,731 | 11,284 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Source: Wal-Mart Stores Inc., Annual Reports | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Stock Analysis on Net (www.stock-analysis-on.net) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Copyright © | 2012 | T |
Walmart Balance Sheet
| Wal-Mart Stores Inc., Consolidated Statement of Financial Position | ||||||||||||||||
| ASSETS | Jan 31, 2012 | |||||||||||||||
| Cash and cash equivalents | 6,550 | 7,395 | 7,907 | 7,275 | 5,569 | 7,373 | ||||||||||
| Receivables, net | 5,937 | 5,089 | 4,144 | 3,905 | 3,654 | 2,840 | ||||||||||
| Inventories | 40,714 | 36,318 | 33,160 | 34,511 | 35,180 | 33,685 | ||||||||||
| Prepaid expenses and other | 1,685 | 2,960 | 2,980 | 3,063 | 3,182 | 2,690 | ||||||||||
| Current assets | 131 | 140 | 195 | – | ||||||||||||
| 54,975 | 51,893 | 48,331 | 48,949 | 47 | 46,588 | |||||||||||
| Property and equipment, net | 109,603 | 105,098 | 99,544 | 92,856 | 93,875 | 85,390 | ||||||||||
| Property under capital leases, net | 2,721 | 2,780 | 2,763 | 2,797 | 3,142 | 3,050 | ||||||||||
| 112,324 | 107,878 | 102, | 307 | 95,653 | 97,017 | 88,440 | ||||||||||
| Goodwill | 20,651 | 16,763 | 16,126 | 15,260 | 16,071 | 13,759 | ||||||||||
| Other | 5,456 | 4,129 | 3,942 | 3,567 | 2,841 | 2,406 | ||||||||||
| Non-current assets | 138,431 | 128,770 | 122,375 | 114,480 | 115,929 | 104,605 | ||||||||||
| Total assets | 193,406 | 180,663 | 170, | 706 | 163,429 | 163,514 | 151,193 | |||||||||
| LIABILITIES | ||||||||||||||||
| Short-term borrowings | 4,047 | 1,031 | 523 | 1,506 | 5,040 | 2,570 | ||||||||||
| Accounts payable | 36,608 | 33,557 | 30,451 | 28,849 | 30,370 | 28,090 | ||||||||||
| Accrued wages and benefits | 5,895 | 5,986 | 5,577 | 5,247 | 5,347 | |||||||||||
| Self-insurance | 3,638 | 3,447 | 3,224 | 3,108 | 2,907 | 2,954 | ||||||||||
| 9,427 | 9,359 | 9,524 | 7,645 | 6,374 | ||||||||||||
| Accrued liabilities | 18,154 | 18,701 | 18,734 | 18,112 | 15,799 | 14,675 | ||||||||||
| Accrued income taxes | 1,164 | 157 | 1,365 | 677 | 1,016 | |||||||||||
| Long-term debt due within one year | 1,975 | 4,655 | 4,050 | 5,848 | 5,913 | 5,428 | ||||||||||
| Obligations under capital leases due within one year | 326 | 336 | 346 | 316 | 285 | |||||||||||
| Current liabilities | 83 | |||||||||||||||
| 62,300 | 58,484 | 55,561 | 55,390 | 58,454 | 51,754 | |||||||||||
| Long-term debt, excluding due within one year | 44,070 | 40,692 | 33,231 | 31,349 | 29,799 | 27,222 | ||||||||||
| Long-term obligations under capital leases | 3,009 | 3,150 | 3,170 | 3,200 | 3,603 | 3,513 | ||||||||||
| Deferred income taxes and other | 7,862 | 6,682 | 5,508 | 6,014 | 5,111 | 4,971 | ||||||||||
| Non-current liabilities | 54,941 | 50,524 | 41,909 | 40,563 | 38,513 | 35,706 | ||||||||||
| Total liabilities | 117,241 | 109,008 | 97,470 | 95,953 | 96,967 | 87,460 | ||||||||||
| STOCKHOLDERS EQUITY | ||||||||||||||||
| Redeemable noncontrolling interest | 404 | 408 | 397 | |||||||||||||
| Preferred stock ($0.10 par value; none issued) | ||||||||||||||||
| Common stock ($0.10 par value) | 342 | 352 | 378 | 393 | 413 | |||||||||||
| Capital in excess of par value | 3,692 | 3,577 | 3,803 | 3,920 | 3,028 | 2,834 | ||||||||||
| Retained earnings | 68,691 | 63,967 | 66,638 | 63,660 | 57,319 | 55,818 | ||||||||||
| Accumulated other comprehensive income (loss) | -1,410 | 646 | -2,688 | 3,864 | 2,508 | |||||||||||
| Walmart shareholders’ equity | 71,315 | 68,542 | 70,749 | 65,285 | 64,608 | 61,573 | ||||||||||
| Noncontrolling interest | 4,446 | 2,705 | 2,180 | 1,794 | 1,939 | |||||||||||
| Total equity | 75,761 | 71,247 | 72,929 | 67,079 | 66,547 | 63,733 | ||||||||||
| Total liabilities and equity | 170,706 |
Ratio Analysis
| Instructions: You will solve 10 ratios for the year 2011 and 10 for the year 2012, using the data available on the Walmart Income Statement and the Walmart Balance Sheet included in this file. Step 1: Provide the formula for the ratio in text format (T) in Column B Step 2: Provide the formula for the ratio in excel/computation (C) format in Column D and Column F |
||
| Wal-Mart Ratio Analysis | ||
| Ratio (text) | Ratio (calculation) | |
| Liquidity Ratios (solve both) | Click on .89 for required excel format | |
| Current Ratio | Current assets / Current liabilities | 0.89 |
| Quick Ratio | ||
| Activity Ratios (choose 2) | ||
| Total Asset Turnover | ||
| Inventory Turnover | ||
| Average Collection Period | ||
| Debt Ratio | ||
| Debt-Equity Ratio | ||
| Times Interest earned | ||
| Profitability Ratios (choose 2) | ||
| Gross Profit Margin | ||
| Operating Profit Margin | ||
| Net Profit Margin | ||
| Earnings per Share* | ||
| Use for PE below | ||
| Return on Total Assets | ||
| Return on Equity | ||
| Market Ratios (solve both) | ||
| PE Ratio | ||
| MB Ratio | ||
| * To calculate Outstanding Shares: Divide the total cost of common stock by the cost per share. |
