XYZ Company Income Suleme For the Year Ended 12/31 $100,000 23,000 Cost of Goods Sold Gross Pre Operating Expenses EBIT Interest Expense ERT Tunes39% Net Income Dividend Adition to Retained in 10,170 4610 SSM 2,168 52,374 XYZ Company Balance Sheet Current Assets 57.500 10.400 Accounts Receivable Imely Prepaid liems OtherCA Total Current Act Net Plant & Equipment Total Ass 540,200 $12.00 XYZ Company Libilities & Equity Current Liabilities Accounts Payable Wages Payable Notes Payable Taxes Payable Total Cumshitis Long To Total Liabilities $30,000 352 $56.100 26.700 Commen Stock Retained aming Total Lities & liquity $122.500 Homework Problem, cont'd The projected sales for the forecast period is $165,000. Assume that the payout ratio will be maintained in the forecast period. The firm estimates that additional net fixed asset investment of $18,000 will be required during the forecast period. Assume that all current assets are spontaneous except Other Current Assets which is assumed not to change. Assume that all current liabilities except Notes Payable are spontaneous A. Prepare the pro forma Balance Sheet and pro forma Income Statement. The EFR will be a plug number that makes the balance sheet balance like in the class example. B. Using the existing financial statements as your basis, estimate firm XYZ's EFR for the forecast period again, but this time using the cookbook model. Assume that the profit margin remains the same in the forecast period. Also based on the cookbook equation, how much funding is expected to come from each of the internal sources of funds (change in SL and retained earnings). Iffirm XYZ must maintain a minimum current ratio of 1.8 and a maximum debt ratio of 0.50, how would you propose the EFR be financed (how much short term debt, long term debt, and equity)? C. Based on your results in part B, prepare a Pro Forma Sources and Uses of Funds Statement to reflect the financing allocations that you decided on in part B. The only format change required is to break the total EFR down into the amounts of short term debt, long term debt, and new equity. You will have to use the numbers for ACA, ASL, addition to R.E., and EFR that you calculated in part B to make it balance, since they may be slightly different than those from part A. Explain the basis for your financing allocations. Homework Problem, contd Hints: Pro Forma TA = Existing TA + ACA+ANFA Max Pro Forma Total Liabilities = (D.R. Constraint)(Pro Forma TA) Max Additional TL - Max. Pro Forma TL -Existing TL Max Additional External Debt - Max Additional TL - ASL Min Additional External Equity - EFR - Max Additional External Debt Pro Forma CA-Existing CA+ACA Max Pro Forma CL - Pro Forma CA/CR Constraint Max Additional CL - Max Pro Forma CL - Existing CL Max Additional Notes Payable (N/P) = Max Additional CL - ASL Additional LTD = Max Additional External Debt - Max N/P Percent of Sales Technique Homework XYZ Company Income Statement For the Year Ended 12/31/xXXX Sales Cost of Goods Sold Gross Profit Operating Expenses EBIT Interest Expense EBT Taxes @ 39% Net Income Dividend Addition to Retained Earnings S140,000 117.000 23,000 12.830 10.170 4.610 5.560 2,168 3,392 1,018 $2,374 XYZ Company Balance Sheet 12/31/xxxx Assets Current Assets Cash Accounts Receivable Inventory Prepaid Items Other CA Total Current Assets Net Plant & Equipment Total Assets $7,500 12,100 10,400 5.900 S40,200 82.300 SI22.500 Liabilities & Equity Current Liabilities Accounts Payable Wages Payable Notes Payable Taxes Payable Total Current Liabilities Long Term Debt Total Liabilities $7,200 3.600 5,400 4.200 $20,400 35,700 $56,100 Common Stock Retained Earnings 28,700 37,700 Total Liabilities & Equity S122,500 Homework Problem, cont'd The projected sales for the forecast period is $165,000. Assume that the payout ratio will be maintained in the forecast period. The firm estimates that additional net fixed asset investment of $18,000 will be required during the forecast period. Assume that all current assets are spontaneous except Other Current Assets which is assumed not to change. Assume that all current liabilities except Notes Payable are spontaneous A. Prepare the pro forma Balance Sheet and proforma Income Statement. The EFR will be a plug number that makes the balance sheet balance like in the class example. B. Using the existing financial statements as your basis, estimate fimm XYZ's EFR for the forecast period again, but this time using the cookbook model. Assume that the profit margin remains the same in the forecast period. Also based on the cookbook equation, how much funding is expected to come from each of the internal sources of funds (change in SL and retained earnings). If firm XYZ must maintain a minimum current ratio of 18 and a maximum debt ratio of 0.50, how would you propose the EFR be financed (how much short term debt, long term debt and equity)? C. Based on your results in part B, prepare a Pro Forma Sources and Uses of Funds Statement to reflect financing allocations that you decided on in part B. The only format change required is to break the total EFR down into the amounts of short term debt, long term debt, and new equity. You will have to use the numbers for ACA, ASL, addition to R.E., and EFR that you calculated in part B to make it balance, since they may be slightly different than those from part A. Explain the basis for your financing allocations. XYZ Company Income Suleme For the Year Ended 12/31 $100,000 23,000 Cost of Goods Sold Gross Pre Operating Expenses EBIT Interest Expense ERT Tunes39% Net Income Dividend Adition to Retained in 10,170 4610 SSM 2,168 52,374 XYZ Company Balance Sheet Current Assets 57.500 10.400 Accounts Receivable Imely Prepaid liems OtherCA Total Current Act Net Plant & Equipment Total Ass 540,200 $12.00 XYZ Company Libilities & Equity Current Liabilities Accounts Payable Wages Payable Notes Payable Taxes Payable Total Cumshitis Long To Total Liabilities $30,000 352 $56.100 26.700 Commen Stock Retained aming Total Lities & liquity $122.500 Homework Problem, cont'd The projected sales for the forecast period is $165,000. Assume that the payout ratio will be maintained in the forecast period. The firm estimates that additional net fixed asset investment of $18,000 will be required during the forecast period. Assume that all current assets are spontaneous except Other Current Assets which is assumed not to change. Assume that all current liabilities except Notes Payable are spontaneous A. Prepare the pro forma Balance Sheet and pro forma Income Statement. The EFR will be a plug number that makes the balance sheet balance like in the class example. B. Using the existing financial statements as your basis, estimate firm XYZ's EFR for the forecast period again, but this time using the cookbook model. Assume that the profit margin remains the same in the forecast period. Also based on the cookbook equation, how much funding is expected to come from each of the internal sources of funds (change in SL and retained earnings). Iffirm XYZ must maintain a minimum current ratio of 1.8 and a maximum debt ratio of 0.50, how would you propose the EFR be financed (how much short term debt, long term debt, and equity)? C. Based on your results in part B, prepare a Pro Forma Sources and Uses of Funds Statement to reflect the financing allocations that you decided on in part B. The only format change required is to break the total EFR down into the amounts of short term debt, long term debt, and new equity. You will have to use the numbers for ACA, ASL, addition to R.E., and EFR that you calculated in part B to make it balance, since they may be slightly different than those from part A. Explain the basis for your financing allocations. Homework Problem, contd Hints: Pro Forma TA = Existing TA + ACA+ANFA Max Pro Forma Total Liabilities = (D.R. Constraint)(Pro Forma TA) Max Additional TL - Max. Pro Forma TL -Existing TL Max Additional External Debt - Max Additional TL - ASL Min Additional External Equity - EFR - Max Additional External Debt Pro Forma CA-Existing CA+ACA Max Pro Forma CL - Pro Forma CA/CR Constraint Max Additional CL - Max Pro Forma CL - Existing CL Max Additional Notes Payable (N/P) = Max Additional CL - ASL Additional LTD = Max Additional External Debt - Max N/P Percent of Sales Technique Homework XYZ Company Income Statement For the Year Ended 12/31/xXXX Sales Cost of Goods Sold Gross Profit Operating Expenses EBIT Interest Expense EBT Taxes @ 39% Net Income Dividend Addition to Retained Earnings S140,000 117.000 23,000 12.830 10.170 4.610 5.560 2,168 3,392 1,018 $2,374 XYZ Company Balance Sheet 12/31/xxxx Assets Current Assets Cash Accounts Receivable Inventory Prepaid Items Other CA Total Current Assets Net Plant & Equipment Total Assets $7,500 12,100 10,400 5.900 S40,200 82.300 SI22.500 Liabilities & Equity Current Liabilities Accounts Payable Wages Payable Notes Payable Taxes Payable Total Current Liabilities Long Term Debt Total Liabilities $7,200 3.600 5,400 4.200 $20,400 35,700 $56,100 Common Stock Retained Earnings 28,700 37,700 Total Liabilities & Equity S122,500 Homework Problem, cont'd The projected sales for the forecast period is $165,000. Assume that the payout ratio will be maintained in the forecast period. The firm estimates that additional net fixed asset investment of $18,000 will be required during the forecast period. Assume that all current assets are spontaneous except Other Current Assets which is assumed not to change. Assume that all current liabilities except Notes Payable are spontaneous A. Prepare the pro forma Balance Sheet and proforma Income Statement. The EFR will be a plug number that makes the balance sheet balance like in the class example. B. Using the existing financial statements as your basis, estimate fimm XYZ's EFR for the forecast period again, but this time using the cookbook model. Assume that the profit margin remains the same in the forecast period. Also based on the cookbook equation, how much funding is expected to come from each of the internal sources of funds (change in SL and retained earnings). If firm XYZ must maintain a minimum current ratio of 18 and a maximum debt ratio of 0.50, how would you propose the EFR be financed (how much short term debt, long term debt and equity)? C. Based on your results in part B, prepare a Pro Forma Sources and Uses of Funds Statement to reflect financing allocations that you decided on in part B. The only format change required is to break the total EFR down into the amounts of short term debt, long term debt, and new equity. You will have to use the numbers for ACA, ASL, addition to R.E., and EFR that you calculated in part B to make it balance, since they may be slightly different than those from part A. Explain the basis for your financing allocations