Question

I need the excel formulas for the information below, please!

Xercise Cycles Company has provided its year ended accounts receivables that were uncollected. The Controller has asked you to help prepare the Aging of Accounts Receivable Schedule and the corresponding journal entries. Use the information included in the Excel Simulation and the Excel functions described below to complete the task.

Xercise Cycles Company has provided its year ended accounts receivables that were uncollected. The Controller has asked you to help prepare the Aging of Accounts Receivable Schedule and the corresponding journal entries.  

REQUIRED:

1. CALCULATE THE NUMBER OF DAYS UNPAID USING THE EXCEL FX----ALREADY ANSWERED NEED QUESTIONS: 2,3,4

2. USE THE INFORMATION ABOVE TO COMPLETE AR SCHEDULED BELOW---CREATE A FORMULA FOR EACH CATEGORY USING EXCEL "IF" AND "AND" FUNCTION (FX) TO DETERMINE WHERE EACH CUSTOMER ACCOUNT BELONG

**THE IF STATEMENT MAY INCLUDE <,>,>=, EXCEL AND FUNCTION (FX)

3. PREPARE THE ADJUSTING JE FOR RECORDING BAD DEBT EXPENSE IF THE ALLOWANCE FOR DOUBTFUL ACCOUNTS HAD THE FOLLOWING ADJUSTMENT BALANCE.

4. SAME AS #3 PREPARE THE ADJUSTING JE FOR RECORDING BAD DEBT EXPENSE IF THE ALLOWANCE FOR DOUBTFUL ACCOUNTS HAD THE FOLLOWING ADJUSTMENT BALANCE.

Question 4 is the same as question 3 except the amount is 100 debit balance

Answer 1

2.

		Formulae
	0-30	31-60	61-90	Over 90
FITNESS MANTRA	=IF(E8<30,B8,0)	=IF(AND(E8>30,E8<=60),B8,0)	=IF(AND(E8>60,E8<=90),B8,0)	=IF(E8>90,B8,0)
HEALTH FOR LIFE	=IF(E9<30,B9,0)	=IF(AND(E9>30,E9<=60),B9,0)	=IF(AND(E9>60,E9<=90),B9,0)	=IF(E9>90,B9,0)
HOLISTIC HEALTH OPTIONS	=IF(E10<30,B10,0)	=IF(AND(E10>30,E10<=60),B10,0)	=IF(AND(E10>60,E10<=90),B10,0)	=IF(E10>90,B10,0)
IMAGE REIMAGING	=IF(E11<30,B11,0)	=IF(AND(E11>30,E11<=60),B11,0)	=IF(AND(E11>60,E11<=90),B11,0)	=IF(E11>90,B11,0)
TODAY'S GYM WORLD	=IF(E12<30,B12,0)	=IF(AND(E12>30,E12<=60),B12,0)	=IF(AND(E12>60,E12<=90),B12,0)	=IF(E12>90,B12,0)
WHOLE LIFE FITNESS CENTRE	=IF(E13<30,B13,0)	=IF(AND(E13>30,E13<=60),B13,0)	=IF(AND(E13>60,E13<=90),B13,0)	=IF(E13>90,B13,0)
WORKOUTS UNLIMITED	=IF(E14<30,B14,0)	=IF(AND(E14>30,E14<=60),B14,0)	=IF(AND(E14>60,E14<=90),B14,0)	=IF(E14>90,B14,0)

3.

	0-30	31-60	61-90	Over 90
FITNESS MANTRA	0	0	0	2500
HEALTH FOR LIFE	2000	0	0	0
HOLISTIC HEALTH OPTIONS	0	0	12750	0
IMAGE REIMAGING	7000	0	0	0
TODAY'S GYM WORLD	0	4600	0	0
WHOLE LIFE FITNESS CENTRE	0	15900	0	0
WORKOUTS UNLIMITED	0	0	3800	0
Total	9000	20500	16550	2500
Estimated uncollectibles(%)	2%	10%	20%	40%
Estimated uncollectibles ($)	180	2050	3310	1000
Total uncollectibles ($)	6540

Credit balance in allowance for bad debts a/c = $ 250, therefore, additional allowance required for $ 6540 - $ 250 = $ 6290

Adjusting entry :

Bad debts expense A/c $ 6290

To Allowance for doubtful accounts $ 6290

4.If allowance for doubtful debts had a debit balance of $ 100, adjustment need to be made for total additional allowance required = $ 6540 + $100 (debit balance ) = $ 6640

Adjusting entry :

Bad debts expense A/c $ 6640

To Allowance for doubtful debts $ 6640