Reference no: EM131624959

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