This preview has intentionally blurred parts. Sign up to view the full document

View Full Document

Unformatted Document Excerpt

3-19 Problem SpreadsheetTemplates FoundationsofFinancialManagem MAINMENUCHAPTER3 Problem 3-20 Problem 3-33 Problem 3-34 Spreadsheet Templates by Block and Hirt Copyright 2008 McGraw-Hill/Irwin and Info-Labs. (www.info-labs.com) plates ialManagement TER3 Problem 3-21 lock and Hirt abs. (www.info-labs.com) FoundationsofFinancialManagement BlockandHirtTwelfthEdition Problem 3-19 Objective Overall ratio analysis Student Name: Course Name: Student ID: Course Number: The balance sheet for Bryan Corporation is shown below. Sales for the year were $3,040,000, with 75 percent of sales sold on credit. BRYAN CORPORATION Balance Sheet 200X Assets Cash Accounts receivable Inventory Plant and equipment Total assets Liabilities and Stockholders' Equity Accounts payable $220,000 Accrued taxes 80,000 Bonds payable (long term) 118,000 Common stock 100,000 Paid-in-capital 150,000 Retained earnings 282,000 Total liabilities and $950,000 stockholders' equity $950,000 $50,000 280,000 240,000 380,000 Compute the following ratios: a. Current ratio. b. Quick ratio. c. Debt-to-total-assets ratio. d. Asset turnover. e. Average collection period. Copyright 2008 McGrawHill/ Irwin Spreadsheet Template by Block and Hirt Problem: 3-19 Solution Problem 3-19 Instructions Enter formulas to calculate the following ratios. If possible, use cell references to the balance sheet. a. Current ratio FORMULA b. Quick ratio FORMULA c. Debt-to-total-assets ratio FORMULA d. Asset turnover FORMULA e. Average collection period FORMULA Copyright 2008 McGrawHill/ Irwin Spreadsheet Template by Block and Hirt Problem: 3-19 FoundationsofFinancialManagement BlockandHirtTwelfthEdition Problem 3-20 Objective Debt-utilization ratios Student Name: Course Name: Student ID: Course Number: The Lancaster Corporation's income statement is given below. a. What is the times-interest-earned ratio? b. What would be the fixed-charge-coverage ratio? LANCASTER CORPORATION Sales Cost of goods sold Gross profit Fixed charges (other than interest) Income before interest and taxes Interest Income before taxes Taxes (35%) Income after taxes Copyright 2008 McGrawHill/ Irwin $200,000 116,000 84,000 24,000 60,000 12,000 48,000 16,800 $31,200 Spreadsheet Template by Block and Hirt Problem: 3-20 Solution Problem 3-20 Instructions Enter formulas to calculate the following ratios. If possible, use cell references to the income statement. a. Times interest earned FORMULA b. Fixed charge coverage FORMULA Copyright 2008 McGrawHill/ Irwin Spreadsheet Template by Block and Hirt Problem: 3-20 FoundationsofFinancialManagement BlockandHirtTwelfthEdition Problem 3-21 Objective Debt utilization and Du Pont system of analysis Student Name: Course Name: Student ID: Course Number: Using the income statement for J.Lo Wedding Gowns, compute the following ratios: a. The interest coverage. b. The fixed charge coverage. The total assets for this company equal $160,000. Set up the equation for the Du pont system of ratio analysis, and compute the answer to part c below. c. Return on assets (investment). J.Lo Wedding Gowns Income Statement Sales Less: Cost of goods sold Gross profit Less: Selling and administrative expense Less: Lease expense Operating profit* Less: Interest expense Earnings before taxes Less: Taxes (40%) Earnings after taxes $200,000 90,000 110,000 40,000 10,000 $60,000 5,000 $55,000 22,000 $33,000 *Equals income before interest and taxes. Copyright 2008 McGrawHill/ Irwin Spreadsheet Template by Block and Hirt Problem: 3-21 Solution Problem 3-21 Instructions Enter formulas to calculate the following ratios. If possible, use cell references to the income statement. a. Times interest earned FORMULA b. Fixed charge coverage FORMULA c. Return on assets FORMULA Copyright 2008 McGrawHill/ Irwin Spreadsheet Template by Block and Hirt Problem: 3-21 FoundationsofFinancialManagement BlockandHirtTwelfthEdition Problem 3-33 Objective Comparing all the ratios Student Name: Course Name: Student ID: Course Number: Using the financial statements for the Snider Corporation, calculate the 13 basic ratios found in the chapter. SNIDER CORPORATION Balance Sheet December 31, 2007 Assets Current assets: Cash Marketable securities Accounts receivable (net) Inventory Total current assets Investments Plant and equipment Less: Accumulated depreciation Net plant and equipment Total assets Liabilities and Stockholders' Equity Current liabilities: Accounts payable Notes payable Accrued taxes Total current liabilities Long-term liabilities: Bonds payable Total liabilities Copyright 2008 McGrawHill/ Irwin $50,000 20,000 160,000 200,000 $430,000 60,000 600,000 -190,000 410,000 $900,000 $90,000 70,000 10,000 170,000 150,000 $320,000 Spreadsheet by Template Block and Hirt Problem: 3-33 Stockholders' equity: Preferred stock, $50 par value Common stock, $1 par value Capital paid in excess of par Retained earnings Total stockholders' equity Total liabilities and stockholders' equity 100,000 80,000 190,000 210,000 580,000 $900,000 SNIDER CORPORATION Income Statement For the Year Ending December 31, 2007 Sales (on credit) Less: Cost of goods sold Gross profit Less: Selling and administrative expenses Operating profit (EBIT) Less: Interest expense Earnings before taxes (EBT) Less: Taxes Earnings after taxes (EAT) $1,980,000 1,280,000 700,000 475,000 * 225,000 25,000 200,000 80,000 $120,000 *Includes $35,000 in lease payments. Copyright 2008 McGrawHill/ Irwin Spreadsheet Template by Block and Hirt Problem: 3-33 Solution Problem 3-33 Instructions Enter formulas to calculate the ratios from this chapter. Profitability ratios Profit margin Return on assets (investment) Return on equity FORMULA FORMULA FORMULA Assets utilization ratios Receivable turnover Average collection period Inventory turnover Fixed asset turnover Total asset turnover FORMULA FORMULA FORMULA FORMULA FORMULA Liquidity ratios Current ratio Quick ratio FORMULA FORMULA Debt utilization ratios Debt to total assets Times interest earned Fixed charge coverage FORMULA FORMULA FORMULA Copyright 2008 McGrawHill/ Irwin Spreadsheet Template by Block and Hirt Problem: 3-33 FoundationsofFinancialManagement BlockandHirtTwelfthEdition Problem 3-34 Objective Ratio computation and analysis Student Name: Course Name: Student ID: Course Number: Given the financial statements for Jones Corporation and Smith Corporation shown here: a. To which one would you, as credit manager for a supplier, approve the extension of (short-term) trade credit? Why? Compute all ratios before answering. b. In which one would you buy stock? Why? JONES CORPORATION Current Assets Cash Accounts receivable Inventory Long-Term Assets Fixed Assets Less: Accumulated Depreciation Net fixed assets* Total assets Liabilities $20,000 Accounts payable 80,000 Bonds payable (long-term) 50,000 Stockholders' Equity $500,000 Common stock -150,000 Paid-in capital 350,000 Retained earnings $500,000 Total liabilities and equity Sales (on credit) Cost of goods sold Gross profit Selling and Administrative expense Less: Depreciation expense Operating Profit Interest expense Earnings before taxes Tax expense Net Income $100,000 80,000 $150,000 70,000 100,000 $500,000 $1,250,000 750,000 500,000 257,000 50,000 193,000 8,000 185,000 92,500 $92,500 * Use net fixed assets in computing fixed asset turnover. Includes $7,000 in lease payments. SMITH CORPORATION Copyright 2008 McGrawHill/ Irwin Spreadsheet Template by Block and Hirt Problem: 3-34 Current Assets Cash Marketable securities Accounts receivable Inventory Long-Term Assets Fixed Assets Less: Accumulated Depreciation Net fixed assets* Total assets Liabilities $35,000 Accounts payable 7,500 Bonds payable (long-term) 70,000 75,000 Stockholders' Equity $500,000 Common stock -250,000 Paid-in capital 250,000 Retained earnings $437,500 Total liabilities and equity Sales (on credit) Cost of goods sold Gross profit Selling and Administrative expense Less: Depreciation expense Operating Profit Interest expense Earnings before taxes Tax expense Net Income $75,000 210,000 $75,000 30,000 47,500 $437,500 $1,000,000 600,000 400,000 224,000 50,000 126,000 21,000 105,000 52,500 $52,500 * Use net fixed assets in computing fixed asset turnover. Includes $7,000 in lease payments. Copyright 2008 McGrawHill/ Irwin Spreadsheet Template by Block and Hirt Problem: 3-34 Solution Problem 3-34 Instructions Enter formulas to calculate the following ratios. If possible, use cell references to the financial statements. Jones and Smith Comparison One way of analyzing the situation for each company is to compare the respective ratios for each on, examining those ratios which would be most important to a supplier or short-term lender and a stockholder. Profit margin Return on assets Return on equity Receivable turnover Average collection period Inventory turnover Fixed asset turnover Total asset turnover Current ratio Quick ratio Debt to total assets Times interest earned Fixed charge coverage Jones Corp. FORMULA FORMULA FORMULA FORMULA FORMULA FORMULA FORMULA FORMULA FORMULA FORMULA FORMULA FORMULA FORMULA Smith Corp. FORMULA FORMULA FORMULA FORMULA FORMULA FORMULA FORMULA FORMULA FORMULA FORMULA FORMULA FORMULA FORMULA a. To which one would you, as credit manager for a supplier, approve the extension of (short-term) trade credit? Why? b. In which one would you buy stock? Why? Copyright 2008 McGrawHill/ Irwin Spreadsheet Template by Block and Hirt Problem: 3-34 ... View Full Document

End of Preview

Sign up now to access the rest of the document