Receivables Using Excel for Aging Accounts Receivable - The Lake Lucerne Company uses the allowance method of estimating bad debts expense. An aging schedule is prepared in order to calculate the b The percentage uncollectible is calculated as follows: 9 1-30 Days 31-60 Days 291 61-90 Days 5% 91-365 Days 50% After 365 days, the account is written off. 72 Use the blue shaded areas on the ENTER-ANSWERS tab for inputs. 13 ALWAYS use cell references and formulas where appropriate to receive full credit. Copy/pasting values will NOT earn full points. 14 15 Possible Points 17 12 16 Requirements 1 Calculate the number of days each receivable is outstanding. 18 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. 19 20 23 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 of the Accounts Receivable Schedule to place the amount for each customer. 4 Format cells using number and percentage formats. S 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. Instructions ENTERANSWERS Receivables Using Excel for Aging Accounts Receivable - The Lake Lucerne Company uses the allowance method of estimating bad debts expense. An aging schedule is prepared in order to calculate the b The percentage uncollectible is calculated as follows: 9 1-30 Days 31-60 Days 291 61-90 Days 5% 91-365 Days 50% After 365 days, the account is written off. 72 Use the blue shaded areas on the ENTER-ANSWERS tab for inputs. 13 ALWAYS use cell references and formulas where appropriate to receive full credit. Copy/pasting values will NOT earn full points. 14 15 Possible Points 17 12 16 Requirements 1 Calculate the number of days each receivable is outstanding. 18 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. 19 20 23 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 of the Accounts Receivable Schedule to place the amount for each customer. 4 Format cells using number and percentage formats. S 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. Instructions ENTERANSWERS
