Role and Function of Workbook, Design Logic, Naming Worksheets, Location for Design Control, Moving around a Workbook, Chronological Commentaries, Linking Data between Spreadsheets, Difference between EWP and Excel Templates, Templates for Designers, User Access to Templates, Iterative Use of Templates, Naming Ranges in EWP, Local Names, Secure Hyperlink Pointers, Evidence Index
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.
One possible rationale for the wide range of approaches for the management of good spreadsheet design lies in the conceptual simplicity of the spreadsheet itself. It can be adapted and usefully employed to solve a huge range of problems. Even though some generalisations can be made, it is not surprising that, when constructing advanced or specialsed spreadsheet models, each spreadsheet designer employs highly specialised control techniques. The considerations that follow examine the generalised control features established by the ACBA-EWP software. This should not be regarded as the end of the story. Each specific model needs to be controlled relative to its functionality.
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:
- indexing worksheet tabs in a clear but flexible hierarchy (like the Sections, Chapters and Pages of a text book) - one of the tabs is always the index itself. In effect, this approach adds the concept of structure to the more widely accepted concept of creating modules within Workbooks.
- providing simple standard methodologies for:
- cross-referencing between separate worksheet tabs and within large workbooks
- creating, managing and employing worksheet templates
- naming cells and cells ranges such that conflicts do not occur
- providing the means to create and control references to essential evidence and proofs outside the workbook
A copy of ACBA-EWP can be obtained on request by email .
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 rationalize 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 preplanning their designs and Working Papers.
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.
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
- the Index page contains automated hyperlinks to the individual Working Papers
- the 'Edit a Working Paper' user form provides a structured search facility
- the 'Forwards' and 'Backwards' buttons on the shortcut toolbar allow the user to move quickly to recently edited Working Papers
- Cross-reference hyperlinks are designed to direct a reader specifically to relevant targets within the project
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.
Data Summary and Carry Forward Connections
One of the difficulties a designer faces is how to connect the data in one worksheet and summarize 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.
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 immediately below.
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
- calculation sheets
- standardised summary sheets and graphs
- diagrams and instructions for use
- checklists for completion of processes
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.
his 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.
- The most common type names the range at workbook level. So for example the name 'VAT_Rate' in cell A1 of Sheet1 of a workbook can be referenced from any cell (other than itself) on any worksheet within the workbook. Any attempt to give another range within the same name workbook the identical name will result in an error.
- The second type names a range at worksheet level. It is often referred to as a local name. The user can give one cell in every worksheet of his workbook the local name 'MySheetTotal'. This is very important characteristic because it means that the designer can call/summarise data from each sheet in his workbook based on a single named cell.
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 one of the standard EWP Working Papers and is given the worksheet name “A003”. It is a list with hyperlink pointers to all the external evidence referred to in your Project.
Last updated 10 October 2016