ACCT 201 Case Fall 2020 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 Island Dream, Inc. for the fiscal year ending December 31, 2020 using EXCEL. You must follow the all of these instructions including the ones provided for EXCEL. The information from Island Dream, 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. Basic instructions are given in this case. If you need additional help, please see your instructor You MUST follow these EXCEL instructions! Page 8 ACCT 201 Case Fall 2020 Island Dream, Inc. Adjusted Trial Balance December 31, 2020 $ 33,500 60,000 $ 6,100 18,600 2,500 2,000 100,000 800,000 440,000 400,000 155,000 42,400 20,500 1,200 25,000 230,000 275,000 222,000 Cash Accounts Receivable Allowance for Doubtful Accounts Merchandise Inventory Supplies Prepaid Insurance Land Building Accumulated Depreciation - Building Equipment Accumulated Depreciation - Equipment Accounts Payable Uncarned Revenue Salaries Payable Notes Payable, due March 31, 2021 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 Utilities Expense Supplies Expense Bad Debt Expense Interest Revenue Gain on sale of Equipment Loss on sale of Building Interest Expense 45,000 225,000 4,400 1,600 110,000 30,000 15,000 500 3,300 12,700 1,500 2,000 3,700 2,000 3,500 1800 Total $_2 $_2 1. 3. 4. 5. 6. ACCT 201 Case Fall 2020 Excel Directions: Adjusted Trial Balance Start Excel and open new worksheet 2. Right click the Sheet 1 tab and rename the worksheet to Adjusted Trial Balance, then prente To create a title for the trial balance and properly format is. click cell A1 on the Adjusted Trial Balance worksheet and type Island Dream, Inc. then press Enter Click cell A2 and type Adjusted Trial Balance, and then press Enter Click cell A3 and type December 31, 2020, then press Enter. 6. Click and drag the mouse pointer over cells A1 through D1 To center the title over the adjusted trial balance, click the Merge and Center tool on the toolbar. This action merges cells A1, B1, C1. and D1 and centers the text in cell A1 over all four. 7. Perform the same steps to merge and center the statement title and period for cell AZ and then cell A3. (If your date is no longer displayed os December 31, 2020. you'll need to change the cell format to Date and this type of format under Type heading.) 8 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. 11. In column C (for debits) and column D (for credits), enter the account balances in the appropriate column. 8. 9. 12. 13. 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 COLUMN 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. 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 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.) 14. 15. Page 10 ACCT 201 Case Fall 2020 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 inceme statement, use examples from chapter 5 of the textbook, For the retained sarnings statement, use examples from chapter 1 of the textbook. For the slossified 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. ACCT 201 Case Fall 2020 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 Island Dream, Inc. for the fiscal year ending December 31, 2020 using EXCEL. You must follow the all of these instructions including the ones provided for EXCEL. The information from Island Dream, 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. Basic instructions are given in this case. If you need additional help, please see your instructor You MUST follow these EXCEL instructions! Page 8 ACCT 201 Case Fall 2020 Island Dream, Inc. Adjusted Trial Balance December 31, 2020 $ 33,500 60,000 $ 6,100 18,600 2,500 2,000 100,000 800,000 440,000 400,000 155,000 42,400 20,500 1,200 25,000 230,000 275,000 222,000 Cash Accounts Receivable Allowance for Doubtful Accounts Merchandise Inventory Supplies Prepaid Insurance Land Building Accumulated Depreciation - Building Equipment Accumulated Depreciation - Equipment Accounts Payable Uncarned Revenue Salaries Payable Notes Payable, due March 31, 2021 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 Utilities Expense Supplies Expense Bad Debt Expense Interest Revenue Gain on sale of Equipment Loss on sale of Building Interest Expense 45,000 225,000 4,400 1,600 110,000 30,000 15,000 500 3,300 12,700 1,500 2,000 3,700 2,000 3,500 1800 Total $_2 $_2 1. 3. 4. 5. 6. ACCT 201 Case Fall 2020 Excel Directions: Adjusted Trial Balance Start Excel and open new worksheet 2. Right click the Sheet 1 tab and rename the worksheet to Adjusted Trial Balance, then prente To create a title for the trial balance and properly format is. click cell A1 on the Adjusted Trial Balance worksheet and type Island Dream, Inc. then press Enter Click cell A2 and type Adjusted Trial Balance, and then press Enter Click cell A3 and type December 31, 2020, then press Enter. 6. Click and drag the mouse pointer over cells A1 through D1 To center the title over the adjusted trial balance, click the Merge and Center tool on the toolbar. This action merges cells A1, B1, C1. and D1 and centers the text in cell A1 over all four. 7. Perform the same steps to merge and center the statement title and period for cell AZ and then cell A3. (If your date is no longer displayed os December 31, 2020. you'll need to change the cell format to Date and this type of format under Type heading.) 8 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. 11. In column C (for debits) and column D (for credits), enter the account balances in the appropriate column. 8. 9. 12. 13. 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 COLUMN 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. 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 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.) 14. 15. Page 10 ACCT 201 Case Fall 2020 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 inceme statement, use examples from chapter 5 of the textbook, For the retained sarnings statement, use examples from chapter 1 of the textbook. For the slossified 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