BXL Integrity Checks
Beyond Excel's (BXL's) Add-in:
Integrity Checks
Gold Standard Made Easy
Errors in our work can kill our careers. Neglecting integrity checks is foolish and amateurish. Beyond Excel's Integrity Checks add-in makes adding world class, gold standard integrity checks quick and easy.
What makes this the gold standard?
I selected the best standards from the best standards organizations and added to them. The gold standard includes:
Overall Status
(FAST) An overall status at the top of every worksheet makes problems anywhere visible everywhere.
Highlight Problems
(Acuity) Highlighting problem cells speeds taking corrective measures.
Centralize Tests
(Operis, Acuity, BPM) Placing all checks in one place makes it easy to audit what we have tested for and what we have not. This also declutters calculation areas making them easier to audit.
Make Alerts Actionable
(BXL) Alerting people to problems is a good. Suggesting corrective actions is better.
Hyperlink Alerts to Issue Locations
(Acuity, BPM) Placing hyperlinks in alert messages can take us directly to problem areas speeding problem resolution.
Who has time for all that?
You do! With Beyond Excel's add-in, adding gold standard integrity checks is quick and easy and can save your career!
Does this add VBA to my project?
No. Beyond Excel's add-in is separate and is only used to create the checks. Once checks are created, the add-in is no longer needed and our projects can be distributed VBA free.
What kinds of things can I test for?
Beyond Excel's Integrity Checks add-in is designed to work with all model types: Financial, Business, Engineering, Scientific, Data Migration and more. That said, we expect most users will be from the Financial space. And so this tool conforms to financial modeling uses and standards, such as the use of tolerances. Below are some things we can test for with this tool (from Microsoft Word - B3 Eusprig 2008 David Colver v2 0 - GJC Edits.doc (arxiv.org) :
Balance Sheet Balances
Financial statements add up
Financial statements have expected signs
Sources match uses
Identities hold true
Balance sheet clears out
Ratio inclusion analysis
Tax reconciliation
Yield analysis
and more
Included Test
Beyond Excel's Integrity Checks add-in includes a wealth of tests. Below are 'calcualted tests'. Calculated tests update when Excel calculates.
All Dates - All cells in a range resolve to dates
All Formulas -All cells in a range have formulas
All Numbers - All cells in a range resolve to numeric values
Balance - First reference total must equal second reference total
Balance Each - Each value in first reference equals corresponding value in second reference
Column Count - A table or range has the required number of columns
Each - Compare each first reference's cells to corresponding second reference's cells
In List - First reference is found in the second reference's list
Maximum - Compare maximum of reference 1 to reference 2
Minimum - Compare minimum of reference 1 to reference 2
No Blanks - No cells in a range evaluate to blank cells
No Errors - No cells in a range evaluate to errors
No Falses - No cells in a range evaluate to false
No Negatives - No cells in a range evaluate to negative numbers
No Positives = No cells in a range evaluate to positive numbers
Nothing Remains - Summing all cells in a range returns no remaining balance
Row Count - A table or range has the required number of rows
Text Length - All cells in range conform to length requirements
Total - Compare both reference totals
Totals 100% - First reference total equals 100%
Beyond Excel's Integrity Checks add-in also includes manual tests so we can record that something was checked, by who, and when. Manual tests do not automatically update when things change; however, two 'manual' test can be reapplied any time and Beyond Excel's Integrity Checks add-in will perform the test for us and record when we did it.
Row consistency - Check a row to make sure all formulas in the row are consistent.
Columns consistency - Check a column to make sure all formulas in the row are consistent.