|
How do I? Index
Main Contents:
Welcome
Introducing hontreasurer.com
Online Manual
How Do I?
HELP! I'm Stuck!
TUTORIAL
The
Forum
TO DO List
How Do I? Contents:
Choose Activity Names
Print Labels or Form
Letters
I can't edit a
Transaction Field
Import existing records
Does the order of letters in
Label
Fields matter?
Print Labels or email People
who have paid for an Activity?
Produce Management Accounts
on a Spreadsheet with numbers from hontreasurer.com?
Post Contra Entries?
Set up and email only user?
Submit
Your Comments/Ideas
|
You
may be lucky and the standard reports on the Accounting Reports menu
may do exactly what you want. But more likely, as I have found, there
are budgets, last year, etc to be taken into accounts and the
presentation is important in terms of colour, boxes, subtotals etc.
This note explains how to make a spreadsheet which is like a template
which you can paste the numbers into. This article does assume
familiarity with two advanced features of spreadsheets: VLOOKUP and
Defined Areas. If you are an advanced spreadsheet user, it might be
worth reading all of this note before starting.
It may seem a but complicated to set up, but I assure you that it will
save you hours of time before
every meeting.
|
First
|
Make
sure you invent Accounting Categories which produce the numbers you
want. Give
the Accounting Categories sensible names; special characters like '
(apostrophe) can
cause troubles so it may be better to skimp on the punctuation a bit!
Make sure all Activities are in the Accounting Categories you want.
|
Second
|
Call
up OpenOffice or Excel. Lay the spreadsheet out how you would like it
on Sheet1. (I always say that Management Accounts should fit onto one
page.) Make each detail line correspond to an Accounting Category. If
the Categories are wrong, change them because this is easy to do. Put
in any
totals, percentages, graphs etc you want referring to these cells.
On Sheet2 (or after the last printed sheet) define an area (in
OpenOffice this is Insert/Name/Define) called "ThisYear" which is a
large number of rows and the first two columns, A & B. This is
where you are
going to paste the numbers copied out of hontreasurer.com. You need as
many
rows as there are Accounting Categories in hontreasurer.com.
Optionally, if you
want to have last year's figures on the accounts, make a similar area
on Sheet 3 called "LastYear".
|
Third
|
Now
this is the clever bit. Let's say you want to put this year's event
income into cell C8. and that you have an Accounting Category called
"Event
Income".
Put the following formula into C8:
=IF(ISNA(VLOOKUP("Event Income:";ThisYear;2;0));0;VLOOKUP("Event
Income:";ThisYear;2;0))
This formula first tests to see if there is a line called Event Income:
in column A of the imported items. If there isn't, the formula returns
0. If there is, it returns the corresponding number in column B.
If you want last year's income next door, paste the following into D8:
=IF(ISNA(VLOOKUP("Event Income:";LastYear;2;0));0;VLOOKUP("Event
Income:";LastYear;2;0))
Don't forget the colon (:).
Expenditure items are imported as negative numbers so you may have to
add * -1 on the end to get them to show as positive, e.g.:
=IF(ISNA(VLOOKUP("Event Expenses:";LastYear;2;0));0;VLOOKUP("Event
Expenses:";LastYear;2;0) * -1 )
Save your spreadsheet.
|
|
Fourth
|
Now all is ready. At any
time, call up the Movements tab on hontreasurer.com and select
Accouting Categories
and the current year. Right click and choose Select All (or press
Ctrl+A). Hold the control key down and press the first line on the
display (this is to get rid of an annoying superfluous null which would
otherwise appear). Now right click and select Copy (or press Ctrl+C).
Now go to Sheet 2 of your spreadsheet, select cell A1 and choose Paste
Special (either from a right click menu or from the Edit menu).
There is another tricky bit here. Your spreadsheet will ask you how to
format the clipboard. Tell it to use tab as a separator (this is
normally the default) and/or use Unicode. This will cause
the Accounting Category name and its value to be put into separate
cells.
Now press OK. The Accounting Category data will be pasted right into
Sheet2.
Repeat for last year if the numbers have changed and paste into Sheet3.
Now look at Sheet1 - all your numbers are in place and you are ready to
print the accounts.
|
|
Finally
|
You can repeat this
process without changing your spreadsheet at all - just make sure that
the new paste covers all of the old paste (i.e.
doesn't leave any lines from the old paste) otherwise silly results may
occur. The only time you
need to change it is when you have new lines in the accounts and
require new Accounting Categories or vice-versa.
|
Further
|
You can improve this
further by naming the Sheet2 Import2007 and naming the area ThisYear.
Then, next year, you can
create a new sheet for the new year (Import2008) and change the area
called LastYear
to point to Import2007 and change the ThisYear area to point to the new
sheet (Import2008). That way there is no
need to change the front sheet(s) at all. |
One
more thing
|
You don't have to use
Accounting Categories. The procedure works just as well with Activities
and Activity Groups. Using Activity Groups, you can produce sliced
analyses of type of expenditure across headings, for example. Remember
that there is no limit to the combinations of Activity Groups,
and that you can include Activities in as many groups as you want (but
be careful about the interpretation of the numbers!). In OpenOffice, *
works as a wild card which should ignore any text you
have after name in the Activity Group Name. So, for example, I
use "Event Income*" for an Activity Group called "Event Income - 2007"
so it will all work next year.And you can use the presentation power of
the spreadsheet to show colours, tables, charts etc.
|
Updated 28/6/07
|
|