Please help me fill out the excel spreadsheet from the information attached. Only answer if you are going to actually provide/help me with an answer.

COMPREHENSIVE CONSOLIDATION PROJECT Purpose This course focuses mostly on the topic of business consolidations. At the beginning of the course we start with easier consolidation situations, and then we gradually add more complexities as the semester progresses. In this project you are asked to take a complex business combination, work through the consolidation process, and prepare the consolidated financial statements for the combined entity. By completing this project, the hope is you will have a better understanding of the entire consolidation process and gain confidence that you can solve a real-life, complicated business consolidation. Directions You will complete the problem described on the next page of this document. All of the requirements (allocation & amortization schedule, consolidation entries, consolidated worksheet, and consolidated financial statements) are to be completed in one pre-formatted Microsoft Excel file that will be sent to you electronically. Here is the format of the Excel file that will be sent to you: The first tab: The second tab: The third tab: The forth tab: The fifth tab: The sixth tab: The seventh tab: The Allocation & Amortization Schedules Income Calculations Consolidation Entries Consolidation Worksheet Consolidated income statement Consolidated Statement of Stockholders' Equity Consolidated balance sheet Note: See the examples on p.165 of your text as a guideline on how to prepare and format the 3 financial statements on Tabs 5-7 of your Excel file. Due Date Friday, December 9, 2016 Estimated Time to Complete Allocation & Amortization Schedules Income Calcualations Consolidation Entries for Shelby & Borner (Father & Son) Consolidation Entries for DeNoma & Shelby (Grandfather & Father) Completed Consolidation Worksheet Completed Financial Statements 45 minutes 30 minutes 60 minutes 60 minutes 75 minutes 30 minutes TOTAL ESTIMATED TIME 5 HOURS ACC 471 CONSOLIDATION PROJECT Shelby Corporation purchases 90% of the outstanding stock of Borner Company on January 1, 2012, for $603,000 cash. At that time, Borner has the following stockholders' equity balances: Common Stock = $200,000 Paid-In Capital in Excess of Par = $80,000 Retained Earnings = $300,000 All book values approximate fair values except for the plant assets (undervalued by $50,000 and with an estimated remaining life of 10 years). Any remaining excess is goodwill. DeNoma Company acquires a 60% interest in Shelby on January 1, 2014, for $750,000. At this time, Shelby has consolidated shareholders' equity of: Common Stock = $500,000 Paid-In Capital in Excess of Par = $150,000 Controlling Retained Earnings = $500,000 (not including amortization of excess price applicable to investment in Borner). At that time, it is also determined that Shelby's plant assets are undervalued by $50,000 and have a 10-year remaining useful life. Any remaining excess is goodwill. Intercompany merchandise sales from Borner to Shelby for 2014 are: Borner's goods in Shelby's beginning inventory = $7,500 Sales during 2014 = $125,000 Borner's goods in Shelby's ending inventory = $10,000 Gross profit on intercompany sales = 80% On January 1, 2014, Shelby sells plant assets with a cost of $80,000 and accumulated depreciation of $45,000 to DeNoma for $50,000. Remaining useful life on the date of sale is estimated to be 5 years. Shelby & DeNoma use the partial equity method to account for their investments. The trial balances on December 31, 2015 are as follows: Required 1. Prepare an allocation & amortization schedule for Shelby's investment in Borner and DeNoma's investment in Shelby. TAB 1 OF EXCEL FILE (Hint: Shelby's consolidated retained earnings on January 1, 2014 does not include amortization of excess price applicable to investment in Borner). 2. Determine income calculations for Borner, Shelby, DeNoma, and the noncontrolling interest's share of net income for 2015. TAB 2 OF EXCEL FILE 3. Complete the consolidation entries for Shelby's ownership of Borner and DeNoma's ownership of Shelby on December 31, 2015. TAB 3 OF EXCEL FILE 4. Complete the consolidation worksheet for this father-son-grandson relationship. TAB 4 OF EXCEL FILE. 5. Prepare the December 31, 2015 consolidated financial statements for this consolidated entity. TABS 5-7 OF EXCEL FILE. (Hint: See the examples on p.165 of your text as a guideline on how to prepare and format the 3 financial statements) NAME ___________________________ SHELBY CORPORATION & BORNER COMPANY Purchase Price Allocation & Annual Amortization January 1, 2012 Useful Life Allocation Consideration Paid By Shelby NCI Fair Value Acquisition-Date Subsidiary Fair Value Book Value of Subsidiary FV in excess of BV Adjustments to: Plant Assets Goodwill Annual excess amortizations Annual Excess Amortizations 0 0 10 Indefinite 0 0 0 DENOMA & SHELBY COMPANY Purchase Price Allocation & Annual Amortization January 1, 2014 Useful Life Allocation Consideration Paid By DeNoma NCI Fair Value Acquisition-Date Subsidiary Fair Value Book Value of Subsidiary FV in excess of BV Adjustments to: Plant Assets Goodwill Annual excess amortizations Annual Excess Amortizations 0 0 10 Indefinite 0 0 0 BORNER COMPANY'S ACCRUAL BASED INCOME & NCI Internally Calculated Operating Income (see consolidated worksheet) Excess Fair Value Amortization of Plant Assets Recognition of Previously Deferred Gross Profits Deferral of Intra-Entity Gross Profits as of 12/31/15 Borner's Accrual Based Net Income Outside Ownership % Noncontrolling Interest in Borner's Net Income 10% 0 SHELBY COMPANY'S ACCRUAL BASED INCOME & NCI Internally Calculated Operating Income (see consolidated worksheet) Excess Fair Value Amortization of Plant Assets Gain on Sale of Plant Assets to DeNoma: Realized Through Plant Asset Use By DeNoma Share of Borner's Accrual Based Net Income Shelby's Accrual Based Net Income Outside Ownership % Noncontrolling Interest in Shelby's Net Income DENOMA COMPANY'S SHARE OF CONSOLIDATED NET INCOME Internally Calculated Operating Income (see consolidated worksheet) Share of Shelby's Accrual Based Net Income Controlling Interest in Consolidated Net Income 3000 40% 0 Consolidation Journal Entries: Shelby's Ownership of Borner (Not Physically Recored in Either Company's Records) Entry B*G ACCOUNT (Recognize gross profit now earned in 2015) B*C (Convert Beg. R/E to full equity method) BS (Eliminate Little's Stockholder's Equity) BA (Allocate unamortized excess) BI (Income accrued from subsidiary - reverse) BE (Expense excess amortization) BTI (Eliminate sale/purchase) BG (Remove unrealized gross profit) DEBIT CREDIT Consolidation Journal Entries: DeNoma's Ownership of Shelby (Not Physically Recored in Either Company's Records) Entry D*G ACCOUNT DEBIT CREDIT N/A (Recognize gross profit now earned in 2014) D*C 16,140 16,140 (Convert Beg. R/E to full equity method) D*TA (Return plant assets to historical cost & adjust other accounts) DS (Eliminate Little's Stockholder's Equity) DA (Allocate unamortized excess) DI (Income accrued from subsidiary - reverse) DE (Expense excess amortization) DTI N/A (Eliminate sale/purchase) DG N/A (Remove unrealized gross profit) DED (Remove unrealized gross profit) (Remove unrealized gross profit) Accounts Income Statement Sales DENOMA COMPANY & CONSOLIDATED SUBSIDIARIES Consolidated Worksheet For Year Ending December 31, 2015 DeNoma Shelby Borner Consolidation Entries Company Company Company Debit Credit ($900,000) ($700,000) ($600,000) Cost of Goods Sold 570,000 425,000 400,000 Expenses 205,000 200,000 150,000 0 (72,000) (197,000) (45,000) 0 (120,000) 0 0 (50,000) (922,000) 0 0 (620,000) 0 0 0 0 (500,000) (197,000) (120,000) (50,000) 0 0 0 (1,119,000) 0 0 0 (740,000) 0 0 0 (550,000) $900,000 75,000 894,000 $2,000 60,000 0 $390,000 40,000 0 0 828,000 0 1,200,000 800,000 600,000 (450,000) (300,000) (200,000) Total Assets NCI in Borner Co. 1/1/14 2,619,000 0 1,390,000 0 830,000 0 NCI in Shelby Co. 1/1/14 0 0 0 Total NCI 12/31/14 Common Stock DeNoma Company Shelby Company Borner Company Additional Paid-In Capital DeNoma Company Shelby Company Borner Company Retained Earnings 12/31/14 (above) Total Liab & Equities 0 0 0 (1,500,000) 0 0 0 (500,000) 0 0 0 (200,000) 0 0 0 (1,119,000) (2,619,000) 0 (150,000) 0 (740,000) (1,390,000) 0 0 (80,000) (550,000) (830,000) Income of Borner Company Income of Shelby Company Separate Company Income Consolidated Net Income Noncontrolling Interest - Borner N.I. Noncontrolling Interest - Shelby N.I. Consolidated Net Income to DeNoma Stmt. of R/E Retained Earnings (1/1) DeNoma Company Shelby Company Borner Company Net Income (above) Dividends Paid DeNoma Company Shelby Company Borner Company Retained Earnings (12/31) Balance Sheet Other Current Assets Inventory Investment in Shelby Co. Investment in Borner Co. Plant Assets Accumulated Depreciation Noncontrolling Consolidated Interest Totals Goodwill 0 0