Question

1 Approved Answer

Posted on Sep 04, 2024

Only Experts take this... 40 points The course project involves the completion of a comprehensive consolidation of financial statements in EXCEL. Please note that most

Only Experts take this... image text in transcribed

image text in transcribed

40 points The course project involves the completion of a comprehensive consolidation of financial statements in EXCEL. Please note that most of the points are associated with the sophistication of the EXCEL program. The goal here is to design an EXCEL application that will complete the consolidation in future years and will minimize the chances that a future user of the EXCEL application will make an error or will struggle with the application. The fewer required inputs ongoing, the better. The fewer required decisions by future users the better. Essentially, attempt to design an EXCEL spreadsheet that automatically completes the consolidation process once the current year financial statement information is input. As a hint, cases earning all the points usually involve the use of HLOOKUPS, VLOOKUPS, or a series of embedded IF statements. Even the numbers for the A and E consolidation/elimination entries can be automatically input via LOOKUP tables or embedded IF statements. Grading allocation: 25 points Accuracy Sophistication of the EXCEL program (minimize the required inputs) 10 points Professional polish Complete the following consolidation: On January 1, 2020, Placht Corporation exchanged $2,500,000 cash for 80% of the outstanding voting common stock of Slader Company. The consideration transferred by Placht provided a reasonable basis for assessing the total 1/1/20 fair value of Slader Company. At the acquisition date, Slader reported the following owner's equity amounts in its balance sheet: Common stock $100,000 Additional paid-in capital $400,000 Retained earnings $1,000,000 In determining its acquisition offer, Placht noted that the values for Slader's recorded assets and liabilities approximated their fair values except for the following: Land (indefinite life) was undervalued by $450,000 Buildings and equipment with a 10-year remaining useful life were undervalued by $100,000; A patent with a 12-year remaining useful life was undervalued by $120,000. An internally generated customer base with a 10-year useful life and an assessed fair value of $400,000 was not reflected on Slader's books. Any remaining difference is attributed to goodwill (or gain on bargain purchase). The subsidiary reported net income in 2020 totaling $200,000 and paid dividends totaling $50,000 to the common shareholders. The December 31, 2021 financial statements information for the affiliates is provided below. Other information The gain on sale of land was associated with a land sale by Slader to Tristen Company (an unrelated entity). Required: 1. Develop an EXCEL spreadsheet application for the consolidation of the affiliates. The application should automatically complete as many of the cells as possible in order to minimize needed human intervention. In other words, if we already know what the number will be or where it will come from program the EXCEL application to do it automatically. Think hard. Just about every amount on the consolidation worksheet can be automated via cell reference or cell calculation or LOOKUPS once the financial statements from the affiliates are input into columns B and C. 2. Complete the consolidation worksheet for Placht Company and subsidiary. 3. On separate tabs, prepare a balance sheet and income statement for the consolidated entity in publishable form from the consolidation worksheet. Placht Inc. and Subsidiary Consolidation Worksheet For the year ended December 31, 2021 Accounts Placht Slader Income Statement: Revenues (5,500,000) (2,200,000) Cost of goods sold 1,500,000 1,600,000 Depreciation expense 400,000 180,000 Amortization expense 100,000 90,000 Interest expense 300,000 30,000 Interest revenue (20,000) Gain on sale of land (50,000) (280,000) (3,500,000 (350,000 (6,500,000) Equity in income of Slader Net Income Statement of Retained Earnings: Retained earnings, 1/1 - Placht Retained earnings, 1/1 - Slader Net income (see above) Dividends paid - common Retained earnings, 12/31 (3,500,000) 700,000 (9.300.000 (1,150,000) (350,000) 50,000 (1.450,000) 740,000 100,000 200,000 Balance Sheet: Cash Accounts receivable Accounts receivable from Slader Inventory Investment in Slader Common Stock Land 990,000 10,000 1,500,000 2,860,000 3,500,000 300,000 400,000 Buildings and Equipment, net 5,000,000 1,700,000 600,000 15,200,000 100,000 2,800,000 (600,000) Patents Total Assets Liabilities: Accounts payable Accounts payable to Placht Bonds payable Discount on bonds payable Stockholders' Equity: Common stock Additional paid-in capital Retained earnings (390,000) (10,000) (500,000) 50,000 (3,300,000) (500,000) (100,000) (1,500,000) (400,000) (9,300,000) (1,450,000) Total liabilities and stockholders' equity (15,200,000 (2.800.000) 40 points The course project involves the completion of a comprehensive consolidation of financial statements in EXCEL. Please note that most of the points are associated with the sophistication of the EXCEL program. The goal here is to design an EXCEL application that will complete the consolidation in future years and will minimize the chances that a future user of the EXCEL application will make an error or will struggle with the application. The fewer required inputs ongoing, the better. The fewer required decisions by future users the better. Essentially, attempt to design an EXCEL spreadsheet that automatically completes the consolidation process once the current year financial statement information is input. As a hint, cases earning all the points usually involve the use of HLOOKUPS, VLOOKUPS, or a series of embedded IF statements. Even the numbers for the A and E consolidation/elimination entries can be automatically input via LOOKUP tables or embedded IF statements. Grading allocation: 25 points Accuracy Sophistication of the EXCEL program (minimize the required inputs) 10 points Professional polish Complete the following consolidation: On January 1, 2020, Placht Corporation exchanged $2,500,000 cash for 80% of the outstanding voting common stock of Slader Company. The consideration transferred by Placht provided a reasonable basis for assessing the total 1/1/20 fair value of Slader Company. At the acquisition date, Slader reported the following owner's equity amounts in its balance sheet: Common stock $100,000 Additional paid-in capital $400,000 Retained earnings $1,000,000 In determining its acquisition offer, Placht noted that the values for Slader's recorded assets and liabilities approximated their fair values except for the following: Land (indefinite life) was undervalued by $450,000 Buildings and equipment with a 10-year remaining useful life were undervalued by $100,000; A patent with a 12-year remaining useful life was undervalued by $120,000. An internally generated customer base with a 10-year useful life and an assessed fair value of $400,000 was not reflected on Slader's books. Any remaining difference is attributed to goodwill (or gain on bargain purchase). The subsidiary reported net income in 2020 totaling $200,000 and paid dividends totaling $50,000 to the common shareholders. The December 31, 2021 financial statements information for the affiliates is provided below. Other information The gain on sale of land was associated with a land sale by Slader to Tristen Company (an unrelated entity). Required: 1. Develop an EXCEL spreadsheet application for the consolidation of the affiliates. The application should automatically complete as many of the cells as possible in order to minimize needed human intervention. In other words, if we already know what the number will be or where it will come from program the EXCEL application to do it automatically. Think hard. Just about every amount on the consolidation worksheet can be automated via cell reference or cell calculation or LOOKUPS once the financial statements from the affiliates are input into columns B and C. 2. Complete the consolidation worksheet for Placht Company and subsidiary. 3. On separate tabs, prepare a balance sheet and income statement for the consolidated entity in publishable form from the consolidation worksheet. Placht Inc. and Subsidiary Consolidation Worksheet For the year ended December 31, 2021 Accounts Placht Slader Income Statement: Revenues (5,500,000) (2,200,000) Cost of goods sold 1,500,000 1,600,000 Depreciation expense 400,000 180,000 Amortization expense 100,000 90,000 Interest expense 300,000 30,000 Interest revenue (20,000) Gain on sale of land (50,000) (280,000) (3,500,000 (350,000 (6,500,000) Equity in income of Slader Net Income Statement of Retained Earnings: Retained earnings, 1/1 - Placht Retained earnings, 1/1 - Slader Net income (see above) Dividends paid - common Retained earnings, 12/31 (3,500,000) 700,000 (9.300.000 (1,150,000) (350,000) 50,000 (1.450,000) 740,000 100,000 200,000 Balance Sheet: Cash Accounts receivable Accounts receivable from Slader Inventory Investment in Slader Common Stock Land 990,000 10,000 1,500,000 2,860,000 3,500,000 300,000 400,000 Buildings and Equipment, net 5,000,000 1,700,000 600,000 15,200,000 100,000 2,800,000 (600,000) Patents Total Assets Liabilities: Accounts payable Accounts payable to Placht Bonds payable Discount on bonds payable Stockholders' Equity: Common stock Additional paid-in capital Retained earnings (390,000) (10,000) (500,000) 50,000 (3,300,000) (500,000) (100,000) (1,500,000) (400,000) (9,300,000) (1,450,000) Total liabilities and stockholders' equity (15,200,000 (2.800.000)