Accounts Receivable Analytical Procedures

This workbook provides the form to document the following basic accounts receivable and sales analytical procedures:
  1. Compare monthly totals for sales accounts with those of prior periods,
  2. Compare balances in various sales accounts by month within the period,
  3. Compare monthly totals for sales accounts with monthly budgeted amounts,
  4. Compare sales for the last month of the fiscal year to sales for the rest of the year and the first month after year end,
  5. Compare monthly sales returns and credit memos for the last few months of the fiscal year to the first few months following year end, and
  6. Investigate any unexpected results.

Entering Data

For the Sales section of this workbook, you may specify the percentage and dollar amount that is considered significant criteria for identifying unexpected monthly results. A red border around a cell in this section indicates amounts that are considered unusual based on the specified criteria as follows:
  • Any individual monthly sales amount that differs from the annual average for that row by more than the specified criteria.
  • Any monthly total that differs from the monthly budgeted amount by more than the specified criteria.
  • Any monthly total that differs from the prior year monthly total by more than the specified criteria.
If you enter only a percentage criterion, then all variances greater than that percentage will be identified (with a red border) regardless of their dollar amount. If you enter only a dollar amount criterion, then all variances greater than that dollar amount will be identified regardless of their percentage. By entering both a percentage and a dollar amount, an item will only be identified as a variance if it meets both criteria.  Specifying both criteria aids efficiency by eliminating the selection of high percentage differences that are insignificant dollar amounts, or high dollar amount differences that are insignificant percentages.
The section provides several input rows to disaggregate total revenues to the desired level of detail (i.e., account number, reporting unit, cost center, etc.) and 13 columns (months) to enter revenue data. For comparisons of actual revenues to budget, the user should enter the budgeted revenues in the row provided near the middle of the worksheet. Also, if this is the first year using this workbook for a particular client, the “Monthly Totals—Prior Year” and “Monthly Totals—2 Years Ago” rows may be manually input (although the cells are shaded yellow) using data from the prior year workpapers. (See the section “General Workpaper Functionality and Tips for Entering Data” for the significance of shaded and non-shaded cells.)
The final two sections of the worksheet, Sales Returns and Credit Memos work independently of the Sales section of the worksheet. That is, the percentage and dollar amount significant criteria specified for the Sales section do not affect these sections. These two sections are used to compare monthly sales returns and credit memos for the last few months of the fiscal year to the first few months following year end. These sections also provides several input rows for disaggregation to the desired level of detail (i.e., account number, reporting unit, cost center, etc.).

Inserting and Deleting Line Items

For the Sales section, input rows should not be added or deleted. Such changes may compromise the integrity of the workbook’s calculations and conditional formatting. However, for the Sales Returns and Credit Memos sections, Additional rows may be added or excess rows may be deleted. See the section “General Workpaper Functionality and Tips for Entering Data” for information concerning the tips for and potential effects of inserting and deleting workbook line items.