please i need the formulas for what you put in excel sheet

Excel Assignment 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: 1. Create a consolidation worksheet, like the examples in the text, to arrive at consolidated total figures a. Give the spreadsheet an appropriate heading Due 10/15 i. Include your name i Include the course name, and that this is the Excel assignment b. You should have columns for . Account names ii. The parent company's balances ii 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 i. Ending retained earnings i. 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 eamings, 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 T cupy anuype uie uata. 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 i. Commas after every three digits Do not show me 800000, but 800,000 i. 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. 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. I Uaiaices... 9. Save your worksheet 10. Submit your worksheet via BlackBoard Data for Excel Assignment For the Vear ended December 31, 20X1 Assume acquisition occurred 1/1/ 20s1 Sub- Accounting 4011 Accounts Inceme statement datar Pareat Cosolidation entrics Revenues 2.300,000 600,000 Etry S Cost of poods sold 1500,000 380,000 Common Stock Aditional paid-in Capital Retained caings aIn idiary 100,000 amortiration expense 190,000 16.000 220,000 depreciation expense 90,000 40,000 400,000 Equity in subsidiary carnings 153,000 720,000 Net income 673.000- 164.000 Entry A Trademarks B1.000 Statement of retained earnings Patented technology 80,000 Retained earmings, 1/1/20x1 2044,000 400,000 Goodwill 38,000 Net income (from above) 673,000 164,000 Fuipmnt 12.000 Dividends declared Retained earnings 12/31/20x1 500,000 70,000 aidiary 197,000 2217,000 494.000 Entry D vemet in subsidiaryp0,000 Balance sheet Dividends T0,000 Cunent assets 1,627.000 500,000 Entry P evestment in subsidiary 1,000,000 Lubilities 40,000 Trademarks ss0,000 240,000 umrent assets 40,000 Patented technology 424,000 Etry 1 Eqity in sbsidiary eamings 680,000 Equipment (net) 350,000 210,000 153,000 Goodwill idiary 153,000 Total assets 4237.000 1.374000 Entry E Amortiration expense 15,000 Liabilities 1,050,000 S60,000 Faipment, act 4.000 Common Stock 870,000 100,000 Depeeciation expnse 4.000 Additional paid-in capital Retained earnings 12/31/20s1 (above) 100,000 220,000 Pced techology 15,000 2217.000 494,000 Total labilities and equity 4237.000 374,000 Excel Assignment 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: 1. Create a consolidation worksheet, like the examples in the text, to arrive at consolidated total figures a. Give the spreadsheet an appropriate heading Due 10/15 i. Include your name i Include the course name, and that this is the Excel assignment b. You should have columns for . Account names ii. The parent company's balances ii 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 i. Ending retained earnings i. 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 eamings, 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 T cupy anuype uie uata. 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 i. Commas after every three digits Do not show me 800000, but 800,000 i. 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. 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. I Uaiaices... 9. Save your worksheet 10. Submit your worksheet via BlackBoard Data for Excel Assignment For the Vear ended December 31, 20X1 Assume acquisition occurred 1/1/ 20s1 Sub- Accounting 4011 Accounts Inceme statement datar Pareat Cosolidation entrics Revenues 2.300,000 600,000 Etry S Cost of poods sold 1500,000 380,000 Common Stock Aditional paid-in Capital Retained caings aIn idiary 100,000 amortiration expense 190,000 16.000 220,000 depreciation expense 90,000 40,000 400,000 Equity in subsidiary carnings 153,000 720,000 Net income 673.000- 164.000 Entry A Trademarks B1.000 Statement of retained earnings Patented technology 80,000 Retained earmings, 1/1/20x1 2044,000 400,000 Goodwill 38,000 Net income (from above) 673,000 164,000 Fuipmnt 12.000 Dividends declared Retained earnings 12/31/20x1 500,000 70,000 aidiary 197,000 2217,000 494.000 Entry D vemet in subsidiaryp0,000 Balance sheet Dividends T0,000 Cunent assets 1,627.000 500,000 Entry P evestment in subsidiary 1,000,000 Lubilities 40,000 Trademarks ss0,000 240,000 umrent assets 40,000 Patented technology 424,000 Etry 1 Eqity in sbsidiary eamings 680,000 Equipment (net) 350,000 210,000 153,000 Goodwill idiary 153,000 Total assets 4237.000 1.374000 Entry E Amortiration expense 15,000 Liabilities 1,050,000 S60,000 Faipment, act 4.000 Common Stock 870,000 100,000 Depeeciation expnse 4.000 Additional paid-in capital Retained earnings 12/31/20s1 (above) 100,000 220,000 Pced techology 15,000 2217.000 494,000 Total labilities and equity 4237.000 374,000