Model documentation: the Audit Trail
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
Model documentation: the Audit Trail
4.1 TAS 100
On the subject of models, the aim of TAS 100 is that any models used in technical actuarial work
shall be fit for the purpose for which they are used and be subject to sufficient controls and testing
so that users can rely on the resulting actuarial information.
This means that:
• the logical structure of the model reflects that of the issue being studied
• the model is a practical tool which can be used to accomplish its objectives
• the model comes with thorough documentation
• the significant limitations of the models are recorded, both in the documentation and in the
report to the user.
The model documentation, which in our case is represented by the Audit Trail, should contain
sufficient detail for a technically competent person with no prior knowledge of the model to assess the
judgements and assumptions made, as well as how the model actually works. The documentation
should be clear, unambiguous and complete for its purpose.
TAS 100 requires that a model include internal checks, both of the kind we looked at previously
and also in the form of reasonableness checks (see at the end of subsection 4.3). The checks must
be included in the model documentation.
For Paper 1 of the CP2 exams, students are required to build a model in EXCEL to address the
task which has been set. The model must be a reasonable representation of the problem and any
assumptions made by the student should be documented. The student is required to produce an
Audit Trail of the model, which should cover the documentation requirements TAS 100.
4.2 Target audience
The principal audience for your Audit Trail is the person who is going to have to work on your
model after you have been moved on to some more exciting work.
At the same time, you should bear in mind that your supervisor might take a look at your work to
see if it achieves the standards required by the company, so you should also include some higher-level
comments.
Do not simply reproduce the formulas used in individual cells of your worksheet: instead, explain
clearly in words the methods applied and the reasons behind them.
4.3 The audit approach
There are various possible approaches to writing an Audit Trail. Some people like to write them
as they go along, whereas others prefer to wait until the end. A popular philosophy is to wait until
you come to the end of a block of work on the spreadsheet, check that it is working OK, and then
write it up in the Audit Trail before going on to the next block.
Same document or different document?
Although it is possible to construct a good Audit Trail by interspersing comments in between the
sections of calculations on the spreadsheet, it is very difficult to do this well, as you generally need
to ensure that the user finds it convenient to refer back to the previous parts of the Audit Trail
1
and this is hard to do when it is so spread out. Thus, typically, the Audit Trail either occupies
an extra worksheet (tab) in the workbook or forms an entirely separate (e.g. Word) document.
** HINT ** For obvious reasons, to keep the AT in an entirely different document (file) is
less preferred as it requires to keep the model and the AT files together, which can easily get
lost/separated. A reasonable practical approach might be to draft the Audit Trail in a separate
file (e.g. Word) and then transfer everything to a separate worksheet of the model, once finished.
Alternatively, you might also find it handy to keep the AT sheet opened in a new window during
editing, which allows easy(er) editing and/or navigation.
Format of the Audit Trail
You should primarily aim to produce a piece of connected prose rather than list of bullet-points.
Bullet-points have their place – for example, when you are listing danger areas – but should be used
sparingly.
Mostly, the commentary on the results of your exercise should only be in the Summary (see Sec-
tion 5), but an exception occurs when the result of one calculation has influenced your approach to
the next section of the task. In that case, you should explain at the top of the new section what
aspect of the result encouraged you to continue with the task in the way you did.
Subdivisions of the Audit Trail
The Audit Trail normally consists of a number of main sections, such us:
1) Task description
2) Data
3) Validation of the data
4) Assumptions
as well as a sub-section for each part of the task, or for each worksheet.
Task description
** WARNING ** Do not assume that whoever is reading your Audit Trail has had access to
the same materials as you.
Thus, always give a concise description of the task so that the reader knows what is going on.
You are allowed to use some of the words and phrases from the Task Description supplied in the
question paper, but should not just copy it entirely. Here you can also clarify your own interpretation
of the aspect(s) of the task that you feel are not entirely obvious (see also the Assumptions section).
Colour coding and other useful information
If you are using a systematic colour-coding scheme in the model, you should explain this in theAudit
Trail. Indicate which cells are for users to input data/parameters and, particularly, whether there
are any restrictions on the values that can be used.
Data and data validation
State what data have been provided, and don’t forget to specify what the source was (i.e. if they
are known) for each part of the data (or set of parameters, tables, etc.) and how reliable you think
they are.
Outline actions taken to validate the data. If changes were required, state what changes you made
and why you think the amended values are reasonable.
2
Assumptions
You should spell out the places where you needed to make assumptions, what assumptions you have
made and why.
If there are assumptions which apply only to one particular part of the work, it is OK to put those
in the relevant section of the Audit Trail, but any assumptions which apply to the whole task
should be mentioned at the start.
What to include for each main section
The techniques you have used to obtain your results are an important feature of the Audit Trail.
You should try to steer between the extremes of too much detail (e.g. “In cell A3 the formula
=VLOOKUP(Sheet1!C7,Sheet1!$B$2:$D$100,3,0) was entered and then copied down into the next
99 cells.”) and too little detail (e.g. “I used a lookup table.”). It is also a good idea to discuss
how you might have done things differently, but there is not always time to specify a number of
alternatives.
You should mention any danger areas flagged up on the spreadsheet and what the user should do
about them if the inputs to the spreadsheet are changed.
Reasonableness checks
Often when you are working through a calculation your spreadsheet produces a value and you stop
and think:
** HINT ** “Is this a sensible value? Is it what I expected to get? Does it match the values
elsewhere in the calculation?”
Further, “Is it sensible to clear a certain amount of borrowing within the length of time indicated
by the model? Is it reasonable to put a certain number of boxes in a given container (i.e. with a
specified max capacity of weight/volume)?”; etc.
These are examples of reasonableness checks that one can consider in a model in the form of simple
conditional (Error/Check) flags (i.e. short automated text messages using logical functions), which
are strongly encouraged. You are often doing this kind of check without realising it. Further, you
should make sure that you also mention (and even discuss) them in the Audit Trail, as there are
marks for reasonableness checks.
Thus, the reasonableness checks:
• should be applied at every stage
• arise when comparing the outputs resulting from different scenarios
• should be carried out if/when you make adjustments to values of parameters
When drafting the AT then insert many sense/reasonableness-checks (e.g. verifications/comments
to the intermediate results). However, not all of these have to appear in the model itself as formulae
(i.e. only the most important ones, or those that are not immediately obvious from the model - just
by visual inspection).
** HINT ** The main audience/target of the sense-checks is the line manager.
4.4 Audit Trail Exercise: back to Geronia
Retrieve the spreadsheet you have been working on. Open a new tab for the Audit Trail
or a new Word document and write up the work you did up until now. Start with an
introduction (e.g. brief task description), then include a section on the data and the data
3
validation you performed. If you have made assumptions, state them clearly. Then talk
about the method you have used to project the population.
Do not forget to include mention of checks you have made; for example, comment on whether
or not your projected population in 1010 was roughly what you have expected.
4.5 How marks are allocated for Paper 1
The marks for each aspect of the paper are not completely fixed, but they do not vary too much.
The figures below are a reasonable approximation to the average number of marks allocated on each
of the various versions of Paper 1 which are publicly available.