Paste Merge& Center \" 00 ?0 Formatting . Table. Number Styles Font Alignment Cl
ID: 2391027 • Letter: P
Question
Paste Merge& Center " 00 ?0 Formatting . Table. Number Styles Font Alignment Clipboard 110 Aging of 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 balance The percentage uncollectible is calculated as follows 1% 2% 5% 50% After 365 days, the account is written off 1-30 Days 31-60 Days 61-90 91-365 Days l 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 debt expense. 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. 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. Formule returns Excel Tips of days between Formula End Date DAYS function VLOOKUP function Given a table with amounts and uncollectible percentages, you can use ViLOOKUP to determine the uncollectible rate for a specilic numbes
Explanation / Answer
A B C=A*D D Customer name Amount Todays date Invoice date Days outstanding Uncollectable Percentage uncollectable Bonneville $900 7/31/2018 6/28/2018 33 $18 2% Chinook $1,100 7/31/2018 7/28/2018 3 $11 1% Duwamish $500 7/31/2018 5/15/2018 77 $25 5% Foss $350 7/31/2018 6/5/2018 56 $7 2% Nisqually $3,245 7/31/2018 7/15/2018 16 $32 1% Worden $250 7/31/2018 4/12/2018 110 $125 50% Total $6,345 Total $218 Schedule of Accounts Receivable Customer name 1-30 days 31-60 days 61-90 days 91-365 days Total balance Bonneville $900 $900 Chinook $1,100 $1,100 Duwamish $500 $500 Foss $350 $350 Nisqually $3,245 $3,245 Worden $250 $250 Total $6,345 Accounts receivable $6,345 Allowance for Bad debts $218 JOURNAL ENTRY Account /Explanation Debit Credit Bad debt expense $153 (218-65) Existing balance in allowance account=$65 Allowance for Doubtful Accounts $153 (To record estimated uncollectable accounts)