Excel Solutions - Chapter 04

Excel Solutions - Chapter 04 - Chapter 4 Problems 1-30...

Info iconThis preview shows pages 1–11. Sign up to view the full content.

View Full Document Right Arrow Icon
Chapter 4 Problems 1-30 Input boxes in tan Output boxes in yellow Given data in blue Calculations in red Answers in green
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Chapter 4 Question 1,2 Input area: Sales $16,000 Costs 12,500 Assets 8,900 Debt 5,100 Equity 3,800 Sales increase 10% Output area: Pro forma income statement Pro forma balance sheet Sales $17,600 Assets $9,790 Debt $5,610 Costs 13,750 Equity 4,180 Net income $3,850 Total $9,790 Total $9,790 Plug variable = Dividends paid = Net income - Equity increase = $3,470 Pro forma income statement Pro forma balance sheet Sales $17,600 Assets $9,790 Debt $5,100 Costs 13,750 Equity 5,725 Net income $3,850 Total $9,790 Total $10,825 Dividends $1,925 External financing needed = $(1,035) Add. To RE 1,925
Background image of page 2
Chapter 4 Question 3 Input area: Sales $4,400 Costs 2,685 Assets 13,400 Debt 9,100 Equity 4,300 Next year's sales 5,192 Output area: Percent increase in sales = 18.00% Pro forma income statement Pro forma balance sheet Sales $5,192 Assets $15,812 Debt Costs 3,168 Equity Net income $2,024 Total $15,812 Total $388
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
$9,100 6,324 $15,424
Background image of page 4
Chapter 4 Question 4 Input area: Sales $19,200 Costs $15,550 Assets $93,000 Debt $20,400 Equity $72,600 Next year's sales $23,040 Dividend paid $963.60 Tax rate 34% Output area: Percent increase in sales = 20% Pro forma income statement Pro forma balance sheet Sales $23,040.00 Assets $111,600.00 Debt $20,400.00 Costs 18,660.00 Equity 74,334.48 Taxable income $4,380.00 Total $111,600.00 Total $94,734.48 Taxes 1,489.20 Net income $2,890.80 Dividends $1,156.32 Add. To RE $1,734.48 $16,865.52
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 6
Chapter 4 Question 5 Input area: Sales $3,600 Costs 2,900 Current assets 4,500 Fixed assets 3,900 Current liabilities 920 Long-term debt 1,840 Equity 5,640 Sales increase 15% Payout ratio 50% Tax rate 34% Output area: Pro forma income statement Pro forma balance sheet Sales $4,140.00 Current assets $5,175.00 Current liabilities $1,058.00 Costs 3,335.00 Fixed assets 4,485.00 Long-term debt 1,840.00 Taxable income $805.00 Equity 5,905.65 Taxes 273.70 Total $9,660.00 Total $8,803.65 Net income $531.30 Dividends $265.65 Add. To RE $265.65 $856.35
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Chapter 4 Question 6,7 Input area: Sales $10,400 Costs 6,820 Current assets 11,000 Fixed assets 27,000 Debt 22,000 Equity 16,000 Payout ratio 20% Tax rate 35% Output area: Income statement Sales $10,400.00 Current assets Costs 6,820.00 Fixed assets Taxable income $3,580.00 Taxes 1,253.00 Total Net income $2,327.00 Dividends $465.40 Add. To RE 1,861.60 Return on assets = Net income / Total assets = 0.06 Internal growth rate = (Return on assets X b) / (1 - Return on as Return on equity = Net income / Total equity = 0.15 Sustainable growth rate = (Return on equity X b) / (1 - Return o
Background image of page 8
Balance sheet $11,000 Debt $22,000 27,000 Equity 16,000 $38,000 Total $38,000 b = 0.8 ssets X b) = 5.15% n equity X b) = 13.17%
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Chapter 4 Question 8
Background image of page 10
Image of page 11
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 35

Excel Solutions - Chapter 04 - Chapter 4 Problems 1-30...

This preview shows document pages 1 - 11. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online