ESM v11.1 Release Highlights
Main highlight #1: Spreadsheet template conformance capability
A key focus for this release was to allow writing rules, which can be used to check if a given spreadsheet conforms to a pre-defined template. As part of this release, an example Risk Check Profile called “Model Template Conformance” is included. This template provides a sample set of rules based on the ICAEW financial modelling code. These rules can be copied and tweaked to match your own template. Below are some of the new capabilities that support writing these new rules:
Cell Dependency information
ESM now internally tracks whether a cell is an input cell, output cell or a calculation cell based on how it is referenced from elsewhere within the workbook. This information is available via a Risk Rule property called Dependency Type. The classification is defined as follows:
- Input: A cell which does not have a formula and is referenced from another cell(s)
- Output: A cell which has a formula but is not referenced from another cell(s)
- Calculation: A cell which has a formula and is referenced from another cell(s)
- Independent: A cell which is neither a formula nor is referenced by other cell(s)
Cell Style information
You can write Risk Rules that can check the font or back colour of any cell.
Figure 1 Rule to check that cell colour matches actual dependency type
Rules which trigger when nothing matches
Before you could only write rules which triggered when a given object such as a cell matched the rule expression. This is useful in situations such as checking for Excel errors as you check for presence of error cells. This release adds the ability to check for absence of objects which match a given rule. For example, you can now check if a workbook has an Assumptions worksheet. This is supported via the availability of the “Not Matched” trigger option.
Figure 2 Rule which triggers on absence
Combining the new capabilities
With the new capabilities outlined above you can write some extremely powerful rules. Examples include:
- Checking that cells formatted as input, output or calculation, are actually input, output or calculation from a strict formula chain perspective.
- Checking that worksheet tabs formatted as input have only cells highlighted as input
- Checking for presence of check cells
- Checking for presence of standard worksheets such as Assumptions, Description, etc
- Checking for presence of standard names such as Tolerance
- Checking for feeder cells and ensuring they are formatted correctly
See the included “Model Template Conformance” profile which covers a number of these scenarios.
Main highlight #2: Risk Check profiles and rules improvements
This release includes a number of improvements which make the entire flow of creating risk rules to testing them a lot more easier. When developing a new Risk Rule or new Risk Rule Profile, the user goes through the steps of creating a risk rule, adding it to a profile, running the profile and checking that the rule or profile works as intended. To make the entire process easier a number of additional actions have been to the various screens:
Risk Rule page improvements
Existing Risk Rules (including the newer rules included as part of the Model Template Conformance) can be copied to create your own version.
On the Risk Rule page you can click the 
button to add the Risk Rule to one or more profiles. On addition of a rule to a profile, you can easily navigate to the profile using the links in the success message.
Risk Check Profile page improvements
Testing a profile by running a Risk Check is now easier. From the View Profile page, click 
to run one or more Risk Checks with that profile.
Adding rules to a profile is also simpler via the Filter capability in the Add Rules dialog.
Figure 3 Adding Risk Rules to a profile
Main highlight #3: Cell change review improvements
The side by side view of the worksheet has been rewritten from the scratch as part of our UI upgrades. This has improved the responsiveness of the worksheet views as well as enabling the ability to resize rows and columns.
Figure 4 Side by side view with resized columns