Receivables

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:

$1-30$ Days $1\%$

$31-60$ Days $2\%$

$61-90$ Days $5\%$

$91-365$ Days $50\%$

After $365$ days, the account is written off.

Use the blue shaded areas on the ENTER$-$ANSWERS tab for inputs.

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.

Requirements Possible Points

$1$ Calculate the number of days each receivable is outstanding. $12$

$2$ Complete the Schedule of Accounts Receivable. $45$

$3$ Journalize the adjusting entry for Bad Debts Expense. $4$

$*$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 Use the following table for your calculations.

Minimum Days Maximum Days $\%$ Uncollectible

$1301\%$

$31602\%$

$61905\%$

$9136550\%$

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.$)$

DATA

Customer Name Amount Today's Date Invoice Date Days Outstanding Uncollectible $\%$

Bonneville $$9007/31/186/28/2018$

Chinook $$1,1007/31/187/28/2018$

Duwamish $$5007/31/185/15/2018$

Foss $$3507/31/186/5/2018$

Nisqually $$3,2457/31/187/15/2018$

Worden $$2507/31/184/12/2018$

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.$)$

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,34565$

$(65)$

$-$

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.$)$

Account $/$ Explanation Debit Credit

HINTS

Cell $|$ Hint:

$"$F$16$:G$21|$ 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."

$"$G$16$:G$21|$ Use absolute cell references for the TABLE$\_$ARRAY argument of the VLOOKUP$()$ function. $($Absolute cell

reference uses $$,$ example $A$$1.$ Use the F$4$ key to lock in the absolute cell reference.$)"$

$"$C$30$:F$35|$ 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 C$51$:I$58$ in the Instructions tab. Different logic will not be

graded as correct one."

$"$G$30$:G$36,$ C$36$:G$36|$ Use the function $=$SUM$()$ to calculate the Totals and Total Balance values in the Schedule of

Accounts Receivable."

C$37$:F$37|$ Refer to the data table in cells C$4$:E$7$ for the corresponding values of the Estimated $\%$ uncollectible.

G$37|$ Leave this cell blank.

$**$When entering account names, click the arrow on the right side of the cell to select the appropriate account.