Excel Essentials for Auditors: Part 3 Techniques for stock and sales analysis

Starting your career as an auditor can be daunting, with exams, lots of audit and accounting standards which keep evolving and the realisation that accountants really do use Excel for everything!

Given this reliance on Excel, there hasn’t always been focused Excel training available incorporating the latest functionality within Excel.

This short series of articles runs through core functionality which should help you in your audit role. The skills can obviously be applied elsewhere but the example exercise, which you can download here, is very much audit focused.

In this third part of the Excel for Auditors series, we move beyond the basics and tackle a scenario much closer to real audit work: analysing stock and sales data to test cost versus net realisable value (NRV). This post will walk you through practical, audit-relevant Excel features: tables, pivot tables, VLOOKUP, XLOOKUP, dynamic arrays and more, using a realistic stock listing and sales dataset.

While the video is the primary format, you can follow along with the article below for a summary of the video.

1. Why use Excel tables?

Excel tables are a powerful way to organise your data. Converting your raw data into tables (using Insert > Table or Ctrl+T) brings several benefits:

• Automatic filters: for easy sorting and searching.
• Dynamic referencing: Formulas and pivot tables update automatically as you add new data.
• Readable formulas: Table references use column names, not cell addresses, making them easier to understand, particularly from other tabs.
• Auto-fill: Formulas entered in one row are automatically copied down the table.

Tip: Give your tables meaningful names (eg, “StockListing”, “SalesData”) for clarity in formulas.

2. Summarising data with pivot tables

Pivot tables are essential for summarising large datasets. For example, to find the minimum sales price for each stock item:

• Insert a pivot table from your sales data table.
• Drag the item/part to the “Rows” area.
• Drag “Sales Price” to “Values” and set it to “Minimum” (via Value Field Settings, see video).

You can also:

• Drag “Document Number” to “Columns” to see which invoices each item appeared on.
• Use “Count” to see how many times each item was sold.

Tip: If you make a mistake, just drag fields out of the pivot table layout and try something else.

3. Comparing stock values with sales: VLOOKUP and XLOOKUP

To test whether stock items were sold for more than their year-end value, you need to compare the stock listing with post-year-end sales prices. This is where lookup formulas come in:

VLOOKUP

• Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
• Use VLOOKUP to pull the minimum sales price for each item from your pivot table into your stock listing.
• Limitation: VLOOKUP can only pull back data to the right of the lookup reference column and it returns the first match. It returns an error if the item isn’t found.
• VLOOKUP has been around for ages so it’s very popular I’ve included here ONLY so that you understand how it works so you can understand other people’s formulae.

XLOOKUP

• Basic syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
• XLOOKUP is more flexible: it can search in any direction, return multiple columns, and lets you specify a custom message (eg, “missing”) if the item isn’t found. NB you may need to use additional parameters not mentioned above.
• You can use XLOOKUP to pull both the minimum sales price and other relevant data in one go. XLOOKUP can return arrays (multiple columns) in one formula, which is useful for more complex comparisons.
  • This doesn’t work inside Excel tables - if you get a spill error, try moving the formula outside the table.
• I would generally encourage XLOOKUP for new worksheets for the reasons above and it can be much easier to review too where data is loaded from a table as the formula becomes easy to read and understand what data is being used.

Tip: Use F4 to lock ranges in your formulas, as covered in earlier parts of this series. This isn’t necessary if your data is in an Excel Table as the column/row limits will be defined by the table.

4. Advanced summarisation: GROUPBY and PIVOTBY

Excel now offers GROUPBY and PIVOTBY formulas for summarising data dynamically (available in newer versions):

• GROUPBY: Summarises data by a chosen field (eg, total quantity sold by item).
• PIVOTBY: Creates pivot-style summaries directly in the worksheet, updating automatically as data changes.

Note: These formulas are powerful but can be less intuitive than traditional pivot tables, especially for charting or multi-level summaries. If you’re new to these, start with traditional pivot tables and explore GROUPBY/PIVOTBY as you gain confidence/understanding of how to interact with the row/column options.

Conclusion

This scenario-based walkthrough demonstrates how Excel’s features can streamline audit testing and analysis, we have assessed the full population of stock for cost v NRV testing instead of a sample of items. By mastering tables, pivot tables, lookups, and dynamic formulas, you’ll be well-equipped to handle real-world audit data efficiently and accurately.

These same principles can be applied to a wide range of audit testing areas and the techniques covered can scale to hundreds of thousands of rows.