ACCG 210 Excel Project #1 Preparation of the Financial Statements Requirement 1 Adjustments: 1. After doing the year-end count of office supplies, Myers found that $200 of office supplies remained. 3. $1,400 of the prepaid insurance has expired at year end. 4. Depreciation of the office equipment for the year was $15,000. 5. Depreciation of the store equipment for the year was $20,000. Excel Specific Requirements: Hints: Use the trial balance to do this project. The completed Excel spreadsheet should be submitted via the assignment folder in D2L. When submitting the file, be sure to put your name in the file name. Also, be sure to use Microsoft Excel. If you do not have it, you can obtain a free copy of it at the link below. Link to Student Version of Microsoft Excel You must use Microsoft Excel. You cannot use other spreadsheet software. If you do not have Excel on your computer, download the free version at the link above. Assignments submitted in a format other than Excel will receive a grade of zero. Complete Myer's Home Supply Company's worksheet. In the first worksheet tab, at the bottom of the Excel workbook, is the beginning of a worksheet. You are given the trial balance portion. You need to enter the adjustments below in the adjustment columns and then complete the remainder of the worksheet through the adjusting trial balance columns. 2. After doing the year-end count of inventory, Myers determined that there was $51,500 in ending inventory. 6. The $34,000 in unearned revenue was for orders that were paid for in advance. At year end, 1/2 of the prepaid orders were fulfilled. 7. Wages that have accrued for the year but have not been paid yet totaled $25,000. This should be split evenly between Sales Salaries and Office Salaries. - When completing the adjusted trial balance, all numbers need to be brought over using formulas. In the adjusted trial balance columns, no number should be entered manually. Also the totals of the adjusted trial balance should be formulas using the SUM function. The spreadsheet needs to be set up so if there is a change in one of the adjustments, all balances in the adjusted trial balance will be correct. - When inputting the adjusting entries, the Salaries Payable account is not listed on the trial balance. You need to insert a row below Accounts Payable and add in a row for Salaries Payable. - Be sure that all of the numbers are formatted the same. The numbers should not show any cents. Remember to have a dollar sign on the first number in the column and on the total of the column, just like the trial balance columns. - Use the thick underline border underneath the last item of the column and Use the double underline border underneath the total of the column. - Format the worksheet so that it prints out onto one page. Be sure the worksheet prints in a landscape format.- If you do not know how to format numbers in Excel, google "formatting numbers in excel". Requirement 2 Excel Specific Requirements: - Set the cell width wide enough to fit the account names and numbers. Hints: - Remember to set the reports up so they print on one page. - If you do not know how to use formulas, google "using excel formulas", and you will find plenty of information on how to use a formula. Also click on the video tab and you will find plenty of videos. - If you do not know how to scale something down to a one page printout, google "printing to one page in excel". Again, you will find plenty of information and videos on how to achieve this. - Be sure when completing the worksheet that you appropriately use the single and double underlines. If you do not know how to single or double underline a cell, google "underlines in excel" - You may want to look at the print layout before finishing to make sure your document appears as you wish. Prepare the multi-step income statement, statement of retained earnings, and the classified balance sheet for Myer's Home Supply Company for the year ended December 31, 2020. Each statement should be done on the appropriate Excel spreadsheet tab (worksheet). Make sure each statement is prepared in the proper format with proper headings and proper use of underlines and dollar signs. Each report should use formulas for each of the numbers. The numbers for the reports should be linked to the adjusted trial balance. So each number should be brought over from the adjusted trial balance using a formula. Numbers cannot just be entered manually. - Be sure that the headers to the statements are properly centered over the report. You need to center the header using the proper commands, not eyeing it using the space bar. There are multiple ways to center a header. The easiest way is using the Merge Cell & Center command in the Home section at the top. - Every number shown on the financial statements needs to be linked to where it originated using a formula. Most numbers come from the adjusted trial balance. Remember that net income on the Statement of Retained Earnings comes from the Income Statement and Retained Earnings on the Balance Sheet comes from the Statement of Retained Earnings. The statements should be set up so that if a journal entry on the worksheet is changed, the financial statement will automatically change. - Be sure that all of the numbers are formatted the same. The numbers should not show any cents. Remember to use dollar signs and underlines appropriately. Use the cell borders for the underlines. - Set each statement so it prints on one page. Use Print Preview to make sure it is printing on one page. Be sure that the statement shows on the page at a reasonable size. - If you do not know how to center a header or title on top of a report google "how to center headers in Excel". - Remember that the net income form the income statement needs to be linked to the statement of retained earnings and retained earnings needs to be linked to the balance sheet. Use formulas to create these links.Meyer's Home Supply Company Worksheet December 31, 2023 Trail Balance Adjustments Adjusted Trial Balance Account Debit Credit Debit Credit Debit Credit Cash $ 39,000 Accounts Receivable 45,000 Office Supplies 1,300 Prepaid Store Insurance 2,400 Inventory 55,500 Store Equipment 130,000 Accumulated Depreciation - Store Equipment $ 60,000 Office Equipment 88,000 Accumulated Depreciation - Office Equipment 32,500 Accounts Payable 38,300 Unearned Revenue 34,000 Note Payable (Due in 3 years) 50,000 Common Stock 60,000 Retained Earnings 50,700 Dividends 10,000 Sales 885,200 Sales Returns and Allowances 36,000 Cost of Goods Sold 510,000 Advertising Expense 36,000 Depriciation Expense - Office Equipment - Depreciation Expense - Store Equipment - Sales Salaries Expense 78,500 Office Salaries Expense 120,000 Sells Department Rent Expense 32,000 Administration Rent Expense 27,000 Store Insurance Expense -Office Supplies Expense - $ 1,210,700 $ 1,210,700