Question

Xenia Distribution, Incorporated

Xenia Distribution, Incorporated is a privately-held company operating in Ohio since 1990. Xenia makes all of its sales to customers on a credit basis, requiring payment within 30 days. Xenia uses the allowance method to estimate the amount currently uncollectible for its accounts receivable. During 2020, Xenia recorded a monthly provision of 1% of credit sales of as an estimate for uncollectible accounts receivable. However, at year-end, an aging of accounts receivable is prepared and the allowance for uncollectible accounts is adjusted based on an analysis of that aging. At December 31, 2019, the adjusted balance of the allowance for uncollectible accounts was $31,900, and the balance of accounts receivable was $282,400.

During 2020, Xenia wrote-off $23,400 of customer accounts that were deemed to be uncollectible, due to customers declaring bankruptcy or experiencing financial difficulties so severe that extensive collection efforts were not successful. One customer’s account with a $9,200 balance, which had been written-off in August 2018, was subsequently collected from the customer in July 2020. Xenia maintained the same monthly provision of 1% of credit sales throughout all of 2020. Monthly credit sales for 2020 are as follows:

January            77,700

February          89,400

March             55,200

April                38,900

May                 47,500

June                 63,400

July                  99,200

August             92,300

September        87,800

October            82,900

November        84,300

December         81,400

           

Total cash collections of accounts receivable during 2020 (not including the collection of the previously written-off account) were $859,000.

In preparation for its year-end closing process, Xenia’s controller prepared the following aging of accounts receivable as of December 31, 2020, assigning probabilities of collection based on discussions with Xenia’s credit manager:

                                         Percentage of

Age of Account Receivable       Accounts Receivable     Probability of Collection                     

0-30 days past due                          75%                                  95%                

31-60 days past due                         15%                                 85%                

61-90 days past due                           6%                                  70%                

Greater than 90 days past due           4%                                 10%                            

Requirements

a) Prepare an analysis computing the unadjusted balance in the allowance for uncollectible accounts as of 12/31/20.

b) Prepare the year-end adjusting journal entry to record bad debt expense based on the

December 31, 2020 aging of accounts receivable.

Anyone know how to do it in a Excel?

Answer 1

a. Unadjusted balance in the "Allowance for Uncollectible Accounts" as of 12/31/20 is tabulated below -

Total credit sales during 2020 as per below table $900,000

Particulars	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Total
Sales	$ 77,700	$ 89,400	$ 55,200	$ 38,900	$   47,500	$ 63,400	$ 99,200	$ 92,300	$ 87,800	$ 82,900	$ 84,300	$ 81,400	$ 900,000

Particulars	Amount
Balance on Dec 31, 2019	$    31,900
Written off during 2020	$   (23,400)
Written off balance collected	$       9,200
Provision maintained (900000 * 1%)	$       9,000
Balance of Allow for Unc. Acc. on Dec 31, 2020	$    26,700

b. Year-end adjusting journal entry -

Date	Journal entry	Dr.	Cr.
31-Dec-20	Bad Debt Account	$ 10,168
	Allowance for Uncollectible accounts		$ 10,168
	(Being Adjustment entry for Bad debt recorded)

Adjusting entry calculation -

Unadjusted Balance in AR on Dec 31, 2020:

Particulars	Amount
Balance as on Dec 31, 2019	$ 282,400
Sales 2020	$ 900,000
Cash collected in 2020	$(859,000)
Balance as on Dec 31, 2020	$ 323,400

Calculation of amount of AR uncollectible as on Dec 31, 2020 based on age:

Age of Accounts Receivable	Accounts Receivable %	Accounts Receivable	Probability of Collection	Collectible	Uncollectible
0-30 days past due	75%	$                   242,550	95%	$      230,423	$          12,128
31-60 days past due	15%	$                     48,510	85%	$         41,234	$             7,277
61-90 days past due	6%	$                     19,404	70%	$         13,583	$             5,821
Greater than 90 days past due	4%	$                     12,936	10%	$           1,294	$          11,642
Total		$                   323,400		$      286,532	$          36,868

Calculation of Allownace Adjustment -	Amount
Expected uncollectible Accounts Receivable	$                          36,868
Balance as on Dec 31, 2020 (as per part a.)	$                        (26,700)
Allownace adjustment required	$                         10,168