 |
Allen Computing and Business Analysis
|
Page Content
Related Pages
Spreadsheet Tools for Pragmatists
Spreadsheets are used for such a wide range of purposes it is virtually impossible for a single set of tools, to provide access to all the potential analytical and presentational functionality that may be needed by any individual user. In fact, we divide our “tools” into two primary packages – ACBA Electronic Working Papers (ACBA-EWP) (for project control and management) and ACBA Audit Tools (ACBA Tools) (for analysis, review and recording).
Although these are marketed as separate packages, within the company at least, they are always used together. ACBA-EWP provides the control environment within which the results of any analytical operations employing ACBA Tools can be presented to a reader or reviewer. Here we consider general usage of each of the packages separately before looking at the potential synergies between them.
Analytical Tools - Our Philosophy
The original purpose of the ACBA Audit Tools was to review
spreadsheets that had been laid out in row and columns mimicking a database. Over the
years new functions have been added to meet our internal requirements. The functionality
now includes:-
- analysing
lists (either numeric or string based) for particular characteristics
- comparing
database style lists for new, missing and altered records
- creating
database style lists of the contents of your spreadshseet
- analysing
strings for their numeric content
- a
variety of 'user-defined' worksheet functions that both mimick the above processes and
introduce a limited range of additional processes (e.g. the apprximately equal function)
A curiousity of this package is that it was never originally intended for sale, rather
for internal use within the company. The pressure to retain its simplicity and directness
for internal purposes is the bain of all marketeers.
- The analytical functions were
strictly single purpose. We avoided combining them, even if this appeared to present
itself as a marketing advantage. The analyst would decide for him or herself the
analytical functions required and the order in which they should be presented.
- In order not to over burden the
recalculation requirement of large complex spreadsheets, we chose an analyse once and
record process.
- In the absence of underpinning cell
formulae (the would require recalculation) the package provides cryptic column headings to
show the analysis undertaken as the basis of the results.
- Where an analysis is clearly subject
to potential future change, a user-defined formula based option is also available.
The single purpose approach means that the analyst must show clearly the order and rationals of his analytical processes.
An Organisational Approach to Analysis
It is clear from the above that we expect the analytical process to proceed in discrete steps. This works well if you only need one worksheet. Difficulties arise, however, when you want analyse a sub-set of the original data or to start again with a different rationale (and, subsequently, make a comparison). Here the obvious way forward is to use a new worksheet, but how can we make the structure and the links between the analytical pathways clears.
ACBA-EWP is designed to encourage logical relationships between working papers. This is particularly advantageous when there is no formula link (between worksheets) to guide the reader/reviewer in the right direction. It creates a series of structured and logical workspaces within which an analyst can create, work on and link together various concepts or trains of thought.
