Question: You are a senior associate with a local accounting firm.  ABC Company, a large transportation company, is your client.  You have been assigned to review the clients’ accounts receivable balance.

Convert the data to a table and name the table ARTable

In the ARTable format the data columns based on their data type (Only amounts and Dates)

While reviewing the data in the ARTable you notice that the company’s aging categories are not the industry standard.     Therefore, you need to add a column to the table to calculate the industry standard aging categories.  This will allow you to evaluate the receivables against other companies in the same industry.

The industry standard aging categories are

Not due (< 0 days outstanding)

Current (0 to 30 days outstanding)

31 to 60 days

61 to 90 days

91 to 180 days

Greater than 180 days

Use the column called days OS to determine each aging category

Name the new column Typical Aging

The trail balance and audit lead schedule for A/R shows that the A/R balance at the end of the year is 644 million dollars.  You need to tie this data to the trial balance and lead schedule.  To do this, create a pivot table that will give you a control total to tie back.  (The pivot table should only have the invoice amount field in it – don’t over think it)

Put the pivot table on a new worksheet and call the worksheet “Control Total”.

Use a tick mark to indicate that the control ties to the audit lead schedule if the amount is 644 M. (Accounting Classes only)

Your audit plan requires your team to randomly vouch 100 invoices out of this data set.   Make a random selection of invoices for your staff to vouch.  To do this you:

Copy the invoice number and invoice date columns to a new worksheet.

Call the worksheet “Sample”

Convert the list to a table

Call the table “InvoiceSample”

Add a column at the end of the table called “Random Number”

Create a formula in the column to generate a random number (use the random function)

The table should automatically copy this formula into all cells of the table (if not, turn on the feature by selecting the icon by the cell below and clicking override all cells in the column with this formula).

Use the quick filter pull down menu to select the top 100 random numbers.

(Note, the random function produces a new number each time the workbook is calculated.  This means the top 100 will only be the top 100 when you first make the selection.  This is expected. You are only trying to select 100 random invoices)

To do an analytical review of invoices past due you should, insert a new worksheet called “Invoices Past Due” into your workbook.

On this worksheet use an advanced filter to extract a subset of invoices that are over 29 days old from the ARTable, exclude customer 0 (customer 0 are cash customers).

Remember that advanced filters must be initiated from the sheet where you want to copy the data to.

The Advanced filters require 3 ranges.  The Criteria range, list range and copy to range.  The list range is the data and will already exist.  However, you must create the criteria range and copy to range before you can run the advanced filter.  These ranges must be created on the worksheet where you want the new table.  Put the Criterial range in Columns A&B and rows 1 and 2. Build the copy to range starting in column A in row 5.  This will keep you from losing the criteria range when you add the subtotal to the table a little later.  If you cannot remember how to set up these ranges, please refer back to the Module 5 workbook.)

Criteria Range

Create a criteria range that include the following columns:

·       Days OS

·       Customer

The criteria must be entered as a text statement.  The best way to assure that excel recognized the criteria as text is to enter the criteria as a formula putting the Criteria in quotation marks.  For example, =">29" or =">0"

Continued on next page

Copy to Range

Include the following columns (in this order) in the copy to range:

·       Customer

·       Invoice Number

·       Invoice date

·       Job Description

·       Activity

·       Typical Aging

·       Invoice Amount

Run the advanced filter.

Run the advanced filter to create a table (list) below the titles in copy to range.

To select the ARTable, type ARTable[#All] into the list range box or with the advance filter box open navigate to the ARTable and hover over the left-hand corner of the table until you see an arrow pointing diagonally into the center of the table and press the left mouse button twice (not double click)

When selecting the titles in the copy to range only select the titles not the row below it.

Put on subtotals

Sort the data in the new table by customer then by invoice number then by invoice date.

Use the subtotal function to group the list by customer with a subtotal on the invoice amount field.

Remember you cannot put subtotals on a range that has been converted to an “Excel table using Ctrl T”

Format and sort the subtotals

Sort the list so the customers with the largest outstanding balance (total of all outstanding invoices for the customer) is on top.

Remember you must collapse the table to the subtotals before you sort. You are sorting the totals not the invoices.

Format the invoice amount as accounting.

Collapse the list to the subtotals.

Put a light gray background on the subtotals.

Expand customer 9999.

Remember you must select only the visible cells before formatting.

As an auditor, do you see something strange about customer 9999?  What would you want to ask the client about this customer?  Add a comment off to the side of this customer to document your question.

Look at the activity and job description how are they different from other customers?

Accounting class only

Your client tells you that it is especially hard to collect invoices for holiday work.   You need to determine if this a problem.

Add another worksheet to your workbook and copy the “Invoices Past Due” worksheet onto this worksheet:

·       Name the Worksheet “Holiday Work”

·       Remove the subtotals

·       Convert to a table

·       Call the table HolidayWork

·       Use the quick filter pull down menu on the job description filter to identify if there are any invoices related to work on the holidays.

If there are, enter a note at the top of the worksheet on how you might determine if these invoices are collectable. (Accounting class only)

To help you do an analytical review of the outstanding receivables create the following pivot table from the ARTable on a new worksheet called Aging.

Summarize the company’s outstanding invoice amount by the aging categories using the Typical Aging category you created is step 3. Put the aging category in the rows and:

·       Show the amount per aging category in Dollars and as a % of the grand total.

·       Move the rows so they are in the correct order.

·       Change the titles of the columns to Dollar and Percent.

·       Format the pivot table to make it easy to read.

·       Validate that your totals tie to your control total.

·       Use a tick mark to document your finding. (Accounting class only)

Companies in this industry typically have the following aging:

·       Not due                       10%

·       Current                        70%

·       31 to 60                       10%

·       61 to 90                         5%

·       91 to 180                       3%

·       Greater than 180 days   2%

Finance class: How does ABC Company compare?  Write a comment with your thoughts.

Accounting Class: As an auditor does this raise any concerns?  Add a note on the worksheet describing the work performed and any concerns or follow up needed.

To do an analytical review of customers, create a pivot table that will give you a list of customers with their outstanding receivable in aging categories using the typical aging.

Put the customer in the row and aging category in the columns.  Format the table as follows:

·       Move the columns so they are in the right order.  Not due, Current …

·       Sort the pivot table by largest balance over 180 days old.

·       Validate that your total ties to your control total.

Extract the detail of the two largest customers over 180 days old into new worksheets.

·       Use the correct procedure to extract the details out of pivot table to a worksheet (if you do not how to do this, refer back to the module 5 workbook).

You should extract all invoices for the customer not just invoices over 180 days old.

·       Convert these worksheets to tables.

·       Give each worksheet the customer number as the name and each table name Table + Customer number

Your firm’s policy is to send confirmations to customers whose balances make up 75% of the outstanding account receivables.  Create a pivot table from the ARTable to help you select the customer’s that you need to send confirmations to:

·       Create a pivot table on a new worksheet from the ARTable.  Call the worksheet confirmation.

·       Put the customers in the row and invoice amount in the value section.

·       Convert the invoice amount to a percent of the grand totals.

·       Change the column title to Percent.

·       Re-put the invoice amount into the value section.

·       Sort by the percent field so the largest percent is on top.

·       Highlight the customers that make up 75% of the total.

Create a statement that you can send to customer 3327 to confirm the balance over 21 days old

·       Convert the customer 3327 list to a table, if not already done and name it Table3327.

·       Create a pivot table from Table3327 with the client’s aging category (“Aging Cat”) in the column.  Sum the invoice amount and format as accounting rounded to two decimal places

·       Put the following fields in the rows

·       Activity

·       Job number

·       Job description

·       Invoice number

·       Invoice date

·       Put the subtotals at the bottom of the section (on the design ribbon).

·       Put a subtotal on the activity and job number. (use the field settings.  If you do not remember how refer back to the workbook on the field value settings tab).

·       Remove subtotals from the other fields in the row (use the field settings).

·       Show in tabular format (on the design ribbon under report layout).

·       Repeat all item labels (on the design ribbon under report layout).

·       Put a light background color on the Job number subtotal.

To put a background color on a subtotal, hover you mouse over the leading edge of the subtotal until the mouse turns to an arrow and click the left mouse button. This should select the entire subtotal row and all other subtotal rows for this field.  Then format the selection as normal.

·       Put a dark background color on the activity subtotal.

·       Sort in activity order largest to smallest.

·       Add slicers for activity, job number and aging category.

·       Collapse at the activity level.

To collapse right click any activity level data element and select Expand / Collapse > collapse the entire field.

·       Filter the aging to over 21 days using the slicers.

You want to have your staff document the revenue processes that make up approximately 80% percent of the revenue.

Create a pie chart that allocates the full receivables by activity to graphically determine what activities to document (use the invoice amount field).

Although the auditors want to document the revenue processes, you do not use the revenue field.  This is because accounts receivable is made up of only the invoices not paid at the end of the month not revenue billed. If you used the revenue field you would not include the entire population.  By using the invoice amount you include the revenue processes that generated the open balance at the end of the year.

·       Create the required pivot table from the ARTable.

·       Label the worksheet Pie Chart

·       Create a pie chart from the pivot table using a pivot chart.

·       Use the top ten option from the label pull down menu on the pie chart to filter out the activities that do not make up the top 80% of revenue.

Do not use the pivot table to determine the top 80%, you should use the pie chart to do the filter.

Base on the inquiry you made to the client in step 6 about customer 9999 you learn that this customer number is used as a default for all small and one time customers rather than setting up a new account for them.

The client also indicated that they use the job description to keep track of the customer’s name as well as other information.

This sends a red flag up in your mind and you wonder if you have a control issue that could misstate the financial statements.

You decide to drill down into this customer further.  Therefore, you create a pivot table from the customer 9999 table to help you drill down.

·       Create the pivot table and name the worksheet 9999 eval.

·       Put the typical aging as the Column and Job Description as the as the row.

·       Put sum of invoice amount in the values.

·       Put count of invoice amount in the values.

·       Using the row pull down menu to filter out the not due and current aging category.

·       Using the row pull down menu sort by the invoice amount.

·       Use the pivot table style command to format the pivot table in a style you feel best presents the data.

·       Change the headings and formats of the values to make the pivot table easy to read.

Based on this review do you see a problem with the customer using this practice?

Is there a material or significant weakness?  If there is a weakness how should this weakness be communicated to the client?

Make a note next the pivot table with your conclusion. (Accounting class only)

This company invoices the customer when the cargo ships but recognizes the revenue when the cargo arrives at destination.

Your manager has asked you to follow up with the client’s manager in locations where there is a high percentage of the receivables that have not been recognized as revenue after the cargo has delivered.

You can tell the delivery date because it is the day that the days outstanding = 0.

To do this you will create a pivot table from the ARTable that will list the percentage of the receivables by site that have been recognized as revenue.

List the sites with the lowest percentage on top so they can be investigated.

·       Create a new pivot table from the ARTable

·       Name the worksheet Revenue Test

·       Put the site in the row

·       Create a calculated field

·       Call it RevRec

·       The RevRec is derived by dividing revenue amount by invoice amount

·       Put RevRec in the values section

·       Sort Revrec in ascending order

·       Format Revrec as a percentage

·       Use a slicer to filter out invoices where the aging days are less than or equal to zero

Use pivot tables, the tests you did above and other data tools to find two things unusual about this data that you as an auditor would like to understand and document in your work papers.  Explain how the use of these excel tools aided you in discovering these issues and show your work.

·       Enter your answer on a new worksheet called Extra Credit.  Keep your answer to just a couple of paragraphs.

·       Note, there are three glaring issues that you should be able to find.  If you find 2 of them, tell me the tools you used to find them and show your work so I know you use excel data tools to find them, I will give you the full pts.  (Accounting Class only)

Finance classes: I will also give you the extra credit if you can figure it out.  I will grade you on finding 2 major issues not the accounting rules they may violate.