Required:

Create a consolidation worksheet, like the examples in the text, to arrive at consolidated total figures. Give the spreadsheet an appropriate heading

You should have columns for

Account names

The parent companys balances

The subsidiary companys balances

The debit side of consolidation entries

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

Insert the initial data from the example into your worksheet, in the columns for parent and subsidiary balances.You MAY NOT compute the totals by hand, and just type them in. You must use a formula in the worksheet to compute:

Net income

Ending retained earnings

Total assets

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.

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:

No pennies

Commas after every three digits --- Do not show me 800000, but 800,000.

Negative numbers should be denoted using ().

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.

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.

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 credits are added across properly.

Save your worksheet at this point.

Label the tab at the bottom of the worksheet as Base case

Copy 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:Assume that the purchase price had been $200,000 higher, make the following changes:

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.

Assume that the intercompany payable and receivable was $100,000 higher than in the base case.

Assume that the subsidiary has $50,000 less in equipment, and also $50,000 less in liabilities.

Make the above changes to the worksheet. Hopefully, it balances