A Preparation of the Financial Statements Use the trial balance to complete 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. 8 Requirement 1 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 9 adjusting trial balance columns. 10 Adjustments: 11. After doing the year-end count of office supplies, Myers found that $380 of office supplies remained. 12 2. After doing the year-end count of inventory, Myers determined that there was $56,600 in ending inventory. 13 3. $1,500 of the prepaid insurance has expired at year end. 14 4. Depreciation of the office equipment for the year was $10,000. 15 5. Depreciation of the store equipment for the year was $15,000. 6. The $34,000 in unearned revenue was for orders that were paid for in advance. At year end, 1/2 of the prepaid 16 orders were fulfilled. 7. Wages that have accrued for the year but have not been paid yet totaled $18,000. This should be split evenly 17 between Sales Salaries and Office Salaries. 18 19 Excel Specific Requirements: - 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. Remeber that the adjustment columns need 20 to have totals. Use the SUM function to make sure your adjustment columns balance. - When inputting the adjusting entries, the Salaries Payable account is not listed on the trial balance. You need to 21 insert a row below Accounts Payable and add 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 Instructions A - Be sure that all of the numbers are formatted the same. The numbers should not show any cents. Remember t. have a dollar sign on the first number in the column and on the total of the column, just like the trial balance 22 columns. - Use the thick underline border underneath the last item of the column and Use the double underline border 23 underneath the total of the column. 24 - Format the worksheet so that it prints out onto one page. Be sure the worksheet prints in a landscape format. 25 26 Hints: 27 - If you do not know how to format numbers in Excel, google "formatting numbers in excel". - If you do not know how to use formulas, google "using excel formulas", and you will find plenty of information 28 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". 29 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 30 not know how to single or double underline a cell, google "underlines in excel". 31 - You may want to look at the print layout before finishing to make sure your document appears as you wish. 32 33 Requirement 2 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. 34 35 36 Excel Specific Requirements: - 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 37 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 adiusted trial balance. Remember that net income on the Statement of Retained Earnings Instructions Meyer's Home Supply Company Worksheet December 31, 2022 34 A 35 36 Excel Specific Requirements: - 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 37 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 Balar.ce 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 38 financial statement will automatically change. 39 - Set the cell width wide enough to fit the account names and numbers. - Be sure that all of the numbers are formatted the same. The numbers should not show any cents. Remember to 40 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 41 that the statement shows on the page at a reasonable size. 42. 43 Hints: 44 - If you do not know how to center a header or title on top of a report google "how to center headers in Excel". 45 - Remember to set the reports up so they print on one page. - Remember that the net income form the income statement needs to be linked to the statement of retained 46 earnings and retained earnings needs to be linked to the balance sheet. Use formulas to create these links