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 in the allowance account.

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.

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.

Excel Tips

End Date

Start Date

Formula returns the number of days between the two dates

Formula =DAYS(enddate, startdate)

DAYS function

3/1/2018

1/15/2018

45

=DAYS(C28,D28)

VLOOKUP function

Given a table with amounts and uncollectible percentages, you can use VLOOKUP to determine the uncollectible rate for a specific number.

Table Array

Column 1

Column 2

Lookup Value

Formula returns the uncollectible rate in column 2

Formula

Formula Evaluation:

Data

Uncollectible %

1,245

5%

=VLOOKUP(F38,$C$39:$D$42,2)

Look up the value of Cell F38 in the range of Cells from C39 to D42. 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.

0

0%

3,600

12%

=VLOOKUP(F39,$C$39:$D$42,2)

1000

5%

150

0%

=VLOOKUP(F40,$C$39:$D$42,2)

2000

10%

3000

12%

Hint: Use the F4 key to lock in the absolute cell reference.

Amount

First Value

Second Value

Formula Returns:

Formula

Formula evaluation

IF function

$1,700

1,500

2,500

1700

=IF(B47>C47, B47, "")

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.

$250

1,500

2,500

=IF(B48>C48, B48, "")

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.

Formula

Formula Returns:

31-60 Days

61-90 Days

Amount

Days

31-60 Days

61-90 Days

IF and AND functions

$500

35

500

=IF(AND(C53>=31, C53<=60),B53,"")

=IF(AND(C53>=61, C53<=90),B53,"")

$600

67

600

Formula evaluation

IF C53>=31 and C53<=60), display B53, otherwise display nothing.

IF (C53>=61 and C53<=90),display B53,otherwise, display nothing.

IF C54>=31 and C54<=60), display B54, otherwise display nothing.

IF (C54>=61 and C54<=90),display B54,otherwise, display nothing.

Aging of Accounts Receivable
		Minimum Days	Maximum Days	% Uncollectible
		1	30	1%
		31	60	2%
		61	90	5%
		91	365	50%
	Requirement 1: Calculate the number of days each receivable is outstanding.
	DATA
	Customer Name	Amount	Today's Date	Invoice Date	Days Outstanding	Uncollectible %
	Bonneville	$ 900	7/31/2018	6/28/2018	33	2%
	Chinook	$ 1,100	7/31/2018	7/28/2018	3	1%
	Duwamish	$ 500	7/31/2018	5/15/2018	77	5%
	Foss	$ 350	7/31/2018	6/5/2018	56	2%
	Nisqually	$ 3,245	7/31/2018	7/15/2018	16	1%
	Worden	$ 250	7/31/2018	4/12/2018	110	50%
	Requirement 2: Complete the Schedule of Accounts Receivable.
	Schedule of Accounts Receivable
	Customer Name	1-30 Days	31-60 Days	61-90 Days	91-365 Days	Total Balance
	Bonneville
	Chinook
	Duwamish
	Foss
	Nisqually
	Worden
	Totals
	Estimated % uncollectible
	Estimated total uncollectible
		Balances on July 31, 2018
		Accounts Receivable			Allowance for Bad Debts
		6,345				65
	Requirement 3: Journalize the adjusting entry for Bad Debts Expense.
		Account / Explanation			Debit	Credit
		xxx			xxx
		xxx				xxx

I need help on Requirement 2&3 with the formulas to plug into excel and the accounts.