Gap, Inc. is a leading global specialty apparel company with 3,263 company-operated and franchise store locations in 2012. The company was established in 1969 and has severalwell-known brand names such as Gap, Banana Republic, and Old navy. Excerpts from the company'sannual report for the fiscal year ended January 28, 2012. Gap paid cash dividends of $.45 per common share in fiscal 2012 and an average of 529 million shares were outstanding during the year
Assume that gap has no stock options or convertible securities. The company's market price on January 28 2012 was $18.93 per share. Compute the following financial ratios for fiscal 2012:
Current ratio
Total debt to equity
Gross profit rate
Return on sales
Return on stockholders equity
Earnings per share
Price earnings
Dividend yield
Dividend payout
Income statement for the year ended January 28, 2012
|
Net sales
|
$14,549
|
|
|
Cost of sales
|
9,275
|
|
|
Gross profit
|
$5,274
|
|
|
Operating expenses
|
3,3836
|
|
|
Operating income
|
1,438
|
|
|
Interest expenses, net
|
69
|
|
|
Income before income taxes
|
$1,369
|
|
|
Income taxes
|
536
|
|
|
Net income
|
$833
|
|
|
|
|
|
|
Balance sheet
|
January 8, 2012
|
January 29, 2011
|
|
Assets
|
|
|
|
Cash and cash equivalents
|
$1,885
|
$1,561
|
|
Inventories
|
1,615
|
1,620
|
|
Other current assets (summarized")
|
809
|
745
|
|
Total current assets
|
$4,309
|
$3,926
|
|
Property and equipment (net)
|
2,523
|
2,563
|
|
Other assets
|
590
|
576
|
|
Total assets
|
$7,422
|
$7,065
|
|
Liabilities and stockholders equity
|
|
|
|
Current liabilities (summarized)
|
$2,128
|
$2,095
|
|
Long term liabilities (summarized)
|
2,539
|
890
|
|
Total liabilities
|
$4,667
|
$2,985
|
|
Stockholders equity (summarized)
|
2,755
|
4,080
|
|
Total liabilities and stockholders equity
|
$7,422
|
$7,065
|
Goal: Create an Excel spreadsheet to calculate financial ratios. Use the results to answer questions about your findings
Scenario: Gap, Inc.Have asked you to calculate financial ratios for the company based on income statement and balance sheet
When you have completed your spreadsheet, answer the following questions
1. Discuss gaps current ratio calculations and their meanings. Compare the results to the rule of thumb presented in the chapter on p733 for this calculation:
Rule of thumb is a benchmark of general consensus on appropriate levels for ratios. For instance the most quoted rule of thumb is a current ratio of 2 to 1.
2. Discuss gaps total debt- to equity calculations and their meaning. Compare the results to the rule of thumb
3. Discuss the results of the dividend payout calculations and your opinion regarding its percentage
Step by step:
1. Open a new excel spreadsheet
2. In column A, create a bold faced heading that contains the following:
Row 1: Chapter 17 Decision Guideline
Row2: Gap. Inc.
Row 3: Financial Ratio Analysis
Row 4: Today's Date
3. Merge and center the four heading rows across columns A-C
4. In row 7, create the following bold faced column headings:
Column A: Financial Data (in millions)
Column B: January 28, 2012
Column C: February 3, 2011
5. Modify the format of the date headings in columns B and C as follows:
6.in column A, create the following row headings:
Row 8: Current Assets
Row 9: Current Liabilities
Row 10: Total Liabilities
Row 11: Stockholders' Equity
Row 12: Net Sales
Row 13: Gross Profit
Row 14: Net Earnings
Row 15: Market Price of Stock
Row16: Dividends Paid
Row 17: Avg. Common Shares Outstanding
7. Use data from the balance sheet and financial statement to enter the amounts for rows 8-17 in columns B and C
8. Skip a row
9. In row 19, create the following bold faced column heading:
Column A: Financial Ratios:
10. In column A: create the following row headings
Row 20: current ratio
Row 21: Total debt to equity
Row 22: Gross profit percentage
Row 23: return on sales
Row 24; return on stockholders' equity
Row 25: Earnings per share
Row 26: Price earnings
Row 27: Dividend yield
Row 28: Dividend payout
11. Use cell-referenced formulas to calculate the amounts in B and C for rows 20-28
12. Format amounts in column B, Rows 8-15 and column C row 8-11 as Number tab: Category: Accounting
Decimal: 0
Symbol: $
13. Format the amounts in column B, row 16 as Number Tab: Category: Accounting
Decimal: 0
Symbol: $
14. Format the amount in column B, row 25, as Number tab: Category: Category
Decimal: 2
Symbol: $
Negative Numbers: Black with parentheses
15. Format amounts in columns B and C, row 20 and Column B, row 26 as
Number tab: Category: Number
Decimal: 2
Negative numbers: Black with parentheses
16. Format amounts in column B rows 21-24 and 27-28 and column C row 21 as follows:
Number tab: Category: Percentage
Decimal: 2