Home › Welcome to ExcelMonkey.org › ABOUT › Using SAFER principles to design excellent spreadsheets

Using SAFER principles to design excellent spreadsheets

Many spreadsheets are required very quickly and as a result are often created in a chaotic way which makes then difficult to understand, update and adapt later on.  At ExcelMonkey we adopt a structured approach using the SAFER principles that we have developed.

SAFER stands for Structured, Appropriate, Flexible, Easy to understand, and Robust:

   S – Structured i.e. constructed in a logical and orderly manner (see section 1)

   A – Appropriate i.e. fit for purpose as specified by client/user (see section 2.3)

   F – Flexible i.e. capable of handling reasonably foreseeable changes in circumstances (see section 2.4)

   E – Easy to understand i.e. the client/user can follow the flow from data to outputs and is able to amend

   R – Robust i.e. the model includes check totals and completeness checks

 

The following Chart summarises the key aspects of SAFER

safer-smart-art

AS an accountant I also find it useful to link this approach to key accounting principles  as per Draft 2017 update of the IASB Conceptual Framework for Financial Reporting.

accounting-and-safer

The following sections give more details on how to apply these principles

1.    Structured

1.1 Use separate tabs for inputs, calculations and outputs:

  • Inputs
    •  – Fixed inputs – I.e. constants that will only be varied infrequently. These can be organisational items (such as product descriptions) or external items such as inflation. Put large lists of fixed inputs on a separate tab.
    •  – Variable inputs – i.e. data that will be updated frequently.  This is updated in line with either an internal or external reporting cycle.  Use different tabs for different variable inputs unless these datasets are structured identically
  • Calculations –
    •  Use separate tabs for different calculations
    •  Use a separate tab for data supporting charts and diagrams
    •  Use  a limited set of excel formulas wherever possible as this makes the model easier for the client to understand
    •  Keep calculations simple and transparent where ever possible
    •  Use range names where appropriate
    •  Never hard code a number into a calculation. If overrides are required by the client then allow for these to be input on a separate tab or in a separate section
    •  Where there is a requirement for overrides then use a separate tab for source data, overrides and total including overrides
    • Keep Calculations consistent within a block of cells
  • Outputs
    • Agree specification of outputs with owner of spreadsheet including any changes that become apparent. This includes Dashboard contents, Scenarios, DEatiled tables and charts etc.
    • Keep formatting consistent including tab colours
  • Workbook structure
    •            Use separators between input, output and calculations.  Separators are a blank tab e.g. called ‘inputs —>’  where appropriate they may contain a list of spreadsheets in next section
    • Use consistent tab names e.g. ACT 2016; BUD 2016; ACT 2015; FC1 2016; PLN 2017
    • Use hyperlinks to navigate (if required)
    • Avoid VBA unless absolutely necessary. Generally not many spreadsheet users have VBA skills and, therefore would not be able to maintain the code. Additionally, stylistically the way VBA code is written varies from one modeller to another which can create a barrier to updating the model if the original modeller is not available.
  • Individual worksheets structures
    • Include a heading for the sheet and a sub heading giving details of purpose and if appropriate the source of the data
    • Use columns and rows for a single purpose only
    • Have clear headings for sheet sections and for rows and columns
    • Use only one calculation within a specific block

1.3.    Appropriate

  • The model is fit for purpose as agreed in the specification.
  • It should reflect key business assumptions without becoming overly detailed.
  • It should be presented as an approximation of reality within agreed confidence limits. It should be clear that it is not reality itself.
  • Be clear with client of Excel is not the correct tool for their purpose

1.4.    Flexible  

  • The model should be capable of handling reasonably foreseeable changes in circumstances in the short and medium term i.e. up to 18 months
  • The model should be capable of being refreshed for flexible data updates
  • The model should be capable adapted in the medium term.  This should be for foreseeable (by the client) key factors (i.e. those that have a material impact on the outputs) only and should not cover every eventuality.
  • Scenarios are included where required.

1.5.    Easy to Understand

  • The client/user can follow the flow from data to outputs
  • The user is able to amend the model for expected changes
  • The model must be transparent to other modellors.
  • Include a flow diagram
  • Provide handover sessions which include a question and answer session
  • Provide training sessions on formula used if required

1.6.    Robust –

  • The model includes check totals
  • The model should include two-way flags to check for completeness checks e.g. to trap new data types included after a refresh
  • Embed controls into a model wherever possible
  • If there are many controls required then use a separate tab to summarise them
  • Use conditional formatting to highlight errors and issues
  • Use data validation to control inputs where specific descriptions are required
  • Thoroughly test update processes and include this in the user acceptance testing
%d bloggers like this: