prepare an income statement, retained earnings statement, and balance sheet for Pepper Shoes, Inc. Also, if you can show me how to set up the trial balance (#9 on page 9) thank you.
Page 7 Question 6: REQUIRED: Based on the adjusted trial balance on the next page, prepare the Income Statement, Retained Earnings Statement, and Balance Sheet for Pepper Shores, Inc. for the fiscal year ending December 31, 2021 using EXCEL You must follow the all of these instructions including the ones provided for EXCEL The information from Pepper Shores Inc.'s trial balance is to be entered onto an EXCEL spreadsheet. Formulas and links to the Trial Balance are to be created in order to generate the three financial statements. instructions are given in this case. If you need additional help, please see your instructor Basic You MUST follow these EXCEL instructions! You should upload a copy of your trial balance and financial statements to your instructor using Moodle. (Please name your uploaded excel file using the following format: Your Name Section. For example, Jane Doe Sectioni.) You should also staple a printed copy of the adjusted trial balance and financial statements into this case in the appropriate place. Be sure to write your name and section # on your printed copy. DO NOT E-MAIL YOUR FILE!!! submissions will NOT be accepted. upload your file to Moodle. E-mail You must DO NOT UPLOAD A ZIP FILE!!!! Pepper Shores, Inc. Adjusted Trial Balance December 31, 2021 $ 43,500 59,000 5,100 18,600 2,500 2,000 100,000 700,000 340,000 400,000 Cash Accounts Receivable Allowance for Doubtful Accounts Merchandise Inventory Supplies Prepaid Insurance Land Building Accumulated Depreciation - Building Equipment Accumulated Depreciation - Equipment Accounts Payable Unearned Revenue Salaries Payable Notes Payable, due May 31, 2024 Mortgage Payable due Dec 31, 2025 Common Stock Retained Earnings Dividends Sales Sales Returns & Allowances Sales Discounts Costs of Goods Sold Salaries Expense Depreciation Expense Freight Out Advertising Expense 165,000 42,400 20,500 1,200 25,000 230,000 275,000 222,000 45,000 215,000 4,300 1,700 110,000 30,000 15,000 500 3,300 2,700 1,500 2,000 Utilities Expense Supplies Expense Bad Debt Expense Interest Revenue Gain on sale of Equipment Loss on sale of Building Interest Expense 3,700 2,000 3,500 1.800 Total $_2 1. 2 3. 4. 5. Excel Directions: Adjusted Trial Balance Start Excel and open new worksheet Right click the Sheet 1 tab and rename the worksheet to adjusted Trial Balance, then press Enter. To create a title for the trial balance and property format it, click cell A1 on the Adjusted Trial Balance worksheet and type Pepper Shores, Inc. then press Enter Click cell A2 and type Adjusted Trial Balance, and then press Enter. Click cell A3 and type December 31, 2021, then press Enter 6. Click and drag the mouse pointer over cells Al through 01. To center the title over the adjusted trial balance, click the Merge and Center tool on the toolbar. This action merges cells A1, B1. CI. and D1 and centers the text in cell A1 over all four. Perform the same steps to merge and center the statement title and period for cell A2 and then cell A3. (If your date is no longer displayed as December 31, 2021, you'll need to change the cell format to Date and this type of format under Type heading.) Click and drag the mouse pointer over all four newly merged cells, then click both the Bold and Italics tools on the format menu. 9. Beginning in cell A5, type the account titles of all the accounts exactly as shown in the Big Data Comprehensive Case Adjusted Trial Balance, DO NOT ABBREVIATEI 10. Resize Column A to fit the account titles. In column C (for debits) and column D (for credits), enter the account balances in the appropriate column 7. 8. 11. 12. Place formulas in the appropriate cells to sum the debit and credit columns. Be sure to check that the TOTALS IN THE DEBIT COLUMN EQUAL THE TOTALS IN THE CREDIT COLUMNI 13. To complete the Trial Balance you will need to properly format the worksheet. In some cases, you will need to format number cells with the comma format. In other cases it is necessary to format number cells with the currency format. Be sure to indicate subtotals and totals with the correct underlining (single or double). These can be found under the Font tab. 14. Place a customized footer in the document (found under insert). Place your name and date in the left section of the footer and the file name in the right section of the footer. 15. Finally, you should save your work and print the adjusted trial balance. Be sure to set the print option to print to one page. (In page layout tab, set width to 1 page.) Financial Statements 1. Rename the Sheet 2 tab to Income Statement 2. Rename the Sheet 3 tab to Retained Earnings Statement 3. Insert another sheet at the end and rename tab to Balance Sheet. 4. Create the correct title for each of the statements and format like the Adjusted Trial Balance. 5. Beginning in cell A5 on each sheet, type the appropriate labels for the financial statements. For a multiple-step income statement, use examples from chapter 5 of the textbook. For the retained earnings statement, use examples from chapter 1 of the textbook. For the classified balance sheet. use examples from chapter 4 of the textbook. Be sure that on your classified balance sheet. TOTAL ASSETS : TOTAL LIABILITIES AND STOCKHOLDERS' EQUITY 6. In order to copy the numbers from the adjusted trial balance to each of the financial statements, click on the cell in which you want the number to appear, then press . Then click the Adjusted Trial Balance sheet tab and click the cell containing the number that you want to transfer to the financial statement and press Enter. This will transfer the number to the financial statement. This should be done for each number on the financial statements, except the totals. That way. if you change a number on the Adjusted Trial Balance, the financial statements will automatically be updated 7. Create formulas when necessary to complete the financial statements. (HINT: Each total and each subtotal should have a formula.) 8. To complete the financial statements, you will need to properly format the worksheets. In some cases you need to format number cells with the comma format. In other cases, it is necessary to format number cells with the currency format. Be sure to indicate subtotals and totals with the correct underlining (single or double). These can be found under the Font tab. 9. Finally, you should save your work and print the financial statements. Remember your file name should be Your Name Section#. Be sure to set the print option to print to one page using the page layout tab. 10. Upload your file as instructed by your instructor using Moodle. Also attach your printed copy to the appropriate page in this case. Be sure to write your name and section # on your printed copy
