Some functions used in these spreadsheets may require that the “Analysis ToolPak” or “Solver Add-In” be installed in Excel.
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) |
Leave a Reply
Want to join the discussion?Feel free to contribute!