- Learning Objectives 1. Prepare a worksheet for a service firm. 2. Prepare financial statements from a worksheet. 3. Alter the worksheet to include a properly designed income statement. 4. Create a pie chart showing the amount of all assets. - Problem Data The general ledger of ABC Company shows the following balances at December 31 , 2019 before adjustments: Smith has asked you to develop a worksheet that will serve as a trial balance. Use the data above as input your model. Information for the adjusting entries is as follows: a. Depreciation on equipment is $800 for the accounting period. Fundamental Accounting (I) $3,000 of supplies were on hand. c. Prepaid insurance had a $1,950 normal balance prior to adjustment. By year cad $600 was unexpired. d. Services provided but unrecorded totaled $1,500. c. Accrued salaries but not paid at year-end are $2,000. f. A 6-month, $30,000 note was signed in November 1, 2019. The annual interest rate is 6%. Principal and interest are payable at the end of the 6 th month. Interest was accrued for 2019. g. On 7/1/2019,ABC received $3,000 as a down payment from a client for the service in the coming 12 months. - Required 1. As the accountant for ABC Company, you have been asked to prepare financial statements for the year. A file called WORKSHEET has been provided to assist you in this assignment. 2. The general ledger before adjustment and adjusting entries for the year ended 2019 are given above. Develop a worksheet that will serve as a trial balance in first tab "ABC Company Worksheet" in the Excel file). 3. In addition, there are ten formulas requested to complete the worksheet, including from FORM1 to FORM10 in gray. 4. Enter the formulas in appropriate cells on the worksheet. Then enter the trial balance and adjusting amounts in Columns B, C, E and F. Also, in Column D, insert the letter corresponding to the adjusting entry (a g). Furthermore, enter the formulas in Columns G and H that reference the appropriate cells in Columns B, C,E, and F. Column A is frozen on the screen to assist you in completing requirement 4. 5. Complete the income statement and balance sheet columns by entering formulas in Columns I, J, K and L that reference the appropriate cells in columns G or H. Net income will be calculated for the income statement and balance shect. Check to be sure that these double underline cells are the same. 6. Using the data on your completed workshect, prepare a pie chart showing the percentage of each asset to total assets after adjustments in the second tab "Pie chart" in the Excel file. - What-if Analysis 7. Suppose you discover that an assistant in your department had misunderstood your instructions and had provided you with wrong information on two of the adjusting entries. Supplies consumed during the year should have been $3,000, and the insurance premiums expired at year end were $600. Make the corrections and 2 provide your answer in the third tab "What-if analysis" in the Excel file. - Financial Statements 8. Use the corrected worksheet to prepare an income statement, a statement of changes in owner's equity, and a statement of financial position in the fourth tab "Financial statements" in the Excel file. - Submission Instruction Submit via Moodle Deadline: December 4 th 23:59 - Learning Objectives 1. Prepare a worksheet for a service firm. 2. Prepare financial statements from a worksheet. 3. Alter the worksheet to include a properly designed income statement. 4. Create a pie chart showing the amount of all assets. - Problem Data The general ledger of ABC Company shows the following balances at December 31 , 2019 before adjustments: Smith has asked you to develop a worksheet that will serve as a trial balance. Use the data above as input your model. Information for the adjusting entries is as follows: a. Depreciation on equipment is $800 for the accounting period. Fundamental Accounting (I) $3,000 of supplies were on hand. c. Prepaid insurance had a $1,950 normal balance prior to adjustment. By year cad $600 was unexpired. d. Services provided but unrecorded totaled $1,500. c. Accrued salaries but not paid at year-end are $2,000. f. A 6-month, $30,000 note was signed in November 1, 2019. The annual interest rate is 6%. Principal and interest are payable at the end of the 6 th month. Interest was accrued for 2019. g. On 7/1/2019,ABC received $3,000 as a down payment from a client for the service in the coming 12 months. - Required 1. As the accountant for ABC Company, you have been asked to prepare financial statements for the year. A file called WORKSHEET has been provided to assist you in this assignment. 2. The general ledger before adjustment and adjusting entries for the year ended 2019 are given above. Develop a worksheet that will serve as a trial balance in first tab "ABC Company Worksheet" in the Excel file). 3. In addition, there are ten formulas requested to complete the worksheet, including from FORM1 to FORM10 in gray. 4. Enter the formulas in appropriate cells on the worksheet. Then enter the trial balance and adjusting amounts in Columns B, C, E and F. Also, in Column D, insert the letter corresponding to the adjusting entry (a g). Furthermore, enter the formulas in Columns G and H that reference the appropriate cells in Columns B, C,E, and F. Column A is frozen on the screen to assist you in completing requirement 4. 5. Complete the income statement and balance sheet columns by entering formulas in Columns I, J, K and L that reference the appropriate cells in columns G or H. Net income will be calculated for the income statement and balance shect. Check to be sure that these double underline cells are the same. 6. Using the data on your completed workshect, prepare a pie chart showing the percentage of each asset to total assets after adjustments in the second tab "Pie chart" in the Excel file. - What-if Analysis 7. Suppose you discover that an assistant in your department had misunderstood your instructions and had provided you with wrong information on two of the adjusting entries. Supplies consumed during the year should have been $3,000, and the insurance premiums expired at year end were $600. Make the corrections and 2 provide your answer in the third tab "What-if analysis" in the Excel file. - Financial Statements 8. Use the corrected worksheet to prepare an income statement, a statement of changes in owner's equity, and a statement of financial position in the fourth tab "Financial statements" in the Excel file. - Submission Instruction Submit via Moodle Deadline: December 4 th 23:59