Question

Xercise Cycles Company has provided its year ended accounts receivables that were uncollected.

ALL answers MUST be an excel formula using the excel function requested in each segment as follows

1. Prepare an aging of accounts receivable and compute bad debt expense. ? - X S8= FILE HOME INSERT Aging of accounts receivable method - Excel PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign In BIU . A A Paste Clipboard Alignment Number Alianment Number Conditional Format as CellCells Formatting Table Styles Editing Styles Font x fx Xercise Cycles Company sells exercise equipment to gyms on account. At year Xercise Cycles Company sells exercise equipment to gyms on account. At year end, the following accounts receivable were uncollected. 3 Required: 4 1) Calculate the number of days unpaid, USING THE EXCEL DAYS FUNCTION (fx). 000 Number of Days Unpaid Customer 8 Fitness Mania 9 Health for Life 10 Holistic Health Options 11 Image Reimaging 12 Today's Gym World 13 Whole Life Fitness Center 14 Workouts Unlimited 15 Total Amount 2,500 2,000 12,750 7,000 4,600 15,900 3,800 48,550 Invoice Date 6/17/2016 12/21/2016 10/12/2016 12/5/2016 11/29/2016 11/20/2016 10/2/2016) Today's Date 12/31/2016 12/31/2016 12/31/2016 12/31/2016 12/31/2016 12/31/2016 12/31/2016 $

Answer 1

Result View

? X GS HOMEINSERT 2019 10 01.xlsx - Microsoft Excel DEVELOPER - 2 Sign in FILE PAGE LAYOUT FORMULAS DATA REVIEW VIEW H8 | Η L M N A Number of Days Unpaid $ $ $ Customer 8 Fitness mania 9 Health for life 10 Holistic health options 11 Image reimaging 12 Today's Gym world 13 Whole Life fitness center 14 Workouts Unlimited 15 Total Amount 2,500 2,000 12,750 7,000 4,600 15,900 3,800 48,550 Invoice Date 6-17-2016 12-21-2016 10-12-2016 12-5-2016 11-29-2016 11-20-2016 10-2-2016 Today's date 12-31-2016 12-31-2016 12-31-2016 12-31-2016 12-31-2016 12-31-2016 12-31-2016 $ $ $ Number of Days Unpaid 31-60 61-90 0-30 Over 90 2,500 $ $ 2,000 $ 12,750 Customer 23 Fitness mania 24 Health for life 25 Holistic health options 26 Image reimaging 27 Today's Gym world 28 Whole Life fitness center ... Sheet202 Sheet203 READY Total 2,500 2,000 12,750 7,000 4,600 15.900 $ 7,000 4,600 15.900 Sheet206 Sheet207 Sheet204 Sheet205 Sheet208 Sheet209 Activate Windows Go to Settings te activate Windows 2 0 0 -- + 100% A F ENG Search the web and Windows 03:25 US 11-11-19

? X GS HOMEINSERT 2019 10 01.xlsx - Microsoft Excel DEVELOPER - 2 Sign in FILE PAGE LAYOUT FORMULAS DATA REVIEW VIEW 120 L M N Number of Days Unpaid 31-60 61-90 0-30 Over 90 2,500 $ $ $ 2,000 $ 12,750 $ 7,000 Customer 23 Fitness mania 24 Health for life 25 Holistic health options 26 Image reimaging 27 Today's Gym world 28 Whole Life fitness center 29 Workouts Unlimited 30 Total accounts receivable 31 Estimated Uncollectiable (%) 32 Estimated Uncollectiable ($) Total 2,500 2,000 12,750 7,000 4,600 15,900 3,800 48,550 4,600 15,900 $ 9,000 $ $ $ $ $ 3,800 16,550 20% 3,310 20,500 10% 2,050 2% 2,500 40% 1,000 $ 180 $ $ $ $ 6,540 36 Allowance for doubtful accounts 250 Credit Balance $ 6,290 38 Bad debts expense 39 Allowance for doubtful accounts 40 6,290 $ 100 Debit Balance $ 6,640 43 Allowance for doubtful accounts 44 45 Bad debts expense 46 Allowance for doubtful accounts 47 . ... Sheet202 Sheet203 READY $ 6,640 Sheet204 Sheet205 Sheet206 Sheet207 Sheet208 Sheet209 + F Activate Windows Go to Settings te activate Windows 9 0 -- + 100% 2 ENG 03:26 US 11-11-19 Search the web and Windows

Calculation

Allowance of doubtful account (credit balance) after adjustment	6,540
Less: Allowance of doubtful account (credit balance) available before adjustment (As per question) (credit balance means already adjustment made.)	(250)
Adjustment of bad debts expense	6,290
Allowance of doubtful account (credit balance) after adjustment	6,540
Add: Allowance of doubtful account (debit balance) available before adjustment (As per question) (debit balance means extra adjustment required to made.)	100
Adjustment of bad debts expense	6,640

Formula View

? X GS HOMEINSERT 2019 10 01.xlsx - Microsoft Excel DEVELOPER - 2 Sign in FILE PAGE LAYOUT FORMULAS DATA REVIEW VIEW B1 : fic Amount Invoice Date Today's date Customer 8 Fitness mania 9 Health for life 10 Holistic health options 11 Image reimaging 12 Today's Gym world 13 Whole Life fitness center 14 Workouts Unlimited 15 Total 2500 2000 12750 7000 4600 15900 3800 =SUM(B8:B14) 42538 42725 42655 42709 42703 42694 42645 42735 42735 42735 42735 42735 42735 42735 Number of Days Unpaid =DAYS(D8,08) =DAYS(D9,09) =DAYS(D10, C10) =DAYS(D11, C11) =DAYS(D12, C12) =DAYS(D13, C13) =DAYS(D14,C14) 0-30 Customer 23 Fitness mania 24 Health for life 25 Holistic health options 26 Image reimaging 27 Today's Gym world 28 Whole Life fitness center ... Sheet202 READY Number of Days Unpaid 31-60 61-90 =IF(AND($E8>0,$E8<=30),$B8,"") =IF(AND($E8>30,$E8<=60),$B8,"") =IF(AND($E8>60,$E8<=90), B8,"") =IF(AND($E9>0,$E9<=30),$B9,"") =IF(AND($E9>30,$E9<=60) $B9,"") =IF(AND($E9>60,$E9<=90), B9,"") =IF(AND($E10>0, $E10<=30) $B10,"") =IF(AND($E10>30,$E10<=60) $B10,"") =IF(AND($E10>60,$E10<=90),B10,"") =IF(AND($E11>0, $E11<=30) $B11,"") =IF(AND($E11>30,$E11<=60) $B11,"") =IF(AND($E11>60,$E11<=90),B11,"") =IF(AND($E12>0,$E12<=30), $B12,"") =IF(AND($E12>30,$E12<=60), $B12,"") =IF(AND($E12>60, $E12<=90),B12,"") =IFIANDISE13>0.SE13<=30).$B13."") =IFIANDISE13>30. SE13<=60).$B13."") =IFIANDISE13>60.5E13<=90).B13."") Sheet203 Sheet204 Sheet205 Sheet206 Sheet207 Sheet208 Sheet209 + Over 90 Total =IF($E8>90,$B8,"") =SUM(B23:E23) =IF($E9>90,$B9,"") =SUM(B24:E24) =IF($E10>90,$B10,"") =SUM(B25:E25) =IF($E11>90,$B11,"") =SUM(B26:E26) =IF($E12>90,$B12,"") =SUM(B27:E27) =IFISE13>90.$B13.") =SUMIB28:E28) Go to Settings to activate Windows 0 0 0 - + 100% Search the web and Windows 03:31 A ENG US 11-11-19

? X GS HOMEINSERT 2019 10 01.xlsx - Microsoft Excel DEVELOPER - 2 Sign in FILE PAGE LAYOUT FORMULAS DATA REVIEW VIEW E42 Customer 0-30 23 Fitness mania =IF(AND($E8>0,$E8<=30) $B8,"") 24 Health for life =IF(AND($E9>0, $E9<=30), $B9,"") 25 Holistic health options =IF(AND($E10>0,$E10<=30) $B10,"") 26 Image reimaging =IF(AND($E11>0,$E11<=30) $B11,"") 27 Today's Gym world =IF(AND($E12>0,$E12<=30), $B12,"") 28 Whole Life fitness center =IF(AND($E13>0,$E13<=30),$B13,"") 29 Workouts Unlimited =IF(AND($E14>0, $E14<=30), $B14,"") 30 Total accounts receivable =SUM(B23:329) 31 Estimated Uncollectiable (%) 0.02 32 Estimated Uncollectiable ($) =B30*B31 Number of Days Unpaid 31-60 61-90 =IF(AND($E8>30,$E8<=60) $B8,"") =IF(AND($E8>60,$E8<=90), B8,"") =IF(AND($E9>30, $E9<=60) $B9,"") =IF(AND($E9>60,$E9<=90),B9,"") =IF(AND($E10>30,$E10<=60) $B10,"") =IF(AND($E10>60, $E10<=90),B10,"") =IF(AND($E11>30,$E11<=60), $B11,"") =IF(AND($E11>60, $E11<=90),B11,"") =IF(AND($E12>30, $E12<=60), $B12,"") =IF(AND($E12>60, $E12<=90),B12,"") =IF(AND($E13>30,$E13<=60),$B13,"") =IF(AND($E13>60,$E13<=90),B13,"") =IF(AND($E14>30,$E14<=60), $B14,"") =IF(AND($E14>60, $E14<=90),B14,"") =SUM(C23:C29) =SUM(D23:D29) 0.1 0.2 =C30*C31 =D30*D31 Over 90 =IF($E8>90,$B8,"") =IF($E9>90,$89,"") =IF($E10>90,$310,"") =IF($E11>90,$811,"") =IF($E12>90,$B12,"") =IF($E13>90,$B13,"") =IF($E14>90, $B14,"") =SUM(E23:E29) 0.4 =E30*E31 Total =SUM(B23:E23) =SUM(B24:E24) =SUM(B25:E25) =SUM(B26:E26) =SUM(B27:E27) =SUM(B28:E28) =SUM(B29:E29) =SUM(B30:E30) =SUM(B32:E32) mm 36 Allowance for doubtful accounts 250 Credit Balance =+B38 38 Bad debts expense =F32-B36 39 Allowance for doubtful accou 40 41 Debit Balance 43 Allowance for doubtful accounts 100 44 45 Bad debts expense =+F32+B43 46 Allowance for doubtful accou 47 ... Sheet202 Sheet203 Sheet204 READY =+B45 Sheet205 Sheet206 Sheet207 Sheet208 Sheet209 Activate Windows Go to Settings to activate Windows - + 7 100% ENG 03:31 Search the web and Windows A US 11-11-19