Input boxes in tan

Chapter 4

Chapter 4
Problems 5, 6, 20, 22
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.”

#5

Chapter 4
Question 5
Input area:
Sales Current assets Current liabilities
Costs Fixed assets Long-term debt
Taxable income $ – 0 Equity – 0
Taxes Total assets $ – Total $ –
Net income $ – 0
Payout ratio
Sales increase
Tax rate
Output area:
Pro forma income statement Pro forma balance sheet
Sales $ – 0 Current assets $ – 0 Current liabilities $ – 0
Costs – 0 Fixed assets – 0 Long-term debt – 0
Taxable income $ – 0 Equity – 0
Taxes (0%) – 0 Total $ – 0 Total $ – 0
Net income $ – 0
Dividends $ – 0
Add. To RE $ – 0
External financing $ – 0

#6

Chapter 4
Question 6
Input area:
Sales Current assets Debt
Costs Fixed assets Equity – 0
Taxable income $ – 0 Total $ – Total $ –
Taxes
Net income $ – 0
Payout ratio
Tax rate
Output area:
Return on assets ERROR:#DIV/0!
Retention ratio 100%
Internal growth rate ERROR:#DIV/0!

#20

Chapter 4
Question 20
Input area:
Profit margin
Total asset turnover
Total debt ratio
Payout ratio
Output area:
Debt/equity ERROR:#DIV/0!
Plowback ratio 1
Return on equity ERROR:#DIV/0!
Sustainable growth rate ERROR:#DIV/0!

#22

Chapter 4
Question 22
Input area:
Beginning equity
Ending TA
Net income
Dividends
Output area:
Addition to RE $ –
Ending equity $ –
Plowback ratio ERROR:#DIV/0!
ROE (beg. equity) ERROR:#DIV/0!
ROE (ending equity) ERROR:#DIV/0!
Exact SGR ERROR:#DIV/0!
ROE x b (using beg. ERROR:#DIV/0!
Equity for ROE)
ROE x b (using end ERROR:#DIV/0!
Equity for ROE)

"Is this question part of your assignment? We can help"

ORDER NOW