Lesson 02 Excel Assignment Financial Ratios and Financial Planning Below you see the income statement and balance sheet of a firm in this year. Using the information to answer question (1) - (3). This year's Income Statement Tax rate 25% $ Sales COGS Other expenses Depreciation EBIT Interest Taxable income Taxes (25%) Net income $ 235,000,000 170,100,000 27,752,200 7,566,900 29,580,900 4,170,100 25,410,800 6,352,700 19,058,100 $ $ Dividends Add to RE $ $ 8,925,000 10,133,100 Assets $ Current Assets Cash Accounts rec. Inventory Total CA This year's Balance Sheet Liabilities & Equity Current liabilities 4,729,200 Accounts payable $ 5,500,800 Notes payable 7,176,200 Total CL $ 17,406,200 Long-term debt $ 7,261,700 15,492,900 22,754,600 $ 40,100,000 Fixed assets Net P&E $ 111,629,300 $ Shareholder equity Common stock Retained earnings Total equity 6,040,900 60,140,000 66,180,900 $ Total assets 129,035,500 Total L&E $ 129,035,500 Growth rate 20% Q1) Using the financial statements information above to calculate the following ratios. ? ? ? ? Current ratio Quick ratio Total asset turnover Inventory turnover Receivables turnover Debt ratio Debt-equity ratio Equity multiplier Interest coverage Profit margin Return on assets Return on equity ? ? ? ? ? ? ? ? Q2) What is the sustainable growth rate of the firm? Retention ratio, b Sustainable growth rate ? ? At this sustainable growth rate, prepare the pro forma statements next year and calculate External Financing Needed (EFN) in the area below: ? ? ? 7,566,900 Sales COGS Other expenses Depreciation EBIT Interest Taxable income Taxes Net income ? 4,170,100 ? ? ? Dividends Add to RE ? ? Assets Current Assets Cash Accounts rec. Inventory Total CA ? ? ? ? Liabilities & Equity Current liabilities Accounts payable ? Notes payable ? Total CL ? Long-term debt $ 40,100,000 6,040,900 Shareholder equity Common stock Retained earnings Total equity Fixed assets Net PP&E $ ? ? ? Total Assets ? Total L&E ? EFN Q3) What if the firm is planning for a growth rate of 20% next year? Prepare the pro forma statements next year and calculate External Financing Needed (EFN) in the area below: ? ? ? 7,566,900 Sales COGS Other expenses Depreciation EBIT Interest Taxable income Taxes Net income ? 4,170,100 ? ? ? Dividends Add to RE ? ? Assets Current Assets Cash Accounts rec. Inventory Total CA ? ? ? ? Liabilities & Equity Current liabilities Accounts payable ? Notes payable ? Total CL ? Long-term debt $ 40,100,000 6,040,900 Shareholder equity Common stock Retained earnings Total equity Fixed assets Net PP&E $ ? ? ? Total Assets ? Total L&E ? EFN Lesson 02 Excel Assignment Financial Ratios and Financial Planning Below you see the income statement and balance sheet of a firm in this year. Using the information to answer question (1) - (3). This year's Income Statement Tax rate 25% $ Sales COGS Other expenses Depreciation EBIT Interest Taxable income Taxes (25%) Net income $ 235,000,000 170,100,000 27,752,200 7,566,900 29,580,900 4,170,100 25,410,800 6,352,700 19,058,100 $ $ Dividends Add to RE $ $ 8,925,000 10,133,100 Assets $ Current Assets Cash Accounts rec. Inventory Total CA This year's Balance Sheet Liabilities & Equity Current liabilities 4,729,200 Accounts payable $ 5,500,800 Notes payable 7,176,200 Total CL $ 17,406,200 Long-term debt $ 7,261,700 15,492,900 22,754,600 $ 40,100,000 Fixed assets Net P&E $ 111,629,300 $ Shareholder equity Common stock Retained earnings Total equity 6,040,900 60,140,000 66,180,900 $ Total assets 129,035,500 Total L&E $ 129,035,500 Growth rate 20% Q1) Using the financial statements information above to calculate the following ratios. ? ? ? ? Current ratio Quick ratio Total asset turnover Inventory turnover Receivables turnover Debt ratio Debt-equity ratio Equity multiplier Interest coverage Profit margin Return on assets Return on equity ? ? ? ? ? ? ? ? Q2) What is the sustainable growth rate of the firm? Retention ratio, b Sustainable growth rate ? ? At this sustainable growth rate, prepare the pro forma statements next year and calculate External Financing Needed (EFN) in the area below: ? ? ? 7,566,900 Sales COGS Other expenses Depreciation EBIT Interest Taxable income Taxes Net income ? 4,170,100 ? ? ? Dividends Add to RE ? ? Assets Current Assets Cash Accounts rec. Inventory Total CA ? ? ? ? Liabilities & Equity Current liabilities Accounts payable ? Notes payable ? Total CL ? Long-term debt $ 40,100,000 6,040,900 Shareholder equity Common stock Retained earnings Total equity Fixed assets Net PP&E $ ? ? ? Total Assets ? Total L&E ? EFN Q3) What if the firm is planning for a growth rate of 20% next year? Prepare the pro forma statements next year and calculate External Financing Needed (EFN) in the area below: ? ? ? 7,566,900 Sales COGS Other expenses Depreciation EBIT Interest Taxable income Taxes Net income ? 4,170,100 ? ? ? Dividends Add to RE ? ? Assets Current Assets Cash Accounts rec. Inventory Total CA ? ? ? ? Liabilities & Equity Current liabilities Accounts payable ? Notes payable ? Total CL ? Long-term debt $ 40,100,000 6,040,900 Shareholder equity Common stock Retained earnings Total equity Fixed assets Net PP&E $ ? ? ? Total Assets ? Total L&E ? EFN