The purpose of this assignment is also to make sure you are building/developing good Excel skills, which are critical in a successful accounting career. Excel is a powerful tool to organize, calculate, verify, and present financial data. Students- For this application assignment, you are going to complete adjusting journal entries, a partial Worksheet, Income Statement, Statement of Retained Earnings, and Balance Sheet. You are to use the worksheet to present the adjusting entries (and their impact on the Trial Balance) and organize the statement information. Use cell references, formulas, and summation functions where applicable and appropriate. Do not hard enter amounts in cells that should be calculated amounts. Specific instructions are outlined below. Instructions: 1. Journalize the following adjusting entries for 6/30/20 in the Journal (see tab) and then transfer to the worksheet in the appropriate Adjusting Entries column. Provide descriptions of all journal entries written up for a complete journal entry. a) The company estimates uncollectible accounts using the % of receivables' allowance method. b) c) 666 d) e) As of June 30th, the company estimates that 5% of the accounts receivable balance is uncollectible. (do not forget to account for the current balance in Allowance for Doubtful Accounts) Supplies on hand are determined to be $2,700 70% of the Prepaid Insurance balance has expired as of June 30th. The Prepaid Rent balance represents 6 months of rent expense. As of June 30, three of those months have passed. Depreciation of assets is recognized as follows: Buildings - $7,400 Equipment - $5,800 Invoices for utilities expenses of $1,050 have been received, but not entered, related to June. Employees working the last 5 days of June will be paid in July. The daily expense f) g) h) 40% of the unearned revenue has been eamed as of June 30. 1) The interest rate on the Mortgage Payable is 7%. Interest expense was last updated and recorded on 12/31/19. for employee wages is $950. (Ignore payroll tax expenses) 2. 3. 4. Update the Adjusted Trial Balance columns in the worksheet. Make sure you carry the correct debit/credit columns across. Use cell references and formulas to create the adjusted balances. Carry over the applicable balances to the Income Statement and Balance Sheet columns. Remember the difference in debits/credits will be the Net Income-which should be added to the bottom of the worksheet columns for each on the line provided. Use the other provided blank worksheets to prepare, in proper format, the Income Statement, Statement of Retained Earings, and classified Balance Sheet for the period ending. Formatting and labeling should be appropriate, consistent, and user-friendly. (USE Spellcheck for each worksheet) Please refer to examples in the book if you question titles and classifications. 5 Note that $8,000 of the Mortgage Payable is due December 1, 2020. Complete these requirements and rename the application file as directed. Attach the completed file to the assignment link in Blackboard by the due date provided in Blackboard. When you complete the assignment, rename your file as "Last name.First name App1_Ch3" and submit the file through the assignment in Blackboard. CHECK FIGURES: Adjusting Entries Debit/Credit Total Adjusted Trial Balance Debit/Credit Total Net Income Total Assets 72,075 1,131,075 157,625 764,750 E F M . Laney Company Worksheet For the Year Ended June 30, 2020 4 Trance Adjusting Entries Adjusted Trial Balance Account th Ok 3 06. Income Statement 06 Balance Sheet Cr 06 16 Ch 7 Accounts Receivable 54.000 000 $4,000 $5,000 $4,000 $5,000 Allowance for DoubtfulAccount 900 2,350 1.250 3,250 D Supplies 18.500 13,800 b 2.700 35,000 24500 10,500 2,700 10,500 1 Prepand Rent 15,000 7,500 7,500 3,500 2 Land 78.000 78,000 78,000 3 Buildings 160.000 160,000 160.000 4 Actum Depreciation $5,000 7,400 62,400 63,400 . Equipment 134.000 124,000 124,000 Accum, Depreciation-Equip 52,500 5.800 58,300 58.300 D Goodwill 318,000 318,000 318,000 Patents 69,000 69.000 69.000 3 Accounts Payable 41.000 1050 42,050 42.050 Unearned Revenue 7500 3,000 4500 4,500 Interest Payable 1.925 1.925 1.925 " Salaries and Wages Payable 4.750 h 4,750 4,750 Mortgage Payable $5,000 55,000 $5,000 Common Stock $1 par 90.000 90,000 90,000 Paidin Capital in Excess of par 45.000 45.000 45,000 Dividends 15,000 15,000 15,000 Retained Earnings 378,900 378.900 378900 income Summary Sales Revenue 383,000 3,000 385,000 385,000 Supplies Expense 3,500 b 11.800 17,300 17,300 Salaries and Wages Expense 82.500 th 4.750 87,250 87.250 Maint and Repairs Exper 24.300 24.300 24,300 Advertising Expense 13.500 13.500 13,500 Depreciation Expense 13,200 13,200 13,200 Bad Debt Experse uities Expense At Expense esurance Expense Interest Expense Totals Net income Totals 2,350 2.350 2,350 12.300 10 1050 13.350 13.350 18.600 d 7.500 26,500 25.100 C 24.500 24,500 24,500 3.600 1.107,800 1,107,000 3,925 72,075 5.535 5,525 72,075 1,131,075 1,131,075 227,375 385,000 903,700 746,075 157,625 157,625 385,000 903,700 REVENUES: Sales Revenue Expenses: Supplies Expense Salaries and Wages Expense Maintenance and Repairs Expense Advertising Expense Depreciation Expense Bad Debt Expense Utilities Expense Rent Expense Insurance Expense Interest Expense Total Expense Net income Candy Company Income Statement For the year Ended 6/30/2020 $ 17,300.00 $ 87,250.00 $ 24,300.00 $ 13,500.00 $ 13,200.00 $ 2,350.00 $ 13,350.00 $ 26,100.00 $ 24,500.00 $ 5,525.00 $ 385,000.00 $ 227,375.00 $157,625.00 Beginning Retained Eamings Add: Net Income Candy Company Statement of Retained Earnings For the year Ended 6/30/2020 3 Less: Dividend 9 Ending Retaind Earnings 0 1 2 3 14 15 16 17 18 19 20 21 22 378900 157625 -15000 521525 Candy Company Balance Sheet June 30, 2020 Assets Liabilities and Owner's Equity