1. Home
  2. Docs
  3. SARM User Guide
  4. The SARM Spreadsheet Tool
  5. Tradeoff Details

Tradeoff Details

This worksheet allows users to drill down below each Quality Characteristic to compare the risk scores for each solution option by Sub-characteristic. The format of the display is similar to the Tradeoff Analysis worksheet, and this one is read-only. It will reflect any filter relating to Impact levels set in the Tradeoff Analysis worksheet in cell D2 or intangible benefits in cell K2. For each option, after the risk score (the colour shaded cell), you will see a percentage. This is a simple translation of the risk score to a percentage of the maximum possible risk score. This percentage has proved to be useful when creating alternative visual representation of risk scores using Powerpoint and Visio. A single strong colour can be used to shade a box for each characteristic or sub-characteristic, and the transparency attribute can be used to adjust the colour according to the risk score. A higher risk score will turn into a higher transparency percentage, so stronger colours will represent lower levels of risk, and weaker, paler colours will represent poorer risk scores. Of course, applying these percentages to a Powerpoint or Visio drawing is a manual process.

By default, this worksheet is specific to the Quality Model set in the References worksheet in cells G1 to N27. If that model is changed, then this worksheet will also have to be changed to reflect the new model and the structure of Quality Characteristics and their corresponding Sub-characteristics. The worksheet will automatically cope with up to six solution options, but users changing this sheet should remember to update all six columns so that any subsequent addition of new options will be accurately reflected on this page.

To update this worksheet, first unprotect the sheet (from the ‘Review’ ribbon) and insert or delete rows for the appropriate number of Characteristics and Sub-characteristics. Within a given Characteristic, an entire row, from Column D to Column V can be copied and pasted into a new row below to cater for an additional Sub-characteristic. For example, if a fourth Sub-characteristic is added to Functionality, cells D5:V5 can be copied and pasted into cells D6:V6. This will automatically pick up the name, scores and formats of the additional sub-characteristic. When you’ve finished, remember to protect the worksheet again to minimise the risk of unintended changes.

Adding a seventh Characteristic is a little more complex, but not much. You can copy the rows from an existing Characteristic and its sub-characteristics and paste them in new rows below the last current Characteristic. The formula for the cell that contains the Characteristic name in column C will need to be changed to point to the value of its corresponding cell in the References worksheet in the range G1..N1. Now update the following columns in the first row that corresponds with the Characteristic’s first sub-characteristic. For the name, in column D, the value should point to its corresponding cell in the References worksheet in the range G3..N3. Now set the correct reference for that row, column F. It should point to the relevant cell in column D of the Quality Model worksheet. Now you can copy that cell and paste it to the cells of the same row in columns I, L, O, R and U. The whole row is now correct, and you can select the row from column D to column V and paste it into however many more rows you need to reflect the total number of sub-characteristics that belong to that Characteristic.

You will now need to create an “Overall” row that reflects the scores of the Characteristic. As these are not a simple average of the rows that represent its sub-characteristics, the score needs to be taken from the Tradeoff Analysis worksheet. Copy an “Overall” row from one of the existing Characteristics, then adjust the cell in column F to refer to the right cell in Tradeoff Analysis. Now manually adjust the corresponding cells in columns I, L, O, R and U to reflect their counterparts in columns H, J, L, N and P respectively in Tradeoff Analysis.

Finally, all that remains is to ‘merge and center’ the Characteristic name cell in column C so that it covers all its constituent sub-characteristics.

How can we help?