1. Use the information in the provided trial balance to create the following financial statements: a. 2019 Income Statement b. 2019 Balance Sheet C. 2019 Cash Flow Statement 2. Create ONE Excel Workbook with the following tabs: a. Tab 1: Trial Balance for 2018 and 2019 provided b. Tab 2: Closing Entries for 2018 Income Statement C. Tab 3: 2019 Income Statement d. Tab 4: Closing entries for 2019 e. Tab 5:2019 Balance Sheet post-closing entries (Use the 2018 Retained Earnings balance as your beginning 2019 balance) f. Tab 6: 2019 Cash Flow Statement 3. Formulas: a. Use Excel to setup the financial statements by linking the values from the trial balance. b. Use Excel formulas to calculate all totals and sub-totals. 4. Save your Excel workbook as last name _Project1. (For example: Koss_Project1.xls). 5. Upload the completed assignment to BlackBoard using the link provided. GRADING RUBRIC: FORMATTING/ACCURACY/COMPLETENESS (15%) Excel spreadsheets should be formatted and easy to read/follow. This includes: Underlining totals and subtotals for financial statements. (Total Assets, Total Liabilities, Total Equity, Net Income, etc.) Consistency in formatting for dollar and percent cells. (i.e. if decimals are used, they should be consistently. Are dollar signs used consistently?) ACCURACY/COMPLETENESS (15%) All required financial statements were included and were accurately pulled from the Trial Balance. CLOSING JOURNAL ENTRIES (10%) All Income Statement accounts are properly closed. Retained Earnings balance is properly calculated. INCOME STATEMENT (20%) All sub-totals and totals are accurately calculated (using Excel formulas). Ensure that $ signs, and statement headings are correct. BCDEF KELLEY ENTERPRISES Credit Credit Debit $ 718 $ 200 $ 363 $ 792 $ 170 $ 4,767 Debit $ 1,185 $ 210 $ 370 $ 801 $ 170 $ 5,688 375 $ 405 $ $ $ 143 710 99 $ $ $ 143 710 99 $ 30 $ 35 $ 182 $ 182 5 473 2017 Trial Balance 4 Account Title 5 Cash and cash equivalents 6 Short-term investments 7 Accounts receivable 8 Merchandise inventories 9 Prepaid expenses and other current assets 10 Property and Equipment 11 Accumulated depreciation 12 Long-term investments 13 Goodwill 14 Intangible assets 15 Accumulated amortization of intangible assets 16 Other assets 17 Current installments of capital lease obligations 18 Accounts payable 19 Accrued payroll, bonus and other benefits due team members 20 Dividends payable 21 Other current liabilities 22 Long-term capital lease obligations, less current installments 23 Other long-term liabilities 24 Common Stock 25 Treasury Stock 26 Retained earnings (beginning of year) 27 Dividends 28 Sales 29 Cost of Goods Sold 30 Selling general and administrative expenses 31 Pre-opening expenses 32 Relocation, store closure and lease termination expenses 33 Investment and other income 34 Income tax expense 35 uutisia 487 50 627 2018 Trial Balance Account Title Cash and cash equivalents Short-term investments Accounts receivable Merchandise inventories Prepaid expenses and other current assets Property and Equipment Accumulated depreciation and amortization Long-term investments Goodwill Intangible assets, net of accumulated amortization Accumulated amortization of intangible assets Other assets Current installments of capital lease obligations Accounts payable Accrued payroll, bonus and other benefits due team members Dividends payable Other current liabilities Long-term capital lease obligations, less current installments Other long-term liabilities Common Stock Treasury Stock Retained earnings (beginning of year) Dividends Sales Cost of Goods Sold Selling, general and administrative expenses Pre-opening expenses Relocation, store closure and lease termination expenses Investment and other income Income tax expense $ 8 $ 480 $ 326 $ 50 $ 585 $ 75 $ 1,250 $ 3,225 1,342 3,194 $ 1,214 $ 1,220 $ 2,017 $ 2,710 $ 45 $ 50 $ 16.144 $ 18,500 $ $ $ $ 10,415 4,632 67 16 $ 11,250 $ 5,250 $ - $ . $ 125 $ 125 $ 401 $ 24,934 $ 446 $ 27,774 $ 24,934 $ 27,774 Trial Balance 2017 Closing Entries 2018 Income Statement 2018 Closing Entries 2018 Balance Sheet 2018 Cash Flow Statement + Diviuerius Pdy dwie VIviuerius dydule SU S 627 $ $ $ 50 627 65 1,342 3,194 $ 585 $ 75 $ 1,250 $ 3,225 $ 1,214 $ 1,220 $ 2,017 $ 2,710 45 $ 50 1 Other current liabilities 2 Long-term capital lease obligations, less current installments 3 Other long-term liabilities 1 Common Stock 5 Treasury Stock 5 Retained earnings (beginning of year) Dividends Sales Cost of Goods Sold Selling, general and administrative expenses Pre-opening expenses 2 Relocation, store closure and lease termination expenses Investment and other income 1 Income tax expense Other current liabilities Long-term capital lease obligations, less current installments Other long-term liabilities Common Stock Treasury Stock Retained earnings (beginning of year) Dividends Sales Cost of Goods Sold Selling, general and administrative expenses Pre-opening expenses Relocation, store closure and lease termination expenses Investment and other income Income tax expense $ 16,144 $ 18,500 $ 10,415 $ 4,632 $ 67 $ 16 $ 11,250 $ 5,250 $ - 3 $ 125 $ 125 $ $ 401 24,934 $ 446 $ 27,774 $ 24,934 $ 27,774 $ - B Additional Information: The company purchased new equipment for $921, but there were no disposals of property/equipment. The company purchased $10 in new investments (including both short and long-term), and sold no investments during the year. . The company made incurred new capital lease obligations of $13. 3 The company did not take on any additional long-term liabilities. Any change in this account reflects payments made. The company sold stock during the year, and there were no additional transactions affecting the common stock account. 5 The company purchased treasury stock during the year, and there were no additional transactions affecting the treasury stock account. 5 The company paid dividents of $45 in 2017, and declared dividends of $50. - The company paid dividents of $50 in 2018, and declared dividends of $55. m v A1 4 X A B 2017 closiilg entries fx C 2017 closing entries D E F G H I J K L M N O mit in in moto 00 OHNM O HNM + ON 00 Trial Balance 2017 Closing Entries 2018 Income Statement 2018 Closing Entries 2018 Balance Sheet Dond C D E F G H I J K L M B 2018 Incorhe Statement Trial Balance 2017 Closing Entries 2018 Income Statement 2018 Closing Entries 2018 Ba 2018 Closing entries mit in in mm Om Om Om in Trial Balance 2017 Closing Entries 2018 Income Statement 2018 Closing Entries 2018 Balance Sheet 2018 V A1 x A B 2018 Balarce Sheet fx C 2018 Balance Sheet D E F G H I J K L M N O P Trial Balance 2017 Closing Entries 2018 Income Statement 2018 Closing Entries 2018 Balance Sheet 2018 Cash F C D E F G H I J K L M N P Q R S T U V A B 1 2018 Cash Flow Statement Trial Balance 2017 Closing Entries 2018 Income Statement 2018 Closing Entries 2018 Balance Sheet 2018 Cash Flow Statement + 1. Use the information in the provided trial balance to create the following financial statements: a. 2019 Income Statement b. 2019 Balance Sheet C. 2019 Cash Flow Statement 2. Create ONE Excel Workbook with the following tabs: a. Tab 1: Trial Balance for 2018 and 2019 provided b. Tab 2: Closing Entries for 2018 Income Statement C. Tab 3: 2019 Income Statement d. Tab 4: Closing entries for 2019 e. Tab 5:2019 Balance Sheet post-closing entries (Use the 2018 Retained Earnings balance as your beginning 2019 balance) f. Tab 6: 2019 Cash Flow Statement 3. Formulas: a. Use Excel to setup the financial statements by linking the values from the trial balance. b. Use Excel formulas to calculate all totals and sub-totals. 4. Save your Excel workbook as last name _Project1. (For example: Koss_Project1.xls). 5. Upload the completed assignment to BlackBoard using the link provided. GRADING RUBRIC: FORMATTING/ACCURACY/COMPLETENESS (15%) Excel spreadsheets should be formatted and easy to read/follow. This includes: Underlining totals and subtotals for financial statements. (Total Assets, Total Liabilities, Total Equity, Net Income, etc.) Consistency in formatting for dollar and percent cells. (i.e. if decimals are used, they should be consistently. Are dollar signs used consistently?) ACCURACY/COMPLETENESS (15%) All required financial statements were included and were accurately pulled from the Trial Balance. CLOSING JOURNAL ENTRIES (10%) All Income Statement accounts are properly closed. Retained Earnings balance is properly calculated. INCOME STATEMENT (20%) All sub-totals and totals are accurately calculated (using Excel formulas). Ensure that $ signs, and statement headings are correct. BCDEF KELLEY ENTERPRISES Credit Credit Debit $ 718 $ 200 $ 363 $ 792 $ 170 $ 4,767 Debit $ 1,185 $ 210 $ 370 $ 801 $ 170 $ 5,688 375 $ 405 $ $ $ 143 710 99 $ $ $ 143 710 99 $ 30 $ 35 $ 182 $ 182 5 473 2017 Trial Balance 4 Account Title 5 Cash and cash equivalents 6 Short-term investments 7 Accounts receivable 8 Merchandise inventories 9 Prepaid expenses and other current assets 10 Property and Equipment 11 Accumulated depreciation 12 Long-term investments 13 Goodwill 14 Intangible assets 15 Accumulated amortization of intangible assets 16 Other assets 17 Current installments of capital lease obligations 18 Accounts payable 19 Accrued payroll, bonus and other benefits due team members 20 Dividends payable 21 Other current liabilities 22 Long-term capital lease obligations, less current installments 23 Other long-term liabilities 24 Common Stock 25 Treasury Stock 26 Retained earnings (beginning of year) 27 Dividends 28 Sales 29 Cost of Goods Sold 30 Selling general and administrative expenses 31 Pre-opening expenses 32 Relocation, store closure and lease termination expenses 33 Investment and other income 34 Income tax expense 35 uutisia 487 50 627 2018 Trial Balance Account Title Cash and cash equivalents Short-term investments Accounts receivable Merchandise inventories Prepaid expenses and other current assets Property and Equipment Accumulated depreciation and amortization Long-term investments Goodwill Intangible assets, net of accumulated amortization Accumulated amortization of intangible assets Other assets Current installments of capital lease obligations Accounts payable Accrued payroll, bonus and other benefits due team members Dividends payable Other current liabilities Long-term capital lease obligations, less current installments Other long-term liabilities Common Stock Treasury Stock Retained earnings (beginning of year) Dividends Sales Cost of Goods Sold Selling, general and administrative expenses Pre-opening expenses Relocation, store closure and lease termination expenses Investment and other income Income tax expense $ 8 $ 480 $ 326 $ 50 $ 585 $ 75 $ 1,250 $ 3,225 1,342 3,194 $ 1,214 $ 1,220 $ 2,017 $ 2,710 $ 45 $ 50 $ 16.144 $ 18,500 $ $ $ $ 10,415 4,632 67 16 $ 11,250 $ 5,250 $ - $ . $ 125 $ 125 $ 401 $ 24,934 $ 446 $ 27,774 $ 24,934 $ 27,774 Trial Balance 2017 Closing Entries 2018 Income Statement 2018 Closing Entries 2018 Balance Sheet 2018 Cash Flow Statement + Diviuerius Pdy dwie VIviuerius dydule SU S 627 $ $ $ 50 627 65 1,342 3,194 $ 585 $ 75 $ 1,250 $ 3,225 $ 1,214 $ 1,220 $ 2,017 $ 2,710 45 $ 50 1 Other current liabilities 2 Long-term capital lease obligations, less current installments 3 Other long-term liabilities 1 Common Stock 5 Treasury Stock 5 Retained earnings (beginning of year) Dividends Sales Cost of Goods Sold Selling, general and administrative expenses Pre-opening expenses 2 Relocation, store closure and lease termination expenses Investment and other income 1 Income tax expense Other current liabilities Long-term capital lease obligations, less current installments Other long-term liabilities Common Stock Treasury Stock Retained earnings (beginning of year) Dividends Sales Cost of Goods Sold Selling, general and administrative expenses Pre-opening expenses Relocation, store closure and lease termination expenses Investment and other income Income tax expense $ 16,144 $ 18,500 $ 10,415 $ 4,632 $ 67 $ 16 $ 11,250 $ 5,250 $ - 3 $ 125 $ 125 $ $ 401 24,934 $ 446 $ 27,774 $ 24,934 $ 27,774 $ - B Additional Information: The company purchased new equipment for $921, but there were no disposals of property/equipment. The company purchased $10 in new investments (including both short and long-term), and sold no investments during the year. . The company made incurred new capital lease obligations of $13. 3 The company did not take on any additional long-term liabilities. Any change in this account reflects payments made. The company sold stock during the year, and there were no additional transactions affecting the common stock account. 5 The company purchased treasury stock during the year, and there were no additional transactions affecting the treasury stock account. 5 The company paid dividents of $45 in 2017, and declared dividends of $50. - The company paid dividents of $50 in 2018, and declared dividends of $55. m v A1 4 X A B 2017 closiilg entries fx C 2017 closing entries D E F G H I J K L M N O mit in in moto 00 OHNM O HNM + ON 00 Trial Balance 2017 Closing Entries 2018 Income Statement 2018 Closing Entries 2018 Balance Sheet Dond C D E F G H I J K L M B 2018 Incorhe Statement Trial Balance 2017 Closing Entries 2018 Income Statement 2018 Closing Entries 2018 Ba 2018 Closing entries mit in in mm Om Om Om in Trial Balance 2017 Closing Entries 2018 Income Statement 2018 Closing Entries 2018 Balance Sheet 2018 V A1 x A B 2018 Balarce Sheet fx C 2018 Balance Sheet D E F G H I J K L M N O P Trial Balance 2017 Closing Entries 2018 Income Statement 2018 Closing Entries 2018 Balance Sheet 2018 Cash F C D E F G H I J K L M N P Q R S T U V A B 1 2018 Cash Flow Statement Trial Balance 2017 Closing Entries 2018 Income Statement 2018 Closing Entries 2018 Balance Sheet 2018 Cash Flow Statement +