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
DateDescriptionSourceDocumentDebitCreditBalanceStock ValueDateDescriptionDocumentIn $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.