Books
| ⚠ Warning: this page pas copied from the old wiki and might not be up to date. |
Contents
Overview
The current system of book keeping is done:
- In a system of "single" book keeping
- Using OpenOffice Calc
- Made to accept Argenta csv files
- Using a python script to anonymise names and process the data
As a financial responsible your job is to:
- Make sure the books are filled in and up-to-date
- Report on the current state of the organisations financial wellbeing every Thursday meeting
- Keep and back-up the books
- Manage the cash in the space (i.e. coin-counting and lugging them to the bank)
The books-spreadsheet currently has 5 sheets:
- Overview: Contains overview and checks on the general state of the organisation and the books.
- Journal IN: Contains a list of money movements that are a plus (incoming) for the organisation, e.g. membership money, drink sales
- Journal OUT: Contains a list of money movements that are a minus (outgoing) for the organisation, e.g. buying club mate, paying rent
- Cash: Contains a list of all money movements happening on the cash "account", can be plus or minus, e.g. sales of drinks, putting money on the bank
- Bank 0x20: Contains a list of all money movements happening on the 0x20 bank account, e.g. money coming from cash account, membership money, paying club mate purchase, paying rent,...
The books-spreadsheet for the first year (2010) also contained the following sheets that are no longer required. They are listed here for reference:
- Bank Tazo 0x20: Old bank account of 0x20, now inactive. Contains all the money movements that happened on that account. For historical reasons only. This sheet will no longer appear in the books of 2011 and onward.
- csv Bank 0x20: Helper sheet: for importing csv files for current bank account, contains full merged contents of all movements downloaded through csv and anonymised plus conversion to "single bookkeeping" friendly mode. The python script will make this sheet superfluous, and it will no longer appear in the books of 2011 and onward.
- csv Bank 0x20 Tazo: Helper sheet: for importing csv files for old bank account, contains full merged contents of all movements downloaded through csv and anonymised plus conversion to "single bookkeeping" friendly mode. For historical reasons only. This sheet will no longer appear in the books of 2011 and onward.
An empty template for the bookkeeping can be downloaded here. A python script is also available to automate the anonymising, sorting and processing of downloaded bank data. Simply run the script in the same folder as the .csv file, and import the .csv file it generates.
Basic single bookkeeping ideas
Duplication
The main idea of single bookkeeping is to have every money movement twice:
- Once in one of the journals (IN if positive, OUT if negative)
- Once in one of the accounts (where the money moved)
A check to see if your account's are correct is to subtract your journal OUT total from your journal IN total, and checking if it matches the total of all your accounts. If you did all of your accounting correctly, this should match.
Attribution
Next to registering all positive IN and negative OUT movements, a journal also keeps track of the type of movement.
- An incoming movement could be because of someone paying it's membership fees, income because of sales of drinks and chips, a gift or an incoming transfer on one account, outgoing from another one.
- An outgoing movement could be because of spending money on equipment for the space, buying goods to sell like drinks and chips, paying rent, paying gas, or an outgoing transfer from one account, going towards another account etc...
Transfers
Transfers are movements from one account towards another. Usually this happens when you remove money from the cash account an put it on the bank account. To keep track of this, you will see a transfer OUT line for the cash register on the OUT register and a transfer IN line for the bank account for the same money. If no money is "in transfer" the total for all OUT transfers should match the total IN transfers.
Accounts
Accounts keep track of the movement for a specific account.
- Cash is regarded as a special account, which, in principle, cannot go below zero. The amount of cash is updated every time someone counts the money and puts it in an envelope. In a perfect world however, the cash account would be almost always match the exact content of the cash register (think automated cash machines).
- Accounts also keep a link to real world documents which need to be stored in some physical place (the bookkeeping folder/map in our case).
Journals
There are two journals, IN and OUT. Both journals have got almost the same columns; The sets of columns have been divided into different color sections, depending on their source.
White section
These contain general columns referring to the source of the money movement. They should reflect the contents of the accounts they are based one, and contain some formulas to do this linking in a patchy kind of way. The only column a financial responsible should modify one of the columns for entering an item number (one of the columns in bold), and use as value the number of the item (found in the other sheet in the Nr. column). All other parts are filled in automatically,
| Column | Content |
|---|---|
| Nr | The number of the line. Usually already prepared when empty lines are added in the sheet. Should by law always contain sequential numbers. We opt for non automatically generated numbers so whenever rows are moved by accident, the correct number does not get lost. |
| Date | The date of the transaction, automatically filled in based on the contents of the referred account. |
| Description | The description of the transaction, automatically filled in based on the contents of the referred account. |
| Bank 0x20 Nr. | If your line concerns a transaction on the bank account, fill in the number found in the column Nr of the bank account sheet. This will trigger auto filling of all other fields in the white section. Otherwise leave this field blank. |
| Bank 0x20 Amount | Will be filled in automatically if this transaction refers to a bank account transaction. Otherwise defaults to zero. |
| Bank 0x20 Tazo Nr. (2010 only) | If your line concerns a transaction on the old tazo bank account, fill in the number found in the column Nr of the old bank account sheet. This will trigger auto filling of all other fields in the white section. Otherwise leave this field blank. You should normally not have to use this field. |
| Bank 0x20 Tazo Amount (2010 only) | Will be filled in automatically if this transaction refers to an old bank account transaction. Otherwise defaults to zero. |
| Cash Nr. | If your line concerns a transaction on the cash account, fill in the number found in the column Nr of the cash account sheet. This will trigger auto filling of all other fields in the white section. Otherwise leave this field blank. |
| Cash Amount | Will be filled in automatically if this transaction refers to a cash account transaction. Otherwise defaults to zero. |
| Total | The total of all amounts of the transaction. Normally should be equal to either cash or 0x20 bank amount, since it should never happen that more than one amount is filled in. |
Blue section (attribution)
This section tells what the money is used for. To register correctly for what the money was used, a financial responsible should enter the same amount as in the Totals column in the white section into the correct attribution column. In some exceptional cases it could be that the total amount needs to be divided over several columns, e.g. for a purchase that contains both goods and investments.
The blue section is the one that differs depending on the journal.
The blue section for the IN journal contains the following columns
| Column | Content |
|---|---|
| Memberships | Money received from members paying their membership fee |
| Gifts | Gifts to the space |
| Drinks | Money received by selling drinks and chips |
| Subsidy | Money received from subsidies, mandated by law (unverified) |
| Others Amount | The amount of money received for other types of income |
| Others Description | The description of the amount classified under other, e.g. Workshop |
| Transfers | Money that was transferred from another account. When the books are balanced, and no money is in transfer (on its way to the bank e.g.) there will be a matching record in the OUT journal for this amount. |
The blue section for the OUT journal contains the following columns
| Column | Content |
|---|---|
| Consumables | Money spent on buying drinks and chips |
| Salary | Money spent on paying employees of the organisation, mandated by law (unverified), will be empty since we don't employ anyone |
| Div. Goods & Services | Investments, goods and services not to be sold on to members or third parties, e.g. light bulbs, chairs, etc... |
| Others Amount | The amount of money spent for other type of expenses |
| Others Description | The description of the amount classified under other, e.g. Gift for new space |
| Transfers | Money that was transferred to another account. When the books are balanced, and no money is in transfer (on its way to the bank e.g.) there will be a matching record in the IN journal for this amount. |
Yellow section (help and checks)
The yellow section is not really part of the journal and contains a column containing a copy of the description (helpful when dealing with a small screen) and a check for non or double attributed money.
| Column | Content |
|---|---|
| Description Source | A copy of the description column |
| Free Money | When non empty, contains the amount the total of the white section differs from the total of attributed money. If this is not empty, something is wrong with the books. |
Accounts
Cash
The cash account sheet contains all the money movements of the cash account.
- When an envelope is made from cash out of the box on the fridge, ultimately, an entry in the cash account sheet should be added, e.g. "Sales drinks and chips" for the date on the envelope.
- When money is transferred from the blue box (cash) to the bank, this should also get a line "Cash to bank account" in the cash account sheet on the date the money was removed from the box to be transferred to the bank.
Specialties:
- A cash register is supposed to never go below zero (negative cash does not exist). That is why the column extra column on the right is there to keep track of the current total for every line
- As the cash cannot really be tracked in real time without continuously counting, a check should be made at least once a year to verify if the saldo in the books is correct.
Bank
How to update the books: step by step
- Count the amount of money in the donation box, and put everything except for ~€25 of loose change in an envelope in the blue box. Mark the envelope with the date and amount and sign it. The envelope is important as bookkeeping evidence. Tape all envelopes completely shut as they tend to open under the weight of coins. More tape is better.
- Take all envelopes out of the blue box, empty them, and try to fill as many plastic coin tubes as possible. Put the bank notes and full coin tubes in another envelope, mark the contents and take it to the bank for deposit. Leave a note saying how much money you took to the bank in the box.
- Mark the cash transactions on the cash sheet of the books-spreadsheet. Envelopes put in the blue box are 'incoming', envelopes taken to the bank are 'outgoing'. Make a mark on the imported column where your first entry was, this will make your work easier later on.
- Goto the argenta internet banking site and download a csv file of the latest transactions. Include some margin of error by taking the from date a couple of days before the last transaction already in the books.
- Run the automator python script. It will anonymise the csv file, and add new listings to the bank account files in the folder corresponding to the appropriate year.
- Import the generated "anonymous 0x20 bank account 20xx.csv" file in OO calc. Use "Language: USA" and "Seperated by: semicolon" options. Set the first column as "Date (DMY)"
- Copy the new entries from the imported spreadsheet to the bank 0x20 sheet of the books-spreadsheet. (Use ctrl+shift+V - past special to only copy data and no format as to leave the format ) Make a mark on the imported column where your first entry was, this will make your work easier later on.
- Now for the hardest part: copy new transactions from cash and bank to incoming and outgoing in chronological order. Mark from which account (cash or bank) the transaction is coming from by filling in the appropriate column with the account transaction number. If this is alright, the lookup description to the right of the sheet should be automatically filled in to the description you copied to the left. Do this for every transaction.
- Attribute all the new transaction amounts to one of the blue column. Fill in an extra description if necessary. If this is done correctly, there should be nothing in the 'free money' column.
- Check your books on the overview page, by looking at the totals and comparing them to what they should equal. Compare to the online banking total.
- Save and backup the books. Mail them to someone (e.g. the previous treasurer) for review.
You're all done!
At the end of the year
- At the end of the year, the books should be made ready to turn in
- An inventory should be made of what is in the space
