Now that you understand some basic accounting terminology, let’s talk about the next step in how to do your own bookkeeping. Because you want to do your own bookkeeping, we are also assuming you want to save money on software. This post explains how to set up spreadsheets, which are readily accessible via Google Sheets or Microsoft Excel, in order to do your own bookkeeping.
Setting up Your Spreadsheets
The first worksheet you will create is called a synoptic. This worksheet is where you will record your daily operating activities. On the sheet you’ll need to make sure you have equal debits and credits for each row. Each transaction should remain on its own row.
If you are familiar with spreadsheets, you may be able to set up your own document. If not, here’s a Google Sheet you can follow to set up a template, or make a copy and use this template yourself. The first tab provides instructions, which we will also cover briefly in this post. We recommend reading this post as well as following the instructions in the spreadsheet itself.
On the tab that has “synoptic” in the title, you need to make sure you enter a debit and a credit on each line. For example, if you enter a transaction for $10 of bank charges at the end of the month, you would show a credit of $10 under the bank account column, and a debit of $10 under the bank charges account column. The column that says, “Check (Zero Sum)” highlighted in yellow is checking to make sure each line has equal debits and credits and therefore adds to zero.
In the row that says, “Ending balance,” you will find the total for each column. This is also highlighted in yellow. These totals are then transferred to the income and expense section via a formula so you can see your net income for the month. These numbers are then transferred to the tab that says, “Financial Statements.”
Recording Transactions
The only thing you should need to enter each month are the debits and credits to record your income and expenses. The other cells are calculated using formulas. To check if a cell uses a formula (and to make sure you don’t accidentally delete it), you can click in the cell. The formula will show in the “fx” bar at the top of the spreadsheet.
The instructions also tell you what to do if you need to add rows and/or columns. Adding rows or columns can sometimes change formulas, so you will want to check that the formula includes the new row or column that you’ve added. Otherwise, the formula won’t include all amounts in its calculation. You may need to change column headings to match your expenses or add columns if you have more account categories than noted on the spreadsheet.
When you start a new month, you will need to create a copy of the synoptic sheet. Name this the current month you are working on. Clear all the cells where you entered data, and you are ready to begin. Remember, the “undo” button is your friend. If you accidentally delete something you shouldn’t have, just hit undo and try again!
Separating GST/HST
If your business is registered for GST or HST, you will need to separate the GST/HST when recording your transactions. This applies to both revenue and expense transactions. Following is an example of how to record revenue with GST collected.
Let’s say you had a sale of $500 with $25 GST collected on top of that. You received $525 from the customer. In the bank or PayPal column (whichever applies to you) you will record a debit of $525. You will record $25 as a credit to the GST collected column, and $500 as a credit to products or services, whichever applies to you. To enter a credit in a cell, you must enter a minus sign in front of the number.
To record an expense where you paid GST, you would record the transaction in a similar way. Let’s say you paid $105 for office supplies which included $5 of GST. Record a credit to the bank account of $105. In the GST paid column, record a $5 debit and then record a $100 debit in the office supplies account. To enter a debit, simply enter the number.
Tracking What You Owe and Who Owes You
There are two tabs on the spreadsheet we haven’t talked about yet–the Accounts Receivable Sub Ledger and the Accounts Payable Sub Ledger. Here is where you can keep track of the amounts customers owe you (accounts receivable) and how much you owe your suppliers (accounts payable).
Amounts entered on the sub ledger tabs will not automatically transfer to your synoptic so when you make or receive a payment, you will need to update the synoptic tab as well as the applicable accounts receivable or accounts payable sub ledger tabs.
In Closing
If you read about debits and credits above and wondered what in the world we were talking about, please read “How to Do Your Own Bookkeeping – Part 1,” where we discuss some basic accounting terminology that’s helpful to know before you start recording your transactions.
In our next “How to Do Your Own Bookkeeping” post, we dig into some more advanced topics. We’ll cover customizing your spreadsheets and analyzing your financial statements. As always, if you have any questions, you can get in touch here.
