Stock/Ledger Reconciliation Report

Run this on your server: There is a lot of data being reviewed. Running on a client could be excessively slow for large data sets.
Consider limiting the amount of data you are trying to display. Your computer could run out of memory trying to display too much data in this report.

Report columns

Ledger on the left, stock/inventory on the right

Ledger

STOCK

Date

Description

Source

Document

Debit

Credit

Balance

Stock Value

Date

Description

Document

In $

Out $

Balance

Date: Invoice posting date
Description: Invoice {InvoiceNumber} Inventory
Source: {Invoice Type}{Invoice Department} {Invoice Number}, or Bill Number
Document: Invoice [{Invoice Unique}], or Bill [{AP Bill Unique}]
Debit: Debit value of invoice account(s) (allows negative)
Credit: Credit value of invoice account(s) (allows negative)
Balance: Debits add to the starting balance, and credits subtract from the starting balance.
- The starting balance is calculated by adding each individual transaction.
- General ledger (GL) accounts have stored beginning of month values cached for performance, and that this data is locked down via minimum book month to make GL balances stable. It is possible that individual transactions could add up to a different value.
Stock Value: back calculated value of inventory/stock. (Total only, no detail values)
[empty]: separator column
Date: Date of the stock transaction (Doing a proof to see if it uses date of entry or date effective)
Description: Description of the part
In $: Value added to stock (can be negative)
Out $: Value removed from stock (can be negative)
Balance: Total added less total removed

What the report provides

We now have the ability to see the detail that makes up back dated inventory value for the ledger and stock values.
We get running balances.
- The ledger balance includes the starting balance.
- The Stock balance doesn't include the starting balance. (Tip: export to excel to calculate)
A document field for the ledger side and stock side that acts as a reference that helps you manually relate stock activity to ledger activity
- It is important to note that there are additional complexities in that the General Ledger (GL) value is a single number usually representing several products on an invoice. This report does not dive further into this complexity even though the report is a huge step forward in providing useful reconciliation information.

What the report doesn't provide

There are no part numbers or unique identifiers for inventory on the report.
- This can cause challenges when you have the same description for more that one part.
Stock quantity: This report focuses on value
It doesn't provide a breakdown of the GL lines on an invoice
It doesn't let us know the the document for stock is a purchase order, stock adjustment, or invoice.
- Many users may know due to a difference in numbering schemes, but that doesn't always hold true.

Using the report to reconcile

This report simply provides detail for you to comb through.
- Take a look at each day to see if you can isolate the day the difference exists in
- Take the STOCK transactions for each invoice, get a total, and see if it matches the invoice total.
Excel TIPS: change the report to a header with detail style.
- Start by combining the ledger and stock sections by lining up the columns and sorting on a common column
- Common column: The ledger Source, and the Stock Document field are the same for most entries. With a little clean up you could get them all matching.
- Sort by the common column to get everything with a ledger header followed by stock detail lines.
  - If you have problems getting the header prior to details: Tweak the Ledger Description column you can use it as a secondary sort column. Select the Ledger Description column and replace Invoice with __. Those are underscores.
Compare the report in excel today against the backup from the period you are reviewing. This will show differences and may provide insight.
Remember: the report is just a tool. Reconciliation is an accounting, and stock management skill. Put the right person on the job.
IMPORTANT: This report calculates stock by back dating it from the current date. This means that the back dated report will eventually change as time progresses.
- You will eventually get a TStock error that prevents a stock change from being recorded, or someone will change the dollar value on an invoice line (on purpose for a good reason, or inadvertently), or any one of a number of possible integrity errors.
Keep the period as small as possible so that the problem is as simple as possible. One day is thousands of times easier to reconcile than one year.
The only way to get a 100% accurate picture of where you were at in the past for Stock/Inventory is to produce it from a backup for the time you are reporting for.
- If you have a month end data set from last month, you can compare it to the month end report you produce today to generate differences.
- SUPER GEEK TIP FOR YOUR PROGRAMMER (we can't help you with this): check out a diff tool. I searched “diff tool windows” on google and got a bunch of results. This is a programmer tool that can compare one text file and another for differences. if you were to produce an excel .CSV of the report from the back dated and current report you might be able to see if the changes quickly.

More tools - Definitely Techie

WARNING: can change prior year ledger balances. Consult our support department and backup before performing this process.
- Fix proof on all periods: Helps get opening Ledger balance to match trial balance.
  - set book month to 00/0000 > run a test proof > select all periods and departments > run fix proof
WARNING: consult with our support team and backup before running any toolbox routine. Many are no longer supported and can break your data.
- Tool box routine > Stock Control Routines > Validate Stock Quantities: Helps find and re-create missing Stock history records (TIP: must run on a department by department basis if you want to re-create missing history records.