In large projects, a single contractual document often known as the Contract End Item (CEI) governs the final product design. Each requirement of the contract is individually numbered to allow for performance verification of the end item. Because the projects are usually complex, this set of requirements is broken down and allocated to different subsystems.
These subsystems also have a set of requirements, and each requirement must be traceable to a requirement above it in the document hierarchy. No requirements should be introduced at a lower level, and each requirement in the upper level must be traceable to a document below it. The hierarchical relationship between the requirements is documented in a Traceability Matrix. The technique can be used for many kinds of hierarchical relationships in layered designs.
When I worked in the aerospace industry many years ago, such a matrix was maintained manually. Today, software takes care of this function. Another option, though, is to use Excel. I doubt that Excel's designers thought the auditing features would ever be used as an end in itself, rather than a means to an end. But it's possible because the auditing tool within Excel permits a graphical representation of the precedents and dependents of a particular cell.
Each item of the CEI is listed in one Excel column. (It would be possible to create a VBA program to read the document automatically and include the first line of the requirement together with the numbering, but that's beyond the scope of this idea.) The items of the lower document are listed in another. A third column would show the origin of the derived requirement (see the figure).
For instance, the CEI requirement 1.3.10 (in cell A11) traces to two lower-level requirements in cell C9 and cell C15. Therefore, cells D9 and D15 have the Excel formula =A11. Clicking on cell A11 and then following the sequence Tools | Auditing | Trace Dependents graphically shows which subrequirements depend on the principal requirement. If the principal requirement is modified, it's very easy to see which dependent requirements may need to be updated. The converse also is true. Given a particular lower-level requirement, it's easy to see what principal requirement was the driver using Tools | Auditing | Trace Dependents.
A subrequirement that derives from multiple primary requirements can be achieved using the concatenation of the cells in the reference coupled with a line-feed character. For example, if CEI requirements 1.3.3 and 1.3.4 drive requirement 2.1.9 of the lower document, cell D11 would contain the Excel formula =A4 & CHAR(10 )& A5. To achieve the multiple line effect, the cell should be formatted to allow Wrap Text (in the Alignment tab of the Format Cells dialog).
This worksheet, Traceability Worksheet.xls, can be found in the online version of this article. It includes macros that:
- Show the dependents of a selected cell
- Show the precedents of a selected cell
- Clear all of the dependents and precedents indicators. (Incidentally, saving the file also clears all of the indicators.)
Excel doesn't directly allow VBA to detect cells that have no precedents or dependents. But through the kluge of forcing an error, it's possible to create this functionality. I've also added two macros to search through a column and find the cells either without a precedent or without a dependent. When a cell is identified as missing the relationship, it is colored red. Finally, there's a macro to clear this indicator.