@Ealfardan, indeed! Permanently linking each transaction of an imported bank statement to payments, receipts and transfers would be a way to resolve the issues. This would have the benefit of having a record of all imported statements, maintain the use of receipts, payment (and if possible newly transfer) rules and reconcile easily. If done properly the linking should allow linking to already manually entered payments and receipts.
1 Like
Correct
Because the intention is for the user not to need to do any reconciliation work manually at all.
Also when matching really matters is when there are some pre existing transactions and the bank statement is first imported. At that time there are no user links. So getting Manager to respond well then will mean it will respond well later when the bank import is reviewed also. As s result there is no need for manual link creation or maintenance.
So this will not provide any documentation of bank reconciliation procedures. Which means I will have to keep relying on spreadsheets.
I canāt see how this will help the import to ignore pre-existing transactions but I am curious how. I would really appreciate if you could you explain more?
I am astounded by this statement. The Manager reconciliation report does not (and never has) displayed enough information for a supervising officer to properly verify that a reconciliation is accurate. There is scarcely any information in the reconciliation report, and noticeably absent is the total debits and total credits of reconciled transactions to be verified against the total credits and total debits on the bank statement. Every bank puts these total debits and total credits on their bank statements for a reason. It is a very first check on the accuracy of a bank reconciliation.
Take this scenario:
The banking officer of a business is responsible for banking the daily takings, but every now and again removes the cash from the takings and only banks the remainder. To compensate for this the banking officer enters on the business books the correct daily takings amount ( as if the cash was deposited) and enters a bogus payment for the corresponding amount of the cash that has been removed. The bank account will reconcile but the total debits and the total credits will not match to the bank statement.
Managerās bank reconciliation report does not show this discrepancy, nor any of the transactions to highlight where the error might be located.
So, I agree with comments regarding the inadequacy of the bank reconciliation in this thread.
1 Like
-
some transactions are entered prior to bank import by any means for a period of time
-
Bank import done for that period of time
-
Manager used a progressive matching algorithm to match transactions entered. Transactions not entered and transactions already entered in error are show.
-
User utilises Bank import data as template to create Manager transactions for data not already entered and corrects any data entered in error.
-
Drill down on the bank import after user processing runs the same matching algorithm and again shows only transactions mismatches by default, however now will show no miss matched unless the user miss calculated when splitting bank transaction to a multiline Manager entry or the user intentionally created transactions without bank record support such as @AJD example or net zero value used instead of a journal entry.
-
An extended time period expires, someone unlocks old records and inadvertency changes the net amount of an old transaction or delete a transaction or adds a transaction dated in the wrong period. The change in running balance is alerted in the reconciliation tab. Revisiting the bank import will show the miss matched transaction pair or transaction (if one was added or deleted).
In summary, because code is written to do step 3. when only step 3 data is available then the same code can be used for step 5 & 6
Ok, now I get it. Itās going to be a mixture of auto matching and user matching. However, the matching will not be saved and the process will have to be repeated on all entries whenever thereās something wrong.
I still think static linking would vastly improve upon this because:
-
As @AJD said ā and shame on me for missing this ā my staff are going to do the reconciliation and I will have to review it somehow and so does every other supervisor (no to be confused with Manager
). Whereās the documented work? A superficial report isnāt going to do. Matching must be saved because itās the only adequate documentation of work. Static matching/links solves this issue. -
In case of lacking information (which is way more common that one might think) especially when a large number of amounts are the same; say in retail restaurants where many orders have the same amount and customers pay using a cross-bank portal without writing any descriptions. Managerās auto matching ā just like every other softwareās ā is guaranteed to be wrong. Having manager redo itās thing again will only make things worse. Static matching/links solves this issue. You will only redo the matching for those with no static links and leave the rest alone.
-
Manager will never be able to identify mismatched amounts without static matching/links.
-
Deleted bank transactions are completely avoided when another tab (
Bank Imports) is referencing it. The user must have access to that tab to delete it and even then they will be prompted to avoid deletion by mistake. Static matching/links solves this issue. -
Currently reconciling locked transactions is impossible without unlocking previous periods and this isnāt good. The developer suggested introducing a separate lock for reconciliation, which is unheard of before this instant and itās just a workaround not having adequate reconciliation procedures. Static matching/links solves this issue.
These problems are all symptoms of lacking bank reconciliation capabilities, namely static matching/links.
What boggles me is that the developer would rather go out of his way to introduce individual solutions to each symptom or ignore the symptom altogether (like #3) rather than addressing the underlying cause.
I donāt see any substantial technical costs or difficulties to store bank statements separately from self-contained transactions and link them together and this boggles me even more, especially when you consider that static links are the least costly fix brought up in this post.
Just as a side note ā and I am nitpicking here, I prefer the term āBank Statementsā better than āBank Importsā because it describes the object/document rather than the action. The action button inside should be labeled"Import". This is more inline with the nomenclature of Manager but thatās no big deal.
I have done enough ranting for a month to come. So I will just have to shut up and get back to reconciling my banks in excel.
Close
Itās going to be a mixture of auto matching and user matching editing / data entry.
Go to the bank import tab, drill down on the bank import. Manager will show you any bank import transactions for which a matching Manager transaction can not be found. No records shown indicates your job is now done.
Managers Matching would show the miss matched pair on orphaned transaction. More general context could be shown by showing more of the bank import and matching.
Which identical transaction was in error and what needs to be done about it will depend on what the error was not what identical bank import template was to generate Managers records.
Overall this looks better than I expected, in fact this method would be tidier and more manageable but I am still not sure this is going to do as good of a job as matching. But I see great potential for a mixed approach.
Thereās going to be lots of false matches especially with larger statements especially when thereās lots of similar amounts and lacking descriptions.
One would assume that picking a match from a filtered drop-down would be less work than editing and data entry.
This is both the best thing about your approach as well as the area where I have my biggest issues:
-
This is negative confirmation is a dangerous method to confirm the accuracy of such a critical task as bank reconciliations. A lazy accountant could just edit the bank statements such that only the total discrepancy is imported and no one is going to be the wiser; how could they, all proof is now gone. That is, up until a customer complains of being overcharged in his statement. Then you will face the wrath of that customer as well as the other customer to whom his statement needs upwards corrections. The same thing when the owner finds out that theyāve been overpaying a supplier because their payment records had some plugs to round off the bank reco. This isnāt hypothetical by any means Iāve been in many similar situations when I was using Tally which uses negative confirmation as well. The lazy accountant would most likely need to split a few bank lines and edit a single pre-existing transaction to achieve that.
-
For false matches, thereās no way to back track and edit. You will have to undo and redo.
Thatās why Iād rather have the full matching documented because 99.9% of the time the total discrepancy wouldnāt match any single figure that hasnāt been match so it would definitely show. Iād rather have that than a blank screen saying nothing is pending.
I will give you that but then again itās going to be a lot of work for the user to edit and enter all the data needed for manager to start suggesting matches. Selecting from a drop down sound a lot easier especially as you scale up.
There is a larger variability in the work flow different user follow when using Manager.
I have tried to suggest an addition to Manager which
- Requires no addition user work. Only identical user tasks are required. New links do not need to be created or maintained.
- It does not constrain valid user tasks or data entry.
The only change is adding new reporting based on data users are already importing into Manager.
Doing so involves forging the bank import prior to it being imported. The implementation I suggest has no facility to change imported bank statements in any way. They are only used as a reference for reporting, and template to generate Manager transactions
What is a false match?
The report will correlate transactions and show just what does not match. You then have very specific information on what is different between the bank records an Managers records.
The process Manager performs in the background is similar to the old paper method.
- Print out the bank statement
- Printout Managers general ledger transaction
- draw a line crossing out the easy entries in the bank and corresponding Manager entry
- Go back through the bank statements and Manager entries crossing out less specific entries.
- Eventually you have a small list of entries which canāt be matched. Sometimes a pair (mismatched date or amount), some times a duplicate or deleted entry (no matching pair).
- This is where Managers matching could get to.
Any automatic correction at this time would be dangerous. Human intervention is required to determine what is the most accurate representation of the true business financial positions, determine why the error occurred and make any corrections which are required.
No need to explain your intent @Patch. My post might seem like a negative criticism but itās not. I really liked most of your method and I just wanted to point out that it could be improved upon by keeping the matching work. In fact I kind of like it a bit better than mine.
I just need the work to be editable because in my 15+ years of experience as an auditor as well as an accountant, I have seen a lot of fishy business in reconciliation. In fact I spent 1 whole year fully hired by a client to reconcile their receivables and ended up reworking their bank reconciliations.
If you have a matching table itās easy, just lookup the statements from the system to a fresh bank download and then see if the matching is 1-to-1. You only need to examine those matches that arenāt 1-to-1 or those that match the amount of an unmatched transaction. Pretty limited scope of work.
You donāt even need to lookup the entire statements if the system keeps track of manual edits. You just examine those.
On the other hand if you donāt have a matching table, thereās absolutely no way to review or audit it without reperforming the entire exercise because you just have nothing to compare.
What I suggested here is linking instead on many-to-many matching because it better suits the structure of Manager.
I suggest that we use you method but allow for static linking and keep records of it. Maybe on the view screen you get the orphan lines only but the edit screen should allow to dig deeper in case you need to.