The rules in this section apply generally to workbook design and/or all worksheets in a model.
FAST 1.01-01 Separate worksheets by type: Foundation, Workings, Presentation, and Control
Following on from the principles of good automotive design, worksheets within a model should be grouped within the following four functional classes:
- Foundation, including sheets for inputs, timing flags, indexation factors: the model’s chassis or main underpinnings. In adapting a model, re-designing elements of the foundation, particularly time structure, are the most hazardous operations;
- Workings, i.e. the build-ups of calculations leading to presented results: the model’s ‘engine’;
- Presentation, including financial statements, charts, primary commercial inputs, and summary results: the model’s dashboard and primary showroom selling points; and
- Control, e.g. check sheets, control of sensitivities and scenarios, change-tracking, list of pending changes, version control, and table of contents: the model’s main control devices and engine status indicators.
Each of these functional groups has a different audience (model driver vs. model mechanic) and hence a different design priority.
An issue that arises in the preceding analysis that often causes design challenge and confusion is the dual role of inputs: on the one hand foundation and on the other presentation and/or control. Where should they be placed? Input organisation is an important design choice; the pros and cons of different approaches should be considered carefully. Should Input sheets ever have calculations? Should inputs ever be located on Workings sheets?
Design specifics for each of family of worksheet are presented in the relevant sections in Chapter 2: Worksheet Design:
- input sheets are described in section 2.04, [page 23];
- presentation sheets are described in section 2.05, [page 24]; and
- control sheets are described in section 2.06, [page 26].
FAST 1.01-02 Maintain consistent column structure across all sheets
Set up a standard column definition and apply this across all sheets if at all possible, even if this causes a requirement for ‘extra’, unused columns on some sheets. For instance, the column used for labels, constants, units, and first column of given time series can usually be conformed across all sheets in a model.
FAST 1.01-03 Maintain a consistent time ruler throughout the model
FAST-1.01-03.1 except when multiple time resolutions are required
Presuming the model can be designed with a consistent time resolution throughout (e.g. monthly, quarterly, annually), each worksheet in the model should have an identical time axis. This means each worksheet uses the same column for the start of the time ruler and each time ruler should run to the same length, even if this means that some worksheets have unused columns.
Inconsistent time rulers in different parts of the model cause confusion; keeping the time ruler as consistent as possible vastly improves readability and reduces the possibility that serious errors are missed during the review process.
FAST 1.01-04 Ensure primary time rulers span time frames of secondary rulers
In some circumstances, a model may require different time resolutions and hence different time rulers with a different timing frequency, for example a ‘secondary’, monthly resolution construction period followed by a ‘primary’ quarterly operations period.
In these cases, ensure that the primary time ruler encompasses the higher-resolution time period such that summarization of data from both periods can be effected more easily.
FAST 1.01-05 Proliferate links to maximize navigation efficiency
Repeated links in the model have the dual benefit of increasing formula comprehension by co-locating ingredients, more formally known as precendents, alongside the formula itself, within a single calculation block. This is one of the cornerstones of the FAST Standard as it is a singularly effective means of increasing the transparency of a financial model.
Links have the additional benefit of increasing navigational efficiency within a model. The inbuilt CTRL + [ keyboard shortcut will go straight to the source of a link, where there is a single link in the reference. F5, ENTER returns to the original link location. This greatly increases ease of review of the model user within a given calculation block and increases navigational efficiency throughout the model.
Note: for users of non-Qwerty keyboards, CTRL + [ will not work. The standard FAST Format Macros book contains a work around for those keyboards, with alternative keystrokes assigned to SHIFT + CTRL + J to following the link, and SHIFT + CTRL + K returning from a followed link.
As stated in FAST 3.06-02: Do not create daisy chains; do not link to links, page 37, all links should point back to the original source calculation and should never be daisy chained. Daisy chained links impair the navigational effectiveness of links by requiring the user for following multiple steps to locate the original calculation, and by destroying the efficient ‘return’ operation possible with F5, ENTER.
FAST 1.01-06 Mark exports with red font and imports with blue font
While the Standard does not attach any philosophical importance to the choice of colours per se, one of the intentions of the Standard is to engender a shared language of modelling across practitioners. There is a case therefore for all users of the Standard adhering to the same colour convention for imports and exports, simply to reduce the incremental effort required to decode a model where the Standard convention has not been followed. Normally the argument for not following the Standard convention comes down to personal preference on the part of the modeller, which, as aesthetically sensitive as they individual may be, is outweighed by the industry network-effect of shared communication protocols.
FAST 1.01-07 Calculate only once
A given calculation should appear only once in a model. While this may sound obvious, it is often violated in practice. Even if it is simple to do otherwise, ensure that subsequent requirements to display or use a set of figures are created by a direct link back to the source calculation, not by repeating a calculation. (The rationale for this principle is similar to the rationale that an input assumption must appear only once in a model.)
FAST 1.01-08 Use normally positive convention on Workings sheets
The normally positive convention sees all figures in a model as positive and the direction of the value – whether it is coming in or going out – is suggested by the label. Positive labels such as revenues and receipts indicate that something is coming in and negative labels such as expenses and expenditure indicate that something is going out.
FAST 1.01-09 Use in-flow / out-flow convention on Presentation sheets
Flows can be of two types, either an inflow or an outflow. A clear distinction should be made between the two. In order to comply with user expectation and thereby enhance model readability, inflows should be represented as a positive value and outflows should be represented as a negative value. This is the inflow / outflow convention.
FAST 1.01-10 Do not overuse macros
Consensus position being developed for subsequent draft.
FAST 1.01-11 Never release a model with purposeful use of circularity
Circularity is Excel’s in-built capacity to iterate to a solution. Test for lack of convergence, for example insufficient debt commitments, rather than setting up models to converge automatically; this often reflects commercial reality anyway. Circular models inevitably suffer from the modeler being blinded by precision over the principle of accuracy.
This has an excellent conceptual foundation. In particular, I agree that it is important that the intended audience is being considered in the design: “different audience (model driver vs. model mechanic)”. This would allow the formation of a different set of duties/roles, purposes and functions – which is more suitable than building one something for all.
The idea of Links is also very informative. In my experience, they have helped me reduce spreadsheet errors as well as reducing the amount of time spent switching in-between worksheets.
I have a couple of additions to be made to the elements “Foundation, Workings, Presentation, and Control”:
DATA layer. Does the current model provide for an area which is purely raw data? (e.g. data that has been downloaded from a database). This could either be separate, or be part of “inputs” within the “Foundation” (although this depends on the way that “inputs” is defined). I have sometimes worked with spreadsheets involving a vast quantity of data that must be cleaned/filtered/sorted/grouped before it becomes used in formulas, and possibly remapped.
Scenario. For example, supposing that we are extracting product sales data from a database, e.g. weekly data, and all the data is downloaded in thousands figures (rather than units). But, suppose that all the formulae in our workbook are based on the analysing monthly data, and in units (rather than thousands form).
Solution. What is the best way to organise the transformation of the raw data? I believe that this is an important question in terms of maintenance and updatability. (The way that I do this is by having one sheet which has raw data direct from the database, and then another sheet which multiplies the currency data by 1000 to convert it to units, and another sheet to add the data and group it so that it is now monthly instead of weekly).
I believe it is important for us to consider how to deal with these data transformation issues (because a lot of institutions may rely on external data, rather than manual inputs), and to think about where does Data fit into our model – i.e. is it in the Foundations layer? Or should Data be a separate layer? Also, should there be any specifications regarding conventions about data connections, naming associated with these connections, guidelines about transformation of data, etc. I see Data as a critical issue because if connections and transformation of data is not done correctly and logically, we could have missing and/or incorrect data, and the functions would have to perform unnecessary data operations.
Thus, I recommend that the standard-setters consider “Data” (transformation and integration of data), how it fits in with the existing conceptual models, and develop guidelines and conventions about the pre-processing of external data for suitable use by the “Workings” layer.
(It could be useful to analyse common existing design patterns such as MVS, MVVM, and consider whether we can learn from their advantages and disadvantages, and use this knowledge to help refine the current design).
- Valentina