I need the excel formulas for the information below, please!Xercise Cycles Company has provided its…
The following solution is suggested to handle the subject “I need the excel formulas for the information below, please!Xercise Cycles Company has provided its…“. Let’s keep an eye on the content below!
Question “I need the excel formulas for the information below, please!Xercise Cycles Company has provided its…”
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
2.
Formulae | ||||
0-30 | 31-60 | 61-90 | More than 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 IS A KEY TO 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 WHERE YOU CAN TRAIN YOURSELF | =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 CCENTRE | =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 | More than 90 | |
FITNESS MANTRA | 0 | 0 | 0 | 2500 |
HEALTH IS A KEY TO LIFE | 2000 | 0 | 0 | 0 |
HOLISTIC HEALTH OPTIONS | 0 | 0 | 12750 | 0 |
IMAGE REIMAGING | 7000 | 0 | 0 | 0 |
TODAY’S GYM WHERE YOU CAN TRAIN YOURSELF | 0 | 4600 | 0 | 0 |
WHOLE LIFE FITNESS CCENTRE | 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 to bad debts a/c =$ 250. Additional allowance is therefore required for $ 6540-$ 250 = $ 6290
Adjusting entry
Bad debts expense A/c $ 6290
To Allowance for Doubtful Accounts $ 6290
4.If there is a doubtful balance on a debit account of $ 100, adjust the amount to get $ 6540 + $100 (debit Balance) = $ 6640
Adjusting entry
Bad debts expense A/c $ 6640
To Allowance for Doubtful Debts $ 6640
Conclusion
Above is the solution for “I need the excel formulas for the information below, please!Xercise Cycles Company has provided its…“. We hope that you find a good answer and gain the knowledge about this topic of business.