Reconciling balance sheet control accounts

There are a number of control accounts on the balance sheet which will often be subject to a reconciliation or audit. This guide will help to explain how to use Brightpearl reports to view the relevant data in order to audit each control account.

Reconciling deferred cost of goods sold (default code 2060)

The deferred cost of goods (deferred COGS) code is used only when cost of goods accounting is activated and deferred COGS is on. Find these settings at Settings > Company > Accounting: Options. It is used when items are shipped before they are invoiced.

Learn more about cost of goods accounting in Brightpearl

The balance in the deferred COGS account code (as set at Settings > Accounting: Nominal codes) should be equal to the value of the items shipped on sales orders which have not yet been invoiced.

Deferred COGS can only be reconciled as of today; it is not possible to reconcile as of a historical date.

The reports which will provide the data to perform the reconciliation of deferred COGS are:

  • Sales detail report

    Filter by shipped items, exclude drop-shipped items and invoiced items. This report will provide the value of deferred COGS as recorded through the sales module. It will not include any manual journals or corrections made in accounting. Compare this balance to the balance of the deferred COGS account code.

  • Trial balance

    This report will give the current balance of the deferred COGS account code. Compare this balance to the balance in sales, taken from the sales detail report.

Investigating discrepancies

If the deferred COGS value from sales does not equal the current balance in the account code, try investigating the following things:

  • Journal corrections to goods movement (GO) journals

    Use the Accounting Corrections report to see if any manual adjustments have been made to COGS journals (type GO), as well as for other journals which are now effecting the COGS account code. To see what changes were made click on the journal ID and view the the change history at the bottom of the journal screen.

  • Manual (JJ) journals entered into accounting (after Aug 2016)

    Deferred COGS is automatically accounted for when items are shipped on a sales order (not invoiced). A JJ journal effecting the deferred COGS after that indicate a manual journal was entered. Use the general ledger to search for JJ journal effecting the deferred COGS code after August 2016. Before August 2016 JJ journals were also used for all goods-in movements, now this type is reserved for manual entry.

  • Search for unexpected dates

    If a sale or journal unexpectedly falls outside of the filtered ranges if will be missed from the reconciliation. This can happen when a date is typed incorrectly by accident. Filter the reports again with a date far in the future to make sure an order or journal isn't hiding.

  • Exclude drop-shipped items

    Drop-shipped items need to be excluded from the report as these won't have any cost of goods sold postings associated with them.

How to see deferred COGS on the sales detail report

  1. Go to Reports > Sales detail.
  2. Filter by:

    Date = Created and Custom date range

    Date from = 00/00/0000 (this ensures all orders are captured in the report)

    Date to = Today

    Invoiced = No

    Shipped = Yes

  3. Use the column configuration to add the following columns:

    Row cost

  4. Apply the filters to view the report on screen, or click Export to send the data to a file which can be analysed in a spreadsheet.

Reconciling goods received not invoiced (default code 2050 (UK) or 2250 (US))

The goods received not invoiced (GRNI) (as set at Settings > Accounting: Nominal codes) is used when goods are received on purchase orders and also when the purchase invoice is received. Once both the goods and the invoice have been received on a purchase order, the GRNI account code should have a zero balance for that order.

The balance in the GRNI account code should be equal to the value of purchased items which are invoiced but not received, less those which have been received but not invoiced.

GRNI can only be reconciled as of today; it is not possible to reconcile as of a historical date.

The reports which will provide the data to perform the reconciliation of GRNI are:

  • Purchase detail report

    This report will provide the value of goods as recorded through the purchases module. It will not include any manual journals or corrections made in accounting. Compare this balance to the balance of the GRNI account code. To get the total expected GRNI value get the following values and subtract one from the other:

    • a) Goods received not invoiced (excluding dropship)
    • b) Goods invoiced but not received (excluding dropship)

    Subtract a from b to get the expected GRNI balance.

  • Trial balance

    This report will give the current balance of the GRNI account code. Compare this balance to the balance in purchases.

Investigating discrepancies

If the GRNI value from purchases does not equal the current balance in the account code, try investigating the following things:

  • Journal corrections to PI, PG or GO journals effecting the account code

    Use the Accounting Corrections report to see if any manual adjustments have been made to journals of type PI, PG or GO, and for any other journals which are now using the the GRNI account code. To see what changes were made click on the journal ID and view the the change history at the bottom of the journal screen.

  • Manual (JJ) journals entered into accounting (after Aug 2016)

    GRNI is automatically accounted for when items are received on a purchase order and when the purchase invoice is received. A JJ journal effecting the GRNI indicates that a manual journal was entered. Use the general ledger to search for JJ journal effecting the GRNI code after August 2016. Before August 2016 JJ journals were also used for all goods-out movements (PG), now this type is reserved for manual entry.

  • Search for unexpected dates

    If a purchase or journal unexpectedly falls outside of the filtered ranges if will be missed from the reconciliation. This can happen when a date is typed incorrectly by accident. Filter the reports again with a date far in the future to make sure an order or journal isn't hiding.

How to see GRNI on the purchase detail report

  1. Go to Purchases > PO detail report.
  2. Filter by:

    Date = Created and Custom date range

    Date from = 00/00/0000 (this ensures all orders are captured in the report)

    Date to = Today

    Invoiced = No

    Row stock status = Items received

    Dropship = No
  3. Use the column configuration to add the following columns:

    Row cost

  4. Apply the filters to view the report on screen, or click Export to send the data to a file which can be analysed in a spreadsheet.
  5. Make a note of this first balance.
  6. Filter by:

    Date = Created and Custom date range

    Date from = 00/00/0000 (this ensures all orders are captured in the report)

    Date to = Today

    Invoiced = Yes

    Row stock status = Items not received

    Dropship = No
  7. Use the column configuration to add the following columns:

    Row cost

  8. Apply the filters to view the report on screen, or click Export to send the data to a file which can be analysed in a spreadsheet.
  9. Make a note of this second balance.
  10. Subtract the second value from the first (not invoiced goods received less invoiced not received) to get the total expected GRNI value.

Reconciling inventory (default code 1001 (UK) or 1200 (US))

The balance of the inventory account code, or codes, should be equal to the value of all in-stock items, including stock in-transit.

The reports which will provide the data to perform the reconciliation of inventory are:

  • Inventory summary

    The inventory summary report provides a list of all items in stock across all warehouses. Use the filters to include stock in transit. These values will also include any landed costs. Use the date filter to view inventory as of a particular date.

  • Trial balance

    This report will display the current balance in the inventory account code. All landed costs are included in this account code.

Investigating discrepancies

If the inventory value from in stock products does not equal the current balance in the account code, try investigating the following things:

  • Journal corrections 

    Use the Accounting Corrections report to see if any manual adjustments have been made to journals effecting the inventory account code(s).

  • Manual (JJ) journals entered into accounting (after Aug 2016)

    Search the general ledger for any JJ journals effecting the inventory account code(s) after Aug 2016. Before Aug 2016 JJ journals were also used for all goods movements which now have their own journal types - GO, PG, IA. JJ is now reserved for manual entry.

  • Multiple inventory account codes

    Ensure that all inventory account codes have been included in the calculation.

Reconciling accounts receivable/payable (debtors/creditors) (default codes 1100 and 2100)

The default accounts receivable (1100) and accounts payable (2100) codes are used for all invoices and credits, payments and refunds.

The balance of the accounts receivable code should be equal to the balance on the Accounts Receivable report / Aged debtors report.

The balance of the accounts payable code should be equal to the balance on the Accounts Payable report / Aged creditors report.

Investigating discrepancies

If the inventory value from in stock products does not equal the current balance in the account code, try investigating the following things:

  • Use the data checker

    The data checker found in the accounting menu, will highlight any sales invoices/credits and purchase invoice/credits which have no contact ID associated with the journal. All these types of journal must be associated with a contact in order to appear on the receivables and payables reports.

  • Journal corrections

    Use the Accounting Corrections report to see if any manual adjustments have been made to journals effecting the receivables and payables code(s).

Have more questions? Submit a request