Most grant-makers require that the organisation keep records on how they spend the grant money. Our spreadsheets help with this.
The grant tracking functionality is entirely separate from the income/expenditure recording. They do not influence each other in any way.
1. Setting Up
If you want to use this functionality you first need to go to the Set-Up Sheet and enable the functionality.
Click on the green ‘Grants’ tab and enter all the grants you want to track in column A. Enter the “Opening Funds” from your previous accounting system next to them. This is the amount from each grant left unspent when you first start using this spreadsheet.
New grants received after you start using the spreadsheet: If you receive a new grant that you need to track, type the name of the grant in the first available line in the ‘Funder/Grant Name’ column. Also enter the date received and the purpose in the respective column to help you distinguish different grants. Do not enter an opening balance. Instead, make sure you allocate the received amount in the Transaction Sheet to the grant you have just entered, which will show in the ‘Funds Received’ column.
You may also have to create a grant account in the Account List Sheet (Income section), if you want the grant to appear separately in your Income/Expenditure reports. Remember, these two systems are completely separate and do not influence each other. Grant tracking is not done ‘automatically’ as it requires a user decision for each transaction.
2. Track as you go
Once you’ve set up your ‘Grants’ sheet, you now simply allocate expenditure (and the grant payment itself) in the green section of the ‘Transactions’ sheet.
In the green section of the Transactions sheet you will find a grey column headed ‘$ Remaining in Grant’. This figure is updated as soon as you select a grant in that row to help you identify how much money you have left in this grant without switching back and forth between tabs.
If you over-allocate expenditure to a grant, the figure in the ‘Current Grant Balance’ column in the ‘Grants’ tab will turn red, and a warning ‘Grant Overspent!’ will appear. It is important to avoid this if you are highly grant-dependent, as the overspent money will come out of your reserves.
We recommend that you allocate all income and expenditure (but not transfers between accounts or GST payments/receipts!). Income or expenditure that is not grant-related should be allocated to ‘untagged funds’. This way you have a running balance of the amount of money you hold that is not tagged to a specific grant.
Splitting income or expenditure over two or more grants
You can split any transaction item across two grants in the same line.
Example 1:
You are paying $300 in rent, $200 of which comes out of a ‘Goodguys’ grant and $100 out of a ‘Charity4All’ grant.
As soon as you enter the -$200 in the Transaction sheet, the ‘Amount 1’ column in the green section changes to ‘-$300’. Overwrite this to ‘-$200’! The ‘Amount 2’ column will automatically change to the balance amount, -$100. Now allocate each amount by finding ‘Goodguys’ and ‘Charity4All’ in the respective drop-down lists.
If you have to split a transaction over more than two grants you will need to add another line.
You will generally have to split an expense to fully expend a grant. For example, you may have an amount of $86.95 left in your ‘Goodguys’ grant. You have an expenditure of $195 that you could allocate to that grant, but it is too much. The solution is to split the transaction as above, put $86.95 to ‘Goodguys’ and the balance to another grant.
3. Grant Reports and Balances
Your Grants sheet will show you the total amount spent from each grant, and a running balance of how much you have left at this point in time. It will also alert you to any overspent grants.
You can also run a specific report for each grant from the Grants Sheet:
To use reporting, Macros need to be enabled. You can do that by choosing ‘Enable Content’ in the yellow banner at the top of the screen when you first open excel.
4. Untagged Funds
By default, each transaction is assigned to ‘untagged funds’ in the Transactions Sheet. Managed properly, the ‘untagged funds’ line in the ‘Grants’ tab will tell you how much of the money in your bank accounts is truly yours, and not committed to be expended from received grants. For highly grant-dependent organisations this is an important management tool, and can provide early warning when an organisation is about to go into a net debt position (more money left unspent from grants than money in the bank).
When you first start using Accounting 5.0, enter an opening balance for untagged funds in the ‘Grants’ tab. This opening balance should be all your bank accounts, including any term deposits, (plus GST refund owed and less GST due for payment) less your opening balance of unexpended grants.
After entering the opening balances for your grants, the ‘Total available’ field under ‘Current Grant Balance’ should add up to your total bank accounts, plus or minus any GST owed or owing (if registered).
When entering transactions, these will automatically be allocated to untagged funds, unless you specify a grant in the green section of the Transactions sheet. You will need to change this field to empty in two situations:
1. transfers between bank accounts
2. GST payments or refunds
There may be other situation, such as the receipt of loans, bonds or funds held on behalf of another group, where you may need to change this field to empty. You can do so by deleting it, or by choosing an empty field from the drop-down box.