Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Jeff Fisher]

All,

I am wondering what user companies are doing for Balance Sheet Account Reconciliations.  I am the IT Applications Manager for a mid-sized user company.  Our Finance Director is discussing automating the account recs. by potentially utilizing a third-party product (one that she has used elsewhere – not with NAV).  I am wondering if any of you have anything that you have done either inside of NAV, with an Add-On, with custom enhancement(s), or integrating with an outside solution.

Note: We are using NAV version 2016.

Thanks,

Jeff

——————————
Jeff Fisher
Manager, Business Applications Software – North America
Nord Anglia Education
Northbrook IL
——————————

[Paul Smith]

Hi Jeff, we dump the GL transactions to Excel and reconcile that way. I’m curious what 3rd party product did your Finance Director previously use?
I’m also hoping to find a good solution.

——————————
Paul Smith
Controller
Human Rights Campaign
Minneapolis MN
——————————
——————————————-

[Ian Ray]

I am not familiar with any external tools, but in NAV there are a few reports that may be useful, at least for the subsidiary ledgers.

For general ledger, there are

• Account Schedule (in Reports and Analysis)
• Trial Balance
• Detail Trial Balance

For payables and receivables

• Aged account receivable/payable
• Reconcile Cust. and Vend. Accs
• Reconcile AP to GL

Inventory

• Post Inventory Cost to G/L (this can be a critical first step depending on your setup)
• Inventory Valuation (my personal “favorite”)
• Inventory to G/L Reconcile
• Inventory – G/L Reconciliation (analysis and more detail than “Reconcile” report)

Fixed Assets

• FA Book Value 01
• FA G/L Analysis

Bank Accounts

• Bank Reconciliation (which is greatly improved in recent NAV versions, imho)
• Bank Acc. – Detail Trial Bal.

I’m not aware which reports are used for the jobs module. Someone else here might know.

However, as stated, if there is a problem, the actual work is usually Excel. For example, recently I found a weird discrepancy amounting to a few dollars. There was not really any report to run to quickly isolate this transaction, so I dumped the general ledger entries from the account into Excel. Then, I created a pivot table. The transaction jumped out after trying a few fields as columns, showing exactly that total by itself. Clicking on the pivot table went to the line and I looked at the particular transaction in NAV. It turned out that the general posting setup had a faulty value for a GBPG/GPPG combination we had never used before.

On posting setups, I would also check out any accounts specified in the specific posting groups, e.g. accounts receivable control accounts in the Customer Posting Groups. Ideally, these should have Direct Posting unchecked on their G/L card. It’s usually simpler to check direct posting when you want to post a general journal for whatever reason and uncheck it when you are done than leave direct posting open and run the risk of human input throwing off your accounts. This is, of course, preference… but, in my experience, weird imbalances will occur more frequently if direct posting is enabled.

From an IT perspective, you will of course want to just advise on all of this rather than change the setup. I’m not an accountant and will attest that you do not want to change anything without detailed consultation… error messages are not the way you want to communicate these type of changes.

One thing IT can do if you have access is go into SQL and query the G/L Entry table to count the entries and system-created entries to see what percentage of your general ledger entries are entered by a human. Alternatively, you can use Jet Express to run this query.

I would suggest looking at this as a general ledger entry table with a high proportion of user-generated entries may trigger more need for reconciliations. This is because you want the system to post all the appropriate transactions from the subsidiary ledgers to the G/L, not a person. 

In Excel, one way to get this data out of the Jet Express query is with a COUNTIF formula

The % human entries should be low. If you include account number, you can filter by account to see what the ratio of non-system-created entries to entries is for a particular account. I would suggest filtering by the control accounts found in the specific posting group setups.

Another tip is if you run Post Inventory to G/L manually, you can select “Per Posting Group” to reduce the number of entries posted. This makes it so group summary data from the Value Entry table is posted instead of every individual entry. Your accounting folks may or may not want this, but it does clean up the number of entries in the G/L.

I realize none of that directly answers your question, but those are some of the tools NAV has besides just exporting data to Excel.

——————————
Ian Ray
Cypress Grove
Arcata CA
——————————
——————————————-

[Amanda Mayer]

This is an ago old question, I think, for accounting teams. I personally think that account reconciliations should be done manually because that is how errors are found. That additional review is key in ensuring errors do not get missed.

——————————
Amanda Mayer
New View Strategies
Milwaukee WI
——————————
——————————————-

[Natalie Lemke]

Hi Jeff,

I would recommend looking into Blackline

The Unified Cloud for Accounting and Finance | BlackLine

Blackline		remove preview
The Unified Cloud for Accounting and Finance | BlackLine BlackLine automates complex, manual & repetitive accounting processes & enables companies to move beyond the legacy record-to-report process. View this on Blackline >

——————————
Natalie Lemke
Senior Functional Consultant
Stoneridge Software Inc.

——————————
——————————————-