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.

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

Solution 1:

Customer	Amount	Invoice Date	Today's date	Nos of days unpaid
Fitness Mania	$2,500.00	17-Jun-16	31-Dec-16	197
Health for life	$2,000.00	21-Dec-16	31-Dec-16	10
Holistic health options	$12,750.00	12-Oct-16	31-Dec-16	80
Image reimaging	$7,000.00	5-Dec-16	31-Dec-16	26
Today's Gym World	$4,600.00	29-Nov-16	31-Dec-16	32
Whole life fitness center	$15,900.00	20-Nov-16	31-Dec-16	41
Workouts unlimited	$3,800.00	2-Oct-16	31-Dec-16	90
Total	$48,550.00

Solution 2:

Customer	Nos of days unpaid				Total
	0-30	31-60	61-90	Over 90
Fitness Mania	$0.00	$0.00	$0.00	$2,500.00	$2,500.00
Health for life	$2,000.00	$0.00	$0.00	$0.00	$2,000.00
Holistic health options	$0.00	$0.00	$12,750.00	$0.00	$12,750.00
Image reimaging	$7,000.00	$0.00	$0.00	$0.00	$7,000.00
Today's Gym World	$0.00	$4,600.00	$0.00	$0.00	$4,600.00
Whole life fitness center	$0.00	$15,900.00	$0.00	$0.00	$15,900.00
Workouts unlimited	$0.00	$0.00	$3,800.00	$0.00	$3,800.00
Total	$9,000.00	$20,500.00	$16,550.00	$2,500.00	$48,550.00
Estimated uncollectible %	2%	10%	20%	40%
Estimated uncollectible ($)	$180.00	$2,050.00	$3,310.00	$1,000.00	$6,540.00

Solution 3:

Journal Entries
Particulars	Debit	Credit
Bad debts expense Dr	$6,290.00
To Allowance for doubtful accounts		$6,290.00

Solution 4:

Journal Entries
Particulars	Debit	Credit
Bad debts expense Dr	$6,640.00
To Allowance for doubtful accounts		$6,640.00

Excel 8 - Microsoft Excel X 90 = File Home Insert Page Layout Formulas Data Review View - 2 x Calculate Now Insert Function AutoSum Recently Financial Logical Text Date & Lookup & Used - Time Reference Function Library Math More & Trig Functions A a Define Name f Use in Formula Name Manager Create from Selection Defined Names Trace Precedents 5 Show Formulas Trace Dependents Error Checking Watch Remove Arrows" Evaluate Formula Window Formula Auditing Calculation Calculate Sheet Options Calculation J18 A K L M Credit Journal Entries Particulars Bad debts expense Dr To Allowance for doubtful accounts Debit | =G23-250 =G23-250 8 9 Journal Entries Particulars Bad debts expense Dr To Allowance for doubtful accounts Debit Credit =G23+100 =G23+100 Customer Amount Invoice Date Today's date Nos of days unpaid 21) 3 Fitness Mania 2500 42538 42735 =E3-D3 4 Health for life 2000 42725 42735 =E4-D4 5 Holistic health options 12750 42655 42735 =E5-D5 Image reimaging 7000 42709 42735 =E6-D6 7 Today's Gym World 4600 42703 42735 =E7-07 Whole life fitness center 15900 42694 42735 =E8-08 Workouts unlimited 3800 42645 42735 =E9-09 10 Total =SUM(C3:09) 11 12 2) Nos of days unpaid Total 13 Customer 31-60 61-90 Over 90 14 Fitness Mania |=IF(F3>0,IF(F3<=30,C3,0)) =IF(F3<30,0,IF(F3>30,IF(F3<=60,C3,0))} =IF(F3<60,0,1F (F3>60,IF(F3<=90,C3,0))} =IF(F3>90,C3,0) =C14+D14+E14+F14 Health for life =IF(F4>0,IF(F4<=30,04,0) =IF(F4<30,0,1F(F4>30,IF(F4<=60,04,0)>=IF(F4<60,0,1F(F4>60,1F(F4<=90,04,0)>=IF(F4>90,c4,0) =C15-D15+E15+F15 16 Holistic health options =IF(F5>0,IF(F5<=30,C5,0)=IF(F5<30,0,1F(F5>30, IF(F5<=60,C5,0))=IF(F5<60,0,1F(F5>60,IF(F5<=90,C5,0))=IF(F5>90,C5,0) =C16+D16+E16+F16 17 Image reimaging =IF(F6>O, IF(F6<=30,C6,0)=IF(F6<30,0,IF(F6>30, IF(F6<=60, C6,0))X=IF(F6<60,0,IF(F6>60, IF(F6<=90,06,0))=IF(F6>90.06.0) 1=C17+D17+E17+F17 18 Today's Gym World I =IF(F7>0, IF(F7<=30,07,0)) =IF(F7<30,0,1F(F7>30,IF(F7<=60,07,0))} =IF(F7<60,0,1F(F7>60,IF(F7<=90,07,0))} =IF(F7>90,07,0) 1=C18+D18E18+F18 19 Whole life fitness center =IF(F8>0,1F(F8<=30,08,0) =IF(F8<30,0,1F(F8>30,IF(F8<=60,08,0)=IF(F8<60,0,1F(F8>60,IF(F8<=90, C8,0))=IF(F8>90,08,0) |=C19+D19+E19+F19 20 Workouts unlimited =IF(F9>0,IF(F9<=30,C9,0)) =IF(F9<30,0,1F(F9>30, IF(F9<=60,C9,0)) =IF(F9<60,0,IF(F9>60,IF(F9<=90,C9,0))} =IF(F9>90,C9,0) =C20+D20E20+F20 Total | ESUM(C14:C20) =SUM(D14:D20) -SUM(E14:E20) =SUM(F14:F20) I-SUM(G14:20) Estimated uncollectible 20.02 0.1 0.2 23 Estimated uncollectible ($) =C21*c22 =D21D22 =E21 E22 =F21 F22 =C23+D23+E23+F23 0-30 15 21 22 14:20) 0.4 28 30 K Sheet2 Sheet252 Sheet66 Sheet70 Sheet29 Sheet252 (7) Sheet18 (2) ™ Ready Sheet530 (22) Circular References BOJ 80% @ o e o dowels + -+ 4:28 AM 11/22/2019 Answers Need To Fill Out... Ned to fill out! - Microsok ec... Microsoft Excel -...