Question

Following is the April 30 Accounts Receivable Subsidiary Ledger of Gabbard, Inc., a retailer of tents and camping equipment. (The ledger is also available for download at the end of the prompt.)

Accounts Receivable Subsidiary Ledger Gabbard, Inc. April 30, 20xx Open Receivables
Customer:	Acme Sporting Goods Central 4726 Interstate Highway 2L Devron, Kansas 76588 507‐945‐8800				Account 43985 Credit Terms: 30 days Credit Limit: $15,000
	Invoice Number	Invoice Date	Due Date	Amount	Customer Total
	77341	April 27	May 27	$1,366.48
	75118	April 1	May 1	4,975.00
	70698	March 15	April 14	4,017.15
	65287	January 14	February 13	659.59	$11,018.22
Customer:	Brufton Outdoors 623 Main Street NW Devron, Kansas 76588 507‐945‐1844				Account 41772 Credit Terms: 30 days Credit Limit: $15,000
	Invoice Number	Invoice Date	Due Date	Amount	Customer Total
	67915	February 20	March 22	$1,946.40
	61190	January 2	February 1	6,763.10	$8,709.50
Customer:	Jafferty Sporting Goods 8466 State Route 33 Elkton, Kansas 76541 507‐312‐9469				Account 30966 Credit Terms: 15 days Credit Limit: $5,000
	Invoice Number	Invoice Date	Due Date	Amount	Customer Total
	74555	March 22	April 6	$4,067.99	$4,067.99
Customer:	Kansas Department of Recreation and Safety 8466 State Route 33 Gray Mountain, Kansas 76529 507‐226‐6881				Account 29667 Credit Terms: 30 days Credit Limit: $25,000
	Invoice Number	Invoice Date	Due Date	Amount	Customer Total
	78021	April 30	May 30	$10,646.20
	77216	April 16	May 16	6,653.15
	77089	April 15	May 15	975.63
	75663	April 1	May 1	299.00
	74277	March 15	April 14	1,104.13
	73586	March 7	April 6	655.31
	67644	February 20	March 22	1,843.83	$22,177.25
Customer:	Pup Scouts of Devron 9064 State Route 16 Devron, Kansas 76588 507‐312‐3126				Account 33117 Credit Terms: 15 days Credit Limit: $10,000
	Invoice Number	Invoice Date	Due Date	Amount	Customer Total
	77911	April 20	May 5	$9,737.91
	75559	March 29	April 13	220.04	$9,957.95
Customer:	Regents Athletic Club 22 West Adams Way Devron, Kansas 76588 507‐945‐2149				Account 30846 Credit Terms: 30 days Credit Limit: $5,000
	Invoice Number	Invoice Date	Due Date	Amount	Customer Total
	66268	January 25	March 24	$2,678.59
	61119	January 2	February 1	862.35	$3,540.94
Customer:	Buddy's Sports Buff 212 Oak Street Silver Horn, Kansas 76557 507‐399‐7761				Account 16200 Credit Terms: 30 days Credit Limit: $10,000
	Invoice Number	Invoice Date	Due Date	Amount	Customer Total
	77115	April 15	May 15	$8,046.40
	70564	March 15	April 14	1,774.92	$9,821.32

Required:
Use Microsoft Excel to prepare an electronic spreadsheet of an accounts receivable aging report for Gabbard for April 30. Organize the spreadsheet with the following column headings:

Customer Name	Total Account Balance	Current Balance		Past Due Balances
				1–30 days		31–60 days		Over 60 days
		Invoice #	Amount	Invoice #	Amount	Invoice #	Amount	Invoice #	Amount

Be sure to include each individual invoice for each customer, and show column totals for each customer. Also show report totals for each column and verify that the report totals cross‐foot and agree with the ending Total Account Balance for all customers.

In addition to completing the spreadsheet, write a brief paper that explains the following:

What is the primary purpose of an accounts receivable aging report?

When should outstanding accounts be written off?

What controls are needed in handling aged accounts?

Answer 1

	Customer Name	Total Account Balance	Current Balance		Past Due Balances
					1–30 days		31–60 days		Over 60 days
			Invoice #	Amount	Invoice #	Amount	Invoice #	Amount	Invoice #	Amount
	Acme Sporting goods	$11,018.22	77341	$1,366.48
			75118	$4,975.00	70698	$4,017.15			65287	$659.59
	Brufton outdoors	$8,709.50					67915	$1,946.40	61190	$6,763.10
	Jafferty Sporting Goods	$4,067.99			74555	$4,067.99
	Kansas Department of recreation & safety	$22,177.25	78021	$10,646.20
			77216	$6,653.15
			77089	$975.63
			75663	$299.00	74277	$1,104.13
					73586	$655.31	67644	$1,843.83
	Pup Scouts of Devron	$9,957.95	77911	$9,737.91	75559	$220.04
	Regents Athletic Club	$3,540.94					66268	$2,678.59	61119	$862.35
	Buddy's Sports Buff	$9,821.32	77115	$8,046.40	70564	$1,774.92
	TOTAL	$69,293.17		$42,699.77		$11,839.54		$6,468.82		$8,285.04
	PRIMARY PURPOSE OF ACCOUNTSRECEIVABLE AGING REPORT:
	Primary Purpose of the report is to help the collection department to collect the overdue payments from the customers
	It also helps in estimating amount of bad debts
	Auditorsvalso may require thisreport for analysis
	Outstanding accounts should be written off when, it is decided that theamount is uncollectable
	Aged accounts needed to be followed up and ensure that amounts in aged accounts are in control.
	This can be done by periodic review and changes in credit limits of cutomers