help with requirements 1-3

Using Excel for Aging Accounts Receivable The Trust uses the allowance method of estimating bad debts expense. An aging schedule is prepared in order to calculate the balance in the allowance account. The percentage uncollectible is calculated as follows: "Always use cell references and formulas where appropriate to receive full credit. Requirements 1. Calculate the number of days each receivable is outstanding 2. Complete the Schedule of Accounts Receivable. 3. Journalize the adjusting entry for Bad Debts Expense 'For all requirements, enter all amounts as positive values. Do not use a minus sign or parentheses for any values. Excel Skills 1. Use the DAYS function to calculate the number of days between the invoice date and the date of the aging schedule. 2. Use the VLOOKUP function to provide the uncollectible accounts percentage based on the days outstanding 3. Use IF and AND functions to determine in which column the Accounts Receivable Schedule to place the amount for each customer 4. Format cells using number and percentage formats. 5. Use formulas to calculate totals 6. Use the Increase Indent button to indent the credit account for the journal entry 7. Use Absolute references to refer to a table in the VLOOKUP formula Use the following table for your calculations. Requirement 1 Calculate the number of days each receivable is outstanding. (See hints below.) (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab, you will be marked wrong. Enter all amounts as positive values. Do not use a minus sign or parentheses for any values.) Requirement 2 Complete the Schedule of Accounts Receivable. (See hints below. Use aSUM() for totals. Complete all blue highlighted cells.) (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the instructions tab, you will be marked wrong. Enter all amounts as positive values. Do not use a minus sign or parentheses for any values.) Requirement 2 Complete the Schedule of Accounts Receivable. (See hints below. Use =SUM() for totals. Complete all blue highlighted cells.) (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab, you will be marked wrong. Enter all amounts as positive values. Do not use a minus sign or parentheses for any values.) Requirement 3 Journalize the adjusting entry for Bad Debts Expense. (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the instructions tab, you will be marked wrong. Enter all amounts as positive values. Do not use a minus sign or parentheses for any values.) Requirement 3 Joumalize the adjusting entry for Bad Debts Expense. (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab, you will be marked wrong. Enter all amounts as positive values. Do not use a minus sign or parentheses for any values.) HINTS Cell | Hint: F16:G21 | Use the functions =DAYS() and =VLOOKUP() to calculate the days outstanding and corresponding uncollectible % values. Do not enter any value for the RANGE_LOOKUP argument of the VLOOKUP function. G16:G21 | Use absolute cell references for the TABLE_ARRAY argument of the VLOOKUPQ) function. (Absolute cell reference uses \$, example \$A\$1. Use the F4 key to lock in the absolute cell reference.) C30:F35 | Use the functions =IF() and =AND() to calculate the amounts of accounts receivable for each customer for each period, if any. Follow the examples provided in cells C51:158 in the Instructions tab. Different logic will not be graded as correct one. G30:G36, C36:G36 / Use the function =SUM() to calculate the Totals and Total Balance values in the Schedule of Accounts Receivable. C37:F37 | Refer to the data table in cells C4:E7 for the corresponding values of the Estimated \% uncollectible. G37 | Leave this cell blank. * When entering account names, click the arrow on the right side of the cell to select the appropriate account