Difference between revisions of "Financial Cluster:Reconciling ledger"

Latest revision as of 16:03, 15 March 2023

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 imported transaction in the 'Checking Account' account in GnuCash
   • be sure to select the QIF format
   • do this for both the 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 finding a matching payee and requiring you to manually match the payee/account. (This is specifically tedious for Square transactions.) 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 that has been updated with the output from the Google Sheet
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

Special Orders who paid for special orders is now tracked in a spreadsheet separately from the gnucash account. The overhead of managing special order tracking was too high in gnucash and is much easier for the tracking to be done now by multiple people. Special Orders are now tracked as sales in their own category in gnucash and now show correctly in revenues and sales reports.

Reimbursement splits will be necessary whenever someone makes a purchase for the bike shop with their own money. In order to record these purchases in GnuCash and have the accounts balance out, you'll need to enter two transactions:

1. The purchase itself
   • Open the relevant account in GnuCash; for example, you would select the 'Shop Expenses, Tools, Tools - sales tax paid' account for any tool purchases

Non-bank accounts

Other accounts that must be synced in Gnucash include Square and PayPal.

Square

Square does not provide a QIF that can be easily imported into Gnucash. Add a quarterly manual entry for sales in GnuCash

PayPal

PayPal provides a QIF that can be easily imported in Gnucash.

Exporting transactions

(This text is only a placeholder)

1. Login to Paypal
2. Download the account activity since the date of the last imported transaction in the 'Paypal' account in GnuCash
   • Reports > Activity Download
   • be sure to select the QIF format

Importing transactions