[PDF] Practical, structured design rules for financial modelling

28903_2FAST_Standard_02b_June_2016.pdf

FAST Standard 02b June 2016 / 1

The FAST Standard

Practical, structured design rules

Version/ FAST Standard 02b June 2016WWW.FAST-STANDARD.ORG

2 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 3

Welcome to the FAST Standard

The FAST Philosophy

How Rules are Organised

Workbook Design

Worksheet Design

The Line Item

Excel Features Used in Modelling

Appendix A: FAST Formatting

Appendix B: FAST Terminology

Appendix C: Rules in Short

Contents

Introduction

1.0 2.0 3.0 4.0 5.0 6.0 7.0 i

Introduction/

Welcome to the FAST Standard

3 8 9 10 13 19 20 22
28
29
32
48
54
56
62

4 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 5

Welcome to the FAST Standard

the structure and detailed design of spreadsheet-

This standard set of rules provides both a clear

The FAST Philosophy

The FAST Standard is published openly

and regularly revised by the FAST Standard

The Signatories to the FAST Standard believe

6 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 7

ɉ

The FAST Acronym

Ɉ Spurious precision is distracting, verging on dangerous, particularly when it - - Ɉ -

AppropriateFlexible

Structured

Transparent

The FAST

Philosophy

8 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 9

How Rules are Organised

chapters, then drill into advice on structuring individual chapters into sections and level are collected in section

1.0/ Workbook Design on page 13

collected in section

2.0/ Worksheet Design on page 22

3.0/ The Line Item on page 32

can be used, but with caution are listed in section

4.0/ Excel Features Used in

Modelling on page 48

A LivingDocument

-

10 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 11

FAST Terminology on page 56

workbook

DocumentConventions

12 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 13

1

Section 1.0/Workbook DesignThe FAST Standard/

14 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 15

and purposeful structure applied with consistent discipline at the workbook

The high-level layout of a

model Ɉ

1.0WorkbookDesign1.01General WorkbookDesignPrinciples

The rules in this section apply generally to

workbook model. Separate worksheets by type: Foundation, Workings,

Presentation and Control.

worksheets within a model

1. Foundation

2. Workings,

3. Presentation

4. Control

Ɉ Ɉ 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 Ɉ

FAST 1.01-01

16 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 17

FAST 1.01-02

FAST 1.01-03

FAST 1.01-04

FAST 1.01-05

࠮ ࠮ and ࠮ Maintain consistent column structure across all sheets. constants,

Maintain a consistent

time ruler throughout the model Except when multiple time resolutions are required worksheet Ɉ Ensure primary time rulers span time frames of secondary rulers model Ɉ

ɈɈ

Ɉ

Proliferate

links ɉ model calculation block Ɉ

FAST-1.01-03.1

FAST 1.01-08

ɉ ɉ page be daisy chained.Ɉ ɉ Mark exports with red font and imports with blue font of colours per se, one of the intentions of the Standard is to engender a shared imports and exports, Ɉ Ɉ

Calculate only once

A given

calculation created by a direct link source calculation, not by repeating a Use normally positive convention on Workings sheets

FAST 1.01-06

FAST 1.01-07

18 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 19

FAST 1.01-09

FAST 1.01-10

FAST 1.01-11

Do not overuse macros

Never release a model with purposeful use of circularity ɉ Ɉ

1.02SheetOrganisation

FAST 1.02-01Arrange sheets so that calculation order

Except to group

Input and Results sheets

FAST-1.02-01.1

FAST 1.02-02

FAST 1.02-03

Do not attempt to optimise calculation layout and user interface / presentation on the same worksheet Ɉ

Workings

Separate

and factors onto dedicated sheets indexation factors

20 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 21

FAST 1.02-04

FAST 1.02-05

FAST 1.03-01

Separate

Workings sheets

into functional 'chapters' "one-sheet wonders"

Minimise inter-linking between sheets

Organising calculations across

worksheets in a workbook should, in part, also Ɉ ɉ

1.03MultipleWorkbookModels

Do not split a

model across multiple workbooks Except when more than one modeller must work concurrently

ɈɈ

Except when a single workbook would be too large and intimidating ɉ Ɉ Ɉ Ɉ

FAST-1.03-01.1

FAST-1.03-01.2

FAST-1.03-01.3

FAST 1.03-02

FAST 1.03-03

FAST 1.03-04

links workbooks Use import / export sheets for line items passed between workbooks links are used, these are func - inputs, Ɉ - links should be Named

FAST-1.03-02.1

22 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 23

2

Section 2.0/Worksheet Design

2.01UniversalLayoutPrinciples

FAST 2.01-01

FAST 2.01-02

FAST 2.01-03Each column should have a single and consistent purpose

Constants

series line items. display totals, units,

Series worksheets

time axis only Except series inputs sheets to avoid too many sheets

Except where local exceptions warranted

sheet ɉ

Make only two columns matter

line item will be constructed via consistent model -

FAST-2.01-02.1

FAST-2.01-02.2

24 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 25

FAST 2.01-04

FAST 2.01-05

FAST 2.01-06

FAST 2.01-07

Calculation

and left to right.

Mark intra-sheet

with grey shade

Limit

to opening balance positions calculation - page 18

Present information horizontally

Except for short vertical series for scenario structuring Except where vertical layout is more clear for printing for presentation / printing or where input structures are best laid out this way to

FAST-2.01-07.1

FAST-2.01-07.2

FAST 2.01-08

Do not hide anything sheets ̶

FAST-2.01-08.1

FAST-2.02-01.1

FAST-2.02-01.2

FAST-2.02-01.3

FAST-2.02-01.4

FAST 2.02-01

FAST-2.01-08.1

2.02CalculationBlocks

- calculation The other rows above this calculation contain the precedents to the calculation, each in turn a link directly to the source line item. These precedents, the 'ingre - so-called live labelling. Construct all calculations in a separate calculation block

Except when the calculation block is a

balance corkscrew

Except when

cascading calculations are warranted

Except when the calculation is a

trivial formula ɉ design solution sheets' -

26 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 27

FAST 2.02-02

FAST 2.02-03

FAST 2.02-04

FAST 2.02-05

FAST 2.02-06

Build calculation blocks so they can be replicated links

List common

calculation block components in a consistent order List precedents in the order they appear in a formula

Except when this violates a 'pyramid' layout

Use corkscrew calculation blocks for balance accumulation - ࠮ ࠮ and ࠮ PPF. partial period factors indexation factors calculation block

FAST-2.02-04.1

FAST 2.03-01

FAST 2.03-02

FAST 2.03-03

FAST 2.03-04

FAST 2.03-05

FAST 2.03-06

2.03Header Design

The time axis is best placed on the worksheet

only once in a freeze pane Display a single end-of-period date in a freeze pane particular area of the model so that all values presented adhere to a single time axis. Include a column counter for cross-reference on F11 quick charts

Include master error

checks and alert indicators in the freeze pane SMU on Presentation sheets

28 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 29

FAST 2.04-01

FAST 2.04-02

FAST 2.04-03

2.04Input Sheets

Organise

inputs both by structure and commercial area separating constant series

Include a dedicated instruction /

comments column on Input sheets The

Create self-documenting Input sheets

Together with readable code, the

model -

FAST 2.05-01

FAST 2.05-02

2.05PresentationSheets

model's Ɉ

ɈɈ

Ɉ Use Presentation sheets to present the model's results Ɉ - ࠮ calculation ࠮Ɉ ࠮ Ɋ ࠮

A model must completely explain how it works without the need for other software applications to present the model outputs

input, calculation

30 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 31

FAST 2.05-03

FAST 2.05-04

FAST 2.05-05

FAST 2.05-06

FAST 2.05-07

Provide a description of the modelling standards and method used to build the model

Provide a description of the

̶ calculations ̶ Provide keys to colour coding, abbreviation, named ranges, and functions model Selection of chart type should correspond to the nature of the data being presented Ɉ 1 , and Stephen Few's 2 Ɉ Charts should be formatted for ease of comprehension of the main messages being communicated

FAST 2.06-01

FAST 2.06-02

2.06ControlSheets

Control sheets

allow the model -

Provide a table of contents

- -

32 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 33

3

Section 3.0/The Line Item

FAST 3.01-01

FAST 3.01-02

constant or a series or a balance

Provide clear indication for

constants vs series purpose,

Treat

line items as the smallest indivisible object in a model link display total

3.01Line ItemTaxonomy

34 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 35

FAST 3.01-04

FAST 3.01-05

FAST 3.01-06

FAST 3.01-07

FAST 3.01-03

Do not use a

series structure to present constants model as/if

This rule applies to

inputs constants

Do not use row totals in

model logic (certainly not a balance (constant display totals. (This rule is

Include

display totals on all - this rule is

Do not include

display totals on balances

Except when the

line item includes a single balance

Place

display totals on the left where they are visible

FAST-3.01-06.1

FAST 3.01-08

FAST 3.02-01

FAST 3.02-02

FAST 3.02-03Make numbers look like what they are with smart format

Formulas must be consistent

Except when marked as

temporary code

Series

Mark temporary code clearly - ets and yellow shade, along the entire row unless the reason for the status will

Do not use partial range references

3.02Formula Design Fun-damentals

FAST-3.02-01.1

36 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 37

FAST 3.03-02

FAST 3.03-03

FAST 3.03-04

3.03Formula Simplicity

FAST 3.03-01

Do not write a formula longer than your thumb No formula should take more than 24 seconds to explain

Do not write multi-line formulas

- - Ɉ

Use a limited set of Excel functions

FAST 3.03-05

FAST 3.03-06

FAST 3.03-07

FAST 3.03-08

Never use nested IFs

models.

Do not use Excel Names

Except for external links

FAST-3.03-08.1

38 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 39

FAST 3.03-10

FAST 3.03-11

Do not use a space as an intersection operator Beware circularity or #ERRORs protected on inactive branch of IF function - Except as a substitute for cell references in macro coding Except where it is impractical to display locally an ingredient in a calculation block Ɉ Except when it is a poor design choice to display locally an ingredient in a calculation block e.g. error check tolerance value

Except in data validation lists

Ɉ Ɉ Ɉ Except for text based criteria in conditional formatting Ɉ Ɉ

Do not construct array formulas

Except when Excel's Data Table feature is being used Except when calculation cannot be achieved without arrays Except when the logic bloat required to avoid arrays creates a solution that ɉ

FAST 3.03-09

FAST-3.03-09.1

FAST-3.03-08.2

FAST-3.03-08.3

FAST-3.03-08.4

FAST-3.03-08.5

FAST-3.03-08.6

FAST-3.03-09.3

FAST-3.03-09.2

40 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 41

FAST 3.04-01

FAST 3.04-02

FAST 3.04-03

FAST-3.04-01.1

FAST-3.04-01.2

3.04Formula Clarity

Do not write formulas with

embedded constants

Except when

constants are universal Except when constants are deliberately embedded to avoid their manipulation idea, as both clarity of model ɉ

Include spaces between arguments in formulas

Do not use parentheses in formulas unnecessarily

Except when they may improve formula clarity

FAST-3.04-03.1

FAST 3.04-04

FAST 3.04-05

FAST 3.04-06

FAST 3.04-07

ɉ sign switches Do not include current sheet references in formulas Do not use elements that appear to be non-structural in model logic

Do not over-anchor

Ɉ- disrupt the ability to copy calculation blocks Ɉ page 26

42 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 43

FAST 3.05-01

FAST 3.05-02

FAST 3.05-03

reviewer through each step of what otherwise would

Provide a label for all

line items model

Invest time in drafting a good label

line items All line items must have a unique label calculation, Ɉ

3.05FAST LabellingConventions

Include a units designator on all

line items

Except it can potentially be omitted when

SMU applies "Label everything with a units designator; a missing unit is somethin g that needs attention. Absence of information to convey meaning is a fundame ntally weak design". of units is for

Standard Monetary Units

Choose a capitalisation convention and stick with it model

Include the word "balance" in labels of

balances , this and balances is clear to anyone reviewing the model.

Include the word "cash" when Label is otherwise

ɉ

Unit designators must be clear and unambiguous

FAST 3.05-04

FAST 3.05-05

FAST 3.05-06

FAST 3.05-07

FAST 3.05-08

FAST-3.05-04.1

44 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 45

FAST 3.05-09

FAST 3.05-10

FAST 3.05-11

FAST 3.05-12

FAST 3.05-13

Include units in the label

Ensure alternative sign version of

are clearly labelled

Ensure distinction between opening and

closing balances is clearly indicated

Presentation

sheet. ɉ conventions of 'brought forward' and 'carried forward' balances are often ɉ

ɉɉ

ɉ given is that closing balances are very often reported on presentation sheets, ɉ Maintain labelling consistency pedantically and precisely ɉ line items. Unit designators must be consistently applied throughout the model

FAST 3.06-01

FAST 3.06-02

3.06Links

FAST-3.06-01.1

Row anchor all

links

Except when setting up to replicate sections

links when setting up a calculation block source

Do not create

daisy chains ; do not link to links input or calculation ,

46 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 47

FAST 3.07-01

FAST 3.07-02

FAST 3.07-03

FAST 3.07-04

3.07Timing Flags and PPFs

Use are being applied to

Include

display totals on all and PPFs

48 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 49

4

Section 4.0/Excel Features Used in Modelling

FAST 4.01-01

the FAST Standard has been drafted on the ࠮ ࠮ ࠮ ࠮

4.01ExcelFunctions

Use the INDEX function over the CHOOSE function

50 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 51

FAST 4.01-02

FAST 4.01-03

FAST 4.01-04

Do not use the NPV function - ever

Do not use OFFSET or INDIRECT functions

- ROUND

FAST 4.02-01

FAST 4.02-02

Do not merge cells

ɉ Ɉ

4.02Formatting Features

52 / FAST Standard 02b June 2016FAST Standard 02b June 2016 / 53

FAST 4.03-01

FAST 4.03-02

Only use Names scoped to the workbook level

Ɉ Ɉ

4.034.04Excel NamesData Validation

4.05

4.06Group

Outline

Macros/

VBA Consid-erations

FAST Standard 02b June 2016 / 5554 / FAST Standard 02b June 2016 5

Section 5.0/Appendix A: FAST Formatting

5.00Appendix A:

FASTFormatting

FAST Standard 02b June 2016 / 5756 / FAST Standard 02b June 2016 6

Section 6.0/Appendix B: FAST Terminology

Ɉ - -

6.00Appendix B:

FASTTerminology

A LERT B

ALANCE

B ASE C ASE C

ALCULATION

C

ALCULATION

B LOC K C

ALCULATION

ORDER FAST Standard 02b June 2016 / 5958 / FAST Standard 02b June 2016 Ɉ Ɉ

CALCULATION SHEET

C

ASCADING

C

ALCULATION

CHEC K C ODE R

EPLICATION

C

ONSTANT

C

ONTROL

S

HEET

C OR K

SCREW

C

OUNTER

-

FLOW

D

ASHBOARD

D AIS Y C

HAIN

D ATA R ANGE D

ISPLAY TOTAL

E

MBEDDED

C

ONSTANT

E

XPORT CALL UP

Ɉ - Ɉ - source is guaranteed, and a single change on source will refresh throughout the -

FAST STANDARD ORGANISATION

F LAG FLOW I

MPORT

I

NDEXATION

FACTOR I NPUT I

NDEXATION

FACTOR L INE I TEM L IN K L IVE L

ABELLING

MODEL N

ORMALLY POSITIVE

C

ONVENTION

FAST Standard 02b June 2016 / 6160 / FAST Standard 02b June 2016 - - on Results sheets, see Presentation Sheets, page 29 ɉ

ONE-SHEET WONDER

P

LACEHOLDER

R

ESULTS PRESENTATION SHEET

S

ERIES

S

IGNATORY

SMU S

OURCE

S

TANDARD

M

ONETARY UNIT

T

EMPORARY CODE

T IME A XIS / T IME R

ULER

T IMING F

LAG PARTIAL PERIOD FACTOR

WORKBOOK

W OR K

SHEET

W OR K INGS U NITS D

ESIGNATOR

FAST Standard 02b June 2016 / 6362 / FAST Standard 02b June 2016 7

Section 7.0/Appendix C: The Rules in Short

7.00Appendix C:

The FASTStandardRules inShort form.

64 / FAST Standard 02b June 2016 FAST Standard 02b June 2016 / 65

Chapter 1:

Workbook Design

ɉ

ɈɈ

16 16 16 16 16 18 18 18 19 19 19 19 20 20 20 20 20

20Page

Chapter 2:

Worksheet Design

and left to right ɉ readable design solution 21
21
21
21

26Page

FAST Standard 02b June 2016 / 6766 / FAST Standard 02b June 2016 and functions data being presented 26
26
26
26
26
28
28
29
29
Page

Chapter 3:

The Line Item

Page

68 / FAST Standard 02b June 2016 FAST Standard 02b June 2016 / 69

ɉ ɉ Page Page ɉ clearly indicated

70 / FAST Standard 02b June 2016 FAST Standard 02b June 2016 / 71

Chapter 4:

Excel Features Used

in Modelling

72 / FAST Standard 02b June 2016

Version/ FAST Standard 02b June 2016WWW.FAST-STANDARD.ORG