Directions: Construct the company's current balance sheet
Construct a pro forma income statement using the company's sustainable growth rate as the projected growth in sales. Note that when you reference the sustainable growth rate in d56 you will see SGR in the formula bar instead of d56; e,g, the formula for pro forma sales in cell D62 will be =D10*(1+SGR)
Construct a pro forma balance sheet based on the company's sustainable growth rate(which will not balance since only costs, current liabilities, and asset are assumed to grown proportional to sales)
Use the pro forma balance sheet to calculate the external financing needs
"pa Sales $ Total Costs (COGS & Admin Cash Accounts receivable Inventory Net plant and equipment Accounts payable Notes payable Long-term debt Common stock & Paid-in Ca Retained earnings Dividends Paid Net income Tax rate 53,000 40,900 3,140 4,200 6,500 43,200 2,600 5,700 28.000 5,000 15.740 4,200 11,326 22% Output areas: Problem 1 ssets Current assets Cash Accounts Receivable Inventory Total HEIR JORDAN CORPORATION Current Balance Sheet Liabilities and Owners' equity Current liabilities $ 3,140.00 Accounts payable 4,200.00 Notes payable 6,500.00 Total $ 13,840.00 Long-term debt $ 2,600.00 5,700.00 $ 8,300.00 $ 28,000.00 Fixed assets Net plant and equipment $ 43,200.00 Owners' equity Common stock & Paid-in Capital Retained earnings Total Equity $ 5,000.00 15,740.00 $ 20,740.00 Total assets $ 57,040.00 Total liabilities and Owners' equity $ 57,040.00 Problem 2 37.08% 62.92% HEIR JORDAN CORPORATION Financial Ratios Payout ratio Plowback ratio ROA ROE TAT Profit margin Debt-equity ratio Sustainable growth rate 54.61% 21.37% 0.40 52.34% Problem 3 HEIR JORDAN CORPORATION Pro forma income statement Sales $ 80,739.51 Costs 62,306.53 Taxable income $ 18,432.98 Taxes 4,055.26 Net income $ 14,377.73 Dividends Add. To Ret. Earnings 4,200.00 9,045.87 $ HEIR JORDAN CORPORATION Pro Forma Balance Sheet Problem 4 Assets Current assets Cash Accounts Receivable Inventory Total $ 4,783.44 6,398.23 9,902.02 $ 21,083.68 Liabilities and Owners' equity Current liabilities Accounts payable Notes payable Total $ 3,960.81 5,700.00 $ 9.660 81 Long-term debt $ 28,000.00 Fixed assets Net plant and equipment $ 65,810.32 Owners' equity Common stock & Paid-in Capital Retained earnings Total Equity $ 5,000.00 23,978.11 28.978.11 Total assets $ 86,893.99 Total liabilities and Owners' equity Problem 5 External financing needed 29,915.88 "pa Sales $ Total Costs (COGS & Admin Cash Accounts receivable Inventory Net plant and equipment Accounts payable Notes payable Long-term debt Common stock & Paid-in Ca Retained earnings Dividends Paid Net income Tax rate 53,000 40,900 3,140 4,200 6,500 43,200 2,600 5,700 28.000 5,000 15.740 4,200 11,326 22% Output areas: Problem 1 ssets Current assets Cash Accounts Receivable Inventory Total HEIR JORDAN CORPORATION Current Balance Sheet Liabilities and Owners' equity Current liabilities $ 3,140.00 Accounts payable 4,200.00 Notes payable 6,500.00 Total $ 13,840.00 Long-term debt $ 2,600.00 5,700.00 $ 8,300.00 $ 28,000.00 Fixed assets Net plant and equipment $ 43,200.00 Owners' equity Common stock & Paid-in Capital Retained earnings Total Equity $ 5,000.00 15,740.00 $ 20,740.00 Total assets $ 57,040.00 Total liabilities and Owners' equity $ 57,040.00 Problem 2 37.08% 62.92% HEIR JORDAN CORPORATION Financial Ratios Payout ratio Plowback ratio ROA ROE TAT Profit margin Debt-equity ratio Sustainable growth rate 54.61% 21.37% 0.40 52.34% Problem 3 HEIR JORDAN CORPORATION Pro forma income statement Sales $ 80,739.51 Costs 62,306.53 Taxable income $ 18,432.98 Taxes 4,055.26 Net income $ 14,377.73 Dividends Add. To Ret. Earnings 4,200.00 9,045.87 $ HEIR JORDAN CORPORATION Pro Forma Balance Sheet Problem 4 Assets Current assets Cash Accounts Receivable Inventory Total $ 4,783.44 6,398.23 9,902.02 $ 21,083.68 Liabilities and Owners' equity Current liabilities Accounts payable Notes payable Total $ 3,960.81 5,700.00 $ 9.660 81 Long-term debt $ 28,000.00 Fixed assets Net plant and equipment $ 65,810.32 Owners' equity Common stock & Paid-in Capital Retained earnings Total Equity $ 5,000.00 23,978.11 28.978.11 Total assets $ 86,893.99 Total liabilities and Owners' equity Problem 5 External financing needed 29,915.88
