1. FinancialProjections_Answers (Excel Spreadsheet)
Complete the table in the “ABC Financial Ratios” tab.
Correct the consolidation worksheet in “ABC Projections – Control”.
2. Written report – you can use the template provided.
The report should be three pages max.
The report should include the following content:
The title page which clearly identifies the authors
Comparison and discussion of ratios for ABC (unconsolidated) vs. ABC (consolidated) with respect to liquidity, solvency, and profitability
ACCT 411 Group Project: Consolidated Financial Statement Analysis Due before midnight on Thursday, May 4, 2023
(40 points)
Overview In this class, we learned that a company may invest in another company as part of a strategic business decision. When the level of ownership exceeds 50%, consolidated reporting is required because the parent company effectively has control of the subsidiary company. As we have seen, the consolidation process complicates the financial statement preparation process. Another significant consequence of consolidation is the effect it has on financial ratios. In this project, I would like you to assume you are working in the accounting department at Firm ABC. ABC currently owns a 45% interest in XYZ Corp. The CEO of ABC is considering acquiring the remaining 55% interest at the beginning of 2026 but is concerned about the reporting consequences. Before committing to anything, the CEO would like to see how consolidated reporting would change certain financial ratios. The CEO attempted to prepare a projected consolidation, but they made several mistakes. Your group has been tasked with fixing the errors and preparing a report that outlines how the acquisition would impact financial ratios for 2026. Data/Resources Provided • FinancialProjections_Class (Excel spreadsheet):
o ABC Financial Ratios for years 2023, 2024, and 2025 o ABC Projected Financials for year ended 2026 (assuming no control is established) o ABC Projected Financials for year ended 2026 & XYZ Projected Financial Statements
for year ended 2026 (assuming control is established) • Report Template (Microsoft Word)
Deliverables 1. FinancialProjections_Answers (Excel Spreadsheet)
o Complete the table in the “ABC Financial Ratios” tab. o Correct the consolidation worksheet in “ABC Projections – Control”.
2. Written report – you can use the template provided or create your own! (PDF) o The report should be 3 pages max. o The report should include the following content:
Title page which clearly identifies authors (i.e., group members) Comparison and discussion of ratios for ABC (unconsolidated) vs ABC
(consolidated) with respect to liquidity, solvency, and profitability.
,
Instruction
| 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 | |||||||||||||||||
image1.png
,
REPORT TITLE
Authored by: GROUP MEMBER NAMES (GROUP #)
|
|
|||||
|
|
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 |
|
|
|
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% |