To speed up your reconciliation you want as much as possible to be automated. Since many everyday transactions are often repeated, we can set up some automation rules while doing the reconciliation so that we only have to touch these transactions the one time. You can use these rules either to simply pre-populate the Upload Expense form with the desired details, or you can actually have a matching expense created automatically the next time you upload a bank statement.
Using the Automate This form (descirbed below) you can create a lookup table such as this:
Rule # | Vendor Name | Payment Method | Hard-coded Details | Comments |
---|---|---|---|---|
1 | 7-Eleven store #32601 | TD Credit Card 3903 | VendorName = "Sherwood 7-Eleven', Description = 'Gas for Catalina's car', Building = 'General' | A purchase only at this particular 7-Eleven by this particular credit card will trigger this rule |
2 | 7-Eleven | TD Credit Card 3903 | VendorName = "7-Eleven', Description = 'Gas for Catalina's car', Building = 'General' | A purchase at any 7-Eleven by this particular credit card will trigger this rule |
3 | 7-Eleven store #32601 | (blank) | VendorName = "Sherwood 7-Eleven', Description = 'Gas for car', Building = 'Sherwood Condo' | A purchase at this specific 7-Eleven by anyone will trigger this rule |
4 | 7-Eleven | (blank) | VendorName = "7-Eleven', Description = 'Gas for car', Building = 'General' | A purchase at any 7-Eleven by anyone will trigger this rule |
Now when you upload a new statement, or do a new reconciliation, these rules will be applied to your bank transactions. For each transaction the system will look in this table to see if there are any full or partial matches. For example, let’s say you have the following transactions in your various bank statements. The system will grab the description as provided on the bank statement, and will then look up the rules, starting with the most specific and then working down to the more general (as shown in the first table):
Note that the method of payment takes priority over the specific store. Also note that the system just looks for the VendorName in the rules to be somewhere in the bank transaction description. Thus the transaction ‘ESSO 7-Eleven store #32601’ will trigger rule 1 because the description contains ‘7-Eleven store #32601′.
Once a match is made, the rules are then applied to the transaction, setting up the expense as specified. Since we know that the 7-Eleven 32601 is in Sherwood, we can put that in the description. Since we know that Catalina carries that credit card, we can assign the expense to her car. And since she looks after the Sherwood Condo only, and not anything else, we can also assign this expense to that building.
And so you can fine-tune our automation as much as you like.
How to Set up the Rules
First, do your reconciliation in the normal manner. When you get to the No AutoMatch stage you will have a bunch of bank transaction on the left with no matching expenses on the right.
Click on the icon to create the new expense, entering the details as normal. When you are happy with your entry, click on the Automate This link at the bottom, to the left of the green button.
At the top of the beige form is the line that looks like this:
What is written in this box is what will go in the VendorName of the lookup table. So this will match that specific store only. If we want to make this just a general rule for all 7-Eleven stores, then we should remove some words, eg
Which will now match any 7-Eleven (including, or course, those without a gas bar).
Then we work down the checkboxes to define which elements are to be hard-coded, and which are to be dynamic.
Note:
- You cannot edit those boxes directly. If you want to change them, scroll back up to the expense form and change them there.
- If you check the box then those details will be used (inserted) the next time you have a matching transaction. If you don’t, then the detais will be grabbed from the bank statement, as they are here for the Description.
- If you check the payment method box, then you are forcing the automation to match the payment method (e.g. rules 2 and 4 above). If it is unchecked, then the payment method will be grabbed from the bank statement.
- If you do not select a building, then ‘General Company Expense’ will be used.
- The Notes are, like the rest, imported from the new expense form, if you enter any.
As it stands at the moment, when you next go to reconcile and you find a 7-Eleven transaction waiting there, when you click the button you will find the form already filled in with the details provided in your rules.
There is another option. The next time you upload a bank statement the system will also check these rules. If it finds a matching rule that also has the next button checked, it will automatically create a new expense with all those details:
This will then be waiting for you when you do an AutoMatch Expenses.
Note: You MUST select a Category if you want to auto create new expenses, as we cannot create an expense without one. If the Category might change each time, then use the automation to pre-populate the form and then chose the right category after you click the button.
At any time, while you are reconciling, you can edit the automation rule that was used to pre-populate the expense you are about the upload. When you save it, it will update the system and the new rule will be used henceforth. If you want to delete the rule completely, just check the delete button and it will be removed. If you alter the rule so that it now matches an existing rule, the old one will be updated. For example if you edit the rule to be more or less specific than it was before, it might now match a previously created rule, which will now get updated.
Finally, if you are totally happy with the rule and the expenses it has been automatically creating, you can tell the system to also reconcile them automatically. You will never see them on your reconciliation page, but you can find them on the List Reconciled Transactions page when you can un-reconcile them. Then you can go back to the reconciliation and change the rule if necessary.