Horizontal and Vertical Analysis/Financial Ratios

Please see the attached and complete the 2 practice problems in excel.

Please complete these 2 practice problems in excel.

1. P13-1A Here are comparative statement data for East Company and West Company, two
competitors. All balance sheet data are as of December 31, 2007, and December 31, 2006.

East Company West Company
2007 2006 2007 2006
Net sales $1,849,035 $546,000
Cost of goods sold 1,080,490 238,000
Operating expenses 230,000 82,000
Interest expense 6,800 1,600
Income tax expense 62,030 31,000

Current assets 325,975 $312,410 83,336 $ 79,467
Plant assets (net) 526,800 500,000 139,728 125,812
Current liabilities 66,325 75,815 35,348 30,281
Long-term liabilities 113,990 90,000 29,620 25,000
Common stock, $10 par 500,000 500,000 120,000 120,000
Retained earnings 172,460 146,595 38,096 29,998
Instructions
(a) Prepare a vertical analysis of the 2007 income statement data for East Company and
West Company.
(b) Comment on the relative profitability of the companies by computing the 2007 return
on assets and the return on common stockholders' equity ratios for both companies.

2. P13-2B The comparative statements of Fraggle Rock Company are shown.

FRAGGLE ROCK COMPANY
Income Statements
For the Years Ended December 31

2007 2006
Net sales $780,000 $624,000
Cost of goods sold 440,000 405,600

Gross profit 340,000 218,400
Selling and administrative expense 146,880 149,760

Income from operations 193,120 68,640
Other expenses and losses
Interest expense 9,920 7,200

Income before income taxes 183,200 61,440
Income tax expense 35,000 14,000

Net income $148,200 $ 47,440

More on page 2

FRAGGLE ROCK COMPANY
Balance Sheets
December 31
Assets 2007 2006
Current assets
Cash $ 23,100 $ 21,600
Short-term investments 44,800 33,000
Accounts receivable 106,200 93,800
Inventory 116,400 64,000
Total current assets 290,500 212,400
Plant assets (net) 485,300 439,600
Total assets $775,800 $652,000

Liabilities and Stockholders' Equity
Current liabilities
Accounts payable $148,200 $132,000
Income taxes payable 25,300 24,000
Total current liabilities 173,500 156,000
Bonds payable 132,000 120,000
Total liabilities 305,500 276,000
Stockholders' equity
Common stock ($10 par) 140,000 130,000
Retained earnings 330,300 246,000
Total stockholders' equity 470,300 376,000
Total liabilities and stockholders' equity $775,800 $652,000

All sales were on account. Net cash provided by operating activities was $94,000. Capital
expenditures were $47,000, and cash dividends were $55,000.
Instructions
Compute the following ratios for 2007.
(a) Earnings per share. (h) Days in inventory.
(b) Return on common stockholders' equity. (i) Times interest earned.
(c) Return on assets. ( j) Asset turnover.
(d) Current. (k) Debt to total assets.
(e) Receivables turnover. (l) Current cash debt coverage.
(f) Average collection period. (m) Cash debt coverage.
(g) Inventory turnover. (n) Free cash flow.