Question

Aging Schedule (Excel) Mets Company had a $1,000 credit balance in Allowance for Doubtful Accounts at December 31, 2018, before the current year's provision for uncollectible accounts. An aging of the accounts receivable revealed the following: Estimated Percentage Uncollectible Current Accounts $110,000 2% 1-30 days past due 30,000 5% 31-60 days past due 20,000 8% 61-90 days past due 25,000 15% Over 90 days past due 15,000 40% Total Accounts Receivable $200.000 Instructions: You must show and explain your answers to receive credit and you need to set up an aging schedule using Excel. You earn more extra points by using formulas in your Excel schedule. (a) Determine the amount to recognize as bad debt expense on December 31, 2010. (b) What is the amount that is reported as the net balance for Accounts Receivable as on December 31, 2018? (c) Assume the same facts as above except that the Allowance for Doubtful Accounts account had a $500 debit balance before the current year's provision for uncollectible accounts. What is the amount of bad debt expense to recognize for the year. (d) What is the amount that is reported as the net balance for Accounts Receivable as of December 31, 2018.

Answer 1

B13 AB M estimated percentage uncollectible current accounts $ 110,000 2% 1-30 days past due $ 30,000 5% 31-60 days past due $ 20,000 8% 61-90 days past due $ 25,000 15% over 90 days past due $ 15,000 40% total $ 200,000 total credit balance required in uncollectible account less: credit balance already existing bad debts expense to be recorded in the year $ 2,200 $ 1,500 $ 1,600 $3,750 $6,000 estimated percentage uncollectible current accounts $ 110,000 2% 1-30 days past due $ 30,000 5% 31-60 days past due $ 20,000 8% 61-90 days past due $ 25,000 15% over 90 days past due $ 15,000 40% total $ 200,000 total credit balance required in uncollectible account less: credit balance already existing bad debts expense to be recorded in the year $ 2,200 $ 1,500 $ 1,600 $3,750 $ 6,000 $ 15,050 $0 $ 15,050 $ 500 $ 15,550 $ 15,050 (a) (b) amount to recognize as bad debt expense on december 31, 2018 amount of net accounts receivable as of december 31, 2018 $ 15,050 $184,950 |(c) amount to recognize as bad debt expense on december 31, 2018 (d) | amount of net accounts receivable as of december 31, 2018 $ 184,950

for formulas and calculations, refer to the image below -

B13 C D E F G H I J K L M 0.05 estimated percentage uncollectible current accounts 110000 0.02 1-30 days past due 30000 0.05 31-60 days past due 20000 0.08 61-90 days past due 25000 over 90 days past due 15000 0.4 total =SUM(D3:07) total credit balance required in uncollectible account less: credit balance already existing bad debts expense to be recorded in the year =D3*E3 =D4*E4 =D5*E5 =D6*E6 =D7*E7 estimated percentage uncollectible current accounts 110000 0.02 1-30 days past due 30000 31-60 days past due 20000 0.08 61-90 days past due 25000 over 90 days past due 15000 0.4 total =SUM(13:17) total credit balance required in uncollectible account less: credit balance already existing bad debts expense to be recorded in the year =J3*K3 =J4*K4 =25*K5 =J6*K6 =J7*K7 =SUM(F3:F7) =SUM(L3:L7) -500 =L9-L10 =F9-F10 (a) amount to recognize as bad debt expense on december 31, 2018 |=F11 (b)| amount of net accounts receivable as of december 31, 2018 | =D8-F9 (c) | amount to recognize as bad debt expense on december 31, 2018 =L11 (d) l amount of net accounts receivable as of december 31, 2018 =J8-L9

In case you have any query, kindly ask in comments.