Welcome to the Blueprint of Actuarial Modelling!
Hello there! If you’ve ever opened Excel and felt a wave of panic because you didn’t know where to start, you are in the right place. In the CP2 – Modelling Practice exam, your goal isn't just to find the "right" number; it’s to build a model that is so clear, logical, and well-documented that another actuary could pick it up and understand it instantly.
Think of this chapter as learning how to draw the blueprints before you start building a house. In this guide, we’ll walk through how to plan your approach and then translate that plan into a professional spreadsheet model. Let’s dive in!
1. The Planning Stage: Think Before You Click
The biggest mistake students make in CP2 is jumping straight into Excel. Stop! Before you touch a single cell, you need a plan. In the exam, this is often captured in your Summary Document (or Audit Trail).
Why is planning so important?
Imagine trying to bake a complex 5-tier wedding cake without a recipe. You might end up with something edible, but it’ll probably be messy, and you’ll definitely forget the sugar. Planning helps you:
• Identify what data you have (Inputs).
• Decide what steps you need to take (Calculations).
• Define what you need to show at the end (Outputs).
The "Input -> Calculation -> Output" Flow
Every good model follows a logical flow. We call this the Salami Sandwich approach (okay, we don't really, but it helps to remember!):
1. The Bottom Bread (Inputs): Data, parameters, and assumptions.
2. The Filling (Calculations): The "engine" where the math happens.
3. The Top Bread (Outputs): The final results and charts.
Quick Tip: Always keep these three sections physically separate in your workbook. Use different tabs or clearly defined areas on one sheet.
2. Structuring Your Spreadsheet Model
Once you have a plan, it's time to build. A professional model must be transparent and flexible.
Avoid "Hard-Coding" Like the Plague!
Hard-coding is when you type a number directly into a formula, like \( =A1 * 0.05 \). If the tax rate changes from 5% to 6%, you have to find every formula and change it manually. Yikes!
Instead, put 0.05 in a cell labeled "Tax Rate" and point your formula there: \( =A1 * \$B\$1 \). This makes your model dynamic.
The Golden Rule: Consistency
If you are calculating a row of values, the formula in the first cell should be exactly the same (copied across) as the last cell. If you change the logic halfway through a row, you're inviting errors to the party.
Using "Flags"
A flag is a simple 0 or 1 (or TRUE/FALSE) used to turn a calculation on or off.
Example: If a pension is only paid after age 65, your formula might look like:
\( \text{Pension Payment} = \text{Amount} \times \text{Age Flag} \)
Where the flag is 1 if Age \(\ge\) 65, and 0 otherwise.
Quick Review: Best Practices
• Label everything: Every row and column needs a clear heading.
• Units matter: Always state if numbers are in \(\$\), \(\text{£}\), percentages, or thousands.
• Format for humans: Use bold for headers and borders to separate sections.
3. Data Integrity and Checks
Don't wait until the end to check if your model works. Build checks as you go! This is a core part of the CP2 curriculum.
Internal Consistency Checks
Does the sum of the parts equal the whole?
Analogy: If you cut a pizza into 8 slices, no matter how you rearrange them, you should still have 1 whole pizza. In your model, you can check this with a simple formula:
\( \text{Check} = \text{Total Assets} - (\text{Asset A} + \text{Asset B}) \)
If the result isn't 0, you've found a bug!
Reasonability Checks
Step back and look at your output. If you are calculating the life expectancy of a human and the answer is 450 years, your logic is probably wrong.
Common Mistake: Forgetting to convert annual interest rates to monthly rates when working with monthly cash flows. Always check your time steps!
4. The Summary Document: Your Audit Trail
In CP2, the spreadsheet is only half the battle. You must produce a document that explains your model. This is where you prove you know what you're doing.
The "Stranger Test"
Could a fellow student (the "Stranger") follow your document and recreate your model exactly? If not, you need more detail.
What to include in your plan/documentation:
1. Objective: What problem are we solving?
2. Data & Assumptions: What are we starting with? What did we assume (e.g., "Inflation is constant at 2%")?
3. Method: High-level steps of the calculation. Don't write out Excel formulas here; use words or mathematical notation.
4. Checks: Explain what checks you performed and why.
Did you know? Many students lose marks not because their math is wrong, but because they didn't explain why they chose a specific method. Documentation is "free marks" if you are disciplined!
5. Summary and Key Takeaways
Don't worry if this seems like a lot of rules at first. Modelling is a skill that gets better with practice. Just remember these three pillars:
• Plan First: Draft your approach in the Summary Document before opening Excel.
• Be Logical: Use an Input -> Calculation -> Output structure.
• Be Transparent: No hard-coding, use clear labels, and build in error checks.
Key Takeaway: A CP2 model isn't a "black box" where data goes in and magic comes out. It is a story told through numbers and logic. Make sure your story is easy to read!
You've got this! Practice building small models using these steps, and soon it will become second nature.