Bookkeeping with a spreadsheet is popular with many small businesses, for several reasons. Primarily, there is effectively no cost for software. While the most popular spreadsheet (Microsoft Excel) is part of an expensive software package, many people have a computer that included a copy pre-installed, plus there are free spreadsheets available such as LibreOffice (the new Open Office), and the online Google Docs.
They also offer unlimited flexibility, and there is no vendor lock in.
That flexibility comes with dangers, however. It is up to the operator to make sure the totals are calculated correctly. Spreadsheets are easy to change, and while critical calculations can be “locked”, it is necessary to make sure that new rows or columns are all being properly accounted for.
There are many templates available that have common calculations already in place. You can download a template, open it with your spreadsheet program, and save a new copy for each month or each accounting period. Some templates are intended for a simple list of transactions, and some attempt to do true double entry accounting. Some use only a single sheet, and others have multiple sheets with different pages for entering sales or purchases. Most have only limited reports, if any.
But any form of organized records will make it easier for your accountant or tax preparer at the end of the year, which will save you money. If you use a single entry process, your accountant will probably transfer your numbers into a traditional accounting program to provide you with any meaningful reports. A double entry process will provide better data for producing reports directly from the spreadsheet, although your accountant may still prefer to transfer the numbers to their own favorite program.
We have created a true double entry process with a single page spreadsheet that you can download for free in Excel (xls) or Open Document (ods) format.
The double entry process requires that you specify both the incoming and outgoing parts of every transaction. If you buy a broom, the incoming part is “cleaning supply”, and the outgoing part is either “cash”, or “credit card”. You would have a positive number in the cleaning supply column, and a negative number in the cash or credit card column. The sum of the positive and negative numbers should always be zero.
When entering sales, the incoming part that gets the positive number is cash, and possibly your bank accounts if you accept credit cards. The outgoing part that gets the negative numbers are your sales, or revenue accounts, and your tax liability account. Although it may sound confusing, sales are recorded as negative numbers.
Each line of the sheet represents a single receipt, or a single group of sales. You could enter the sales daily, weekly, or monthly, if your cash register or POS provides you with the weekly or monthly totals. Each line must add up to zero to have the books in balance.
Opening balances can be entered for each account, so current bank balances and vendor debts can be recorded. Several of the accounts that are necessary for balancing the opening line are not included in the account columns, so there are a few generic accounts included to cover anything missing. When accounting for details like owner’s equity, depreciation, and amortization, you will probably need assistance from your accountant to get the opening balances “in balance” (so the sum of the line is zero).
At the end of each period, the closing balances from each column can be copied into the opening balances of a new spreadsheet. When you copy and paste those numbers into the new spreadsheet, make sure you only paste the numbers, and not the formulas.