Page Content

Related Pages

Impact of ACBA-EWP on Spreadsheet Design

This web page considers primarily the impact that ACBA's Electronic Working Papers software has on its approach to spreadsheet and systems design, but these do not override the basic concepts of good design.

The concepts of good spreadsheet design and construction have been widely discussed and written about. However there is no universal agreement on what the "rules" should be. Below we provide links to web sites and articles. In general, these provide an excellent basis for understanding how to construct user friendly and robust spreadsheets. If you read them all, however, you will find some contradictions of detail between them and indeed within them.

ACBA Electronic Working Papers (EWP) - what it does

EWP is designed to make it easy control the creation of worksheets and to find your way around an Excel workbook, even those with large numbers of worksheet tabs. So far as spreadsheet design is concerned its most important features are:

A copy of ACBA-EWP can be downloaded from the link. This trial version is free of charge but is fully operational for at least one month.

Why does this make a difference?

The Role and Function of the Workbook/Project

In ACBA-EWP parlance we call an Excel workbook managed by EWP a 'Project', since it is most often found as the only file in a special directory. The project index groups together worksheets (we call them 'Working Papers') like the Sections and Chapters of a text book. A key consideration in the planning of any project is what should be included within it. This provides users and designers an important opportunity review their spreadsheet usage in relation to the project subject matter and rationalise it if appropriate.

The facility for indexing and quickly moving around a project means that much larger groups of associated spreadsheets  can be brought together and controlled within a single workbook. Effective use of projects can have a significant impact in improving the enterprise facility for retaining and monitoring that part of its information resource held as spreadsheets.

The Index Hierarchy Makes Sense of the Contents

The core of EWP's indexing process lies in the hierarchy. The user can establish the hierarchy of working papers within his workbook in whatever way he pleases, but if it does not make common sense it shows up immediately in the index. This simple control is sufficient to keep every one on their toes and pre-planning their designs and Working Papers.

Compare the Index below with

wpe1.jpg (152076 bytes)

this one which contains exactly the same Working Papers, but without an effective hierarchical construction.

wpe5.jpg (146767 bytes)

Naming Worksheet Tabs

A common call from gurus on spreadsheet design is that you should rename your worksheet tabs such that they are clear and meaningful. This means that the user/designer must generate or adopt a naming methodology that is clear, rational and meets the Excel rules for naming worksheet tabs. All this takes time and is frequently not as clear as we would wish. Under EWP the worksheet tab naming process is automated, the user must concentrate on the full title of his working paper. This is what appears in the index - see above.

Even though you intend that tab names should be useful, they are often confusing.

wpe5.jpg (129068 bytes)

History and Controlled Development

Standard Modules (Index, Background, Resources, Report, Reminders) provide a logical location for the history, development, design and control of the project within the workbook. In the additional software package we call 'Controlled Statements,' the user can also print out and demonstrate chronological development of each of the Working Papers within the project.

Navigating the Workbook

Placing different parts of your system or process on separate worksheets is common ploy among designers and, in many respects, is regarded as good practice. However as soon as you have more than five or so worksheets tabs (even if they are clearly named), you are faced with the practical problem of moving around the workbook since there are too many to see a single view in the tabs toolbar. EWP tackles this problem of moving around the workbook in several different ways. The user can choose which ever is most convenient to him at the time. These are

Chronological Commentary

One of Excel's most powerful innovations in recent years is the introduction of cell comments. EWP can take this functionality a stage further by linking chronological comment sheets through the standard cross-referencing methodologies to individual cells on a separate worksheet, thus providing a full history of important events and issues associated with spreadsheet control records.

In the diagram below the bold red references point to the specific Working Paper cells that record the error.

wpe7.jpg (145511 bytes)

Data Summary and Carry Forward Connections

One of the difficulties a designer faces is how to connect the data in one worksheet and summarise it or carry it forward to another? The question arises as to whether to hard code those links or not. If you hard code then you generate a rigid inflexible structure to your workbook (this often proves impractical and unfriendly) and if you don't, how do you ensure that the user creates the link effectively.

EWP provides a simple solution to flexible links through the cross-referencing methodology which always references the worksheet name. This methodology, in conjunction with the INDIRECT() worksheet function, provides the simple pointers that are needed. In the example shown below, the data from the linked worksheets is only called when the working paper reference (WP Ref column holds the name of the subsidiaryworksheet) is posted. The summarised values are based on named cells in the subsidiary worksheets.

This template has been set up such that it automatically gathers data from the 'WP Ref' source when the reference is posted.

wpe8.jpg (172832 bytes)

EWP Templates are Different

You can create worksheet templates in Excel, why should EWP be any different? The most obvious difference is in the way that EWP approaches the concept of a template model. Under standard Excel methodology a template represents a fully fledged model (either a single worksheet or a combination of linked worksheets), usually held in a separate workbook with an '.xlt' extension. Under EWP a template is repesented by an individual component of a model. The user is encouraged to add new components to his model within a single workbook as required. One particular advantage is that the user can add different components to the same primary model - see the description of the Su Doku model templates.

In practice, however, EWP employs the concept of templates to much wider range of functions than just logical models - see Templates as a Designer Tool.

Under EWP you can store many worksheet templates (components) in a single workbook and create references to each one of the templates in a single process. The EWP module for creating, amending and deleting template references is functionally simple but comprehensive. In particular the description/naming of templates is both specific and allows for a two level logical hierarchy.

Templates as a Designer Tool

The provision of user access to re-useable, well controlled templates is the single most powerful tool for a spreadsheet designer. It allows the designer to advocate and control both the presentational and programming approaches associated with his system. Although not exhaustive, under EWP templates can cover

The project based concept of EWP means that, where appropriate, the user can incorporate several different styles of template/model within a single workbook or project. If the components of a model cover more than one worksheet then it is for the user to ensure that the links between the components are correctly generated. EWP provides a range of modelling and control processes to ensure that the user generates the correct connections, but it is down to the designer to ensure that the processes for constructing a model are logical and clearly explained. Note that diagrams and advice on using complex modelling templates can be made available to the user through instructions which are themselves templates. The similarity of the processes for providing both the model itself and the instructions for creating it, is one of the reasons for the double layered, hierarchical approach to naming templates.

Templates for Process Control

Process control is a multi-layered activity. Not every element of a process or activity is susceptible to automatic control mechanisms. Accordingly those templates that are designed to manage and monitor user actvitiy as part of the process form a very important part of any project. This is particularly true for audit related projects for which "checklists for completion of processes" form a major element of the control requirements.

EWP has adopted hyperlink pointer targets (known as cross-references) that can both name the worksheet containing the target and specify the range of the target within that worksheet. It is common practice for designers to make use of this particular functionality to ensure that user user activity is controlled and completed in accordance with the designer's requirements. In effect the hyperlinked references automatically tell another user or reviewer that a required activity has been completed and where the record of that activity is held.

One of the most common process controls employed with in EWP is designed to ensure that the user completes all the required elements of a process before moving on to the next phase. In the example illustrated below the user must complete all the elements of the Risk Index (column B - green cells) before the overall index will compute. The completion process is monitored in column H. (This column isn't normally visible to the user.) When the sum of H13:H23 = 11 see H24 then overall Risk Index(cell B27) is allowed to compute, otherwise it remains blank.

wpe91.jpg (138426 bytes)

This standardised approach to process control can also be employed to ensure that elements of a model linked to other EWP Working Papers are processed before finally evaulating the model's answer. This uses the standardised layout of the worksheet names to verify the links.

Indexing and the Iterative use of Templates

Within EWP user access to worksheet templates is instant. It forms one of the choices that the user makes when creating and indexing a new Working Paper. This means that templates can be used quickly and repetitively within a project while retaining clear distinctions as to their role through the index.

One of the clearest examples of the repetitive use of templates is in Auditing Su Doku.xls. For one of the solutions the same template is reused iteratively over 50 times before the puzzle is finally solved.

Making the Most of Excel's Named Range Functionality

In Excel there are two types of named range.

EWP's naming function only uses local names, but users still have access to the standard Excel naming functionality. Most ACBA prepared templates, that call or summarise data from other worksheet sources use local names and the standard cross-reference functionality to gather the data.

Secure Hyperlink Pointer Targets

Local names also play an important role as permanent targets for hyperlink pointers. Their particular advantage over cell addresses is that the cell name moves with the cell if you add or delete rows or columns. Accordingly they provide secure targets.

The Audit Evidence Index

There are times when you need to prove beyond any reasonable doubt that a value or statement is correct/valid. EWP allows the user to index and to copy into the Project directory any electronic evidence to which he has direct access. The Audit Evidence Index is a list with hyperlink pointers to all the external evidence referred to in your Project.