Written by Stephen Beard, Managing Director of Plyo Bookkeeping, a Vancouver-based bookkeeping firm.

Introduction

So you’ve started a business, and it’s time to get your bookkeeping in order. There are many different accounting software to choose from, but one that people tend to forget about is Excel. You might be surprised to hear that you can use Microsoft Excel for bookkeeping, but if your business is relatively simple then it can be a good free option to use.

I’ll get into the details below, but the too long didn’t read version is that a lot of very small businesses overestimate how complicated their bookkeeping requirements are, and they could easily manage it themselves using Excel. As soon as your business gets larger or more complex, Excel has some major drawbacks – but for sole-proprietors and contractors it can do just fine.  

What Actually are ‘Books’ and How to Prepare them in Excel

Cash Basis

‘Books’ is a shorthand way of referring to your bookkeeping records. Your books could include different things, but I’ll start by explaining the most basic approach to preparing them, called a cash basis. You simply take all the transactions from your business bank and credit card statements, and you assign each transaction to a category (also called an account or a ledger). All of your sales would be categorized as ‘Sales’, your purchases of inventory would be categorized as ‘Cost of Goods Sold’, your payments to contractors would be booked to the expense account ‘Contractors’. It’s really that simple, almost.

Adding GST/HST and PST

I say almost, because even most small businesses are likely to be registered for GST/HST and/or PST (if your province has a PST tax). So when you see deposits on your bank statements relating to sales, most of it will be categorized as ‘Sales’, but some of it also needs to be categorised as ‘GST/HST Payable’, which represents the sales tax you owe to the federal government.

The same applies to your expense. If you’re registered for GST/HST then you can reclaim any GST/HST you pay on allowable business expenses. This means you also have to categorize part of your expense payments as ‘GST/HST Payable’.

You’re probably starting to think that this sounds too complicated for excel, but it’s really not. All you need to do is export your bank and credit card statements to an excel document. Then add a column for ‘Category’, a column for ‘GST/HST’ and if you’re registered for PST you’ll also need to include a column for this.

Keep it Consistent

You’ve just got to make sure that you use the same wording for the GST/HST and PST tax rates. Creating drop down menus in excel will ensure consistency. Likewise for categories, I’d advise creating a dropdown menu, and trying to limit the number of categories to no more than 15.

By doing the above you’ve categorized all of the raw information needed to create accounts. You could add formula to your excel sheet that would automatically calculate the total amounts in each category, including the GST/HST and PST categories – but if you’re a small business you don’t actually need to do this. If your only reason for doing bookkeeping is to be able to file your tax returns at year end, then you can present your Excel document to your accountant at year end, and they can quickly convert it into the required format using a pivot table and some other excel formula (something an experienced accountant should be able to do very quickly).

Excel is Amazing – Why Would I Need QuickBooks

So I just spent a long time explaining how easy to use excel is, why would anyone want to pay for QuickBooks? Well excel can work well for small and simple business, but that’s the only time you should be using it for bookkeeping.

To start with, it’s way to easy to delete a cell or include a typo, and suddenly your formula aren’t working and you’ll be spending the next hour trying to fix it. Unless you have both advanced excel and accounting knowledge, then there isn’t an easy way of knowing if there is an error in your worksheet, and things can get very wrong very quickly.

It also doesn’t allow you to easily raise sales invoices, enter supplier bills that aren’t paid or prepare GST/HST and PST returns. You also can’t easily generate financial reports, making it harder to stay on top of your business performance.

QuickBooks – The Good, The Bad and The Ugly

The Good

QuickBooks is the most powerful accounting software aimed at small businesses. You can raise sales invoices and send them directly to clients, enter and track your supplier bills and automatically import of all your bank transactions using their live bank feeds.

It’s got a lot of powerful automation features to speed you up, loads of ready-made reports and the ability to integrate with hundreds of other software. The app ecosystem around QuickBooks can be extremely useful, as it allows you to do things like automatically import sales invoices from your online store, or to track inventory levels in your accounting system based on what you’ve sold.

The Bad

QuickBooks charges a monthly subscription fee, with the QuickBooks Essentials level package costing $54 at the time of writing. For smaller or newer businesses this can be a consideration.

The Ugly

QuickBooks online is not user friendly. It’s a software product that’s evolved over time, meaning there are numerous ways to accomplish the same task, which can be confusing to even experienced users.

The software uses a substantial amount of accounting jargon, adding to the challenge of finding the right tool or process. The software does provide excellent training videos, which I’d strongly recommend to anyone looking to use QuickBooks Online to manage their own bookkeeping.

Conclusion

For small business owners or startups, just using Excel for bookkeeping is a forgotten option. If you don’t have the budget to hire a professional bookkeeper, then Excel may be the easiest option for preparing your books for your annual tax returns. However, as soon as things get more complicated or larger in scale, then a professional accounting software such as QuickBooks Online becomes essential.