+1(978)310-4246 credencewriters@gmail.com
  

 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

On January 1, 2020, Paloma Corporation exchanged $1,710,000 cash for 90 percent of the outstanding voting stock of San Marco Company. The consideration transferred by Paloma provided a reasonable basis for assessing the total January 1, 2020, fair value of San Marco Company. At the acquisition date, San Marco reported the following owners’ equity amounts in its balance sheet:
Common stock $ 400,000
Additional paid-in capital 60,000
Retained earnings 265,000
In determining its acquisition offer, Paloma noted that the values for San Marco’s recorded assets and liabilities approximated their fair values. Paloma also observed that San Marco had developed internally a customer base with an assessed fair value of $800,000 that was not reflected on San Marco’s books. Paloma expected both cost and revenue synergies from the combination.
1. Prepare an excess fair-value allocation and amortization schedule and goodwill allocation schedule:
2. Use the following separate financials for the year ended December 31, 2021 to prepare consolidated totals:
Paloma San Marco DR CR NCI Consolidated Totals Entry S
Revenues (1,843,000) (675,000) (2,518,000)
Cost of goods sold 1,100,000 322,000 1,422,000
Depreciation expense 125,000 120,000 245,000
Amortization expense 275,000 11,000 – 0 286,000
Interest expense 27,500 7,000 34,500
Equity in income of San Marco (121,500) – 0 – 0 (121,500) Entry A1
Net income (437,000) (215,000)
Consolidated net income (652,000)
Noncontrolling interest in CNI – 0
Controlling interest net income </
error: Content is protected !!