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 transactions sheet. Reconciling your bank statements helps flag checks that haven’t been cashed (yet), unexpected charges, and human & machine errors.
Tiller Labs has developed two 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.)
Adding the Statements Template
- Open the Tiller Labs add-on.
- Add the Statements solution to your spreadsheet.
Using the Statements Template
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.
- 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.
Statement Details Template
Adding the Statement Details Template
- Open the Tiller Labs add-on.
- 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 Labs add-on will add it automatically.
Using the Statement Details Template
- Use the steps above to create statements in the Statements template and to flag them in the the Transactions sheet.
- Switch to the Statement Details template.
- Use the
Accountdropdown to choose an account for the statement detail reporting.
- Use the
Showdropdown 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
Statementdropdown. (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
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.