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