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) |
