www.ebbemunk.dkarrowEbbe's Bookkeeping Detail Page

Ebbe's HTML Bookkeeping Spreadsheet Detail Page

Download (220 kB):  

ebbes_bookkeping_spreadsheet.xls

Using spreadsheets for bookkeeping implies both pros and cons:

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:

  1. The data is entered in the Bookkeeping spreadsheet. There are no formulas except in cell G1.
  2. Later, it is copied to the Sorting spreadsheet, sorted and counted.
  3. 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

A B C D E
Account Entry no. Date Text Amount

Excel proposes text for new entries like the former entries in the same column provided you fill in all of the rows.

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. 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. 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. 3. Insert into the Sorting sheet cell A1. Check the value of cell G1 to be zero once again.
  4. 4. Sort the content of columns A-E by choosing Data / Sorting / Sort by Accounts increasing, 2. Sort by Dates increasing
  5. 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. 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.

Checksum1

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.

Checksum2

3. Check the Internal Account

The internal account must always be equal to zero, or there may be errors in your result.

Checksum3