Error Checking Tips

[Peter]

- 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

 

[Ronald Dodge]

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).

 

[Claude]

Peter,

For what it's worth: I've solved a similar problem by
inserting validation checks all over the spreadsheet,
giving a specific output in case of an error, e.g.: =IF(ABS
(SUM(I9;N9;T9)-BS!E$10)>.001;BS!E$10;"<>").
I then wrote a macro that scans the whole file and stops
at the first error it finds:

Sub Finderrors()

'this macro searches through the whole file and highlights
the first cell
'it finds with output "<>". Otherwise, it gives output "No
(further) errors found !"

For x = 1 To Worksheets.Count
Worksheets(x).Outline.ShowLevels RowLevels:=3
Set errorcell = Worksheets(x).Cells.Find("<>",
LookIn:=xlValues)
If Not errorcell Is Nothing Then
firstcell = errorcell.Address
Do
Worksheets(x).Activate
errorcell.Activate
Select Case MsgBox("Find next
error?", 4)
Case vbNo
Exit Sub
End Select
Set errorcell = Worksheets
(x).Cells.FindNext(After:=ActiveCell)
Loop While Not errorcell.Address =
firstcell
End If
Next x

If errorcell Is Nothing Then
MsgBox ("No (further) errors found !")
End If

End Sub

I don't use a test like "SUM(I9;N9;T9)=BS!E$10, because of
excel's problems with binary rounding - see
http://support.microsoft.com/default.aspx?scid=%
2Fservicedesks%2Fbin%2Fkbsearch.asp%3FArticle%3D48606

 

[Peter]

Ronald,

Thanks for some great tips on the design side. Many of your ideas are
already in the spreadsheet but some sparked thoughts a different approach.

Peter

 

[Peter]

Claude,

Thanks for the macro. I will use it to scan the sheet looking for errors. I
may alter it to, on error, look-up an error code and provide a bit of a
description. But as is this will be very useful.

Peter

 

[Tom Ogilvy]

Have your data entry in one sheet in your report/entry format

Have your calculations in another sheet organized in a sequential fashion
with links to bring in the input data so that the data is all located in
proximity to the results.

Put your validation formulas in adjacent cells. Then you can scan down
until find the first error. Fix this and it should remove some subsequent
errors that depended on this one. So you are working from the bottom up.
If you disable "allow editing directly in cells", you can double click on
the cell with input data (containing a link to the acutal source) and you
will be taken to the source data where you can change it.

On the "show" sheet, you can create formulas linking to the calculate sheet
to show results.

Top sheet meets the format required

second sheet organized to facilite the error analysis you need to do.