Reconciling stock to nominal

Overview

This help sheet is designed as a quick guide to help you find differences between Stock and Nominal. It is assumed the reconciliation will be performed retrospectively (i.e., balance the value on the Stock Valuation not Confirmed/Un-confirmed).

Before you start

It is important that a regular reconciliation is done as it is easier to find a difference within a shorter space of time.

The recommended length of time between reconciliations is one month, however if a large volume of stock transactions are performed this should be reduced.

Points to consider when troubleshooting a difference

Standing Costing

If you are using Standard Costing the Standard Cost Variances routine should be run to view differences between the Stock cost and the purchased cost (i.e., the cost of the Purchase Order/Invoice).

Navigate to Purchase Order Processing, POP Maintenance and Standard Cost Variances, Variances.

If any differences are found these should be posted to the Nominal Ledger by selecting Reset Variances.

Landed Costs

If using Landed Costs when stock is received/invoiced in for stock items that are set to include Landed Costs the cost will include the landed cost. The landed cost value should also be either added into the Nominal accounts to be reconciled or the debit posted to a Nominal Stock account.

You can check the account specified in Stock Control, Utilities, System Settings, Stock Control Settings – Landed Costs tab.

Nominal Ledger

All postings have been updated

Navigate to Nominal Ledger, Period End Routines, and Update Waiting Postings.

Nominal Journals posted to Nominal Stock Account(s)

Any journals that have been posted to the Balance Sheet accounts need to be considered for the reconciliation.

These would have affected the Nominal stock value with no effect on Stock.

Stock Control

Stock transactions coded to the same Nominal account will contra in the Nominal.

Although we would expect Sage 200 to be setup with a different Stock and Issues account the system allows for the account codes to be changed on the Analysis tab. In this example a Stock Write Off transaction is coded back to Finished Goods thereby debiting and crediting the same account with the same URN.

Purchase Ledger

Direct Purchase Invoices/Credit Notes analysed to Stock account(s)

These would not have a corresponding stock transaction i.e., GRN. If Add/Write off Stock was used to post the quantity into/out of Stock this would double the value posted to the Nominal Stock account.

Free Text Purchase Order lines posted to Nominal Stock account(s)

Like direct Purchase Invoices no posting would be made to Stock however the Nominal Stock account(s) would record a value.

Purchase Order Invoices not analysed to Nominal Stock account(s)

Stock would be updated via the GRN/Purchase Invoice however the Nominal stock balance sheet account is not, therefore causing a difference for this transaction.

Un-posted Unauthorised Purchase Invoices posted to stock account

Stock would have been updated from GRN however the Purchase Invoice would have been posted to the account specified for Unauthorised Purchase Invoices.

Purchase Order Invoices value changed/zeroed when analysed to Nominal Stock account(s)

When a Purchase Order has been matched to the GRN stock will be updated with the price, in this example £994.50.

At the Nominal analysis stage of the Purchase Invoice the value has been zeroed therefore no value for this Invoice will be posted to the Nominal Stock account(s).

What do to next

If the reconciliation balanced the previous month and still balances, then the difference is only in the current month.

Stock transactions posted within the month being reconciled will need to be ticked back to the Nominal Stock account(s) used in the reconciliation.

All postings to Nominal are assigned a Unique Reference Number (URN) however the transactions within Stock Control do not. There is no standard report to show what has been posted in a particular period with the value.

Add the Stock Reconciliation report to the Stock Reports menu using our Help Sheet.

Run the report with a transaction date range for the period you are reconciling. Or reduce the range to a more manageable period initially.

The types of transactions that affect Nominal Ledger have been pre-defined however please check to ensure you have captured all relevant postings by using the In criteria.

Export the report to Excel. The detail and Page Header sections will be exported. To do this navigate to File and select Export.

You can use the new Data to Excel functionality but there will be columns not required that will require deletion.

Change the file type to Excel and save into the required location.

The detail and Page Header sections will be exported.

This can then be used to sort/filter by transaction type and ticked back to the matching Nominal transaction(s).

To help with finding the difference it may be good practice to split the transactions into different worksheets to make it easier to match with the Source entries on the Nominal transactions i.e., one for POP, one for Stock etc.

The Nominal transactions can be exported to Excel via the Send to Excel function within Transaction Enquiry.  These can perhaps be added side by side the stock transactions.

It is recommended that you sort both sides by the date then reference to try and get an even match.

Please note: for some Stock transactions there can be multiple entries but potentially only one for the Nominal.

Not all Stock transactions will have a URN, for example the first screen within Record Invoice in POP updates stock but the URN is only known at the point of posting the Nominal analysis.

To help with reconciliation of POP Invoices in future it is recommended that the PO number is entered into the second reference on the second screen.