The Lake Lucerne Company uses the allowance method of estimating bad debts expense. An aging schedule is prepared in order to calculate the balance in the allowance account.

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

The instructions are in the screenshots. PLEASE HELP.

D G H I J K L M N O Q R C E 1 Chapter 8 1 2 Using Excel 3 P8-42 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 balance in the allowance account. 5 The percentage uncollectible is calculated as follows: : 1-30 Days 1% 31-60 Days 2% 61-90 Days 5% 91-365 Days 50% After 365 days, the account is written off. 1. Calculate the number of days each receivable is outstanding. 2. Complete the Schedule of Accounts Receivable. 3. Joumalize the adjusting entry for Bad Debts Expense. 7 8 9 10 11 12 13 Requirements 14 15 16 17 18 Excel Skills: 19 20 21 22 23 24 25 26 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 joumal entry 7. Use Absolute references to refer to a table in the VLOOKUP formula. Excel Tips: Formula returns the number of days Formula between the two dates DAYS(enddate, startdate) 45 DAYS (C28,028) End Date Start Date 3/1/18 1/15/18 27 28 DAYS function 29 30 31 32 33 VLOOKUP function 34 35 36 Given a table with amounts and uncollectible percentages, you can use VLOOKUP to determine the uncollectible rate for a specific number. Table Array Formula returns the uncollectible rate in column 2 37 Column 1 Column 2 Lookup Value Formula Formula Evaluation: Look up the value of Cell F38 in the range of Cells from C39 to 042. Once you find the value that is no larger than F38, continue in the same row until you get to the second column in the range. Return the value in the second column. 38 39 40 Data Uncollectible % 0 0% 1000 5% 1,245 3,600 150 5% =VLOOKUP(F38,5c$39:$D$42,2) 12% =VLOOKUP(F39,$C$39:$D$42.2) 0% =VLOOKUPLF40 $C$39:$D$42,2) Instructions AR Aging + B E G H J K L L M N 0 0 P Q R Q Given a table with amounts and uncollectible percentages, you can use VLOOKUP to determine the uncollectible rate for a specific number. 32 33 VLOOKUP function 34 35 36 Table Array Formula returns the uncollectible rate in column 2 37 Column 1 Column 2 Lookup Value Formula Formula Evaluation: Data Look up the value of Cell F38 in the range of Cells from C39 to 042. Once you find the value that is no larger than F38, continue in the same row until you get to the second column in the range. Return the value in the second column . 1,245 3,600 150 luncollectible % 01 0% 1000 5% 2000 10% 3000 12% 38 39 40 41 42 43 44 45 5% =VLOOKUP(F38.$C$39:$D$42,2) 12% =VLOOKUP(F39,$C$39:$D$42,2) 0% =VLOOKUP(F40,$C$39:$D$42,2) Hint: Use the F4 key to lock in the absolute cell reference. First Value 46 Amount Second Value Formula Returns: Formula Formula evaluation If the value in Cell B47 is greater than the value in Cell C47, display the value in B47, otherwise display nothing. The double quotes enclose nothing between them, 47 IF function $1,700 1,500 2,500 1700 - IF(B47>C47, B47, "") If the value in Cell B48 is greater than the value in Cell C48, display the value in B48, otherwise display nothing. The double quotes enclose nothing between them. Nothing is displayed because $250 is not greater than $1,500. $250 1,500 2,500 IF(B48>C48, B48,") Formula 31-60 Days 61-90 Days Amount $500 $600 Days 35 67 Formula Returns: 31-60 Days 61-90 Days 500 600 IF(AND(C53>231, C53c60), B53,"") =IF(AND(C53>=61, C53ca90),B53,") Formula evaluation IF CS3>=31 and C53c60), display B53, otherwise display nothing IF (C5361 and C53c90),display B53, otherwise, display nothing IF 054531 and C54C60), display B54, otherwise display nothing IF (C5461 and C54c90),display B54, otherwise, display nothing 48 49 50 51 52 53 IF and AND functions 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 Instructions AR Aging + F G . H J K L M N O P Q R R S T U A B C D E 1 Aging of Accounts Receivable 2 3 Minimum Days Maximum Days % Uncollectible 4 1 30 1% 5 31 60 2% 6 61 90 5% 7 91 365 50% 8 9 Requirement 1: Calculate the number of days each receivable is outstanding. 10 11 DATA Days Outstanding Uncollectible % Customer Name Bonneville Chinook Duwamish Foss Nisqually Worden $ $ $ $ $ $ Amount 900 1,100 500 350 3,245 250 Today's Date 7/31/18 7/31/18 7/31/18 7/31/18 7/31/18 7/31/18 Invoice Date 6/28/2018 7/28/2018 5/15/2018 6/5/2018 7/15/2018 4/12/2018 Requirement 2: Complete the Schedule of Accounts Receivable. Schedule of Accounts Receivable 1-30 Days 31-60 Days 61-90 Days 91-365 Days Total Balance 12 13 14 15 16 17 18 19 20 22 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Customer Name Bonneville Chinook Duwamish Foss Nisqually Worden Totals Estimated % uncollectible Estimated total uncollectible Balances on July 31, 2018 Accounts Receivable 6,345 Allowance for Bad Debts 65 41 42 Instructions AR Aging + A B D E F G H I J J K L M N 0 P Q Q R S T U 1-30 Days 31-60 Days 61-90 Days 91-365 Days Total Balance Customer Name Bonneville Chinook Duwamish Foss Nisqually Worden Totals Estimated % uncollectible Estimated total uncollectible Balances on July 31, 2018 Accounts Receivable 6,345 Allowance for Bad Debts 65 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 Requirement 3: Journalize the adjusting entry for Bad Debts Expense. Account / Explanation Debit Credit ce Instructions AR Aging +