AutoSave . Off ) CB Kayak Rentals Excel Assignment FA22 . Saved to this PC O Search (Alt+Q) A Emilia Onrubia X File Home Insert Draw Design Layout References Mailings Review View Help Comments V Share Calibri (Body) 11 ~ A A Aa Ap AaBbCcDc AaBbCcDc AaBbCc AaBbcct AaB Find Gc Replace Paste BIUab X X A DA 1 Normal 1 No Spac... Heading 1 Heading 2 Title Dictate Editor Select Clipboard Font Paragraph Styles Editing Voice Editor Chesapeake Bay Kayak Rentals (CBKR) is located near the mouth of the Lynnhaven River on the Chesapeake Bay in Virginia Beach, VA, and rents kayaks and paddle boards to be used on the bay and the Lynnhaven River in addition to providing tours. Matt Redfern decided to start the business because of his love for the water and outdoors. Matt decided to incorporate the business to limit his legal liability. The business began operations April 1, 2021, and has a December 31 year end. Matt has asked you to prepare Chesapeake Bay Kayak Rentals' financial statements for the nine months ended December 31, 2021. He has been doing his own accounting but isn't sure if he's done everything correctly since he only took principles of financial accounting in college. He has provided you with an unadjusted trial balance at December 31, 2021. Chesapeake Bay Kayak Rentals Trial Balance December 31, 2021 Unadjusted Debit Credit Cash 35,832 Accounts Receivable 14,500 Prepaid Insurance 9,600 Supplies 793 Land Building 330,000 Accumulated Depreciation - Building Computer Equipment 3,745 Accumulated Depreciation - Computer Equipment Kayaks, paddleboards, and life vests (equipment) 17,300 Accumulated Depreciation - Equipment Accounts Payable 6,850 Page 1 of 4 1126 words X Text Predictions: On Accessibility: Investigate Focus + 100%AutoSave . Off ) CB Kayak Rentals Excel Assignment FA22 . Saved to this PC O Search (Alt+Q) A Emilia Onrubia X File Home Insert Draw Design Layout References Mailings Review View Help Comments V Share Find Calibri (Body) 11 ~ A A Aa Ap AaBbCcDc AaBbCcDc AaBbCc AaBbcct AaB Gc Replace Paste BIU ab X X A DA 1 Normal 1 No Spac... Heading 1 Heading 2 Title Dictate Editor Select Clipboard Font Paragraph Styles Editing Voice Editor Accounts Payable 6,850 Wages Payable Interest Payable Note Payable 318,144 Unearned Revenue 1,500 Common stock, $ 5 par (4,000 shared issued and outstanding) 20,000 Paid In Capital In Excess of Par 12,000 Retained Earnings Rental Revenue 129,065 Advertising Expense 6,832 Depreciation Expense - Building Depreciation Expense - Equipment Depreciation Expense - Computer equipment Insurance Expense Interest Expense Supplies Expense Utilities Expense 8,532 2 Page 1 of 4 1126 words X Text Predictions: On Ex Accessibility: Investigate Focus + 100%AutoSave . Off ) CB Kayak Rentals Excel Assignment FA22 . Saved to this PC O Search (Alt+Q) A Emilia Onrubia X File Home Insert Draw Design Layout References Mailings Review View Help Comments V Share 11 ~ A A Aav Ap AaBbCcDc AaBbCcDc AaBbCc AaBbcct AaB Find Calibri (Body) Gc Replace Paste BIU ab X X A DA 1 Normal 1 No Spac... Heading 1 Heading 2 Title Dictate Editor Select Clipboard Font Paragraph Styles Editing Voice Editor Telephone and Internet Expense 1,485 Wages Expense 57,640 Income Summary TOTALS 487,559 487,559 In addition to the trial balance, he has provided you with the following information (Round all items to nearest whole dollar.): a. Annual liability insurance premium of $9,600 was paid April 2, 2021. Record appropriate insurance expense for the period ending December 31. b. Katie, the only employee, worked 16 hours on December 30 and 31 for which she has not been paid. She earns $15/hour. C. A physical count shows $165 of supplies on hand at December 31. d. CBKR purchased land/building on April 1, 2021, for $330,000. He has not separated the value of the land from the building. The appraisal performed at the time of purchased assessed the land at $266,000 and the building at $114,000. Properly allocated the purchase price between the land and the building with an adjusting entry. e. Building purchased on April 1, 2021, is expected to have a useful life of 40 years with no salvage value. Matt has chosen to use the straight-line method of depreciation based on number of months in service. Round expense to nearest whole dollar. f . CBKR financed 100% of the cost of the land/building with a 30 year note payable with monthly payments of $1,482, and an interest rate of 3.5%. His first payment was made May 1, 2021. Matt tells you he posted the entire amount of each payment (8) against the note payable account. You will need to prepare an AJE to reclassify interest portion of payments to Interest expense. (Note: Prepare an amortization schedule using an excel template. Loan amount: $330,000, annual interest rate:3.5%, loan period in years: 30, number of payments narunar: 12 chart data of loan. C/1/21 antional outro naumante: CO. scheduled noument Page 2 of 4 1126 words X Text Predictions: On x Accessibility: Investigate Focus + 100%AutoSave . Off ) CB Kayak Rentals Excel Assignment FA22 . Saved to this PC O Search (Alt+Q) A Emilia Onrubia X File Home Insert Draw Design Layout References Mailings Review View Help Comments V Share Calibri (Body) 11 ~ A A Aav Ap AaBbCcDc AaBbCcDc AaBbCc AaBbcct AaB Find Gc Replace Paste BIU ab X X A DA 1 Normal 1 No Spac... Heading 1 Heading 2 Title Dictate Editor Select Clipboard Font Paragraph Styles Editing Voice Editor f. CBKR financed 100% of the cost of the land/building with a 30 year note payable with monthly payments of $1,482, and an interest rate of 3.5%. His first payment was made May 1, 2021. Matt tells you he posted the entire amount of each payment (8) against the note payable account. You will need to prepare an AJE to reclassify interest portion of payments to Interest expense. (Note: Prepare an amortization schedule using an excel template. Loan amount: $330,000, annual interest rate:3.5%, loan period in years: 30, number of payments per year: 12, start date of loan: 5/1/21, optional extra payments: $0; scheduled payment should equal $1,481.85. Matt rounded up and is paying $1,482/month.) Save amortization schedule as part of workbook (file). g. December interest on the note payable is due on January 1, 2022, with the January 1 payment. Accrue December interest expense h. Kayaks, paddle boards, and life vests (equipment) were purchased on April 2, 2021, at a cost of $17,300. This equipment is expected to have a service life of five years and no salvage value. The straight-line depreciation method is used. i. Computer equipment with a value of $3,745 was acquired April 1, 2021, and is expected to have a seven year life with a $385 salvage value. Straight-line method of depreciation is used. j . Matt is estimates that 2% of account receivable balance at December 31 is uncollectible. Matt wrote off a $1,300 account in November. 1. Create an Excel file to complete this assignment. a. File name should use the following naming convention: lastname, firstname ACCT 307 Excel Assignment b . File should include four worksheets: trial balance, adjusting entries, amortization schedule, and financial statements. Page 2 of 4 1126 words X Text Predictions: On Ex Accessibility: Investigate Focus + 100%AutoSave . Off ) CB Kayak Rentals Excel Assignment FA22 . Saved to this PC O Search (Alt+Q) A Emilia Onrubia X File Home Insert Draw Design Layout References Mailings Review View Help Comments V Share AaBbCcDc AaBbCcDc AaBbCc AaBbcct AaB Find Calibri (Body) 11 ~ A A Aa Ap Gc Replace Paste BIUab X X A DA 1 Normal 1 No Spac... Heading 1 Heading 2 Title Dictate Editor Select Clipboard Font Paragraph Styles Editing Voice Editor c. Using the trial balance worksheet prepare the unadjusted trial balance and include columns for the adjusting entries and adjusted trial balance. 2. Use the information provided to prepare adjusting journal entries in the Adjusting entries worksheet and then post these to the trial balance worksheet. a. Column totals should be calculated using SUM formula b. Calculate adjusted balances using IF formulas. Net income should equal $31,500. 3. Copy the amortization schedule you created above to the Amortization worksheet or move to your assignment file and rename tab. Prepare the following statements: a. Single step income statement - use formula to calculate total expenses and net income b. Statement of retained earnings - use formula to create ending retained earning balance, use cell reference for net income C. Classified balance sheet - use formulas for all subtotals and totals. Note: determine current portion of note payable from amortization schedule and include in current liability section. Remember to subtract this from NP balance for non-current portion. 5. Completed Excel file should be uploaded in Canvas. CB Kayak Rental Excel Assignment RUBRIC Possible Pts. Part 1, a. Follow naming convention: lastname, firstname ACCT 307 Excel 1 Assignment Part 1, b. File should include four worksheets: Trial balance, Adjusting entries, 1 Amortization schedule, and financial statements. Part 1, c. Set up workbook (file) in Excel with unadjusted TB, and include columns for 1 AJEs and adjusted TB Part 2 Using information provided prepare AJEs in AJE worksheet, then post to TB 10 workcheat Q antriac 1 nt each /1/2 nt antru 1/2 nt calculation) Page 3 of 4 1126 words X Text Predictions: On x Accessibility: Investigate Focus + 100%