Financial Cluster:Reconciling ledger

Reconciling the ledger is the first step towards accurate accounting.

Bank accounts

Chase allows you to export account transactions in the QIF file format, which can then be imported into GnuCash, helping you to more quickly reconcile the ledger.

Exporting transactions

1. Login to Chase
2. Download the account activity since the date of the last transaction in the 'Checking Account' account in GnuCash
   • be sure to select the QIF format
   • do this for both the Chase Checking and Savings accounts
3. Download any check images, including cut checks and those in a deposit

Importing transactions

The QIF file format contains the details of an account's transactions, including the date, payee, amount, etc. Each transaction in the file is demarcated by a '^' and the payee of each transaction is named on the line prefixed with a 'P' (for example, 'PQuality Bicycle Produ...').

While importing the QIF file into GnuCash, you'll be prompted to match the payees found in the QIF to a corresponding account in GnuCash. If GnuCash recognizes a payee that has previously been matched to an account, it will automatically match that payee/account. However, payee information in the QIF file often includes superfluous information (such as 'PQUALITY BICYCLE PRODU 952-941-93'), preventing GnuCash from automatically matching the payee and requiring you to manually match the payee/account. Therefore, you can reduce the amount of time spent matching the payees/accounts in GnuCash by performing a quick find/replace in the QIF file before importing it into GnuCash using this Google Sheet.

Savings account

1. Open GnuCash
2. Select File -> Import -> Import QIF
3. Select the QIF download for the Savings account
4. Start the import
   • Set the default QIF account name to 'Savings Account'
   • Match the 'Savings Account' name from above to the GnuCash account name
   • Match payees/memos to GnuCash accounts
     • you should only have to match Interest Payment
   • Perform the import
5. Verify that the balance in GnuCash matches the balance (on that date) in the Savings account

Checking account

1. Open GnuCash
2. Select File -> Import -> Import QIF
3. Select the QIF download for the Checking account
4. Start the import
   • Set the default QIF account name to 'Checking Account'
   • Match the 'Checking Account' name from above to the GnuCash account name
   • Match payees/memos to GnuCash accounts
   • Perform the import
5. Verify that the balance in GnuCash matches the balance (on that date) in the Checking account
   • If the balances don't match, this indicates a missing or erroneous transaction in GnuCash
   • First, check that the balance of the last transaction in GnuCash before the import was performed matches the balance in the Checking account on the date of that last transaction
   • Then, compare the balance after the last transaction on a given date with the Checking account balance on that same date, starting at the beginning of the previous import
     • (instead of comparing on a day by day basis, it can help to check in two week increments, working your way backwards once a discrepancy is found)

Reconciling transactions

GnuCash is a double entry bookkeeping system, meaning every financial transaction has equal and opposite effects in at least two different accounts. Therefore, while the imported transactions above will accurately reflect the balances of the Savings and Checking accounts, reconciling the ledger requires that you appropriately debit or credit other accounts, and may require that you split some transactions.

Most of the imported transactions for which the payee was matched with a pre-existing account will be automatically offset by a debit or credit to the appropriate account. Take a moment to verify the first few imported transactions before reviewing the remaining transactions to be split or updated.

Transactions to update

Because GnuCash is a double entry system, updating transactions is a two step process: first, imported transactions must be 'split' so that funds are attributed to the appropriate account; second, some of the 'equal and opposite' accounts must be reconciled. It is only some of the accounts because some of them (like 'Tools', for example) will only ever increase, while others (like 'Special Orders' payments), will need to credit the appropriate account (and will need to be split even further).

Split

QBP transactions will need to be split in order to debit the appropriate accounts. These include but are not limited to Tools, Consumables, Inventory and Special Orders.

1. Collect the 'baskets' for each QBP order from the Inventory Manager.
2. Expand the QBP transactions by clicking in that row
3. Delete all splits except the 'Current Assets:Checking Account' row
   • This should leave you with a new row without an Account, showing only an amount in the Increase column
4. For each basket, enter the name of the basket in the Memo field (for example, 'Tools'), select the appropriate Account and enter the amount in the Increase column
   • Entering a previously used name in the Memo field should result in the appropriate Account being selected automatically (for example, typing 'Tools' will result in the 'Shop Expenses:Tools:Tools - untaxed' account being selected)
5. When all baskets have been added, there should be no remaining balance
   • If a balance remains, either the individual basket amounts were entered incorrectly, or additional charges were incurred that weren't reflected in a basket (for example, shipping charges)

Deposit transactions will also need to be split to account for any checks included in the deposit.

1. Refer to the downloaded images from the deposit or refer to the banking site
2. Expand the Deposit transactions by clicking in that row
3. Delete the duplicate 'Current Assets:Checking Account' row showing an amount in the Decrease column
   • This should leave you with a new row without an Account, showing only an amount in the Increase column
4. For each check, enter 'Check - ' and the name of the payer (for example, 'Check - Scott Marinoff'), select the 'Assets:Current Assets:Undeposited Funds' Account and enter the amount in the Decrease column
5. If it was an all cash deposit, or when all checks have been added, attribute the remaining amount to cash (by entering 'Cash' in the Memo field) and the same 'Undeposited Funds' Account

Reconcile

Cost of Special Order splits will appear in QBP transactions. These splits will debit a person's 'Outstanding Cost of Special Orders' account; those accounts will then be credited when the person submits payment.

Since payment of a special order is the cost of goods sold (COGS) plus 15%, the payment will need to be split - otherwise, there would be an imbalance in the accounts. Therefore, the payment is split to credit the person's 'Special Orders' account for the amount of the balance and to credit the 'Sales Tax Due' Liabilities account; any remaining balance is credited to the 'Special Orders' Income account.

1. Open the Undeposited Funds account