Font Paragraph Styles 2 Editing On the sheet called Working Trial Balance" you will find the working trial balance for SNIFFER as of the end of the 1st quarter. This is the information you will use to complete the following tasks. 1) On the Working Trial Balance" In column B titled as FS indicate which financial statement will include each account listed on the working trial balance using these abbreviations (you may have to do some internet searching to determine which kind of financial statement element you are working with if you are not familiar with the account name): B for balance sheet I for income statement C for statement of comprehensive income S for statement of shareholders' equity 2) At the far right you will see a column titled Adjusted TB. In this column enter a formula that captures the beginning balance of the account and any debits or credits made during the year. I have provided an example in the cash account (NOTE: because the Cash account is normally a debit account, the formula I have provided shows that all "debit" items increase the balance while all "credit" items decrease the balance. When you enter a formula for credit accounts you will need to have all debit" items decrease the balance while all "credit" items increase the balance= 3)Open a new sheet in the workbook by clicking on the + on the bottom left of the screen. This is going to be your Income Statement page. Using the numbers from the Adjusted Trial Balance column on the working trial balance, create a multi-step income statement that includes every account you identified as l" in step 1 You should create the income statement such that it updates automatically if you add any debits or credits to the accounts existing on your working trial balance. 4) Open a new sheet in the workbook by clicking on the + on the bottom left screen. This is going to be your Balance Sheet page. Using the numbers from the Adjusted Trial Balance column on the working trial balance, create a classified balance sheet that includes every account you identified as B in step 1 above. IMPORTANT NOTE: When you get to the Retained Earnings"(or Accumulated Deficit) line on the balance sheet you will need to pick up the balance from the Adjusted Trial Balance column like you did for the other accounts PLUS you will need to pick up the Net Income (Loss) from your income statement PLUS you will need to subtract any (Dividends) from the Adjusted Trial Balance page. FYI the extra steps are necessary because you have not done closing entries for the period in this assignment. You also need to adjust the Accumulated Comprehensive loss line on the balance sheet by picking up any debits or credits on the working trial balance that you have marked "C" as items that flow through the Statement of Comprehensive Income. FYI - these extra steps are necessary because you have done closing entries for the period in this assignment 5 6 SIZE OF NUMBERS: 7 Pay attention to the size of the numbers. In the trial balances the numbers are presented in thousands ($000's). 8 When you start making entries be careful to post your numbers in $000's. Otherwise, your statements will not make sense. 9 10 NO NEGATIVE NUMBERS ON THE WORKING TRIAL BALANCE: 11 You should only have positive balances. If a balance switches from a debit to a credit, or vice versa, be sure to include it in the proper column. 12 13 EACH ACCOUNT ONLY HAS A DEBIT OR CREDIT BALANCE in the ADJUSTED TRIAL BALANCE: 14 In the adjusted trial balance each account will either have a debit or a credit balance, but never both. 15 A w Do not post these entries to the WTB as they are already included in your 1st quarter numbers. They are shown here just as information for you to 3 use in the statement of shareholders' equity. 4. 5 6 1/1/2020 Treasury Stock 120658 7 Cash 120658 8 Purchase of treasury shares 9 10 11 1/1/2020 Addl Pid-in-Capital 92957 12 Common Stock 1 13 Cash 95958 14 Stock based award settlement D E F G I - J K L B C Final Trial Balance Dec. 31, 2019 $ 3,139,295 19,993 323,060 $24,726 249,367 491,353 120,000 2,854,462 578,474 353,052 167,054 301,441 13,750 94,356 397,490 502,003 1 SNIFFER, INC. 2 CONSOLIDATED BALANCE SHEET - USD ($) $ in Thousands 3 4 Cash and cash equivalents 5 Marketable securities 6 Accounts receivable 7 Allowance for Doubtful Accounts 8 Other current assets 9 Property and equipment, net 10 Accumulated depreciation 11 Goodwill 12 Intangible assets, net 13 Long-term investments 14 Deferred income tax-asset 15 Equity method investments 16 Current portion of long-term debt 17 Accounts payable, trade 18 Deferred revenue 19 Accrued expenses and other current liabilities 20 Total current liabilities 21 Long-term debt, net 22 Income taxes payable 23 Asset Retirement Obligations 24 Other long-term liabilities Common stock $.001 par value; authorized 1,600,000 shares; issued 263,230 and 25 outstanding 78,890 Class B convertible common stock $.001 par value; authorized 400,000 shares; issued 26 16,157 shares and outstanding 5,789 shares 27 28 29 Additional paid-in capital 30 Retained earnings 31 Accumulated other comprehensive loss 32 Treasury stock 194,708 shares 33 34 35 36 TOTALS 37 38 3,121,572 36,489 21,388 202,932 263 16 0 0 12,698,602 1,689,925 136,349 10,309,612 18,923,512 18,923,512 0 File Home Insert Formulas Data Review View Help Tell me what you want to do Open in Desktop App Cut 2 Wrap Text AutoSum 47 0 Paste BI U Dab A $ % 9 9 Merge & Center Insert Delete Format Idea Clear Le Copy 3 Format Painter Clipboard Conditional Format Cell Formatting as Table Styles Tables Sort & Find & Filter Select Editing ndo Font Alignment Number Cells Idea: 23 B C D E F Changes for the trending Jun 30, 2020 DR CR G H Changes for the qtr ending Sept. 30, 2020 DR CR F/S Changes for the qtr en Dec. 31, 2020 DR SNIFFER, INC 2 CONSOLIDATED BALANCE SHEET - USD ($) $ in Thousands 3 Cash and cash equivalents Marketable securities Accounts receivable Allowance for Doubtful Accounts Other current assets Property, capitalized software and equipment 0 Accumulated depreciation 1 Goodwill 2 Intangible assets, net of accumulated amortization 3 Long-term investments 4 Deferred income taxes-asset 5 Equity method investments 6 Current portion of long-term debt 7 Accounts payable, trade 8 Deferred revenue 9 Accrued expenses and other current liabilities 0 Long-term debt, net 1 Income taxes payable 2 Asset Retirement Obligations 3 Other long-term liabilities Common stock $.001 par value; authorized 1,600,000 shares; issued 263,502 and 4 outstanding 76,852 Class B convertible common stock $.001 par value; authorized 400,000 shares; issued 16,157 5 shares and outstanding 5,789 shares Balances as of Mar. 31, 2020 DR CR $ 2,822,729 69,912 404,523 28,669 267,814 518,299 144,738 3,042,139 671,467 301,592 190,849 318,832 13,750 102,367 433,728 514,571 3,625,008 18,398 19,398 210,274 264 16 Workbook Statistics 24 264 16 12,605,645 1,689,925 136,349 10,430,270 28,397 1,228,765 outstanding 76,852 Class B convertible common stock $.001 par value; authorized 400,000 shares; issued 16,157 25 shares and outstanding 5,789 shares 26 Additional paid-in capital 27 Retained earnings 28 Accumulated other comprehensive loss 29 Treasury stock 196,908 shares 30 Cash dividends 31 Revenue 32 Cost of revenue 33 Selling and marketing expense 34 General and administrative expense 35 Product development expense 36 Depreciation 37 Amortization of intangibles 38 Goodwill impairment 39 Interest expense 40 Other expense, net of other income 41 Income Tax benefit 42 Change in unrealized gains and losses of available-for-sale securities 43 323,221 432,697 256,021 105,733 24,738 52,162 211,973 44,866 49,893 89,896 20,936 44 45 TOTALS $ 20,725,412 $0 $ 20,725,412 $0 46 47 48 49 Paste BI U Dab A Merge & Center $ % 9 20 Insert L Copy 3 Format Painter Clipboard Insert Delete Format Conditional Format Cell Formatting as Table Styles Tables Undo Font Alignment Number Cells S10 S T V w 1 og K Year-end Adjusting Entries DR CR M N Adjusted Trial Balance Dec. 31, 2020 DR CR $ 2,822,729 P Closing Entries for the year ended December 31 DR CR Q R Final Trial Balance Dec. 31, 2020 DR CR 2 3 4 5 6 7 8 28,669 9 10 L 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Font Paragraph Styles 2 Editing On the sheet called Working Trial Balance" you will find the working trial balance for SNIFFER as of the end of the 1st quarter. This is the information you will use to complete the following tasks. 1) On the Working Trial Balance" In column B titled as FS indicate which financial statement will include each account listed on the working trial balance using these abbreviations (you may have to do some internet searching to determine which kind of financial statement element you are working with if you are not familiar with the account name): B for balance sheet I for income statement C for statement of comprehensive income S for statement of shareholders' equity 2) At the far right you will see a column titled Adjusted TB. In this column enter a formula that captures the beginning balance of the account and any debits or credits made during the year. I have provided an example in the cash account (NOTE: because the Cash account is normally a debit account, the formula I have provided shows that all "debit" items increase the balance while all "credit" items decrease the balance. When you enter a formula for credit accounts you will need to have all debit" items decrease the balance while all "credit" items increase the balance= 3)Open a new sheet in the workbook by clicking on the + on the bottom left of the screen. This is going to be your Income Statement page. Using the numbers from the Adjusted Trial Balance column on the working trial balance, create a multi-step income statement that includes every account you identified as l" in step 1 You should create the income statement such that it updates automatically if you add any debits or credits to the accounts existing on your working trial balance. 4) Open a new sheet in the workbook by clicking on the + on the bottom left screen. This is going to be your Balance Sheet page. Using the numbers from the Adjusted Trial Balance column on the working trial balance, create a classified balance sheet that includes every account you identified as B in step 1 above. IMPORTANT NOTE: When you get to the Retained Earnings"(or Accumulated Deficit) line on the balance sheet you will need to pick up the balance from the Adjusted Trial Balance column like you did for the other accounts PLUS you will need to pick up the Net Income (Loss) from your income statement PLUS you will need to subtract any (Dividends) from the Adjusted Trial Balance page. FYI the extra steps are necessary because you have not done closing entries for the period in this assignment. You also need to adjust the Accumulated Comprehensive loss line on the balance sheet by picking up any debits or credits on the working trial balance that you have marked "C" as items that flow through the Statement of Comprehensive Income. FYI - these extra steps are necessary because you have done closing entries for the period in this assignment 5 6 SIZE OF NUMBERS: 7 Pay attention to the size of the numbers. In the trial balances the numbers are presented in thousands ($000's). 8 When you start making entries be careful to post your numbers in $000's. Otherwise, your statements will not make sense. 9 10 NO NEGATIVE NUMBERS ON THE WORKING TRIAL BALANCE: 11 You should only have positive balances. If a balance switches from a debit to a credit, or vice versa, be sure to include it in the proper column. 12 13 EACH ACCOUNT ONLY HAS A DEBIT OR CREDIT BALANCE in the ADJUSTED TRIAL BALANCE: 14 In the adjusted trial balance each account will either have a debit or a credit balance, but never both. 15 A w Do not post these entries to the WTB as they are already included in your 1st quarter numbers. They are shown here just as information for you to 3 use in the statement of shareholders' equity. 4. 5 6 1/1/2020 Treasury Stock 120658 7 Cash 120658 8 Purchase of treasury shares 9 10 11 1/1/2020 Addl Pid-in-Capital 92957 12 Common Stock 1 13 Cash 95958 14 Stock based award settlement D E F G I - J K L B C Final Trial Balance Dec. 31, 2019 $ 3,139,295 19,993 323,060 $24,726 249,367 491,353 120,000 2,854,462 578,474 353,052 167,054 301,441 13,750 94,356 397,490 502,003 1 SNIFFER, INC. 2 CONSOLIDATED BALANCE SHEET - USD ($) $ in Thousands 3 4 Cash and cash equivalents 5 Marketable securities 6 Accounts receivable 7 Allowance for Doubtful Accounts 8 Other current assets 9 Property and equipment, net 10 Accumulated depreciation 11 Goodwill 12 Intangible assets, net 13 Long-term investments 14 Deferred income tax-asset 15 Equity method investments 16 Current portion of long-term debt 17 Accounts payable, trade 18 Deferred revenue 19 Accrued expenses and other current liabilities 20 Total current liabilities 21 Long-term debt, net 22 Income taxes payable 23 Asset Retirement Obligations 24 Other long-term liabilities Common stock $.001 par value; authorized 1,600,000 shares; issued 263,230 and 25 outstanding 78,890 Class B convertible common stock $.001 par value; authorized 400,000 shares; issued 26 16,157 shares and outstanding 5,789 shares 27 28 29 Additional paid-in capital 30 Retained earnings 31 Accumulated other comprehensive loss 32 Treasury stock 194,708 shares 33 34 35 36 TOTALS 37 38 3,121,572 36,489 21,388 202,932 263 16 0 0 12,698,602 1,689,925 136,349 10,309,612 18,923,512 18,923,512 0 File Home Insert Formulas Data Review View Help Tell me what you want to do Open in Desktop App Cut 2 Wrap Text AutoSum 47 0 Paste BI U Dab A $ % 9 9 Merge & Center Insert Delete Format Idea Clear Le Copy 3 Format Painter Clipboard Conditional Format Cell Formatting as Table Styles Tables Sort & Find & Filter Select Editing ndo Font Alignment Number Cells Idea: 23 B C D E F Changes for the trending Jun 30, 2020 DR CR G H Changes for the qtr ending Sept. 30, 2020 DR CR F/S Changes for the qtr en Dec. 31, 2020 DR SNIFFER, INC 2 CONSOLIDATED BALANCE SHEET - USD ($) $ in Thousands 3 Cash and cash equivalents Marketable securities Accounts receivable Allowance for Doubtful Accounts Other current assets Property, capitalized software and equipment 0 Accumulated depreciation 1 Goodwill 2 Intangible assets, net of accumulated amortization 3 Long-term investments 4 Deferred income taxes-asset 5 Equity method investments 6 Current portion of long-term debt 7 Accounts payable, trade 8 Deferred revenue 9 Accrued expenses and other current liabilities 0 Long-term debt, net 1 Income taxes payable 2 Asset Retirement Obligations 3 Other long-term liabilities Common stock $.001 par value; authorized 1,600,000 shares; issued 263,502 and 4 outstanding 76,852 Class B convertible common stock $.001 par value; authorized 400,000 shares; issued 16,157 5 shares and outstanding 5,789 shares Balances as of Mar. 31, 2020 DR CR $ 2,822,729 69,912 404,523 28,669 267,814 518,299 144,738 3,042,139 671,467 301,592 190,849 318,832 13,750 102,367 433,728 514,571 3,625,008 18,398 19,398 210,274 264 16 Workbook Statistics 24 264 16 12,605,645 1,689,925 136,349 10,430,270 28,397 1,228,765 outstanding 76,852 Class B convertible common stock $.001 par value; authorized 400,000 shares; issued 16,157 25 shares and outstanding 5,789 shares 26 Additional paid-in capital 27 Retained earnings 28 Accumulated other comprehensive loss 29 Treasury stock 196,908 shares 30 Cash dividends 31 Revenue 32 Cost of revenue 33 Selling and marketing expense 34 General and administrative expense 35 Product development expense 36 Depreciation 37 Amortization of intangibles 38 Goodwill impairment 39 Interest expense 40 Other expense, net of other income 41 Income Tax benefit 42 Change in unrealized gains and losses of available-for-sale securities 43 323,221 432,697 256,021 105,733 24,738 52,162 211,973 44,866 49,893 89,896 20,936 44 45 TOTALS $ 20,725,412 $0 $ 20,725,412 $0 46 47 48 49 Paste BI U Dab A Merge & Center $ % 9 20 Insert L Copy 3 Format Painter Clipboard Insert Delete Format Conditional Format Cell Formatting as Table Styles Tables Undo Font Alignment Number Cells S10 S T V w 1 og K Year-end Adjusting Entries DR CR M N Adjusted Trial Balance Dec. 31, 2020 DR CR $ 2,822,729 P Closing Entries for the year ended December 31 DR CR Q R Final Trial Balance Dec. 31, 2020 DR CR 2 3 4 5 6 7 8 28,669 9 10 L 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25