Adjustments Dr ar Adjusted Trial Bata Dr C Cash Accounts receviable Allowance for doubtful accounts Prepaid insurance Prepaid rent Inventory Land Computer hardware and software Accumulated depreciation -computer hardware and software Furniture and equipment Accumulated depreciation - fumiture and equipment Leasehold improvements Accumulated depreciation - leasehold improvements Intangibles Accumulated amortization - Intangibles Goodwill Accounts payable Unearned revenue Income tax payable Interest payable Notes payable Bonds payable Preferred shares Common shares Cash dividend-common Cash dividend preferred Retained earnings Unadjusted trial balance Dr Or $ 98,546 $ 25,032 $ 500 $ 1,600 $ 24,000 $ 985,362 $ 39,856 $ 19,333 $ 12,057 $ 14,523 S 8,816 $ 856,365 $ 480,369 $ 45.000 S 33.000 $ 250.000 $ 12,698 $ 1,253 $ 1.500 $ 58,302 $ 475,000 $ 176,522 171,130 S 75,000 $ 50,000 $ 256,845 $ 15,157,369 $9.856,554 $ 2, 183,659 $ Sales Cost of goods sold Salaries expense Depreciation expense Amortization expense Insurance expense Bad debt expense General and administrative expenses Rent expense Utilities expense Interest expense Gain/Loss on sale of asset Income tax expense Loss on discontinued operations Gain on sale of discontinued operations 60.500 1,700 26985 3.65 $ S 57.147.593 5 125,000 $16.9650100 5 16.965.360$ For adjusting entries: Management has reviewed the aging accounts receivable and estimates that the allowance for doubtful accounts should be $2,000. The prepaid insurance balance is a 12-month insurance policy which was purchased and paid for on July 1, 2020. Rent is prepaid annually on April 1. On September 15th some computers were sold for $1,000. They had a cost of $5,000 and a net book value of $200 (This transaction is NOT included in the unadjusted trial balance) cei did an inventory count on Dec 31, 2020 and determined that they had inventory on hand worth $895,000. On October 31, 2020 C91 issued a 5-year, 4%, $500,000 bond at .95, when the market rate was 5% and it pays interest semi-annually on Mar 31 and Sept 31. C9I uses the effective interest rate amortization method. The recording of the issuance of this bond was done correctly but nothing has been recorded since. The bookkeeper currently records the entire note payable payment as a reduction to principal; therefore, the interest needs to be separated at year end. The note payable is paid every 6 months with blended payments of $8,349. The annual interest rate is 3.5%. The previous accountant correctly prepared the following amortization table Pmt # Dato Principal Interest Payment Balanco Jan 1st 2020 $6,849 $1,500 $8,349 $68,151 2 Jul 1st 2020 $6,986 $1,363 $8,349 $61,164 Total 2020 $13,835 $2,863 $ 16,698 $61,164 3 Jan 1st 2021 $7,126 $1,223 $8,349 $54,038 1 Jul 1st 2021 $7,269 $1,081 Total 2021 $14,395 $2,304 5 Jan 1st 2022 $7,414 $935 6 Jul 1st 2022 $7,562 $787 Total 2022 $14,976 $1,722 7 Jan 1st 2023 $7,714 $636 8 Jul 1st 2023 $7,868 $482 Total 2023 $15,582 $1.118 9 Jan 1st 2024 $8,025 $324 10 Jul 1st 2024 $8,186 $164 Total 2024 $16,211 $488 Grand Total $75,000.00 $8,495 $8,349 $46,769 $16,699 $46,769 $8,349 $39,355 $8,349 $31,793 $16,699 $31,793 $8,349 $24,079 $8,349 $16,211 $16,699 $16,211 $8,349 $8,186 $8,349 $0 $16,699 $0 $83,495 $0 Depreciation and Amortization Policy Depreciation and Amortization is only taken at the end of each fiscal year and C9I uses only one Depreciation expense account for all tangible assets and one Amortization account for all intangibles. Furniture and equipment - amortized straight-line with no expected salvage value and an estimated useful life of 10 years. Leasehold Improvements, amortized straight-line with an expected salvage value of $15,000 and an estimated useful life of 20 years. Computer hardware and software- amortized straight-line with no expected salvage value and an estimated useful life of 3 years. Intangibles - amortized straight-line with an an estimated useful life of 20 years. Other information (to complete the assignment which has already been recorded): Included in the unadjusted trial balance was dividends declared and paid during the year of $125,000 but the bookkeeper was not sure where to record them. c91 has both preferred shares and common share authorized and outstanding. Col is authorized to issue 100,000 $2 non-cumulative preferred shares and at December 31, 2020, it has 15,000 issued and outstanding. Col is authorized to issue unlimited common shares and at December 31, 2020 has 75,000 issued and outstanding In March 2020 Glenn, the recently promoted Regional Manager of C9I decided to discontinue operations related the store's pharmacy. On Oct 30th, 2020 col sold all assets relating to the pharmacy business and recorded a gain on the transaction. The tax rate for C9l is 25%. C9l includes EPS calculations on its Income Statement even though ASPE doesn't require it because its shareholders like to know this information. Required: Prepare the following in the excel workbook provided: a) A tab with a workbook of trial balances (Unadjusted (provided), Adjusted, and Closing Trial Balances) b) A tab with all adjusting entries (please include the disposal of computer equipment here) c) A tab with the financial statements for Cloud 9 Incorporated for the year ended December 31 in good form including ALL EPS (continuing, dis ops and full) calculations. (Please include a Multi-Step Income Statement, Statement of Retained Earnings and a Classified Balance Sheet (Please note: A Statement of Cash Flows is not required) d) A tab with closing entries. Adjustments Dr ar Adjusted Trial Bata Dr C Cash Accounts receviable Allowance for doubtful accounts Prepaid insurance Prepaid rent Inventory Land Computer hardware and software Accumulated depreciation -computer hardware and software Furniture and equipment Accumulated depreciation - fumiture and equipment Leasehold improvements Accumulated depreciation - leasehold improvements Intangibles Accumulated amortization - Intangibles Goodwill Accounts payable Unearned revenue Income tax payable Interest payable Notes payable Bonds payable Preferred shares Common shares Cash dividend-common Cash dividend preferred Retained earnings Unadjusted trial balance Dr Or $ 98,546 $ 25,032 $ 500 $ 1,600 $ 24,000 $ 985,362 $ 39,856 $ 19,333 $ 12,057 $ 14,523 S 8,816 $ 856,365 $ 480,369 $ 45.000 S 33.000 $ 250.000 $ 12,698 $ 1,253 $ 1.500 $ 58,302 $ 475,000 $ 176,522 171,130 S 75,000 $ 50,000 $ 256,845 $ 15,157,369 $9.856,554 $ 2, 183,659 $ Sales Cost of goods sold Salaries expense Depreciation expense Amortization expense Insurance expense Bad debt expense General and administrative expenses Rent expense Utilities expense Interest expense Gain/Loss on sale of asset Income tax expense Loss on discontinued operations Gain on sale of discontinued operations 60.500 1,700 26985 3.65 $ S 57.147.593 5 125,000 $16.9650100 5 16.965.360$ For adjusting entries: Management has reviewed the aging accounts receivable and estimates that the allowance for doubtful accounts should be $2,000. The prepaid insurance balance is a 12-month insurance policy which was purchased and paid for on July 1, 2020. Rent is prepaid annually on April 1. On September 15th some computers were sold for $1,000. They had a cost of $5,000 and a net book value of $200 (This transaction is NOT included in the unadjusted trial balance) cei did an inventory count on Dec 31, 2020 and determined that they had inventory on hand worth $895,000. On October 31, 2020 C91 issued a 5-year, 4%, $500,000 bond at .95, when the market rate was 5% and it pays interest semi-annually on Mar 31 and Sept 31. C9I uses the effective interest rate amortization method. The recording of the issuance of this bond was done correctly but nothing has been recorded since. The bookkeeper currently records the entire note payable payment as a reduction to principal; therefore, the interest needs to be separated at year end. The note payable is paid every 6 months with blended payments of $8,349. The annual interest rate is 3.5%. The previous accountant correctly prepared the following amortization table Pmt # Dato Principal Interest Payment Balanco Jan 1st 2020 $6,849 $1,500 $8,349 $68,151 2 Jul 1st 2020 $6,986 $1,363 $8,349 $61,164 Total 2020 $13,835 $2,863 $ 16,698 $61,164 3 Jan 1st 2021 $7,126 $1,223 $8,349 $54,038 1 Jul 1st 2021 $7,269 $1,081 Total 2021 $14,395 $2,304 5 Jan 1st 2022 $7,414 $935 6 Jul 1st 2022 $7,562 $787 Total 2022 $14,976 $1,722 7 Jan 1st 2023 $7,714 $636 8 Jul 1st 2023 $7,868 $482 Total 2023 $15,582 $1.118 9 Jan 1st 2024 $8,025 $324 10 Jul 1st 2024 $8,186 $164 Total 2024 $16,211 $488 Grand Total $75,000.00 $8,495 $8,349 $46,769 $16,699 $46,769 $8,349 $39,355 $8,349 $31,793 $16,699 $31,793 $8,349 $24,079 $8,349 $16,211 $16,699 $16,211 $8,349 $8,186 $8,349 $0 $16,699 $0 $83,495 $0 Depreciation and Amortization Policy Depreciation and Amortization is only taken at the end of each fiscal year and C9I uses only one Depreciation expense account for all tangible assets and one Amortization account for all intangibles. Furniture and equipment - amortized straight-line with no expected salvage value and an estimated useful life of 10 years. Leasehold Improvements, amortized straight-line with an expected salvage value of $15,000 and an estimated useful life of 20 years. Computer hardware and software- amortized straight-line with no expected salvage value and an estimated useful life of 3 years. Intangibles - amortized straight-line with an an estimated useful life of 20 years. Other information (to complete the assignment which has already been recorded): Included in the unadjusted trial balance was dividends declared and paid during the year of $125,000 but the bookkeeper was not sure where to record them. c91 has both preferred shares and common share authorized and outstanding. Col is authorized to issue 100,000 $2 non-cumulative preferred shares and at December 31, 2020, it has 15,000 issued and outstanding. Col is authorized to issue unlimited common shares and at December 31, 2020 has 75,000 issued and outstanding In March 2020 Glenn, the recently promoted Regional Manager of C9I decided to discontinue operations related the store's pharmacy. On Oct 30th, 2020 col sold all assets relating to the pharmacy business and recorded a gain on the transaction. The tax rate for C9l is 25%. C9l includes EPS calculations on its Income Statement even though ASPE doesn't require it because its shareholders like to know this information. Required: Prepare the following in the excel workbook provided: a) A tab with a workbook of trial balances (Unadjusted (provided), Adjusted, and Closing Trial Balances) b) A tab with all adjusting entries (please include the disposal of computer equipment here) c) A tab with the financial statements for Cloud 9 Incorporated for the year ended December 31 in good form including ALL EPS (continuing, dis ops and full) calculations. (Please include a Multi-Step Income Statement, Statement of Retained Earnings and a Classified Balance Sheet (Please note: A Statement of Cash Flows is not required) d) A tab with closing entries