One key to keeping your finances in sync is verifying that all the transactions that appear on your bank statement are also available in your Tiller Money transactions sheet. Reconciling your bank statements helps flag checks that haven’t been cashed (yet), unexpected charges, and human & machine errors.
There are two community-built templates that assist in bank-statement reconciliation:
Statements: Use Statements to log, track and compare statements to grouped transactions in your Transactions sheet.
Statement Details: Use Statement Details to review a single statement and its associated grouped transactions in line-item detail. (Statement Details requires the Statements template.)
Open the add-on and choose "Bank Statements” from the tags dropdown on the Explore tab
Click on Statement Details or Statements
Click “Add to spreadsheet”
Using the Statements sheet
Identifying Transactions within a Statement Period
In the Statements sheet, enter a statement end date into column D. (You can find this on your actual bank statement.)
Select the account to which the statement corresponds from the dropdown in Column E.
The Statements sheet will automatically generate a statement name (Column A) that consists of the statement end date plus the last four digits of the account that corresponds to the statement. You will apply the statement name to transactions in the Transactions sheet.
Enter the starting balance from your bank statement.
Enter the total amount of deposits from your bank statement.
Enter the total amount of withdrawals from your bank statement.
The ending balance will be calculated for you by the template.
Switch to your Transactions sheet.
Apply the statement name (it should pre-populate into the dropdown) to all transactions for that account that occurred during the statement period in the Statement column. Keep in mind that Tiller may bring in transactions that will be dated outside the statement start or end date that should be included in the statement. Review your statement against the transaction description and amounts.
Check the Statements sheet to verify that the Deposits and Withdrawals under Tiller Transactions show a green “matched” status. If they don’t you know there is an error somewhere or a transaction for the statement hasn’t been pulled into Tiller yet.
Additional Notes
Statement Names. So long as statement names are unique and consistent with those used in your Transactions sheet, you can choose any format that works for you. Two dropdowns in the Statements template provide some options for your statement naming convention, but if you’d like to use a different statement name feel free to clear the statement name formula and manually enter your own naming convention.
Add the Statement Details solution to your spreadsheet.
Note: The Statements template is required for Statement Details to function. If the Statements template has not already been added to your spreadsheet, the Tiller Community Solutions add-on will add it automatically.
Using the Statement Details sheet
Use the steps above to create statements in the Statements sheet and to flag them in the the Transactions sheet.
Switch to the Statement Details sheet
Use the Account dropdown to choose an account for the statement detail reporting.
Use the Show dropdown to choose either “Statement Transactions” or “Unreconciled Transactions” for reporting. Choosing “Statement Transactions” will allow you to select a statement name and view all transactions flagged within that statement. Choosing “Unreconciled Transactions” will show all transactions within the selected Account that have not been assigned to a statement.
If you chose “Statement Transactions” in the Show dropdown, choose a statement name in the Statement dropdown. (Sometimes it takes a few seconds for the list of statements to populate. Be patient.) If you chose “Unreconciled Transactions”, you do not need to make a selection in the Statement dropdown.
Once you have performed these steps, the relevant transactions will appear in the table at bottom.
Note that if you are viewing a Liability account statement, charges will be listed as positive values that increase the amount due. This will be opposite how they appear in the Transactions sheet.
I just found this add-on today and love it - especially the Running Balance numbers. Is there a way for the “Unreconciled Transactions” to reference the last Statement Ending Balance to reflect an accurate Running Balance in the selected account?
Row 9 shows the Statement name and record count for the last Reconciled Statement that I looked at, even when I am looking at the Unreconciled Transactions, so I am hoping that there is an easy way to grab the Ending Balance number from there and make the Running Balance number reflect the actual balance in the account.
I have upcoming transactions manually entered that will eventually reconcile with downloaded transactions but knowing my cash balance on any given date is important to me.
I am very new to Tiller in the last few days - giving up on Quicken as we move into 2020, so there a lot of features I have yet to explore and learn. There may already be something that does this and I just haven’t found it yet. Thank you for your help.
Yes @Blake, the Statements and Statement Details sheets are designed to work AFTER you get your statement.
@martha.rudkin : We liked your suggestion to add the last statements balance to the running total when viewing Unreconciled Transactions. I’ve updated the sheet to include this feature. It’s available now in the Tiller Labs add-on. If you select Manage Solutions, you will be able to update the Statement Details sheet to the new version 1.02.
I’ve been using the Statements Template for a little over a year, without any problems. Today I am having difficulties after adding some new lines. The Statements Template is not autogenerating the Name, Ending Balance, or last two Tiller Transactions columns. I tried pasting formats from lines above, which seems to work on the Statements Template–but when I go to the Transactions page the Statement Name is not showing as an option in the dropdown of the Statements column. Therefore, I’m not able to reconcile… What can I do to fix this? Thanks!
It sounds like maybe the data validation rules in the Transactions sheet do not include the new rows in the Statements sheet. Try selecting the entire Statement column in the Transactions sheet (row 2 down) clicking Data / Data Validation in the menu and then where it says List from range ensuring that all rows in the Statements sheet are included in the range.
Started experimenting with Statements this weekend and it’s having difficulty with split transactions. Particularly where there is income and expenses. For example, if you were to split your work salary or any income situation where there are deductions/taxes. Let’s say you have a net deposit of $30, but you split that into +$35 income, -$5 taxes. The total of the transaction is still a $30 deposit and my reports would show the total salary $35 and taxes $5 correctly. Yet Statements doesn’t produce a Match in Net Deposits and Net Withdraws. They are both -$5. @randy@heather
BTW, Statements is super easy to use! It fills a nice gap between transactions and balances. I’m going to be using AutoCat more with the advanced features to categorize and enter notes, then I’ll use statements to quickly reconcile. Overall it’ll take me less time!
That’s a good catch, @richard. Both the Statements templates and the splitter workflow have a long history with Tiller. Statements was launched nearly 3 years ago as part of the original small business tools release. And I believe an early version of the splitter predates Statements; it was originally deployed as a standalone add-on.
The recent refreshes & integration of both of these tools into the Tiller Labs add-on has brought these old tools into closer contact than any time in their history.
To be honest with you, I hadn’t thought through how they could work together when splits are mixed across transactions types. Do you envision a way that the two concepts can work together in your scenario?
Randy
P.S. Glad to hear you are liking (most of ) the new tools.
Fascinating history @randy. Yes I envision them working together because the statement’s transactions do reconcile to zero if the math is updated. I suppose each line is reviewed individually rather then based on something like the transactionID. With each row, presumably the sum of all positive transactions and the sum of negative transactions add up to the debits/credits on the statement, but since there’s been a split it doesn’t add up that way. Can you group by transactionID to know the amount of that transactions, then sum? Seems like a sub total of the splits to create a parent is the key to them working together. Well, good luck let me know how I can help.
@Richard and @randy, I noticed this same problem when I started using the statements sheet and breaking my paychecks into more details. The workaround I use (definitely a workaround!) is to leave the amount deposited into my account ($30, in Richard’s example) alone, then “split” it into $5 in “paycheck deductions” (income category) and $5 in “taxes”. Essentially the “paycheck deductions” category is the total of all the individual amounts that are withheld from my paycheck (taxes, health insurance, 401k, etc). Then in the statements column I clear out the data validation behind all the paycheck splits and fill them in with generic text like “paycheck split” so that it doesn’t look like a blank cell that I forgot to tie to a statement. Then all the statements calculator sees is the true deposit amount that matches what it’s looking for.
Like I said, definitely a workaround, but it gets the job done for now! I think Richard’s suggestion of subtotaling the splits for comparing to the statement amounts is spot on - if it can be implemented.
) the new tools.
let me know how I can help.