1. Create a consolidation worksheet, like the examples in the text, to arrive at consolidated total figures. a. You should have columns for i. Account names ii. The parent company's balances iii. The subsidiary company's balances iv. The debit side of consolidation entries v. The credit side of consolidation entries vi. Consolidated totals. If you wish, you can have one column for the totals, or separate columns for debit and credit balances 2. Insert the initial data from the example into your worksheet, in the columns for parent and subsidiary balances. a. You MAY NOT compute the totals by hand, and just type them in. You must use a formula in the worksheet to compute: i. Net income ii. Ending retained earnings iii. Total assets iv. Total liabilities and equities. b. 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. c. 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. 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. e. Please format the data as follows: i. No pennies ii. Commas after every three digits -.- Do not show me 800000 , but 800,000 . iii. Negative numbers should be denoted using (). iv. Appropriate underlining to indicate figures before a total, and a total. Follow the example in the data you are given. 3. Insert the data from the consolidation entries into the appropriate columns of your worksheet. a. Note 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. b. Note that the total adjustments from the income statement will be useful in your statement of owners' equity, and the total adjustments from your statement of owners equity will help you balance the retained earnings figure on your balance sheet. 4. Using formulas, have the spreadsheet compute the consolidated totals, based on the data from your entries and the starting data. Use appropriate formulas to ensure debits and Make the following changes to the revised worksheet: a. Assume that the purchase price had been $300,000 higher, make the following changes: i. In the Parent company column, reduce current assets by $300,000 and increase Investment in Subsidiary by $300,000. ii. In Entry A, increase the debit to goodwill by $300,000, and the credit to investment in subsidiary by $300,000. b. Assume that the intercompany payable and receivable was $150,000 higher than in the base case. c. Assume that the subsidiary has $75,000 less in equipment, and also $75,000 less in liabilities. d. Make the above changes to the worksheet. Hopefully, it balances...... 1. Create a consolidation worksheet, like the examples in the text, to arrive at consolidated total figures. a. You should have columns for i. Account names ii. The parent company's balances iii. The subsidiary company's balances iv. The debit side of consolidation entries v. The credit side of consolidation entries vi. Consolidated totals. If you wish, you can have one column for the totals, or separate columns for debit and credit balances 2. Insert the initial data from the example into your worksheet, in the columns for parent and subsidiary balances. a. You MAY NOT compute the totals by hand, and just type them in. You must use a formula in the worksheet to compute: i. Net income ii. Ending retained earnings iii. Total assets iv. Total liabilities and equities. b. 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. c. 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. 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. e. Please format the data as follows: i. No pennies ii. Commas after every three digits -.- Do not show me 800000 , but 800,000 . iii. Negative numbers should be denoted using (). iv. Appropriate underlining to indicate figures before a total, and a total. Follow the example in the data you are given. 3. Insert the data from the consolidation entries into the appropriate columns of your worksheet. a. Note 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. b. Note that the total adjustments from the income statement will be useful in your statement of owners' equity, and the total adjustments from your statement of owners equity will help you balance the retained earnings figure on your balance sheet. 4. Using formulas, have the spreadsheet compute the consolidated totals, based on the data from your entries and the starting data. Use appropriate formulas to ensure debits and Make the following changes to the revised worksheet: a. Assume that the purchase price had been $300,000 higher, make the following changes: i. In the Parent company column, reduce current assets by $300,000 and increase Investment in Subsidiary by $300,000. ii. In Entry A, increase the debit to goodwill by $300,000, and the credit to investment in subsidiary by $300,000. b. Assume that the intercompany payable and receivable was $150,000 higher than in the base case. c. Assume that the subsidiary has $75,000 less in equipment, and also $75,000 less in liabilities. d. Make the above changes to the worksheet. Hopefully, it balances