Financial accounting project, please follow instructions and use excel template provided
Name ACC 2000 Winter 2020 Excel Project Name: Worksheet (1) End of Period Worksheet For the Year Ended December 31, 2019 UnadjustedAdjusted Account TitleTrial BalanceAdjustmentsTrial BalanceIncome StatementBalance Sheet DRCRDRCRDRCRDRCRDRCR Cash233,400 Accounts Receivable489,956 Allowance for Doubtful Accounts16,782 Interest Receivable Note Receivable154,689 Merchandise Inventory361,866 Prepaid Insurance13,284 Prepaid Rent25,000 Supplies46,777 Equipment413,678 Accumulated Depreciation - Equipment34,555 Accounts Payable51,709 Salaries & Wages Payable Unearned Revenue32,000 Interest Payable Utilities Payable Note Payable (final payment due 2023)350,550 Common Stock270,600 Retained Earnings424,500 Dividends120,789 Sales5,269,790 Consulting Revenue935,933 Sales Returns and Allowances30,520 Sales Discounts63,552 Cost of Goods Sold4,525,014 Salaries & Wages Expense848,608 Depreciation Expense - Equipment Bad Debt Expense Insurance Expense Rent Expense Supplies Expense Utilities Expense59,286 Interest Revenue Interest Expense 7,386,4197,386,419 Net Income Aging Analysis (2) Prepare the Aging Schedule to determine the adjustment required for the allowance method for bad debt: Age of AccountsBalance December 31, 2019Estimated % UncollectibleEstimated Amount Uncollectible Current287,6162% 1–30 days past due125,7055% 31–90 days past due65,34512% Over 90 days past due11,29030% Total Accounts Receivable$ 489,956 Adjustments (3) Although entries have been recorded all year, adjusting entries have not been recorded since December 31, 2018 when the financial statements were last prepared. Refer to the Worksheet (1) tab for unadjusted account balance information as of December 31, 2019. Use the following information to help in preparing adjusting entries for Rochester Enterprise. a.On November 1, 2019, Rochester Enterprise had signed a 8% bank loan (refer to the worksheet) due in 4 years. This is the only outstanding note payable. b.Prepaid insurance represents a 6-month insurance policy purchased on October 1, 2019. c.On September 1, 2019, Rochester Enterprise paid $25,000 for a 5-month lease for office space. d.Unearned revenue represents a 12-month contract for consulting services that will be performed monthly. The payment from the customer was received on December 1, 2019. e.A physical count revealed that supplies on hand at the end of the month total $23,230. f.Equipment is depreciated on a straight-line basis; residual value is estimated to be $25,000 with an estimated service life of 7 years. The assets were held the entire year. g.On December 1, Rochester Enterprise accepted from Greene Supplies a 7-month note receivable at a 5.5% annual interest rate. h.The company uses the percentage-of-receivables basis for estimating uncollectible accounts. The aging schedule of accounts receivable must be completed to determine management's desired balance for 2019. i.Accrued wages totaling $52,789 were unpaid and unrecorded at December 31, 2019. j.Utility costs incurred but unrecorded for the month of December were estimated to be $6,941. Adjusting Journal Entries(4) General Journal DateAccount TitlesDebitCredit Income Statement(5) Statement of RE(6) Balance Sheet(7) Closing Entries(8) General Journal DateAccount TitlesDebitCredit Microsoft Word - ACC 2000 Winter 2020 Excel Project Instructions ACC 2000 EXCEL Project Winter 2020 Instructions This project has been posted early for you to begin looking at it and working on it. You may not be able to complete all the parts until we have covered more chapters in the book including accounts receivable, fixed assets, long-term liabilities, and stockholder’s equity. A file entitled “ACC 2000 Winter 2020 EXCEL Project Template” has been placed on MOODLE for you to download and save along with a rubric. This file contains 9 worksheet tabs (the first tab is for your name). You have been given the unadjusted trial balance for your company as of 12/31/19. Each company prepares financial statements annually. Required Tasks: 1. Using the information in the Worksheet (1) tab, Aging Analysis (2) tab, and Adjustments (3) tab, prepare the adjusting journal entries for the period. Record the entries in the Adjusting Journal Entries (4) tab. (Use letters a-j instead of a date for the date column and round all numbers to the nearest dollar.) SHOW YOUR WORK FOR THE ADJUSTMENT IN THE CELL OR YOU WILL NOT RECEIVE CREDIT FOR THE ENTRY. Use formulas and link the values to the adjustment. 2. After the above adjusting entries are entered on the Adjusting Journal Entries (4) tab, the amounts should be linked to the Adjustments column of the Worksheet (1). Your adjustment amounts should be a formula so if you change the debit/credit amount on the Adjusting Journal Entries (4), the column amount will automatically change. 3. Complete the Adjusting Trial Balance columns of the by the Worksheet (1) using formulas. Think about the best way to do this. Your last three sets of columns should never contain constant numbers but will include formulas only. 4. Prepare a multiple-step income statement on the Income Statement (5) tab. Your Income Statement should be in good form (proper titles, etc., see text page 230) and well formatted. The accounts of the financial statements should follow the order of accounts on the trial balance. You should use formulas in all cells, not constant numbers. (That means, your income statement should be linked to the adjusted numbers on your worksheet.) 5. Prepare a Statement of Retained Earnings (6) on the proper worksheet. Your Statement should be formatted. You should use formulas in all cells, not constant numbers. 6. Prepare a Classified Balance Sheet (7) on the proper worksheet as of 12/31/19. Your Statement should be formatted as shown on page 175. You should use formulas in all cells, not constant numbers. 7. On the Closing Entries (8) worksheet tab, prepare in journal entry form the appropriate closing entries and the end of 12/31/19. Link entries to the Worksheet (1) and/or financial statement tabs. Due Date: Submit a finished file through MOODLE by Monday, April 13, 2020 by 11:59 p.m. Your file should be named yourlastnamesEXCELproject. Only files submitted by MOODLE using the provided template will be accepted. Grading: Your grade will be based on correctness, statement form, and spreadsheet formatting. Please refer to the grading rubric posted on Moodle. Microsoft Word - ACC 2000 Excel Project Rubric Winter 2020 ACC 2000 EXCEL Project Rubric Note: Each cell must be linked or formula based. Hard cell data will receive “0” points for the requirement. This project is worth a total of 100 points. Task Possible Points Total Points Aging Schedule 4 points Must contain formulas, and be linked to worksheet account. 4 points Adjusting Journal Entries 4 points each entry. Must contain formulas (where applicable), and linked to worksheet accounts. List entries in alphabetical order as shown. 40 points Adjustments on worksheet linked to AJE Must be linked to AJE 5 points Adjusted Columns Income Statement Columns Balance Sheet Columns 1 point Contains formulas 1 point Linked to Adjusted totals 1 point Net Income, DR, CR totals 1 point Linked to Adjusted totals 1 point Net Income, DR, CR totals 5 points Multiple-step Income Statement (see p. 230 for example) 1 points Proper title, date 7 points Multiple-step Format 6 points Calculated properly 1 point List expenses in account order per worksheet. 15 points Statement of Retained Earnings 1 point Proper title, date 2 points Calculated properly 2 points Appropriate formatting 5 points Classified Balance Sheet (see p. 175 for example) 2 points Proper title, date 6 points Proper classifications and accounts 2 points Balance Sheet balances 3 points List accounts in order of liquidity as shown on worksheet. 13 points Closing Entries 3 points Close necessary credit accounts 3 points Close necessary debit accounts 2 points Close income summary 2 points Close dividends 10 points Miscellaneous 1 point No abbreviations on financial statements, or spelling errors 1 point Use of Commas, dollar signs, underscore totals 1 point Saved file with appropriate name 3 points Total Points __________________