Many small organisations will enter transactions in a way that exactly mirrors their bank account. In fact, you may be able to export your transactions from your internet banking facility in a CSV format, and copy-and-paste the columns into the Transactions spreadsheet.
Best practice, however, is the following procedure:
- Enter the transaction as you pay the invoice (or as you receive the remittance advice). You may want to assign a reference number and enter this in the ‘ref’ field. Also write it on the invoice.
- Enter any automatic payments or direct debits for the week or month.
- When you receive a bank statement, reconcile your entries with the statement (see below). You will need to make some more entries (bank fees, for example), but there should be no surprises. Any undocumented transactions should be followed up as soon as possible.
This procedure lets you spot unpresented cheques, for example, which you would otherwise have to track separately, or unauthorised transactions.
1. Recording a transaction in the Transaction sheet:
Fields in green are required for each transaction!
[Date]: enter date of transaction. The spreadsheet will not let you make entries not in the current financial year, so make sure you’ve got the year right! (Set-Up sheet)
[Details]: enter who was paid, who you received the money from, or any other information that will make sense to an independent person.
[Ref]: enter an invoice number, ‘DD’, ‘DC’ or ‘AP’ for Direct Debit, Direct Credit or Automatic Payment, or your own reference number if helpful.
[Account #]: Enter the number of the account from the ‘Accounts List’ tab. For example if you have put ‘Wages’ next to the number 411 in ‘Accounts List’, and your expense is for wages, use 411. The [Account Name] column will fill automatically.
[GST?] (only when GST functionality is selected): This column fills automatically with True or False. Verify that this is correct for the income or expense you are entering. If it isn’t overwrite the field with False or True for this transaction only.
[Project] (only when Project functionality is selected): Pick the project you want to allocate this income or expenditure to from the drop-down list, or leave blank if no project.
[Amount]): Enter the amount of the transaction, negative if it is an expense. [GST] and [Amount ex GST] will fill automatically, if your spreadsheet is GST-enabled (otherwise they will not appear).
[Bank Account?]: Enter a,b,c or d, depending on which bank account the transaction was made through. Use the code that corresponds to the right bank account name (beneath the code in the light blue section). If this field is left blank, the spreadsheet treats it as an unbanked income or expense, and your reports may give the wrong results.
[Grant 1, Amount 1 etc] (only when Grant functionality is enabled): For instructions on how to use these fields refer to the ‘Tracking Funding’ section in this guide.
If you are running out of lines use the button at the bottom of the sheet to create more, before filling in the last line. Never try to insert rows yourself.
2. Transfers between bank accounts
If you are transferring funds from one bank account to another, you will only need one line to record this. In the [Account #] column enter the account number for the bank account you are transferring to. In the [banked] column enter the code for the bank account you are transferring from. In the amount column enter the transferred amount as an expense (negative). The below example transfers $5,000 from the ‘Main’ Account (‘a’) into the Savings Account (‘b’):
3. Sorting and searching the ‘Transactions’ sheet
Excel lets you sort and filter the Transactions table in many ways to find specific transactions you are looking for.
To sort by date, press Ctrl+Shift+s. This will sort all transactions in date order from oldest to newest without upsetting any programming or formula. Note that this cannot be undone once sorted.
To filter other columns of the spreadsheet, click on the arrow next to the header you want to filter for. For example, if you want to see all the ‘phone’ transactions you have made so far, click the arrow next to ‘Account Name’. In the list of names, uncheck ‘Select All’ and then check ‘Phone’. The spreadsheet will then only show the phone transactions and give you a total amount for all of them.
To show the whole spreadsheet again, click the arrow next to the filtered column (it has a filter symbol now) and check ‘Select All’.
For more information on sorting and filtering Excel tables, refer to the Microsoft web site: http://office.microsoft.com/en-us/excel-help/CH010369011.aspx
4.Acquiring a new fixed asset.
A ‘fixed asset’ is an item you expect ongoing use from. Most commonly these are computers, printers or office furniture but also tools, vehicles or trailers, buildings. Software and other ‘intangible’ items are not usually considered assets in not-for-profit accounts.
You should have a separate Asset Register, where you record each asset purchase, disposal, and (if applicable) depreciation each year. You can add a sheet to the workbook for your Asset Register if you want to have everything in one place.
It is good practice for not-for-profits to record every fixed asset, regardless of value. If you are exempt from Income Tax, IRD’s thresholds (currently $1,000) and depreciation tables do not apply to you.
5. Recording the purchase of a new asset
- Make a new account for the asset in the Account List sheet, if there isn’t an account for this asset type already.
- Record the transaction in the Transaction sheet as normal, using an asset account in Account #.
6. Disposal/Sale of a Fixed Asset
In accrual accounting, only the difference between the asset’s book value and what you got for it is income (or an expense, for example if you give the asset away or it is worthless). The accounts for this are usually called ‘Gain on Sale of Asset’ or ‘Loss on Sale of Asset’.
Unless you are comfortable making the relevant journal entries yourself, we recommend that you create an ‘Asset Sales’ account under Assets in the Account List Tab, and use this account to record the received money in the Transaction Sheet for now. Otherwise, use the Journal Tab to record gain/loss on disposal of asset and to reverse cost and accumulated depreciation for it.
In cash accounting, money received from an asset sale is considered capital income. You should create an ‘Asset Sales’ account under Assets in the Account List Tab, and remove the asset from your Asset Register. You do not need to do anything else.