Creating and modifying worksheets in Excel.

a. Give the spreadsheet an appropriate heading i. Include your name ii. Include the course name, and that this is the Excel assignment b. You should have columns for i. Account names ii. The parent company's balances iii. The subsidiary company's baiances 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 eamings 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 eamings 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 0 . 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 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" 7. Copy the tab to a second worksheet, in the same workbook. Label the tab at the bottom of this second worksheet (revised data) 8. 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.... 9. Save your worksheet. 10. Submit your worksheet via BlackBoard Note - it is good form to use the total of the adjustments from your income statement in the adjustment columns of your statement of retained earnings for the "net income" row. Similarly, the total of adjustments from your statement of retained earnings can be used in the adjustment columns of your balance sheet for the "retained earnings" row. This allows these rows to add across. Accounting 4011 Data for Excel Assligument Fot the Year ended December 31, 20xi Accounts inconse statement data Trademaris Patented technology 240,000 currentassets 40,000 Equipmens (net) 424,000 Ertryy Goodwill 210,000 Equity insubsidary earnings 153,000 rotal assets 4,12000424,0001 cnetyr subsidiary 153,000 Amorizzation expense 15,000 Listaities. Common stock. Additional paid-incapital 20,000270,000 Depreclation expensie 4,000 Retained carningr,12/31/20x fabovel 2,21,000195,000 Total liabities and equaty 4.137,0004 a. Give the spreadsheet an appropriate heading i. Include your name ii. Include the course name, and that this is the Excel assignment b. You should have columns for i. Account names ii. The parent company's balances iii. The subsidiary company's baiances 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 eamings 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 eamings 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 0 . 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 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" 7. Copy the tab to a second worksheet, in the same workbook. Label the tab at the bottom of this second worksheet (revised data) 8. 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.... 9. Save your worksheet. 10. Submit your worksheet via BlackBoard Note - it is good form to use the total of the adjustments from your income statement in the adjustment columns of your statement of retained earnings for the "net income" row. Similarly, the total of adjustments from your statement of retained earnings can be used in the adjustment columns of your balance sheet for the "retained earnings" row. This allows these rows to add across. Accounting 4011 Data for Excel Assligument Fot the Year ended December 31, 20xi Accounts inconse statement data Trademaris Patented technology 240,000 currentassets 40,000 Equipmens (net) 424,000 Ertryy Goodwill 210,000 Equity insubsidary earnings 153,000 rotal assets 4,12000424,0001 cnetyr subsidiary 153,000 Amorizzation expense 15,000 Listaities. Common stock. Additional paid-incapital 20,000270,000 Depreclation expensie 4,000 Retained carningr,12/31/20x fabovel 2,21,000195,000 Total liabities and equaty 4.137,0004