AGING OF ACCOUNTS RECEIVABLE
Create a spreadsheet template for a company that uses the Aging of Accounts Receivable
method of recognizing bad debts. Set up the spreadsheet template so that each month the
balances in the receivables and the allowance accounts can be inputted and the bad debt
expense will be automatically calculated.
You will want a layout that is easy to review for possible changes in the uncollectibility
percentages for each category. Therefore, be sure that your percentages are displayed in
A typical format might look like:
Aging of Accounts Receivable:
A/R % Balance Needed
Amount Uncollectible in Allowance Acct.
Accounts that are
Current _______ _____ (formula)
1-30 days past due _______ _____ "
31-60 days past due _______ _____ "
61-90 days past due _______ _____ "
91-120 days past due _______ _____ "
Over 120 days past due _______ _____ "
Total in A/R (formula)** Total in Allow Acct. (formula)*
* Remember, this is the first step of a two step process to calculate bad debt expense.
**At this point, you will want some mechanism in your spreadsheet to compare the total
in the A/R column with the actual balance at the end of the month for the company's A/R
to verify that the aging was done on all receivables. So you will want to have an area in
the spreadsheet where the actual balance can be compared to this total.
After you have verified that the actual equals the total from your spreadsheet, then you
will want the spreadsheet to calculate bad debt expense by comparing the total from this
formula * with the balance in the Allowance for Bad Debts account prior to adjustment.
This means that you will have to have an area in the spreadsheet so that the current
balance in the Allowance account can be inputted. Then you will have to write a formula
for the calculation of bad debt expense.
Save your template after making sure you remembered to put in a heading. Test your
template by using one of your homework problems. Solve the problem manually and
compare the result with the template's result. Save the solution to the problem as a
different file. Then print it. Make any necessary changes in your formulas in the
original template and save it again. Then print the template, as well as the formulas.
Your boss has indicated that she would like to see a bar chart on the status of the
company's receivables once you've completed the aging schedule.
Create a bar chart as part of the template for the aging of the company's receivables. See
the spreadsheet instructions for creating a bar chart. Save the template again.