Author: Guy Shepherd

In the life insurance industry, financial models—often built in complex spreadsheets—play a critical role in pricing, reserving, capital management, and strategic decision-making. These models are sophisticated, calculation-heavy, and require rigorous assumptions and projections over long time horizons. However, the very complexity that makes these spreadsheets powerful also makes them difficult to maintain, understand, audit, or transfer between teams—especially in the absence of proper documentation.

The Challenge of Spreadsheet Documentation

Despite the high stakes, many financial models in the life insurance sector suffer from inadequate or the complete absence of documentation. This results in several challenges:

1. Complexity and Scale: Life insurance models typically involve thousands of rows and columns, interlinked worksheets, and macros. Documenting such complexity can be time-consuming and is often perceived as non-value-adding by modellers under time pressure. Comments or perhaps excuses like “spreadsheets are self-documenting” are not uncommon across the financial modelling world.
2. Tacit Knowledge: Much of the model’s logic is often held in the modeller’s head. Without documentation, institutional knowledge is lost when staff move on, making model handovers difficult, increasing the risk of misinterpretation, and often leading to a replacement spreadsheet being developed as personnel move on.
3. Version Control Issues: Models evolve over time, often with quick fixes or ad-hoc additions that are not always tracked or documented. This creates confusion about the model’s current state and purpose of certain components. It can also lead to a proliferation of versions across personal and shared file systems, leading to uncertainty regarding the versions actually being used for business activities.
4. Regulatory Expectations: Regulatory frameworks such as Solvency II, IFRS 17, or local supervisory guidelines increasingly demand robust model governance. Poor documentation can lead to compliance issues and audit failures.

Why Proper Documentation Matters

Well-documented spreadsheets are not just a regulatory box-ticking exercise—they add genuine value to actuarial teams and the broader business. Key benefits include:

• Transparency: Clear documentation allows stakeholders to understand the logic, data sources, and assumptions used in the model, making the output more reliable and defensible.
• Efficiency: Good documentation saves time during audits, reviews, and model updates. Future users can quickly orient themselves without needing lengthy explanations from the original developer.
• Risk Reduction: Documentation reduces model risk by minimising misunderstandings, incorrect use, and accidental overwrites or deletions of critical logic.
• Support for Automation: Well-structured and documented spreadsheets are easier to convert into automated tools or migrate to more robust systems like Mo.net,

Key Features of Good Spreadsheet Documentation

When documenting a spreadsheet, the most important features to capture might depend on its complexity and purpose, but generally the following should be included:

1. Purpose and Scope

• What the spreadsheet is used for.
• The business process or function it supports.

2. Structure Overview

• The name and purpose of each sheet and what it includes.
• Description of named ranges and key sections (e.g., inputs, calculations, outputs).

3. Key Inputs

• Cells, ranges, or sheets where data is entered manually.
• Any data sources (e.g., imports from other files or systems).

4. Formulas and Calculations

• Important or complex formulas, especially those driving decisions.
• Named ranges and their definitions.
• Any custom functions (e.g., VBA macros or Power Query steps).

5. Outputs and Results

• Final results, summaries, or reports the spreadsheet generates.
• Where to find them and what they represent.

6. Dependencies and Links

• External files or data sources the spreadsheet pulls from or sends data to.
• Links to other worksheets and external workbooks.

7. Validation and Controls

• Data validation rules.
• Conditional formatting.
• Error-checking mechanisms.

8. Version History and Change Log

• Updates made, who made them, and why.
• Current version and date.

9. Security and Access

• Protected sheets or cells.
• Passwords or restricted editing settings.

10. Usage Instructions

• How to use the spreadsheet (with step-by-step guidance if necessary).
• Common actions, like refreshing data or printing reports.
• How to update the spreadsheet as part of regular business cycles (e.g. year end)

Taking the Pain Out of Spreadsheet Documentation

While the main objective of the Mo.net Model Adapter is the conversion of unmanaged spreadsheet-based models to fully managed Mo.net projects, a valuable byproduct of the migration process is a comprehensive analysis of the source workbook, far beyond that available from within Excel itself.

Figure 1 – Analysing workbook formulae with MMA

Figure 2 – Understanding worksheet dependencies with MMA

Rather than leaving this analysis hidden from the user, we have chosen to expose it interactively within Mo.net Model Adapter and allow it to be captured in a structured document, which can be further enriched if required. The analysis performed by Mo.net Model Adapter satisfies many of the documentation requirements listed above, and provides additional analysis not typically performed, due to the effort that’s usually involved. This includes analysing the risks associated with each workbook, which may help determine which spreadsheets require additional controls or perhaps refactoring.

Figure 3 – Structured documentation of a workbook with MMA

To complete the picture, two versions of the same spreadsheet can be compared and the differences analysed & documented in a similar way.
Mo.net Model Adapter is completely agnostic regarding the design or purpose of the spreadsheet, and can analyse any workbook used for any purpose, not just those used for financial modelling.

Conclusion

Financial modelling in life insurance is too important to be left undocumented. As the industry moves towards greater transparency, automation, and regulatory oversight, documenting spreadsheets is no longer optional—it’s essential. By harnessing the analysis & documentation functionality of Mo.net Model Adapter, insurers can reduce operational risk, improve efficiency, and build a more resilient modelling environment. Ultimately, good documentation is not just about compliance; it’s about clarity, control, and confidence in the models that underpin critical business decisions.