In: Finance
XYZ Company Income Statement |
Sales | $140,000 |
Cost of Goods Sold | 117,000 |
Gross Profit | 23,000 |
Operating Expenses | 12,830 |
EBIT | 10,170 |
Taxes @39% | 2168 |
Net income | 3,392 |
Dividend | 1,018 |
Addition to Retained Earnings | $2,374 |
XYZ Company | |
Balance Sheet |
Current Assets | |
Cash | $7,500 |
Accounts Receivable | 12,100 |
Inventory | 10,400 |
Prepaid Items | 5,900 |
Other CA | 4,300 |
Total Current Assets | $40,200 |
Net Plant $ Equipment | 82,300 |
Total Assets | $122,500 |
XYZ Company Balance Sheet |
Current Liabilities | |
Accounts Payable | $7,200 |
Wages Payable | 3,600 |
Notes payable | 5,400 |
Taxes Payable | 4,200 |
Total Current Liabilities | $20,400 |
Long Term Debt | 35,700 |
Total Liabilities | $56,100 |
Common Stock Retained Earnings |
28,700 37,700 |
Total Liabilities & Equity |
$122,500 |
Questions
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). If firm 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 △CA, △SL, 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.
Answers
-Pro forma EFR = $18,589
-Cookbook EFR = $18,941
-Financing Plan with constraints at their limits
Additional Notes Payable: $2,820
Additional LTD: $11,861
Additional Equity: $4,260
Pro forma Financial Statement:
A pro forma financial statement is prepared for the income statement and a balance sheet. A projected income statement and balance sheet are called pro format financial statements. These contain the projected future values.
A)
Income Statement | |||
---|---|---|---|
Current | Ratio (Vertical Analysis) (see Note 1) | Projected (See Note 2) | |
Sales | $140,000 | 100.00% | $ 165,000 |
Cost of Goods Sold | 117,000 | 83.57% | $ 137,893 |
Gross Profit | $23,000 | 16.43% | $ 27,107 |
Operating Expenses | 12,830 | 9.16% | $ 15,121 |
EBIT | 10,170 | 7.26% | $ 11,986 |
Interest Expense | 4,610 | 3.29% | $ 5,433 |
EBT 5,560 Taxes @ 39% | 2,168 | 1.55% | $ 2,555 |
Net Income | $3,392 | 2.42% | $ 3,998 |
Dividend | 1,018 | 0.73% | $ 1,200 |
Addition to Retained Earnings | $2,374 | 1.70% | $ 2,798 |
Balance Sheet | |||
Current | Ratio (Vertical Analysis) (see Note 3) | Projected (See Note 4) | |
Assets | |||
Current Assets: | |||
Cash | $7,500 | 6.12% | $ 8,840 |
Accounts Receivable | 12,100 | 9.88% | $ 14,261 |
Inventory | 10,400 | 8.49% | $ 12,257 |
Prepaid Items | 5,900 | 4.82% | $ 6,954 |
Other CA | 4,300 | 3.51% | $ 4,300 |
Total Current Assets | $40,200 | 32.82% | $ 46,612 |
Net Plant & Equipment | 82,300 | 67.18% | $ 100,300 |
Total Assets | $122,500 | 100.00% | $ 146,912 |
Liabilities & Equity | |||
Current Liabilities: | |||
Accounts Payable | $7,200 | 5.88% | $ 7,200 |
Wages Payable | 3,600 | 2.94% | $ 4,243 |
Notes Payable | 5,400 | 4.41% | $ 5,400 |
Taxes Payable | 4,200 | 3.43% | $ 4,950 |
Total Current Liabilities | $20,400 | 16.65% | $ 21,793 |
Long Term Debt | 35,700 | 29.14% | $ 53,700 |
Total Liabilities | $56,100 | 45.80% | $ 75,493 |
Common Stock | 28,700 | 23.43% | $ 30,961 |
Retained Earnings | 37,700 | 30.78% | $ 40,498 |
Total Liabilities & Equity | $122,500 | 100.00% | $ 146,912 |
Note 1: The elements in the current year has been computed as percentage of revenue (sales).
Note 2: The amount and percentage in the projected year have been computed based on new projected revenue.
Note 3 & Note 4: The percentage in the balance sheet items for asset components and equity & liability components have been computed as the percentage of the total assets and total shareholders' equity and liability, respectively, which has been computed based on the given constraints. All amount has been found based on percentage.
B) & C)
Estimation of firm XYZ's EFR for the forecast period | |||
---|---|---|---|
Particulars | Amount | ||
Ending Total Asset (a) | $ 146,912 | ||
Less: Beginning Total Asset | $ 122,500 | ||
Change in the total asset ($146,912 - $122,500) | $ 24,412 | ||
Less: Cash generated from operations | $ (2,798) | ||
Required External Finance (b) | $ 21,614 | ||
EFR (External Finance Ration) (b/a) | 14.71% | ||
Source of External Finance | |||
Current (a) | Projected (b) | Amount (a - b) | |
Wages Payable | $ 3,600 | $ 4,243 | $ 643 |
Taxes Payable | $ 4,200 | $ 4,950 | $ 750 |
Long Term Debt | $ 35,700 | $ 53,700 | $ 18,000 |
Common Stock | $ 28,700 | $ 30,961 | $ 2,261 |
Total | $ 21,614 |