Question

A F G 1 B с D E Xercise Cycles Company sells exercise equipment to gyms on account. At year end, the following accounts receivable were uncollected. 2 3 Required: 4 1) Calculate the number of days unpaid, USING THE EXCEL DAYS FUNCTION (fx). 5 6 Number of 7 Customer Amount Invoice Date Today's Date Days Unpaid 8 Fitness Mania $ 2,500 6/17/2016 12/31/2016 1971 9 Health for Life 2,000 12/21/2016 12/31/2016) 10 10 Holistic Health Options 12,750 10/12/2016 12/31/2016 80 11 Image Reimaging 7,000 12/5/2016 12/31/2016 26 12 Today's Gym World 4,600 11/29/2016 12/31/2016 32 13 Whole Life Fitness Center 15,900 11/20/2016 12/31/2016 14 Workouts Unlimited 3,800 10/2/2016 12/31/2016 900 15 Total $ 48,550 16 411

I need to use the excel functions to find the correct answers. The first picture has the correct numerical values. Please solve the rest, and show me how you got those answers.

Answer 1

If you like my explanation, please give a "thumbs up"

A B с E F G H 1 2 3 197 4 5 6 7 1 Customer Fitness Mania Health for life Holistic Health Option Image Reimaging Todays Gym World Whole life fitness centre Workout Unlimited Total Amount Invoice Date Todays date Number of days unpaid 2500 6/17/2016 12/31/2016 2000 12/21/2016 12/31/2016 10 12750 10/12/2016 12/31/2016 80 7000 12/5/2016 12/31/2016 26 4600 11/29/2016 12/31/2016 32 15900 11/20/2016 12/31/2016 41 3800 10/2/2016 12/31/2016 90 48550 9 10 11 12 2 13 14 15 16 17 Customer Fitness Mania Health for life Holistic Health Option Image Reimaging Todays Gym World Whole life fitness centre Workout Unlimited Total account Receivable Estimated Uncollectable(%) Estimated Uncollectable($) NUMBER OF DAYS UNPAID 0-30 31-60 61-90 Over 90 Total 0 0 0 2500 2500 2000 0 0 0 2000 0 0 12750 0 12750 7000 0 0 0 7000 0 4600 0 0 4600 0 15900 0 0 15900 0 0 3800 0 3800 9000 20500 16550 2500 48550 2% 10% 20% 40% 180 2050 3310 1000 6540 18 19 20 21 22 23 24 25 26 6290 3 Bad Debt Expense Allowance for doubtful accounts 6290 6640 27 28 29 30 4 Bad Debt Expense Allowance for doubtful accounts 6640

For calculation ref:

G A B C D E F 1 2 1 Customer Amount Invoice Date Todays date Number of days unpaid 3 Fitness Mania 2500 42538 42735 =E3-D3 4 Health for life 2000 42725 42735 =E4-D4 5 Holistic Health Option 12750 42655 42735 =E5-D5 6 Image Reimaging 7000 42709 42735 =E6-D6 Todays Gym World 4600 42703 42735 =E7-07 8 Whole life fitness centre 15900 42694 42735 =E8-08 9 Workout Unlimited 3800 42645 42735 =E9-09 10 Total 48550 11 12 2 NUMBER OF DAYS UNPAID 13 Customer 0-30 31-60 61-90 Over 90 14 Fitness Mania =IF(F3<30,03,0) =IF(AND(F3>30,F3<61),C3,0) =IF(AND(F3>61,F3C91),C3,0) =IF(F3>90,C3,0) 15 Health for life =IF(F4<30,C4,0) =IF(AND(F4>30,74<61),C4,0) =IF(AND(F4>61,74<91),C4,0) =IF(F4>90,c4,0) 16 Holistic Health Option =IF(F5<30,C5,0) =IF(AND(F5>30,F5<61),C5,0) =IF(AND(F5>61,F5<91),C5,0) =IF(F5>90,C5,0) 17 Image Reimaging =IF(F6<30,06,0) =IF(AND(F6>30,F6<61),C6,0) =IF(AND(F6>61,F6C91),C6,0) =IF(F6>90,06,0) 18 Todays Gym World =IF(F7<30,07,0) =IF(AND(F7>30,F7<61),c7,0) =IF(AND(F7>61,F7<91),C7,0) =IF(F7>90,07,0) 19 Whole life fitness centre =IF(F8<30,08,0) =IF(AND(F8>30,F8<61),C8,0) =IF(AND(F8>61,F8<91),C8,0) =IF(F8>90,08,0) 20 Workout Unlimited =IF(F9<30,C9,0) =IF(AND(F9>30,F9<61),C9,0) =IF(AND(F9>61,F9<91),C9,0) =IF(F9>90,09,0) 21 Total account Receivable =SUM(C14:C20) =SUM(D14:020) =SUM(E14:520) =SUM(F14:F20) 22 Estimated Uncollectable(%) 0.02 0.1 0.2 0.4 23 Estimated Uncollectable(s) =C21*C22 =D21*D22 =E21*E22 =F21*F22 24 25 3 Bad Debt Expense =G23-250 26 Allowance for doubtful accounts =C25 27 28 4 Bad Debt Expense =G23+100 29 Allowance for doubtful accounts =C28 30 KE Sheet92 Sheet93 Sheet94 Sheet95 Sheet96 Sheet97 Sheet98 Sheet99 Stats Total =SUM(C14:F14) =SUM(C15:F15) =SUM(C16:F16) =SUM(C17:F17) =SUM(C18:F18) =SUM(C19:F19) =SUM(C20:F20) =SUM(G14:G20) =SUM(C23:F23)