Â 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 10year 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
