Advance financial accounting-Financial Projections project
Kevin121847009
FinancialProjections_Class1.xlsxInstruction
| The CEO provides the following assumptions to prepare the consolidated totals: | ||
| 1 | On the acquisition date, XYZ's accounting records indicate there is no difference in book value and fair value for net assets EXCEPT a piece of equipment with 10-year remaining life that is undervalued on the books by $60,000. | |
| 2 | The acquisition will generate indefinite life goodwill of $81,000. | |
| Using the information above, please complete the following: | ||
| 1 | Fix the errors in the "ABC Projections - Control" tab to generate the correct consolidated balances. | |
| 2 | Complete the table in the "ABC Financial Ratios" tab using data from "ABC Projections - Control" and "ABC Projections - No control". | |
ABC Financial Ratios
| Calculated using PY data | Calculated using PY data | Calculated using PY data | Calculated using Projections | Calculated using Projections | ||
| Dec. 31, 2023 | Dec. 31, 2024 | Dec. 31, 2025 | Dec. 31, 2026 (No control) | Dec. 31, 2026 (Control) | ||
| LIQUIDITY RATIOS | ||||||
| Current ratio | 0.51 | 0.54 | 0.53 | --> Populate the cells in BLUE. | ||
| Working capital | (245,100) | (232,200) | (259,290) | |||
| SOLVENCY RATIOS | ||||||
| Debt to equity ratio | 0.51 | 0.54 | 0.52 | |||
| Times interest earned ratio | 36.56 | 39.60 | 37.42 | |||
| PROFITABILITY RATIOS | ||||||
| Return on assets (%) | 13.55% | 13.27% | 14.10% | |||
| Return on equity (%) | 19.08% | 22.02% | 21.87% | |||
| Financial ratio calculations: |
ABC Projections - No control
| ABC - Projections for 2026 | ||
| Revenues | (1,328,000) | |
| Cost of goods sold | 457,500 | |
| Depreciation expense | 424,000 | |
| Interest expense | 16,000 | |
| Income tax expense | 30,000 | |
| Equity in subsidiary (assuming 45% interest) | (162,900) | |
| Net income | (563,400) | |
| Retained earnings, 1/1/21 | (1,343,500) | |
| Net income | (563,400) | |
| Dividends declared | 120,000 | |
| Retained earnings, 12/31/21 | (1,786,900) | |
| Current assets | 302,000 | |
| Investment in subsidiary (assuming 45% interest) | 1,210,400 | |
| Equipment (net) | 1,048,000 | |
| Buildings (net) | 810,000 | |
| Land | 704,000 | |
| Goodwill | - 0 | |
| Total assets | 4,074,400 | |
| Current Liabilities | (560,000) | |
| Other Liabilities | (827,500) | |
| Common stock | (900,000) | |
| Retained earnings | (1,786,900) | |
| Total liabilities and equity | (4,074,400) | |
ABC Projections - Control
| ABC - Projections for 2026 | XYZ - Projections for 2026 | Adjustment | ABC Consolidated - Projections for 2026 | Consolidation Entries (INCORRECT!) | CEO Notes | ||||||||||||
| Revenues | (1,328,000) | (668,000) | (1,996,000) | Common stock | 300,000 | ||||||||||||
| Cost of goods sold | 457,500 | 168,000 | 625,500 | Retained earnings | 674,500 | Removed sub's RE but consolidated ending RE is still wrong? | |||||||||||
| Depreciation expense | 424,000 | 358,000 | 14,100 | 796,100 | Investment in subsidiary | 974,500 | |||||||||||
| Interest expense | 16,000 | 62,000 | 78,000 | ||||||||||||||
| Income tax expense | 30,000 | 12,000 | 42,000 | Equipment | 60,000 | I know this is right. | |||||||||||
| Equity in subsidiary | (62,000) | - 0 | 62,000 | - 0 | Goodwill | 81,000 | |||||||||||
| Net income | (462,500) | (68,000) | (454,400) | CHECK | Investment in subsidiary | 141,000 | |||||||||||
| Retained earnings, 1/1/21 | (1,343,500) | (626,500) | (1,970,000) | CHECK | Equity in subsidiary | 62,000 | I know this is right. | ||||||||||
| Net income | (462,500) | (68,000) | (454,400) | CHECK | Investment in subsidiary | 62,000 | |||||||||||
| Dividends declared | 120,000 | 20,000 | (140,000) | - 0 | CHECK | ||||||||||||
| Retained earnings, 12/31/21 | (1,686,000) | (674,500) | (2,424,400) | CHECK | Investment in subsidiary | 140,000 | Don't I want to remove dividends? | ||||||||||
| Dividends declared | 140,000 | ||||||||||||||||
| Current assets | 302,000 | 159,000 | 461,000 | ||||||||||||||
| Investment in subsidiary | 1,109,500 | - 0 | (1,037,500) | 72,000 | CHECK | Depreciation expense | 14,100 | Don't I have to recognize depreciation expense for the sub's undervalued assets? | |||||||||
| Equipment (net) | 1,048,000 | 680,000 | 54,000 | 1,782,000 | Equipment | 6,000 | |||||||||||
| Buildings (net) | 810,000 | 592,000 | 1,402,000 | Goodwill | 8,100 | ||||||||||||
| Land | 704,000 | 117,000 | 821,000 | ||||||||||||||
| Goodwill | - 0 | - 0 | 81,000 | 81,000 | |||||||||||||
| Total assets | 3,973,500 | 1,548,000 | 4,619,000 | ||||||||||||||
| Current Liabilities | (560,000) | (500,000) | (1,060,000) | ||||||||||||||
| Other Liabilities | (827,500) | (73,500) | (901,000) | ||||||||||||||
| Common stock | (900,000) | (300,000) | 300,000 | (900,000) | |||||||||||||
| Retained earnings | (1,686,000) | (674,500) | 674,500 | (2,424,400) | CHECK | ||||||||||||
| Total liabilities and equity | (3,973,500) | (1,548,000) | (5,285,400) | ||||||||||||||
| CHECK | |||||||||||||||||
