Can you please help me with steps to solving Problem 2 (Ratios)?
Student instructions: This worksheet is for problem 6-10. This tab contains forecasting assumptions. The next tab contains Bright Future's financial statements and the pro forma forecast (Question 1.). The final tab contains questions 2 a-e, 3, and 4. Review the forecasting assumptions below and then proceed to the next tab to complete the forecast. PROBLEM 6-10 Bright Future Corporation Forecasting Assumptions: 20% given Sales growth Cost of Goods Sold Selling & Admin Expenses Cash Marketable Securities Accounts Receivable Inventory Prepaid Expenses Accounts Payable Accrued Expenses These items are projected to remain the same percentage of sales in 2007 as they were in 2006. That is the same as saying that in 2007 the items will grow at the same rate as sales Depreciation Expense Interest Expense Gross Plant & Equipment Notes Payable Long-Term Debt Common Stock Capital in Excess of Par These items are projected to remain the same value in 2007 as they were in 2006. Tax rate 40% Dividends: pay same dollar amount in 2007 as in 2006 Bad Debt Allowance 17% of accounts receivable Student instructions: This worksheet is for problemi 6-10. The problem begins with the forecasting assumptions on the previous tab. This tab contains Bright Future's financial statements and the pro farma forecast (Question 1), and Question 2a. Enter fomulas in column C to complete the pro forma forecast for the year 2007. For Question 2a, incorporate Additonal Funds Needed (AFN) or Excess Financing into the balance sheet forecast in column D where indicated (Note: You may disdregard any additional interest expense associated with the new debt). Finally, proceed to the next tab to answer the remaining items in Question 2. BRIGHT FUTURE CORPORATION Historical and Projected Income Statements Historical 2006 Projected 2007 Sales Cost of goods Sold Gross Profit Selling & Admin. Expenses Depreciation Expense Operating Income (EBIT) Interest Expenses Earnings Before Tax (EBT) Income Tax (40%) Net Income (NI) $10.000.000 4.000.000 $6,000,000 $800.000 $2.000.000 $3.200.000 $1.350.000 $1.350.000 $740.000 $1.110.000 $12,000,000 $4.800.000 $7.200 000 $960 000 $2,000,000 4.240.000 $1.350.000 $2.890.000 $1,156 000 $1,734 000 1.562162162 Common Stock Dividends paid Addition to Retained earings Earnings per Share (1,000,000 shares) $400.000 $710.000 51.11 $400.000 $1.314.000 51.73 BRIGHT FUTURE CORPORATION Historical and Projected Balance Sheets Historical Dec 31, 2006 Projected Dec 31, 2007 Projection with AFNI Excess Financing incorporated ASSETS Current Assets Cash Marketable Securities Accounts Receivable (gross) Less: Allowance for bad Debts Accounts Receivable (Net) Inventory Prepaid Expenses Total Current Assets Plant and Equipment (gross) Less: Accumulated Depreciation Plant and Equipment (net) TOTAL ASSETS $9.000.000 $,000,000 $1.200.000 $200.000 $1.000.000 $20.000.000 $1.000.000 $39,000 000 $20.000.000 $9.000.000 $11.000.000 $50.000.000 IIIIIIIII $10.800.000 $9,600,000 $1.440.000 $244 BOO $1.195.200 $24.000.000 $1.200.000 $46.795 200 $20.000.000 $11,000,000 $9.000.000 $55,795 200 LIABILITIES AND EQUITY Current Liabilities: Accounts payable Notes Payable Accrued Expenses Total Current Liabilities L-T Debt (Bonds Payable. 5%, due 2015) Total Liabilities Common Stock (1.000.000 shares, $1 par) Capital in Excess of Par Retained Eamings Total Equity TOTAL LIABILITIES AND EQUITY $12.000.000 $5.000.000 $3.000.000 $20,000,000 $20.000.000 $40.000.000 $1.000.000 $4,000,000 $5.000.000 $10.000.000 50.000.000 $14.400.000 $5.000.000 $3,600,000 $23 000 000 $20.000.000 $43.000.000 $1.000.000 $4 ODD DOO $6.334.000 $11,334 DOO $54.334.000 Incorporate as new Long-term Debt Question 2a. Excess Financing (Additional Funds Needed) $1.461.200 Student instructions. This worksheet is for problem 6-10. The problem begins with the forecasting assumptions on the first tab and continues with the forecast itself on the second tab. This tab contains Questions 2b through e, and Questions 3 and 4. For Question 2, enter formulas in columns B and C to calculate the ratios indicated. For Questions 3 and 4, click on the text boxes to enter comments and recommendations Question 2. Ratios: 2006 2007 b. Current Ratio c. Total Asset Turnover Inventory Turnover d. Total Debt to Assets e. Net Profit Margin Return on Assets Return on Equity Question 3. Comments: Comments on liquidity, asset productivity, debt management and profitability: Question 4. Recommendations: Recommendations
