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.

Note: Before the 4.90 update in July 2016, all inventory journals used to post with a journal type of JJ. Now, JJ journals are used exclusively for manually-entered journals. If your account dates to before July 2016, bear this in mind when running reports.

Reconciling deferred cost of goods sold (default code 2060, 2260)

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: Account (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:

    • Date from/to: 00/00/0000 to Today's date
    • Row shipping status: Shipped items

    • Drop-ship: No

    • Invoiced: No

    • Order type: Sales orders

    Add a column for:

    • Row cost

    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.

    The report can be found under Reports > Sales detail.

  • 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 corrections report (located under Reports > Accounts > Corrections) to see if any manual adjustments have been made to COGS journals (type GO), as well as for other journals which are now affecting 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.

  • Journal entries not related to goods out

    Deferred COGS is automatically accounted for when items are shipped on an uninvoiced sales order. Only journals with a type of GO should be posting to this code. Look for any journals of other types. 

  • Search for unexpected dates

    If a sale or journal unexpectedly falls outside of the filtered ranges it 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.

Historical deferred cost of goods sold

While it is not possible to use the sales list to reconcile the deferred cost of goods account to a historic date, you can export the general ledger code and analyze the data to obtain a summary describing what orders are making up the balance as of a particular date. This can also be a useful exercise to identify issues affecting the code.

An example of a method to analyze the data is below:

  1. Filter the general ledger to display the 2060/2260 code from the start of time up until the required date
  2. Export the report
  3. Ensure every row has an order ID - any rows with no order ID may be a potential issue
  4. Use a pivot table to sum up the debits and credits all rows by order ID
  5. Hide any rows where debits do not equal credits

What you’re left with is a list of all the order IDs making up the balance.

Reconciling stock received not invoiced (default code 2050, 2250)

The stock (or goods) received not invoiced (GRNI) account (as set under Settings > Accounting: Accounts (nominal codes)) is used when inventory is received on purchase orders and then 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 on 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 can be found under Purchases > PO Detail report. It 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:

    • Goods received not invoiced (A) 

      Filter by:

      • Date from/to: 00/00/0000 to Today's date
      • Row stock status: Items received

      • Drop-ship: No

      • Invoiced: No

      • Order type: Purchase orders

      Add a column for:

      • [Base currency] row net
    • Goods invoiced but not received (B) 

      Filter by:

      • Date from/to: 00/00/0000 to Today's date
      • Row stock status: Items not received

      • Drop-ship: No

      • Invoiced: Yes

      • Order type: Purchase orders

      Add a column for:

      • [Base currency] row net

    Then subtract A from B to get the expected GRNI balance.

  • Trial balance

    This report will give the current balance of the good received not invoiced account code. Compare this balance to the balance calculated from the purchase detail report.

Investigating discrepancies

If the goods received not invoiced 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 affecting the account code

    Use the 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.

  • Journal entries not related to GRNI

    GRNI is automatically accounted for when items are received on a purchase order and when the purchase invoice is received. Only journals with a type of PI, PG or GO should be posting to this code. Look for journals of any other types.

  • 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.

Historical goods received not invoiced

While it is not possible to use the purchases list to reconcile the stock received not invoiced account to a historic date, you can export the general ledger code and analyze the data to obtain a summary describing what invoices are making up the balance as of a particular date. This can also be a useful exercise to identify issues affecting the code.

An example of a method to analyze the data is below:

  1. Filter the general ledger to display the 2050/2250 code from the start of time up until the required date 
  2. Export the report
  3. Ensure every row has an invoice reference - it's necessary to use invoice references because purchase orders can be batch invoiced
    • You can do this by exporting the purchases list (Purchases > Recent purchase order) filtered for invoiced orders, and do a VLOOKUP or similar to populate any empty cells in the Order ID column
    • Rows for orders which have not yet been invoiced will all be grouped together, and will have to be separated out in a later stage
  4. Use a pivot table to sum up the debits and credits all rows by invoice reference. Hide any rows where debits do not equal credits

You'll be left with a list of the invoice references which have a balance on the code, which you can then investigate individually.

Note that all orders which have not yet been invoiced will be grouped together. You can create a separate pivot table or add a column for order ID to break those orders down further.

Reconciling inventory (default codes 1001, 1200)

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 corrections report to report to see if any manual adjustments have been made to journals affecting the inventory account code(s).

  • Journal entries not related to inventory

    The inventory codes are used only by journal entries corresponding to inventory movements, which will always be of type GO, SG, PG, IA or LC. Search the general ledger for journals of any other type of journal.

  • Inventory journals posting to non-inventory codes

    It is possible that an inventory journal may post to a non-inventory code, usually due to default codes being misconfigured. Search the general ledger for journals of type GO, SG, PG, IA or LC which do not post to an inventory code. This may require you to export the general ledger and manipulate the data.

  • Multiple inventory account codes

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

  • Date differences
    If the "Use invoice date as posting date for cost of goods sold" setting is enabled under Settings > Company > Accounting: options, shipping invoiced sales could result in inventory journals being back- or forward-dated. Filter the balance sheet by the "Created" date to check. 

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 (Aged Debtors) report.

The balance of the accounts payable code should be equal to the balance on the Accounts Payable (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.

Have more questions? Submit a request