4
Problems Chapter 1-33
Input boxes in tan Output boxes in yellow Given data in blue Calculations in red Answers in green NOTE: Some functions used in these spreadsheets may require that the "Analysis ToolPak" or "Solver Add-In" be installed in Excel. To install these, click on the Office button then "Excel Options," "Add-Ins" and select "Go." Check "Analyis ToolPak" and "Solver Add-In," then click "OK."
Chapter 4
Question 1,2 Input area: Income statement Sales $23,000 Costs 16,700 Net income $6,300 Sales increase Payout rate Output area: Pro forma income statement Sales $26,450 Costs 19,205 Net income $7,245 Dividends $5,655 Pro forma balance sheet $18,170 Debt Equity $18,170 Total 15% 50% Balance sheet $15,800 Debt Equity $15,800 Total
Assets Total
$5,200 10,600 $15,800
Assets Total
$5,980 12,190 $18,170
Pro forma income statement Sales $26,450.00 Costs 19,205.00 Net income $7,245.00 Dividends Add. To RE $3,622.50 $3,622.50
Assets Total
Pro forma balance sheet $18,170 Debt $5,200.00 Equity 14,222.50 $18,170 Total $19,422.50 $(1,252.50)
External financing needed
Chapter 4
Question 3 Input area: Income statement Sales Costs Net income Next year's sales Output area: Percent increase in sales Pro forma income statement Sales $7,434 Costs 4,590 Net income $2,844 External financing $450 18.00% Pro forma balance sheet Assets $21,594 Debt Equity Total $21,594 Total $12,400 8,744 $21,144 $6,300 3,890 $2,410 $7,434 Assets Total Balance sheet $18,300 Debt Equity $18,300 Total
$12,400 5,900 $18,300
Chapter 4
Question 4 Input area: Sales Costs Taxable income Taxes Net income Next year's sales Dividend paid Tax rate Output area: Percent increase in sales Pro forma income statement Sales $21,840.00 Costs 16,800.00 Taxable income $5,040.00 Taxes (40%) 2,016.00 Net income $3,024.00 Dividends $1,568.00 Add. To RE $1,456.00 External financing $10,304.00 12% Pro forma balance sheet Assets $109,760.00 Debt Equity Total $109,760.00 Total $52,500.00 46,956.00 $99,456.00 $19,500 15,000 $4,500 1,800 $2,700 $21,840 $1,400 40% Assets Total $98,000 Debt Equity $98,000 Total $52,500 45,500 $98,000
Chapter 4
Question 5 Input area: Sales Costs Taxable income Taxes Net income Sales increase Payout ratio Tax rate Output area: Pro forma income statement Sales $4,830.00 Costs 3,795.00 Taxable income $1,035.00 Taxes (34%) 351.90 Net income $683.10 Dividends $273.24 Add. To RE $409.86 External financing $1,000.14 Pro forma balance sheet Current assets $4,140.00 Current liabilities Fixed assets 9,085.00 Long-term debt Equity Total $13,225.00 Total $4,200 3,300 $900 306 $594 15% 40% 34% Current assets Fixed assets Total assets $3,600 Current liabilities 7,900 Long-term debt Equity $11,500 Total $2,100 3,650 5,750 $11,500
$2,415.00 3,650.00 6,159.86 $12,224.86
Chapter 4
Question 6,7 Input area: Sales Costs Taxable income Taxes Net income Payout ratio Tax rate Output area: Income statement Sales $13,250.00 Costs 9,480.00 Taxable income $3,770.00 Taxes 1,508.00 Net income $2,262.00 Dividends $678.60 Add. To RE 1,583.40 Return on assets Retention ratio Internal growth rate Return on equity Sustainable growth rate 5.78% 70% 4.21% 10.45% 7.89% Balance sheet $10,400 Debt 28,750 Equity $39,150 Total $13,250 9,480 $3,770 1,508 $2,262 30% 40% Current assets Fixed assets Total $10,400 Debt 28,750 Equity $39,150 Total $17,500 21,650 $39,150
Current assets Fixed assets Total
$17,500 21,650 $39,150
Chapter 4
Question 8 Input area: Sales Costs Taxable income Taxes Net income Payout ratio Tax rate Output area: Return on equity Retention ratio Sustainable growth rate Maximum increase in sales 15.91% 70% 12.53% $5,264.03 $42,000 28,500 $13,500 4,590 $8,910 30% 34% Current assets Fixed assets Total 21,000 Debt 86,000 Equity $107,000 Total 51,000 56,000 $107,000
Chapter 4
Question 9 Input area: Sales Costs Taxable income Taxes Net income Dividends Addition to RE Tax rate Increase in sales Output area: Pro forma income statement Sales $45,600.00 Costs 22,080.00 Taxable income $23,520.00 Taxes 7,996.80 Net income $15,523.20 Dividends $6,240.00 Add. To RE $9,283.20 Payout ratio 40.20% $38,000 18,400 $19,600 6,664 $12,936 $5,200 $7,736 34% 20%
Chapter 4
Question 10 Input area:
Sales Current assets Cash Accounts Receivable Inventory Total Fixed assets Net plant and equipment
$38,000 $3,050 6,900 7,600 $17,550 $34,500 Current liabilities Accounts payable Notes payable Total Long-term debt Owners' equity Common stock Retained earnings Total Total liabilities and Owners' equity $1,300 6,800 $8,100 $25,000
$15,000 3,950 $18,950 $52,050
Total assets Output area:
$52,050
Assets Current assets Cash Accounts Receivable Inventory Total Fixed assets Net plant and equipment
HEIR JORDAN CORPORATION Balance Sheet Liabilities and Owners' equity Current liabilities $3,050 8.03% Accounts payable $1,300 6,900 18.16% Notes payable 6,800 7,600 20.00% Total $8,100 $17,550 46.18% Long-term debt $25,000 $34,500 90.79% Owners' equity Common stock Retained earnings Total Total liabilities and Owners' equity $15,000 3,950 $18,950 $52,050
3.42% n/a n/a n/a n/a n/a n/a
Total assets
$52,050
136.97%
Chapter 4
Question 11 Input area: Sales Costs Cash Accounts receivable Inventory Net plant and equipment Accounts payable Notes payable Long-term debt Common stock Retained earnings Dividends Net income Increase in sales Tax rate Output area: Payout ratio 0.40 $38,000 18,400 3,050 6,900 7,600 34,500 1,300 6,800 25,000 15,000 3,950 6,240 15,523 15% 34%
Pro forma income statement Sales $43,700.00 Costs 21,160.00 Taxable income $22,540.00 Taxes 7,663.60 Net income $14,876.40 Dividends $5,980.00 Add. To RE 8,896.40
Assets Current assets Cash Accounts Receivable Inventory Total Fixed assets Net plant and equipment
HEIR JORDAN CORPORATION Balance Sheet Liabilities and Owners' equity Current liabilities $3,507.50 Accounts payable $1,495.00 7,935.00 Notes payable 6,800.00 8,740.00 Total 8,295.00 $20,182.50 Long-term debt $25,000.00 $39,675.00 Owners' equity Common stock Retained earnings Total Total liabilities and Owners' equity $15,000.00 12,846.40 $27,846.40 $61,141.40
Total assets External financing needed
$59,857.50 $(1,283.90)
Chapter 4
Question 12 Input area: Return on assets Payout ratio Output area: Plowback ratio Internal growth rate 80% 6.84% 8% 20%
Chapter 4
Question 13 Input area: Return on equity Payout ratio Output area: Plowback ratio Sustainable growth rate 75% 12.68% 15% 25%
Chapter 4
Question 14 Input area: Profit margin Capital intensity ratio Debt-equity ratio Net income Dividends Output area: Return on equity Plowback ratio Sustainable growth rate 15.31% 72.09% 12.40% 8.2% 0.75 0.40 $43,000 $12,000
Chapter 4
Question 15 Input area: asset Total turnover Profit margin Equity multiplier Payout ratio Output area: Return on equity Plowback ratio Sustainable growth rate 35.10% 40.00% 16.33% 2.50 7.8% 1.80 60%
Chapter 4
Question 16,17 Input area: Percent of capacity Current sales ### Fixed assets ### Projected sales Output area: Full capacity Maximum sales growth Fixed assets / Full capacity sales Total fixed assets New fixed assets $578,947 5.26% 0.7600 $478,800 $38,800.00 95% $550,000 $440,000 $630,000
Chapter 4
Question 18 Input area: Sustainable growth rate Debt/equity ratio Payout ratio Assets/sales Output area: Plowback ratio Return on equity Profit margin 70% 15.31% 5.22% 12% 1.20 30% 0.75
Chapter 4
Question 19 Input area: Sustainable growth rate Payout ratio Assets/sales ratio Profit margin Output area: Plowback ratio Return on equity Equity multiplier Debt/equity ratio 70% 14.73% 1.43 0.43 11.50% 30.0% 0.60 6.20%
Chapter 4
Question 20 Input area: Internal growth rate Payout ratio Profit margin Output area: Plowback ratio Return on assets Total asset turnover 75% 8.72% 1.74 7% 25% 5%
Chapter 4
Question 21 Input area: Profit margin Total asset turnover Total debt ratio Payout ratio Output area: Debt/equity Return on assets Plowback ratio Return on equity Sustainable growth rate 1.86 6.00% 70.00% 17.14% 13.64% 4.8% 1.25 0.65 30%
Chapter 4
Question 22 Input area: Sales Net income Dividends Debt Equity Output area: Plowback ratio Return on equity Sustainable growth rate New total assets Debt/equity New total debt Additional borrowing Return on assets Internal growth rate 0.4686 30.17% 16.47% $167,710.84 1.48 $100,160.64 $14,160.64 0.1215 6.04% $195,000 $17,500 $9,300 $86,000 $58,000
Chapter 4
Question 23 Input area: Beginning equity Ending TA Net income Dividends Output area: Ending equity Plowback ratio ROE (beg. equity) ROE (ending equity) Exact SGR ROE x b (using beg. Equity for ROE) ROE x b (using end Equity for ROE) $151,500 86.84% 14.07% 12.54% 12.22% 12.22% 10.89% $135,000 $250,000 $19,000 $2,500
Chapter 4
Question 24 Input area: Beginning equity Ending TA Net income Dividends Output area: Plowback ratio ROA (ending TA) IGR ROA x b (end ROA) Beginning TA Beginning ROA ROA (beginning TA) 86.84% 7.60% 7.07% 6.60% $233,500 8.14% 7.07% $135,000 $250,000 $19,000 $2,500
Chapter 4
Questions 25-29 Input area: For problems 26, 27, and 29, change the percent sales growth rate in the input area below. Sales Costs Other expenses EBIT Interest expense Taxable income Taxes Net income Dividends Add. to retained earnings Sales increase Operating capacity Tax rate Output area: Dividend payout ratio 0.30 Assets Current assets Cash Accounts receivable Inventory Total Fixed assets Net plant and equipment Liabilities and owners' equity Current liabilities Accounts payable $91,800 Notes payable 17,000 Total $108,800 Long-term debt 158,000 Owners' equity Common stock and paid-in surplus Retained earnings Total Total liabilities and owners' equity $929,000 723,000 19,000 $187,000 14,000 $173,000 60,550 $112,450 $33,735 78,715 35.00% 80% 35% Total assets $565,900 Assets Current assets Cash Accounts receivable Inventory Total Fixed assets Net plant and equipment Liabilities and owners' equity Current liabilities Accounts payable $68,000 Notes payable 17,000 Total $85,000 Long-term debt $158,000 Owners' equity Common stock and paid-in surplus Retained earnings Total Total liabilities and owners' equity
$25,300 40,700 86,900 $152,900 $413,000
$140,000 182,900 $322,900 $565,900
2009 Pro Forma Income Statement Sales $1,254,150 Costs 976,050 Other expenses 25,650 EBIT $252,450 Interest expense 14,000 Taxable income $238,450 Taxes 83,458 Net income $154,993 Dividends $46,498 Add. To RE 108,495
$34,155 54,945 117,315 $206,415 557,550
$140,000 291,395 $431,395 $698,195
Total assets External financing #25 and #26 Full capacity sales Fixed assets required at full capacity Total fixed assets External financing needed #27 and #29 2006 Debt/equity ratio Debt-asset ratio Equity-asset ratio New total debt New LTD Excess debt raised EFN $(45,740) $65,770
$763,965
$1,161,250 0.35565 $446,040
0.75255 0.42940 0.57060 $324,648 $57,848 $(7,922) $65,770 Assets Current assets Cash Accounts receivable Inventory Total Fixed assets Net plant and equipment $34,155 54,945 117,315 $206,415 $557,550 Liabilities and owners' equity Current liabilities Accounts payable $91,800 Notes payable 17,000 Total $108,800 Long-term debt $215,848 Owners' equity Common stock and paid-in surplus Retained earnings Total Total liabilities and owners' equity
$140,000 291,395 $431,395 $756,043
Total assets Assets Current assets Cash Excess cash Accounts receivable Inventory Total Fixed assets Net plant and equipment
$763,965
$34,155 (7,922) 54,945 117,315 $198,493 557,550
Liabilities and owners' equity Current liabilities Accounts payable $91,800 Notes payable 17,000 Total $108,800 Long-term debt 215,848 Owners' equity Common stock and paid-in surplus Retained earnings Total Total liabilities and owners' equity
$140,000 291,395 $431,395 $756,043
Total assets Total debt at current D/A Total equity at current D/A Debt repurchase Equity repurchase $328,050.00 $435,915.00 $(3,401.74) $(4,520.25) Assets Current assets Cash Accounts receivable Inventory Total Fixed assets Net plant and equipment
$756,043
$34,155 54,945 117,315 $206,415 557,550
Liabilities and owners' equity Current liabilities Accounts payable $91,800 Notes payable 17,000 Total $108,800 Long-term debt 219,250 Owners' equity Common stock and paid-in surplus Retained earnings Total Total liabilities and owners' equity
$140,000 295,915 $435,915 $763,965
Total assets
$763,965
#28 Plowback ratio ROA Internal growth rate
0.70 19.87% 16.16%
#29 Plowback ratio ROE Internal growth rate
0.70 34.83% 32.24%
Chapter 4
Question 30 Input area:
Growth rate Debt-equity ratio Profit margin Total assets to sales
12% 0.30 6.70% 1.35
Output area:
Return on equity Plowback ratio Payout ratio
6.45% 1.66 (0.66)
This is a negative dividend payout ratio of 66% which is impossible; the growth rate is not consistent with the other constraints. The lowest possible payout ratio is 0, which corresponds to b = 1, or total earnings retention. Maximum sustainable g 6.90%
Chapter 4
Question 31,32 Output area: External financing needed = Increase in assets - Addition to retained earnings Increase in assets = A X g Addition to retained earnings = (Net income X b) (1 + g) Net income = PM(S) Thus, EFN = A(g) - PM(S)b(1 + g) = A(g) - PM(S)b - [PM(S)b]g = -PM(S)b + [A-PM(S)b]g
Internal growth rate: EFN = 0 = -PM(S)b + [A - PM(S)b]g g = [PM(S)b] / [A - PM(S)b] Since ROA = NI / A = PM(S) / A, dividing numerator and denominator by A gives g = [(PM(S)b) / A] / [(A - PM(S)b) / A] = b(ROA) / [1 - b(ROA)]