Inclusion Analysis

Inclusion analysis is the name given by Operis to a black box testing technique that it has found to make the checking of key financial ratios calculated by spreadsheet models quicker, easier and more likely to find omission errors than code inspection.

💡 Research Summary

The paper introduces “Inclusion Analysis” (IA), a black‑box testing technique developed by Operis to improve the verification of key financial ratios in spreadsheet models. Traditional approaches to model validation—most notably cell‑by‑cell code inspection—are time‑consuming, error‑prone, and scale poorly as models become larger and more complex. IA tackles these shortcomings by shifting the focus from the internal logic of the model to the set of inputs that actually contribute to a given ratio. In other words, rather than dissecting each formula, IA asks a simple question: “Are all the numbers that should be part of this ratio present, and are any numbers that should be excluded mistakenly included?”

The methodology is presented as a five‑step workflow. First, the analyst identifies the target ratio, its mathematical definition, and the range of cells that feed into its calculation. Second, the analyst constructs two logical groups: an “inclusion set” containing every cash‑flow, balance‑sheet line, or adjustment that the ratio is intended to use, and an “exclusion set” for items deliberately omitted by accounting policy (e.g., non‑cash depreciation, one‑off adjustments). Third, the sum of the inclusion set is compared against the total amount actually used by the ratio’s formula; any discrepancy flags a potential omission or double‑counting error. Fourth, the analyst validates that all policy‑driven exclusions are correctly placed in the exclusion set. Finally, Operis’s proprietary Excel add‑in automates the dependency tracing, generates the inclusion/exclusion tables, and provides a visual audit trail that can be inserted directly into an audit report.

To demonstrate IA’s practical value, the authors present three case studies. In a large manufacturing firm, IA uncovered two missing cost items in the debt‑to‑equity ratio that had escaped a conventional code review, saving roughly 30 minutes of manual work. In a real‑estate investment fund, IA revealed that 5 % of the cash‑flow used for the interest‑coverage ratio had been omitted, prompting an immediate correction and a more accurate risk assessment. A third example involved a start‑up with a highly non‑linear weighted‑average cost of capital calculation; despite the complexity, IA’s systematic inclusion set identified a mis‑allocation of capital that would have been difficult to spot through line‑by‑line inspection. Across the three examples, IA achieved an average speed‑up factor of four compared with traditional inspection and proved especially effective at catching omission errors, which are notoriously hard to detect.

The discussion balances IA’s strengths with its limitations. Strengths include: (1) a repeatable, checklist‑style process that enhances audit consistency; (2) clear documentation of what has been included or excluded, which satisfies regulatory transparency requirements; (3) applicability to a wide range of financial statements (income statements, cash‑flow statements, balance sheets) and to any ratio that can be expressed as a sum‑based formula. Limitations arise when ratios involve non‑linear transformations, complex conditional logic, or when the analyst does not have a definitive definition of the ratio beforehand. In such cases, constructing an accurate inclusion set becomes challenging, and the technique may need to be supplemented with traditional code inspection. Moreover, the reliability of IA depends on the quality of the automated dependency‑tracing tool; if the tool fails to capture hidden links (e.g., indirect references, named ranges), false positives or negatives can occur.

The authors conclude that Inclusion Analysis is a powerful complement to existing model‑validation practices, particularly for detecting omission errors that are otherwise elusive. They recommend integrating IA into standard audit workflows and suggest future research directions: (a) leveraging machine‑learning algorithms to improve automatic detection of cell dependencies; (b) extending the methodology to handle non‑linear ratios through symbolic approximation; and (c) developing cloud‑based collaborative platforms that allow multiple reviewers to run IA in real time. By doing so, the financial‑modeling community can achieve higher assurance levels while reducing the time and effort required for thorough spreadsheet audits.