Question

Xercise Cycles Company sells exercise equipment to gyms on account. At year end, the following accounts receivable were uncollected. 

 Required: 

1) Calculate the number of days unpaid, USING THE EXCEL DAYS FUNCTION (Ex). 

2) Use the information above to complete the Aging of Accounts Receivable Schedule below. 

Create a formula for each age category, using the Excel IF and AND FUNCTIONS (Ix) to determine where each customer amount belongs.  The IF statement may include the following arguments: <.>.>,<, Excel AND FUNCTION (fx).

Answer 1

						Number of
		Customer	Amount	Invoice Date	Todays date	days unpaid
		Fitness Mania	$2,500	6/17/2016	12/31/2016	197
		Health for life	$2,000	12/21/2016	12/31/2016	10
		Holistic Health Option	$12,750	10/12/2016	12/31/2016	80
		Image Reimaging	$7,000	12/5/2016	12/31/2016	26
		Todays Gym World	$4,600	11/29/2016	12/31/2016	32
		Whole life fitness centre	$15,900	11/20/2016	12/31/2016	41
		Workout Unlimited	$3,800	10/2/2016	12/31/2016	90
		Total	$48,550
			NUMBER OF DAYS UNPAID
		Customer	0-30	31-60	61-90	Over 90	Total
		Fitness Mania				$2,500	$2,500
		Health for life	$2,000				$2,000
		Holistic Health Option			$12,750		$12,750
		Image Reimaging	$7,000				$7,000
		Todays Gym World		$4,600			$4,600
		Whole life fitness centre		$15,900			$15,900
		Workout Unlimited			$3,800		$3,800
	A	Total account Receivable	$9,000	$20,500	$16,550	$2,500	$48,550
	B	Estimated Uncollectable(%)	2%	10%	20%	40%
	C=A*B)	Estimated Uncollectable($)	$180	$2,050	$3,310	$1,000	$6,540
	3)	Bad Debt Expense	$6,290
		Allowance for doubtful accounts		$6,290	(6540-250)
	4)	Bad Debt Expense	$6,640
		Allowance for doubtful accounts		$6,640	(6540+100)