# Intermediate Account 1 Excel Case

Intermediate Accounting Excel Problem

Due Date: Monday 28th of October

Requirements and instructions are attached.

Each case solution must be prepared using Excel. Your solution must include all supporting calculations and these supporting calculations must be performed with appropriate Excel formulas. For each case, you must submit one printed copy of the Excel spreadsheet which contains your case solution and a second printed copy displaying the formulas contained in each cell of the spreadsheet. Be sure to format your spreadsheet so that your work is clearly presented and easy to review.

Wright Products Company

Wright Products Company was founded in 2005. Wright offers credit terms (net 30 days) to all of its customers. As Wright has been required to write-off several customer accounts, Wright uses the allowance method to provide for uncollectible receivables. During 2019, Wright provided for uncollectible accounts receivable with a monthly provision of 1% of credit sales. However, at year-end, an aging of accounts receivable is prepared and the allowance for uncollectible accounts is adjusted based on an analysis of the aging. At December 31, 2018, the adjusted balance of the allowance for uncollectible accounts was \$27,390, and the balance of accounts receivable was \$232,300.

During 2019, Wright wrote-off \$23,500 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 \$4,500 balance, which had been written-off in May 2017, was subsequently collected from the customer in April 2019. Wright maintained the same monthly provision of 1% of credit sales throughout 2019. Monthly sales for 2019 are as follows:

January \$75,000

February 79,000

March 71,500

April 86,000

May 88,000

June 74,600

July 57,400

August 73,000

September 78,500

October 91,300

November 76,000

December 69,700

Total cash collections of accounts receivable during 2019 (not including the collection of the previously written-off account) were \$895,800.

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

Percentage of

Age of Account Receivable Accounts Receivable Probability of Collection

0-30 days past due 78% 95%

31-60 days past due 10% 85%

61-90 days past due 8% 50%

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/19.

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

December 31, 2019 aging of accounts receivable.

0 replies