2 4 5 6 7 Given Data: Harbor Clinic provides all services on account. The total amount of services it provided on account in 2020 was $4,600,000. Management at the clinic believes that the following percentages of accounts receivable will be uncollectible for each category: Not yet due (current) 5% 1 - 30 days past due 15% 31 - 60 days past due 35% 61 - 90 days past due 45% More than 90 days past due 75% 8 9 -10 11 12 16 18 19 14. The balance of Allowance for Uncollectible Accounts on December 31, 2020, 15 before adjustment: $ 5,025.00 (credit balance) Details on the clinic's Accounts Receivable, as of December 31, 2020, are shown below: Harbor Clinic Accounts Receivable 20 December 31, 2020 21 Days Past Due Not Yet Due 1 - 30 31 - 60 22 Customer Name 61 - 90 More than 90 (Current) 23 Anderson, Lars $ 15,045.00 24 Gopalan, Alisha $ 3,250.00 25 Harting, Cheyenne $ 14,220.00 26 Johnson, Nina $ 8,215.00 27 Lee, Jay $ 6,505.00 28 Lopez, Sofia $ 5,780.00 Martin, Trevor $ 5,995.00 Lab 5 Given Data 29 77 18 19 20 21 Harbor Clinic Accounts Receivable December 31, 2020 Days Past Due 1 - 30 31 - 60 61 - 90 More than 90 Not Yet Due (Current) $ 15,045.00 $ 3,250.00 $ 14,220.00 $ 8,215.00 $ 6,505.00 $ 5,780.00 $ 5,995.00 $ 22 Customer Name 23 Anderson, Lars 24 Gopalan, Alisha 25 Harting, Cheyenne 26 Johnson, Nina 27 Lee, Jay 28 Lopez, Sofia 29 Martin, Trevor 30 Mills, Olin 31 Moser, Tirah 32 Nguyen, Joy 33 Petterson, Jessica 34 Scott, Karen 35 Smythe, Troy 36 Thomas, Zachary 37 Trang, Jackie 38 Wong, Mei 39 Zhou, Bao 2,460.00 $ $ 5,880.00 6,400.00 $ 7,355.00 $ 20,400.00 $ 16,115.00 $ 9,650.00 $ 2,305.00 $ 11,975.00 $ 5,840.00 Check figure: Total A/R as of December 31, 2020 = $147,390 41 42 43 44 45 4 ACCT& 201 Lab 5 Fall Quarter 2020 5 7 In this computer lab assignment, you will use Microsoft Excel to construct an aging schedule of 8 accounts receivable and make the year-end adjusting entry to estimate future bad debt expense. 9 See Chapter 5 PowerPoint slide 48 for a sample completed aging schedule. 10 Points Possible: 10. To receive full credit, you must use cell references (linking) when possible 11 and use Excel formulas for all calculated amounts. 12 73 Total 14 Use the information on the Given Data worksheet to complete this assignment 16 Harbor Clinic 17 Aging of Accounts Receivable 18 December 31, 2020 19 Days Past Due Not Yet 31-60 1 - 30 20 Customer Name 61 - 90 More than 90 Due 21 Anderson, Lars 15,045.00 22 Gopalan, Alisha 3,250.00 23 Harting, Cheyenne 14,220.00 24 Johnson, Nina 8,215.00 25 Lee, Jay 6,505.00 26 Lopez, Sofia 5,780.00 27 Martin, Trevor 5,995.00 28 Mills, Olin 2.460.00 29 Moser, Tirah 5,880.00 30 Nguyen, Joy 6,400.00 31 Petterson, Jessica 7,355.00 32 Scott, Karen 20,400.00 33 Smythe, Troy 16,115.00 34 Thomas, Zachary 9,650.00 35 Trang, Jackie 2,305.00 36 Wong, Mei 11,975.00 37 Zhou, Bao 5,840.00 Lab 5 Given Data 5,840.00 0 37 Zhou, Bao 38 Estimated % 39 Uncollectible Amount 40 Uncollectible 41 Credit (It is 43 Harbor Clinic 44 Journal 46 Date Account Debit In the space below, make the year-end adjusting entry to estimate future bad debt expense. 47 okay to type in the account titles, but use Excel to calculate the amount.) 48 December 31, 2020 49 50 (Estimate future bad debts) 52 How much is the Net Realizable Value of Accounts Receivable at Dec. 31, 2020? 53 54 55 56 57 58 59 60 61 Lab 5 Given Data BH