I know what you are going through with regards to long complex formulaes and
I also understand Accounting terminology as I majored in that field.
Currently, I'm working on creating a DB program that will eventually get
into that area. Currently, I'm mainly working on getting production type
stuff into it and setting it up as a MRP type system. For more info on MRP
type system, you can get some basic info through APICS CPIM program at
www.apics.org, which is where I got my training from, company paid for.

What you may want to do is the following:

Create either an algorithm and/or a flow chart of *ALL* the different
processes involved, which if put to programming terms and format, this would
be known as psuedo coding.

Given the complexity of what's involved (as it sounds like from your
description), VBA programming would probably suit your needs better.
However, using a combination of formula writing and VBA coding can be very
effective.

One other thing I do with my reports, I archive them all once all of the
reports for the year has been completed, and currently, I have 4 complete
years of history plus the current year. In your case, it would be a matter
of archiving all of your accounts with their account numbers and balances
(Those listed on your "Chart of Accounts" including revenue and expenses
after all adjustments for the year has been made and just before resetting
them to close out the year). If this archiving was to take place in a DB
environment, it may look something like:

ID# Year Account # Balance

The ID# would only be in there to give the table a Primary Key, even though
you could setup the combination of the year and account # as a primary key
since no 2 records should have the same year and account number, but often
times, not done in practice that way.

Once this table is setup and populated, you could then run your end of year
processes on these different numbers, and set it up into a report type
format.

above, when I mentioned about adjustments and closing, I basically meant
that all adjustments that would need to be done such as your paper
transactions (I.e. Depreciation adjustments) and accruals that has been
incurred as of the end of the last day of your fiscal (or calendar) year.
Once all of these adjustments has been made, then archive your numbers as
mentioned above, then do your financial statements processes (not sure if
you use the direct or indirect method for creating your Cash Flow
Statement), which does include closing out all of your revenue and expense
accounts to the Summary Account, which then is closed out to the equity
account(s).

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Peter" wrote in message
...
- was cross posted to excel.templates..

Folks,

I am busy working on a rather complex spreadsheet that allocates
operations
costs and salaries to feed multi-year Income Statements, balance sheets
and
cash flow statements. In all the sheets have several hundred of formulas
that can go wrong. copied incorrectly, logic errors, etc) With all the
allocations and consolidation it has become neccessary to put in
significant
error checking..

Error checking used so far:

(1) Conditional Formating - Display the word "Error" when two cells that
should match don't. This is acheived by using a white font colour when
the
cells match and red when they don't.

(2) If Statements - Display the cell value when the logic test fails.

(3) Display the delta between two cells that should match - rounding where
required.

(4) A verification sheet to summarize the key tests using a combination of
the above.

It still takes too long to track down the offending formula or logic
error.

Being rather certain that others have solved these problems long before I
knew they existed I am looking for advice and tips.

Thanks in advance.

Peter