 I only need the FinancialProjections_Answers (Excel Spreadsheet)
 o Complete the table in the “ABC Financial Ratios†tab.Â
o Correct the consolidation worksheet in “ABC Projections – Controlâ€Â
Please provide the calculation for the answer in the excel spreadsheet. I want to know how you get the answer. FYI I also provide a sample of the consolidation.Â
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<
Practice
|
