Embedded below is the quick tour video of the accounting spreadsheet which is available for download.
This is the first video in a three part series and is a short video which provides a high level overview of the spreadsheet so that you can assess whether it might be useful for your particular needs.
The second video in the series provides an overview of accounting principles relevant to operation of the spreadsheet, which is basically double entry accounting in ledgers, and then preparation of an associated income statement and balance sheet. If you are familiar with double entry accounting and the accounting equation you might be able to skip video #2.
The third and final video in the series is a long one, over an hour long, and goes through use of the spreadsheet in detail, including running through the entry of various types of transactions into the spreadsheet.
Below the embedded video is a rough summary of some of the key points made in the overview video.
KEY POINTS FROM THE QUICK OVERVIEW VIDEO
Goal and purpose of the accounting spreadsheet
The goal and purpose of the accounting spreadsheet is to record accounting transactions in a set of ledgers applying double entry accounting principles such that debits match credits, and then to produce an income statement, and a balance sheet, which, according to the accounting equation, must balance. These type of records should be kept by most small businesses, especially because the resulting information is needed for income tax reporting purposes, and having well organized accounting records is important in the event of a tax audit.
This spreadsheet is designed for small businesses who wish to use double entry accounting to ensure their books properly balance, but using Excel rather than commercially available accounting software.
This spreadsheet is not intended for large businesses which have hundreds of accounting transactions each day. Such businesses will typically have very sophisticated accounting software, perhaps incorporating invoicing and cheque writing capabilities, and have full time bookkeepers to record daily transactions in real time as they happen. This spreadsheet on the other hand, is intended for start up, or small, businesses that have perhaps up to 50 transactions per month, and whose owners do the accounting themselves, perhaps at month end using the bank statements as a partial guide to what transactions have occurred during the month.
Professionals who operate corporations which invoice a small number of clients e.g. the law firm they work through, and perhaps some additional clients who they bill directly, may find the spreadsheet useful to keep track of income, expenses, statutory remittances, paid dividends, etc., and indeed this is the purpose for which the spreadsheet was originally built.
Quick summary of the 4 tabs of the spreadsheet
The spreadsheet has 4 tabs:
Ledgers tab – this is a list of the various ledger accounts that individual transactions are entered into. The ledgers form the foundation of the accounting records as a whole, and carefully selecting a set of ledger accounts is an important first step when setting up the books of a business. Data from the ledgers is later exported to the income statement and the balance sheet.
Ledger index tab – this lists, by way of references to the ledgers tab, all of the ledgers that are contained on the ledgers tab. This list of ledgers is a key part of operation of the spreadsheet, and so must be kept up to date.
Income statement tab – this is a standard income statement which brings in income and expense account balances from the ledgers tab, and calculates the net profit. The accounts listed in the income statement are entered by way of cell references to the ledgers tab.
Balance sheet tab – this is a standard balance sheet which checks that the accounting equation balances, including by considering the net profit figure calculated on the income statement tab. The accounts listed in the balance sheet are entered by way of cell references to the ledgers tab.
Ledgers tab in more detail
The ledgers tab is the sheet where transaction data is entered in the first instance. This spreadsheet does not use journals to initially record transactions, but rather transactions are entered directly into the ledgers.
The spreadsheet available for download has some predefined ledger accounts, but you can edit and add to what is there as needed.
The overview video runs through the entry of a sample transaction to demonstrate operation of the spreadsheet.
A fundamental premise of the spreadsheet is: balancing in baby steps, based on the idea that it is easier to work sequentially through transactions one by one, in baby steps, balancing each one as you go, rather than entering a vast amount of data and then struggling to reconcile a complicated set of accounts involving a large number of new transactions, any one of which could contain the errors preventing the books from balancing.
The spreadsheet requires users to let go of the idea that income statements and balance sheets are only generated at year end, or month end, but encourages users to generate an income statement and balance sheet after entering each transaction, or perhaps each days’ worth of transactions. Checking entered transactions can be done lightening fast with the built in macros, and this allows one to efficiently work in baby steps and avoid struggling with a big tangled web of transactions containing difficult to trace errors.
It is intended that a fresh and empty copy of the spreadsheet will be started at the beginning of each accounting year, and for accounts that have run-on balances that carry over to the next year the end balances for the accounts from the end of the previous year will be entered into the new spreadsheet as opening account balances. End balances are typically carried over to the next year for asset, liability, and equity accounts.
Advantages of the spreadsheet
This spreadsheet project grew out of a need to keep accounting records when I (Michael Dew) had when I started running a small corporation and I did not want to buy a commercial accounting program, no so much because of the cost, but because the black box aspect of commercial packages does not appeal to me. I do not like the idea of using software that may be doing calculations behind the scenes that I do not fully understand, but rather I prefer the transparency of an Excel spreadsheet. Then when I started building the spreadsheet it turned out to be challenging to make it as simple as possible, but also powerful to facilitate quick and convenient recording of transactions, and so it turned into a bit of a challenge and took quite a lot of programming, so I thought I would make it available online.
Conclusion
There are quite a few nuances to the operation of the spreadsheet, and some extra functionality which is not covered in the quick tour video, and so if you plan to use this spreadsheet it is strongly recommended that you watch video #3 which goes through use of the spreadsheet in detail, and if you are not very familiar with double entry accounting principles and the accounting equation you should also watch video #2 which explains some of the accounting principles underlying this spreadsheet.