70.2K

Verified Solution

Link Copied!

hi I have post it before and it's not clear enough here is a clear one. the first two page is the instructions and the last two page is the data that need to be consolidated. thanks

ACCT 4011 The purpose of this assignment is to give you practice in creating and modifying worksheets in Excel. It requires you to use the attached information. Required: Create a consolidation worksheet, like the examples in the text, to arrive at consolidated total figures. a. Give the spreadsheet an appropriate heading i. Include your name Include the course name, and that this is the Excel assignment ii. b. You should have columns for i. Account names ii. The parent company's balances ii. The subsidiary company's balances The debit side of consolidation er The credit side of consolidation entries Consolidated totals. If you wish, you can have one column for the totals or separate columns for debit and credit balances v. vi. 2. Insert the initial data from the example into your worksheet, in th and subsidiary balances columns fo You MAY NOT compute the totals by hand, and just type them in. You must use a formula in the worksheet to compute a. i. Net income ii. Ending retained earnings iii. Total assets iv. Total liabilities and equities. You must use a formula to ensure that net income, as shown in the statement of retained earnings, equals the net income from the income statement data - do NOT hand copy and retype the data. You must use a formula to ensure that the ending retained earnings in the retained earnings statement is the same balance as in your ending balance sheet. Do NOT hand copy and retype the data. b. c. d. Using an "if statement, create a test at the bottom of your worksheet that will say "balanced" or "Unbalanced", depending on whether assets in the following columns equal liabilities plus equity" the parent company column, the subsidiary company column, and the consolidated total columns. Please format the data as follows: e. i. No pennies ii. Commas after every three digits Do not show me 800000, but 800,000. iii. Negative numbers should be denoted using 0 iv. Appropriate underlining to indicate figures before a total, and a total. Follow the example in the data you are given. Insert the data from the consolidation entries into the appropriate columns of your worksheet 3. a. Note that vou will need more than one row to contain the information related to e that you will need more than one row to contain the information related to adjustments to the investment in subsidiary account. Insert whatever rows you need 4. Using formulas, have the spreadsheet co mpute the consolidated totals, based on the data from your entries and the starting data. Use appropriate formulas to ensure debits credits are added across properly. 5. Save your worksheet at this point. 6. Label the tab at the bottom of the worksheet as "Base case opy the tab to a second worksheet, in the same workbook. Label the tab at the bottom of this second worksheet (revised data) Make the following changes to the revised worksheet: 8. Assume that the purchase price had been $200,000 higher, make the following a. changes: i. In the Parent company column, reduce current assets by $200,000 and increase Investment in Subsidiary by $200,000. In Entry A, increase the debit to goodwill by $200,000, and the credit to investment in subsidiary by $200,000 ii. Assume that the intercompany payable and receivable was $100,000 higher than in the base case. b. Assume that the subsidiary has $50,000 less in equipment, and also $50,000 less in liabilities. c. d. Make the above changes to the worksheet. Hopefully, it balances..... 9. Save your worksheet. 10. Submit your worksheet as an email attachment. My email is Daniel. Tinkelman @brooklyn.cuny.edu Accounting 4011 Data for Excel Assignment For the Year ended December 31, 20X1 Assume acquisition occurred 1/1/ 20x1 Sub Accounts Parent Income statement data Revenues Cost of goods sold amortization expense depreciation expense Equity in subsidiary earnings 2,000,000 1,200,000 190,000 90,000 153,000 673,000 600,000 380,000 16,000 40,000 Consolidation entries: Entry S Common Stock Additional paid-in Capital Retained earnings 100,000 220,000 400,000 Inv. In subsidiary 720,000 Net income 164,000 Entry A Trademarks Patented technology 31,000 80,000 Statement of retained earnings Retained earnings, 1/1/20x1 Net income (from above) 400,000 164,000 70,000 2,217,000 494,000 2,044,000 Goodwill 98,000 673,000 Equipment Inv. In subsidiary 12,000 Dividends declared 500,000 197,000 Entry D Investment in subsidiary Retained earnings, 12/31/20x1 Balance sheet Current assets Investment in subsidiary 70,000 Dividends 70,000 1,627,000 500,000 Entry P 1,000,000 Liabilities 40,000 Trademarks Patented technology Equipment (net) Goodwill 580,000 650,000 380,000 240,000 424,000 210,000 current assets 40,000 Entry I Equity in subsidiary earnings 153,000 Inv. In subsidiary 153,000 4,237000 1,374,000 Entry E Amortization expense Total assets 15,000 560,000 100,000 220,000 Retained earnings, 12/31/20x1 (above) 2,217000 494,000 4,237,000 1,374,000 1,050,000 800,000 170,000 Liabilities Equipment, net 4,000 Common Stock Depreciation expense 4,000 Additional paid-in capital Patented technology 15,000 Total liabilities and equity ACCT 4011 The purpose of this assignment is to give you practice in creating and modifying worksheets in Excel. It requires you to use the attached information. Required: Create a consolidation worksheet, like the examples in the text, to arrive at consolidated total figures. a. Give the spreadsheet an appropriate heading i. Include your name Include the course name, and that this is the Excel assignment ii. b. You should have columns for i. Account names ii. The parent company's balances ii. The subsidiary company's balances The debit side of consolidation er The credit side of consolidation entries Consolidated totals. If you wish, you can have one column for the totals or separate columns for debit and credit balances v. vi. 2. Insert the initial data from the example into your worksheet, in th and subsidiary balances columns fo You MAY NOT compute the totals by hand, and just type them in. You must use a formula in the worksheet to compute a. i. Net income ii. Ending retained earnings iii. Total assets iv. Total liabilities and equities. You must use a formula to ensure that net income, as shown in the statement of retained earnings, equals the net income from the income statement data - do NOT hand copy and retype the data. You must use a formula to ensure that the ending retained earnings in the retained earnings statement is the same balance as in your ending balance sheet. Do NOT hand copy and retype the data. b. c. d. Using an "if statement, create a test at the bottom of your worksheet that will say "balanced" or "Unbalanced", depending on whether assets in the following columns equal liabilities plus equity" the parent company column, the subsidiary company column, and the consolidated total columns. Please format the data as follows: e. i. No pennies ii. Commas after every three digits Do not show me 800000, but 800,000. iii. Negative numbers should be denoted using 0 iv. Appropriate underlining to indicate figures before a total, and a total. Follow the example in the data you are given. Insert the data from the consolidation entries into the appropriate columns of your worksheet 3. a. Note that vou will need more than one row to contain the information related to e that you will need more than one row to contain the information related to adjustments to the investment in subsidiary account. Insert whatever rows you need 4. Using formulas, have the spreadsheet co mpute the consolidated totals, based on the data from your entries and the starting data. Use appropriate formulas to ensure debits credits are added across properly. 5. Save your worksheet at this point. 6. Label the tab at the bottom of the worksheet as "Base case opy the tab to a second worksheet, in the same workbook. Label the tab at the bottom of this second worksheet (revised data) Make the following changes to the revised worksheet: 8. Assume that the purchase price had been $200,000 higher, make the following a. changes: i. In the Parent company column, reduce current assets by $200,000 and increase Investment in Subsidiary by $200,000. In Entry A, increase the debit to goodwill by $200,000, and the credit to investment in subsidiary by $200,000 ii. Assume that the intercompany payable and receivable was $100,000 higher than in the base case. b. Assume that the subsidiary has $50,000 less in equipment, and also $50,000 less in liabilities. c. d. Make the above changes to the worksheet. Hopefully, it balances..... 9. Save your worksheet. 10. Submit your worksheet as an email attachment. My email is Daniel. Tinkelman @brooklyn.cuny.edu Accounting 4011 Data for Excel Assignment For the Year ended December 31, 20X1 Assume acquisition occurred 1/1/ 20x1 Sub Accounts Parent Income statement data Revenues Cost of goods sold amortization expense depreciation expense Equity in subsidiary earnings 2,000,000 1,200,000 190,000 90,000 153,000 673,000 600,000 380,000 16,000 40,000 Consolidation entries: Entry S Common Stock Additional paid-in Capital Retained earnings 100,000 220,000 400,000 Inv. In subsidiary 720,000 Net income 164,000 Entry A Trademarks Patented technology 31,000 80,000 Statement of retained earnings Retained earnings, 1/1/20x1 Net income (from above) 400,000 164,000 70,000 2,217,000 494,000 2,044,000 Goodwill 98,000 673,000 Equipment Inv. In subsidiary 12,000 Dividends declared 500,000 197,000 Entry D Investment in subsidiary Retained earnings, 12/31/20x1 Balance sheet Current assets Investment in subsidiary 70,000 Dividends 70,000 1,627,000 500,000 Entry P 1,000,000 Liabilities 40,000 Trademarks Patented technology Equipment (net) Goodwill 580,000 650,000 380,000 240,000 424,000 210,000 current assets 40,000 Entry I Equity in subsidiary earnings 153,000 Inv. In subsidiary 153,000 4,237000 1,374,000 Entry E Amortization expense Total assets 15,000 560,000 100,000 220,000 Retained earnings, 12/31/20x1 (above) 2,217000 494,000 4,237,000 1,374,000 1,050,000 800,000 170,000 Liabilities Equipment, net 4,000 Common Stock Depreciation expense 4,000 Additional paid-in capital Patented technology 15,000 Total liabilities and equity

Answer & Explanation Solved by verified expert

See Answer