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?