Based on several problems from the textbook, this assignment provides an opportunity to review income statements, balance sheets, and financial ratios and consider how they can be applied to decision making.

• Compute the financial ratios for Problem 17-B5 on page 738.
• Create an Excel spreadsheet to calculate financial ratios for Gap Inc., P.

Gap, Inc. is a leading global specialty apparel company with 3,263 companyoperated and franchise store locations in 2012. The company was established in 1969 and has several well-known brand names such as Gap, Banana Republic, and Old navy. Excerpts from the company's annual 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 Cost of sales Gross profit Operating expenses Operating income Interest expenses, net Income before income taxes Income taxes Net income Balance sheet Assets Cash and cash equivalents Inventories Other current assets (summarized\") Total current assets Property and equipment (net) Other assets Total assets $14,549 9,275 $5,274 3,3836 1,438 69 $1,369 536 $833 January 8, 2012 January 29, 2011 $1,885 1,615 809 $1,561 1,620 745 $4,309 2,523 $3,926 2,563 590 $7,422 576 $7,065 Liabilities and stockholders equity Current liabilities (summarized) Long term liabilities (summarized) Total liabilities Stockholders equity (summarized) Total liabilities and stockholders equity $2,128 $2,095 2,539 890 $2,985 $4,667 2,755 4,080 $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 Row 2: 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: Number tab: Type: Category: Date March 14, 2012 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 2028 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