ACCT 212 Project 1 Template Fall 2021 - Excel ? 0 - 3X FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Alise Markova & Cut Calibri 11 - A A Wrap Text General AutoSum ~ Ay Be Copy + Fill Paste BIU - - LA- Merge & Center $+ % " 6.0 .00 Conditional Format as Cell Insert Delete Format .00 +.0 Format Painter Clear Sort & Find & Formatting Table * Styles Filter * Select Clipboard Font Alignment Number Styles Cells Editing A5 X V Cash and cash equivalents A B C DEF G H K L M N 2019 Trial Balance 2020 Trial Balance Account Title Debit Credit Account Title Debit Credit UI A W Cash and cash equivalents 364 Cash and cash equivalents 718 6 Short-term investments 155 Short-term investments 200 7 Accounts receivable 218 Accounts receivable 363 8 Merchandise inventories 500 Merchandise inventories 792 9 Prepaid expenses and other current assets 307 Prepaid expenses and other current assets S 170 10 Property and Equipment 3,363 Property and Equipment 4,767 11 Accumulated depreciation S 200 Accumulated depreciation S 375 12 Long-term investments S 63 Long-term investments $ 143 13 Goodwill 710 Goodwill 710 14 Intangible assets 99 Intangible assets 15 Accumulated amortization of intangible assets S 20 Accumulated amortization of intangible assets 30 16 Other assets S 182 Other assets S 182 17 Short-term notes payable 3 Short-term notes payable 5 18 Accounts payable 295 Accounts payable 473 19 Accrued payroll, bonus and other benefits due team members 436 Accrued payroll, bonus and other benefits due team members $ 487 20 Dividends payable S 45 Dividends payable S 45 21 Other current liabilities 473 Other current liabilities S 627 22 Long-term notes payable 6 Long-term notes payable S 65 23 Other long-term liabilities 658 Other long-term liabilities 1,342 24 Common Stock 2,904 Common Stock S 3,194 25 Treasury Stock 1,152 Treasury Stock S 1,214 26 Retained earnings (beginning of year) 1,521 Retained earnings $ 2,017 27 Dividends S 45 Dividends S 28 Sales $ 15,389 Sales $ 16,184 29 Cost of Goods Sold S 9,973 Cost of Goods Sold $ 10,415 30 Selling, general and administrative expenses 4,488 Selling, general and administrative expenses 4,632 31 Interest expense 62 Interest expense 68 Trial Balance 2019 Closing Entries 2020 Income Statement 2020 Closing Entries 2020 Balar ... (+ READY + 80%ACCT 212 Project 1 Template Fall 2021 - Excel '? 1 _ Hi |_' Alise Markova V Ill AI. 7 l l:|:| l l()l V :l ism Calibri ~|11 v A' A' I E E *2 v 12me Text General v a: 3.4 (\"a ' j 2 Aumsum 91? H 13 Copy V . . 44 . Paste V V , : : : H , , a (-11 JED Conditional Format as Cell Insert Delete Format Sorta! Flnd Bi V \\' Format Painter I g {a A Merge 5' Center $ A , no 9 D Formatting V Ta ole V Styles V V V '1 Clear ' Filter V Select V Clipboard I: Font n. Alignment K. Number I: Styles Cells Editing A5 V - r C351 and cash equivalents A l B C [IEF G H | K L M N 21 Other current liabilities S 473 Other current liabll es 3 627 22 Longcterm notes payable 5 62 Longcterm notes payable 5 65 23 Other Iongcterm liab s 658 Other Iongcterm liabilities 5 1,342 24 Common Stock S 2,904 Common Stock 3 3,194 25 Treasury Stock 5 1,152 Treasury Stock 5 1,214 25 Retained eamings (beginning ofyear) 5 1,521 Retained earnings 5 2,017 27 Dividends S 45 Dividends S 45 28 Sales 5 15,339 Sales S 16,184 29 Cost of Goods Sold 5 9,973 Cost of Goods Sold 5 10,415 30 Selling general and administrative expenses 5 4,488 Selling, general and administrative expenses 5 4,632 31 lnterest expense 3 62 Interest expense 3 6B 32 lnvestment and other income 5 17 Investment and other income 5 75 33 income tax expense 3 342 Income tax expense 3 401 34 5 22,023 5 22,023 5 24,919 5 24,919 35 36 37 Additional Information: 38 39 The company purchased new equipment for $1,404, but there were no disposals of property/equipment. 40 The company purchased $125 in new investments (including both short and longcterm), and sold no investments during the year. 41 The company made payments of $3 on its notes payable, and incurred a new notes payable of $0. 42 The company made no payments on its longcterm liabilities. Any change in this account reflects new longcterm liabilities incurred. 43 The company sold stock during the year, and there were no additional transactions affecting the common stock account. 44 The company purchased treasury stock during the year, and there were no add 45 The company paid dividends of $45 in 2020. 46 47 48 49 nal transactions affecting the treasury stock account. b Trial Balance READ 2019 Closing Entries l 2020|ncomeStatement l 2020 Closing Entries l 2020 Balar... G) 5 4 1. Use the information in the provided trial balance to create the following financial statements: a. 2017 Income Statement 1). 2017 Balance Sheet c. 2017 Cash Flow Statement 2. Create ONE Excel Workbook with the following tabs: a. Tab 1: Trial Balance for 2016 and 2017 provided 1). Tab 2: Closing Entries for 2016 Income Statement c. Tab 3: 2016 Income Statement 01. Tab 4: Closing entries for 2017 e. Tab 5: 2017 Balance Sheet post-closing entries (Use the 2016 Retained Earnings balance as your beginnin 2017 balance) f. Tab 6: 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