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 (fx). 

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 (fx) to determine where each customer amount belongs. 

 The IF statement may include the following arguments: <,>, >>, <=, Excel AND FUNCTION (fx). 

3) Prepare the adjusting journal entry for recording bad debt expense if the Allowance for Doubtful Accounts 

4) Prepare the adjusting journal entry for recording bad debt expense if the Allowance for Doubtful Accounts

Answer 1

1) No of days unpaid using the Excel DAYS function

Customer	Amount	Invoice date	Today's Date	Number of days unpaid
Fitness Mania	2500	6-17-16	12-31-16	197
Health for life	2000	12-21-16	12-31-16	10
Holistics Health Options	12750	10-12-16	12-31-16	80
Image Reimaging	7000	12-5-16	12-31-16	26
Today's Gym World	4600	11-29-16	12-31-16	32
Whole Life Fitness Center	15900	11-20-16	12-31-16	41
Workouts Unlimited	3800	10-2-16	12-31-16	90
Total	48550

Formula used in Number of days column : =DAYS(Ending date,beginning date)

2. Ageing of Accounts receivable schedule

Customer	Number of days Unpaid				Total
	0-30	31-60	61-90	Over 90
Fitness Mania	0	0	0	2500	2500
Health for life	2000	0	0	0	2000
Holistics Health Options	0	0	12750	0	12750
Image Reimaging	7000	0	0	0	7000
Today's Gym World	0	4600	0	0	4600
Whole Life Fitness Center	0	15900	0	0	15900
Workouts Unlimited	0	0	3800	0	3800
Total Accounts Receivable	9000	20500	16550	2500	48550
Estimated Uncollectible (%)	2%	10%	20%	40%
Estimated Uncollectible ($)	180	2050	3310	1000	6540

Formula used in the above calculations are

Number of days Unpaid
0-30	31-60	61-90	Over 90
=IF(E2<=30,B2,0)	=+IF(AND(E2>30,E2<=60),B2,0)	=+IF(AND(E2>60,E2<=90),B2,0)	=IF(E2>90,B2,0)

c.Journal Entry if Allowance for Doubtful debt had a credit balance of $250

Bad Debt Expense Account	6290
Allowance for Doubtful Account		6290

d.Journal Entry if Allowance for Doubtful debt had a debit balance of $100

Bad Debt Expense Account	6640
Allowance for Doubtful Account		6640