Money–Goods movement (reconcile stock movements with general ledger)

The money–goods movement is an important audit procedure because it helps you determine whether the flow of goods recorded in the inventory system has been fully and correctly captured in the financial records. This ensures a reliable reconciliation between the physical flow of goods and the financial flow. In this article, we walk through a simple money–goods movement example using a Python script.

A money–goods movement analysis is not directly applicable in every company. Aside from data quality and availability, these analyses are mainly performed in trading companies, manufacturing firms, and wholesalers—because in these organizations the flow of goods is directly linked to revenue and purchasing. If you have no inventory, there’s little to trace, after all.

Requirements

For this audit procedure we will use:

• The financial transactions: Dummy dataset.
• Inventory movements: Dummy dataset.

We use Python (pandas) to load the files and perform the analysis step by step. Haven’t installed Python yet? Learn here how to install Python and the packages for audit work.

Assumptions

In our analysis we assume both files are reliable. However, it’s always important to verify this. For example, have you performed a physical inventory count and reconciled it with the opening or closing balance of inventory? And do the movement totals agree with the trial balance? That gives you more confidence in the data.

Step 1: Load the Files

First, load the datasets. We use the dummy data for this example.

import pandas as pd

# Import financial data
fin = pd.read_csv("dummy_financial_bookings.csv", sep=";")

# Import inventory data
stock = pd.read_csv("dummy_stock_movements.csv", sep="|")

Step 2: Calculate the Formula from the Inventory Movements

Next, we map the full flow for each item. We first group the different movement types into the following categories:

• Opening Inventory
• Purchases
• Sales
• Closing Inventory

You would expect that starting inventory plus purchases increases the stock (+), and then sales decrease it (–), leading to the closing balance. For the audit, we can express this as:

Closing Inventory = Opening Inventory + Purchases – Sales.

To test completeness of recorded sales against the financial records, we rearrange:

Expected Sales = Opening Inventory + Purchases – Closing Inventory.

We implement this as follows:

# Total opening quantity per item
start_inventory = stock[stock['MutationType'] == 'Balance Begin'].groupby('ItemID')['Quantity'].sum()

# Total closing quantity per item
end_inventory = stock[stock['MutationType'] == 'Balance End'].groupby('ItemID')['Quantity'].sum()

# Total purchases per item
purchases = stock[stock['MutationType'] == 'Purchase'].groupby('ItemID')['Quantity'].sum()

# Total sales per item
sales = stock[stock['MutationType'] == 'Sales'].groupby('ItemID')['Quantity'].sum()

# Perform the calculation
calculated_sales = start_inventory + purchases - end_inventory
calculated_sales = calculated_sales.rename('Calculated_Sales')

# Merge with recorded sales
result = pd.concat([calculated_sales, sales.rename('Registered_Sales')], axis=1)

# Compute differences
result['Difference'] = result['Calculated_Sales'] - result['Registered_Sales']

print(result)

The output might look like this:

You’ll notice some discrepancies. That’s because the inventory data also includes other movement types:

• Stock transfers
• Product write‐offs

These are common in practice and affect the totals. Transfers don’t change the net total, but write‐offs do. Let’s include these in our calculation to see if they explain the differences:

Now everything reconciles perfectly, confirming that sales movements are completely recorded. We’ve mapped the goods flow, but to perform a true money–goods movement, we now need to link to the audit file.

Step 3: Reconcile with the Financial Records

You can do this reconciliation at the invoice level or at an aggregate level. Invoice‐level matching requires invoice identifiers in both datasets, which isn’t always available. At an aggregate level, you can do:

# Create an amount column in the financial data
fin['Debit']  = pd.to_numeric(fin['Debit'],  errors='coerce').fillna(0)
fin['Credit'] = pd.to_numeric(fin['Credit'], errors='coerce').fillna(0)
fin['Amount'] = fin['Debit'] - fin['Credit']

# Sum total revenue from goods (account 8000 in this example)
revenue = fin[fin['Account Number'] == 8000]['Amount'].sum()

print(f"Total revenue (financial system): {revenue}")

Then compare it with the sales value from the inventory movements. In this case the unit price is included in the stock data, but you might need to source it from sales invoices in practice:

stock['SalesValue'] = stock['Quantity'] * stock['UnitPrice']
sales_value = stock[stock['MutationType'] == 'Sales']['SalesValue'].sum()

print(f"Total revenue (financial system): {revenue}")
print(f"Total revenue (goods movement): {sales_value}")

Total revenue (financial system): -805962.17
Total revenue (goods movement): 805961.55

Here the figures almost match. Differences are well below any materiality threshold and likely caused due to rounding.

Using this approach, you can verify that the revenue recorded in the financial system agrees with the revenue calculated from inventory movements.