Plan and produce a spreadsheet model to solve a specified problem

Welcome to the Blueprint of Actuarial Modelling!

Hello there! If you’ve ever opened Excel and felt unsure where to start, you are in the right place. In the CP2 – Modelling Practice exam, your goal isn't to reach an elusive single "right" numeric answer; it is to build a model that is robust, transparent, logical, and accompanied by clear documentation that allows colleagues to follow, review, and reproduce your work.

Think of this chapter as learning how to draw the blueprints before laying the bricks. In this guide, we will walk through how to plan your approach and translate that plan into a professional spreadsheet model.

1. The Planning Stage: Think Before You Click

A frequent pitfall in CP2 is jumping straight into Excel without a strategy. Stop! Before you enter a single formula, you need a coherent plan. In Paper 1, this planning underpins both your spreadsheet design and the written Audit Trail you produce alongside it.

Why is planning essential?

A well-planned model prevents rework, saves time under strict exam conditions, and guarantees that your structure remains clean:
• Identify the raw data and parameters you have been given (Inputs).
• Determine the sequential methodology and intermediate stages required (Calculations).
• Define the tables, scenario comparisons, and charts you need to present (Outputs).

The Core Model Flow: Inputs → Calculations → Outputs

Every professional actuarial spreadsheet adheres to a disciplined flow:
1. Inputs & Parameters: Base data, user-defined inputs, and explicit assumptions.
2. Calculations: The calculation engine where intermediate steps and actuarial methods are executed.
3. Outputs: Summary tables, key performance metrics, and charts that directly address the problem.

Best Practice: Physically separate these three stages across distinct worksheets or within clearly labelled sections. Ensure your spreadsheet reads logically from left-to-right and top-to-bottom.

2. Good Spreadsheet-Modelling Practice

CP2 tests good modelling principles that ensure your model is easy to understand, verify, and modify by others.

Make Parameters Explicit — Avoid Hard-Coding

Hard-coding means placing fixed numerical values directly into calculation formulas (for example, writing \(=A1 \times 0.05\)). If that parameter changes, every embedded formula must be tracked down and updated manually.

Instead, define constants as explicit parameters in designated cells (e.g., cell \(\$B\$1\) labelled "Tax Rate") and link calculations to that parameter: \(=A1 \times \$B\$1\). This ensures the model is dynamic and adaptable to scenario changes.

Simplicity and Intermediate Calculations

Keep your formulas as simple as possible. Avoid dense, nested compound formulas. Breaking complex logic into transparent intermediate calculations makes error-checking straightforward for both you and your reviewers.

Formula Consistency Down Rows and Across Columns

Maintain consistent formula logic across columns or down rows. If you must deviate from a standard formula pattern, clearly flag the deviation so another user will not copy an inconsistent formula across.

Flags and Manual Steps

Use flags (values such as 0 and 1, or TRUE and FALSE) to activate or deactivate cash flows or policy states dynamically.
Example: A retirement pension paid only from age 65 can be modelled as:
\(\text{Pension Payment} = \text{Amount} \times \text{Eligibility Flag}\)
Where the flag is 1 if \(\text{Age} \ge 65\), and 0 otherwise.

Where manual interventions cannot be avoided (such as running Goal Seek or sorting a data table), highlight these danger areas prominently in both the workbook and your documentation with step-by-step instructions.

3. Model Integrity and Testing

Testing must be built into the spreadsheet as you build, not tacked on at the end. CP2 specifically allocates marks for automatic and reasonableness checks.

Automatic Mathematical and Range Checks

Build automatic formulas to alert you if logic fails:
• Reconciliation checks: Confirm that the sum of constituent parts matches the control total: \(\text{Check} = \text{Total} - \sum(\text{Components})\).
• Range checks: Ensure probabilities stay between 0 and 1, or that percentage allocations sum exactly to 100%.

Reasonableness Checks

Always inspect intermediate and final results for real-world plausibility under each scenario. Check that compounding periods, discount rates, and time horizons align with the underlying question narrative.

4. Documenting the Model: Audit Trail vs Summary

CP2 requires two distinct written documents across its two papers. Knowing the purpose and target audience of each is vital:

1. Paper 1: The Audit Trail
• Written for two specific readers simultaneously: a senior actuary (who requires an overview of data, key assumptions, methodology, and reasonableness checks without needing to look at Excel formulas) and a fellow student (who must be able to follow, review, and reconstruct the technical model logic without opening the workbook).
• Because the fellow student must understand every calculation, model mechanics, cell logic, and danger areas (like Goal Seek) must be clearly explained.

2. Paper 2: The Project Summary
• Addressed solely to the senior actuary, covering the entire project (both the original model and subsequent modelling).
• It provides a high-level review of data, methodology, assumptions, scenario outcomes, conclusions, and next steps.
• It must never include Excel cell references, as it is a communication tool for decision-making rather than a technical model-checking aid.

5. Summary and Key Takeaways

Modelling practice becomes intuitive when you follow standard structural habits:

• Plan First: Establish inputs, intermediate calculations, and final outputs before building.
• Structure for Clarity: Read left-to-right and top-to-bottom, keeping inputs, calculation engines, and summary results clearly separated.
• Parameterise: Never hard-code values; make assumptions explicit and easy to update.
• Build in Checks: Use automated cell checks and range tests to validate integrity immediately.
• Document with Audience in Mind: Detail technical methodology for the fellow student in the Audit Trail, and deliver high-level actuarial insights to the senior actuary.