Ebbe's HTML Bookkeeping Spreadsheet Detail Page
Using spreadsheets for bookkeeping implies both pros and cons:
- Pro: Easy overview, easy to correct compared to both bookkeeping computer programs and bookkeeping on paper
- Con: But the easiness in correction means that it is easy to correct what should not have been corrected – cheating those who should learn from your bookkeeping
This is a workbook with three spreadsheets. In general there are no formulas in the spreadsheet except in very few cells. Description of the three sheets:
- The data is entered in the Bookkeeping spreadsheet. There are no formulas except in cell G1.
- Later, it is copied to the Sorting spreadsheet, sorted and counted.
- The content of the Account Plan spreadsheet is only used as a template for the registration in the other spreadsheets. Further, the third sheet contains this description.
An overview of the spreadsheet columns
Excel proposes text for new entries like the former entries in the same column – provided you fill in all of the rows.
- Account is entered according to the Account Plan. Formerly chosen accounts may be pointed out by pressing Alt + arrow down. You may design the account names with letters instead of numbers to exploit the feature that Excel proposes text for new entries like the former entries in the same column.
- Entry no. entered. You only have to enter the two first figures, then you can fill in the rest by drawing the black square as shown in the figure below.
- Dates are easily entered with a hyphen: "1-2" + Enter yields first of February this year (or second of January, according to your language settings). If you want to enter a date from another year you just add that as well, "1-2-8" results in first of February 2008, etc. You may change the formatting in Format / Cells / Numbers / Dates, or you may create your own in Format / Cells / Numbers / Custom.
- Text added after your own wish. Not all entries need a text.
- Amount: The sum of all amounts in column E is shown in cell G1. If you select more than one cell, Excel will show the sum in the gray bottom status bar.
Bookkeeping Rules in the Bookkeeping Sheet
In principle bookkeeping is to show transition of amounts between various states. Some times the money is on the bank account, and some times the money is spent for various purposes. In general, the sum of stocks and spending is constant. The calculation will allways be in balance if the stocks is counting positively at the beginning and negatively at the end of a period.
Copying to the Sorting Sheet
- 1. Start by removing subtotals in the Sorting sheet, if any. Then delete the content of the Sorting Sheet, if any. The value of cell G1 should be zero. The active cell should be A1.
- 2. Check that the value of cell G1 in the Bookkeeping Sheet is zero as well. Select all figures in columns A-E and copy.
- 3. Insert into the Sorting sheet cell A1. Check the value of cell G1 to be zero once again.
- 4. Sort the content of columns A-E by choosing Data / Sorting / Sort by Accounts increasing, 2. Sort by Dates increasing
- 5. If your Account Plan is built up with result accounts in the beginning and stock accounts in the end, you may now block up all of the result amounts and thereby see the result of the period.
- 6. Select Data / Subtotals / Sum for column E
Export to an External Spreadsheet
The Bookkeeping Sheet and the Sorting Sheet are good for computing correctly, but the design is rather rough. If you want to use the figures for a wider audience then move the content to an external spreadsheet while you get rid of decimals, etc.
Example and Default Checks
In the Bookkeeping Sheet is shown a simple example using the general account plan. I propose that you print the example, delete it, and enter the data again while you study the sum formula, etc.
Copy your data to the Sorting sheet and be sure that you make the three checks described below. Remember that you are not allowed to correct any figures or accounts in this sheet. Make the correction in the Bookkeeping sheet and copy once again!
1. Result Check
Before adding subtotals block up all parts of the result, i.e. not the financial accounts. In the bottom line is shown the sum, that is the result of the period.
2. Check the Financial Accounts
For every bank account block up all lines except the first and the last. These lines are the internal ones, and the sum must be equal to zero.
3. Check the Internal Account
The internal account must always be equal to zero, or there may be errors in your result.