Programme Syllabus

Syllabus Version 1.5

Mitigating against Spreadsheet Risks in your Organisation with the Recognised Skills Standard.

Spreadsheet Safe® Syllabus Copyright Q-Validus © 2016

1.1 SETUP 1.1.1 Prepare Determine that a spreadsheet application is fit for the proposed purpose. State spreadsheet purpose clearly, and indicate any assumptions. List the requirements. Give the author name, change history, version number. Set, examine spreadsheet file properties. Set out any conventions used, such as  calculation methods or functions, the meaning of formatting and styles. Recognise that spreadsheet data formats such as formats for dates, numbers and codes should be explicit, appropriate and applied consistently.
1.1.2 Organise Lay out spreadsheet content logically and coherently over a clearly organised worksheet scheme. Recognise the value of using a summary or index worksheet to help users navigate and understand the organisation of the spreadsheet. Save, backup and verify distinct spreadsheet versions. Apply strong password protection, using mixed case, and non-alphanumeric characters (at least 8) to protect against unauthorised access. Recognise different macro security levels in spreadsheets and apply appropriately.
1.2 INPUTS 1.2.1 Controls Place a single instance of a given constant (eg conversion or tax rates) in separate cells. Break down more complex formulas into smaller component parts to help with readability, comprehension, and for ease of update. Apply named ranges to make formulas more manageable. Use manual and automatic calculation modes.
1.2.2 Integrity Make units of measure explicit. Apply the precision as displayed setting. Import fixed length or delimited text file (eg CSV) into a spreadsheet and validate that the data types are correct. Reconcile the integrity of data from external sources as complete, consistent and correct.
1.3 CALCULATE 1.3.1 Formulas Use mathematical and logical formulas and functions in a spreadsheet. Correct the order of precedence in mathematical operators. Understand the concept of circular references in a spreadsheet. Recognise and remove circular references. Recognise array (matrix) formulas in spreadsheets. Check number and type of function arguments (Lookup function). Check for and correct relative, absolute and mixed cell references. Create worksheet, spreadsheet links, and validate that they summarise, update correctly, and are complete.
1.3.2  Errors Check for missing input values. Check for and correct missing precedent cells. Apply ISERROR, ISNA functions. Correct #DIV/0! occurrences where appropriate. Check for and correct ####, #VALUE!, #NAME, #REF!, #NUM!, error values.
1.3.3 Totals Create cross-totals as a way to validate totalling. Check for and correct errors in totalling caused by row, column insertion or deletion. Check for and correct double counted sub-total errors. Check for and correct mismatched cross-totals. Check for and correct automatic sum total errors.
1.4 OUTPUT 1.4.1 Data Show hidden data by changing custom formats (font or background colour), hidden zero values. Distinguish format decimal commands and round functions. Apply format decimal commands, round functions. Export data as CSV, tab or other file format, and validate as correct. Print and proof spreadsheet outputs.
1.4.2 Charts Create a chart based on source data. Switch between different chart types. Check the orientation of a chart so that all data series are visible. Apply appropriate chart axes, orientation, scales, titles and annotation to enhance chart meaning. Express data in a chart meaningfully by choosing an appropriate chart type.
1.5 AUDIT 1.5.1 Review Understand how spreadsheet criticality, risk and potential business impact determine the extent of review and control requirements. Submit the spreadsheet for independent review, approval before circulation. Recognise the need for periodic re-review. Run and validate test cases, with typical and extreme values, for all calculations. Check outputs using alternate calculation methods. Un-hide rows, columns, worksheets. Un-hide formulas. Inspect formulas for logic and output accuracy. Recognise the presence of advanced features in a spreadsheet such as macros and pivot tables.
1.5.2 Validation Use IF function to test for call values being within expected ranges. Review for data type mis-entry (eg text entry for numeric characters.). Apply conditional formatting to highlight errors. Apply validation criteria: values, whole numbers, and decimals. Apply validation criteria: date, time, character lengths. Apply custom validation criteria.
1.5.3 Laws & Guidelines Be aware of data protection legislation or conventions in your country. Be aware that spreadsheets may need to be controlled as part of regulatory requirements. Recognise that spreadsheets may be controlled records and subject to archive requirements in legislation. Recognise the significance of disability / equality legislation in helping to provide all users with access to information.