Page Content

Related Pages

ACBA Spreadsheet Design Facility - User's Guide

Activate Design Facility You can also review summaries of previously prepared ACBA Model Descriptions from the link.

Introduction

Although spreadsheets are ubiquitous in business, the ease with which spreadsheets can be put together and the huge range of analytical and arithmetic facilities that are available to the user, makes the process of describing them logically very difficult.

This on-line facility provides a controlled means of describing the fundamental elements of a spreadsheet such that a spreadsheet designer/programmer can construct a system or process in spreadsheet format that  meets the user/client's requirements. While the facility is intended to help professional designers and programmers get better and more useful information about the spreadsheet systems they are asked to build, in practice the detail needed to complete the descriptive process will often lead the user resolve for him or herself the design issues associated with building the spreadsheet. If this happens then the facility will be useful addition to the reference material, instructions and training available to spreadsheet users.

The Process

The process takes place in the following distinct phases

After each phase there is a verification process. This validates your entries for the minimum requirements and ensures that your entries do get posted into the database. The buttons that will take you on to the next phase do not appear until the verification process has been satisfied.

Three of the phases are iterative. That is, the system expects you to enter a series of inputs, processes and presentational requirements. While the natural logic of the facility is for you to enter these in sequence, after each verification process you are given the opportunity to decide what to do next. This includes skipping forwards, jumping backwards or just viewing every thing you have entered before deciding what to do next. Indeed if you want to stop writing up the description part way through to think about it, then it is possible to leave the description and come back to it using the Viewing Module. But remember to take a note of your quotation and organisation references.

Collect Customer Details

These are collected at the very beginning because each description held in the on-line database is defined, in part, by reference to the customer who requested it. In order to generate a unique reference for you and your system request we have to know who you are, otherwise you will not be able to review your own systems descriptions.

General Description of the Spreadsheet

This form has five fields. All of them must be completed.

Spreadsheet Title A short title for your spreadsheet. It is useful if this can also serve as a windows file name.
Spreadsheet Function This drop down list has five general descriptions of types of process. They, of course, overlap but the purpose of this field is to give the designer an appreciation of what you see as the most important aspect of the design.
No. of Users This is pre-entered as 1. Where there are more than one users then a detailed security consideration of the more sensitive fields becomes a significant issue.
Spreadsheet Description This is a free text field. There is no limit to the length of your description.
Spreadsheet Size/No. of Calculations The size of a spreadsheet is important. Some of the most frequent errors occur when attempting to copy formulae. It is more secure to have the spreadsheet and its formulae pre-prepared. The description here might say-

one line for every day of the year, or

need room for 500 tests per month.

The level of detail you put here depends on the overall complexity or sophistication of your requirement.

Inputs

There are nine fields for completion on the Input form, but only the fields marked with an asterisk (*) are compulsory.

Field Name* A short descriptive name for the field. Helps identify it quickly.
Input Type* Either 'Fixed' or 'Variable'. 'Fixed' fields tend to be entered only once on a spreadsheet and are used as constants within formulae. 'Variable' fields represent a column within a series of transactions or records, often a sequence of rows.
Input Description* A fuller description of the Input.
Input units Like or Ft/sec. Can help the designer verify that he has all the Inputs necessary for a particular process or calculation.
Field Data Type Limited to a fixed list of types. Frequently data types are self determined. It is only essential to make an entry here where the data type is unexpected. For example a number must be treated as text.
Field Format For example "Number, 2 decimal places"
Field Validation Source Where did the information come from? How can you verify that it is correct, should it be necessary?
Field Validation Type Limited to a fixed list of standard validation types in Excel.
Field Validation Limits This should be completed if there is an entry in 'Validation Type'. In Excel, users are required to enter minimum and maximum limits for numeric validations. For fixed lists, enter the list values if practicable.

Calculations and Processes

There are nine entry fields on this form. The five fields marked with an asterisk (*) are compulsory.

Process Description* A written description of what the calculation or process does.
Process Type Limited to fixed list of possible entries. These can indicate the sophistication or complexity of the calculation .
Input Source 1* A fixed list determined by the Inputs that you have posted already.
Input Source 2 Any process may have a number of Inputs. The fields Input Source 2, Input Source 3  allows the user to identify the second and third input source for a calculation.
Input Source 3 The fields Input Source 2, Input Source 3  allows the user to identify the second and third input source for a calculation.
Other Input Where there are more than 3 sources of input, the user should note the remaining sources here.
Result Type* Limited to 'Interim' or 'Final'. This determines how the result is classified and what you can subsequently do with the data. Essentially 'Interim Results' are posted back to the Input Data table and can be re-used to generate further calculations.
Result Name* This identifies the result of the process for reference purposes. It must be unique, so that you can differentiate from other input sources and final results. The system automatically verifies the uniqueness of field names.
Result Description* Bound to be similar to the 'Process Description' above, but this description is used in the Inputs or Final Results table depending on the 'Result Type'.

Presentational Requirements

There are ten fields in this form, but only two (the first and last) are compulsory. Essentially there are three ways of presenting results - comparing a primary result with one or more other results, selecting specific results for detailed analysis or summarising results.

Frequently users would prefer to undertake their own analyses ad hoc, rather than have prep-prepared presentations. In such cases users of the facility may wish to skip this form entirely.

Primary Result Field* This is a drop down list of all the results and inputs you have previously generated. Indicate which is the most important field so far as the presenting the results.
Compare with If you are comparing two fields e.g. Last Year vs. This Year, then post the second field from the drop down list.
Multiple Compare Note any fields that form part of a series for comparison.
Compare Commentary Describe what it is you want compared and how you would like to see it presented.
Select From If you want to look at the component parts of a summarised result (for example as a pivot table) note the field you want analysed here.
Select By Entries limited to a list of 'Item', 'Time/date Period' or 'Value Range'. Enter the selection methodology here.
Select Commentary Expand on the details of how you want to see the selected items presented here.
Summary Function Results can be summarised in a variety of ways. You can use this drop down list or ...
Summary Commentary Describe the summary you want more flexibly in this commentary field.
Present As* You must enter an appropriate item from this drop down list.

Completing the Specification

Once you are satisfied you have entered all the components of your system/spreadsheet, then selecting the 'COMPLETE' button will generate a summary of the inputs, calculations, final results and presentational requirements posted for that system specification. It will lock the specification preventing further editing. The process will also automatically generate an email to the user giving the reference details and the cost of preparing a 'Feasibility Study' based on the specification.

Additional Help

ACBA maintains a discussion forum for its software users. This includes users of the Spreadsheet Design Facility who are also welcome to join. The Group home page is http://groups.yahoo.com/group/ACBA_Functions.