Excel Project

Cloud Nine Corporation (CNC) sells and distributes household goods and cars across Canada. Their fiscal year end is December 31 and they perfrom their adjusting entries annually. CNC uses ASPE to report its financial statements. You have been hired by CNC to perform their accounting cycle starting with preparing the financial statements for the year ended December 31,2021. They had provided you with the an unadjusted trial balance and the following information

For adjusting entries: Management has reviewed the aging accounts receivable and estimates that the allowance for doubtful accounts should be $3,300. The prepaid insurance balance is a 12-month insurance policy which was purchased and paid for on August 1, 2021. Rent is prepaid annually on October 1. On September 31, 2021 CNC issued a 5-year, 5%, $100,000 bond when the market rate was 5.214% and it pays interest semi-annually on Mar 31 and Sept 31. CNC uses the effective interest rate amortization method. The notes payable was issued on March 1, 2021 and it has an interest rate of 4%. CNC pays interest on this note payable quarterly on June 1, Sept 1, Dec 1 and March 1. Depreciation Policy Depreciation is only taken at the end of each fiscal year and CNC uses only one Depreciation expense account for all tangible assets. Furniture and equipment - amortized straight-line with no expected salvage value and an estimated useful life of 8 years. Leasehold Improvements- amortized straight-line with an expected salvage value of $1,000 and an estimated useful life of 15 years. Computer hardware and software- amortized straight-line with no expected salvage value and an estimated useful life of 2 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 $35,000 $90,000 CNC has both preferred shares and common share authorized and outstanding. CNC is authorized to issue 20,000 $5 non-cumulative preferred shares and at December 31, 2021, it has 5,000 issued and outstanding. CNC is authorized to issue unlimited common shares and at December 31, 2021 has 300,000 issued and outstanding. On February 28, CNC issued 10,000 common shares and on August 1, CNC issued 70,000 common shares. In May 2021 Glenn Sturgis, Store Manager of CNC determined that their car business was not profitable and could not compete with better known car dealerships and decided to discontinue operations. On Nov 27th, 2021 CNC sold all assets relating to the car business and recorded a loss on the transaction The tax rate for CNC is 35%. CNC 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, Adjusted, and Closing Trial Balances). Make sure your trial balance is in the appropriate order (follow the accounting equation A=L+E). b) A tab with all adjusting entries c) A tab with the financial statements for Cloud Nine Corporation for the year ended December 31 in good form including ALL EPS (continuing, dis ops and full) In May 2021 Glenn Sturgis, Store Manager of CNC determined that their car business was not profitable and could not compete with better known car dealerships and decided to discontinue operations. On Nov 27th, 2021 CNC sold all assets relating to the car business and recorded a loss on the transaction The tax rate for CNC is 35%. CNC 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, Adjusted, and Closing Trial Balances). Make sure your trial balance is in the appropriate order (follow the accounting equation A=L+E). b) A tab with all adjusting entries c) A tab with the financial statements for Cloud Nine Corporation 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. B24 fx c D E Unadjusted trial balance Dr Cr 66,195 10,994 21,816 80,369 12,057 400 99,460 112,475 65,000 25,000 171,130 1 Cloud Nine Corporation 2 Trial Balance in Alphabetical Order 3 December 31, 2022 4 5 6 7 Accounts payable 8 Accounts receviable 9 Accumulated depreciation - furniture and equipment 10 Accumulated depreciation - Leasehold improvements 11 Accumulated depreciation-computer hardware and software 12 Allowance for doubtful accounts 13 Amortization expense 14 Bad debt expense 15 Bonds payable 16 Cash 17 Cash dividend-common 18 Cash dividend-preferred 19 Common shares 20 Computer hardware and software 21 Cost of goods sold 22 Depreciation expense 23 Furniture and equipment 24 Gain on discontinued operations 25 General and administrative expenses 26 Goodwill 27 Income tax expense 28 Income tax payable 29 Insurance expense 30 Interest expense 31 Interest payable 32 Inventory 31 Land 34 Leasehold improvements 35 Loss on sale of discontinued operations assets 36 Notes payable 37 Preferred shares 3 Prepaid insurance 39 Prepaid rent 40 Rent expense AL Retained earning 42 Salaries expense 18,776 1,347,776 78,214 100,000 30,190 151,682 5,221 988,333 23,349 229,605 67,000 57,000 176,522 1,728 15,307 17,240 190,486 183,857 2.398,202 Marking Key Trial Balance in Alphabetical Ready Trial Balance Adjusting Entries Financial Stat Paste BIU FL a. A = = == = Merge & Center B24 + x fx D E 99,450 112,475 65,000 25,000 171,130 18,776 1,347,776 78,214 100,000 30,190 151,682 14 Bad debt expense 15 Bonds payable 16 Cash 17 Cash dividend-common 18 Cash dividend preferred 19 Common shares 20 Computer hardware and software 21 Cost of goods sold 22 Depreciation expense 23 Furniture and equipment 24 Gain on discontinued operations 25 General and administrative expenses 26 Goodwill 27 Income tax expense 28 Income tax payable 29 Insurance expense 30 Interest expense 31 Interest payable 32 Inventory 33 Land 34 Leasehold improvements 35 Loss on sale of discontinued operations assets 36 Notes payable 37 Preferred shares 38 Prepaid insurance 39 Prepaid rent 10 Rent expense 41 Retained earnings 42 Salaries expense 3 Sales 44 Utilities expense 85 5,221 988,333 23,349 229,605 67,000 57,000 176,522 1,728 15,307 17,240 190.486 183,857 2,398,202 1,890 3,373,637 3,373,537 67 48 50 51 52 S3 54 55 Marking Key Trial Balance in Alphabetical Trial Balance Ready Adjusting Entries