CA2 model - best practice

Hi, just a few initial questions on the best practice for the excel model, based on having a look at Sept 2017 exam paper (paper 1)

1. Is it better to code revenue as + cashflow and cost as - cashflow and then sum everything for totals. Or would it be better to model all figures as + and then in Total Cashflow make the distinction which is + and which is -? Or it does not matter at all?
I feel that the model is more transparent using the first of the methods.

2. The instructions in point 2 of paper 1 ask for two types of checks:
- self checks
- reasonableness checks

Are these really two different types or are we just required to produce as many checks as possible within given time?

3. An idea of a check on calculations is for example recalculate that the total number of units of bread in the whole projection year do equate to the 25% - would this be an example of a self check?

4. Is it enough to just implement the check in the spreadsheet and then explain exactly what it checks and how it checks it in an audit trail?

 

1. There's no wrong or right answer here. The key is that what you're doing is clear in the model, and correctly described in the audit trail. I agree with you, though, that it's a bit simpler having the signs of cashflows right, so that you just add them up using a single SUM() function rather than a B5 + C5 - D5 - E5 + F5 type equation.

2. Self checks are there to ensure that the model is working right. It's a check that a goal seek meets its target, or that mortality rates are 0<qx<1, or that probabilities add up to 1. Reasonableness checks are there to show that you understand what you're doing, and that the answer you're getting makes sense. If the scenario calls for the end bank balance to be higher than the base scenario, then you'd expect the salary being drawn to decrease. These are key differentiators for borderline cases - get into the habit of trying to figure out what the answer will be before you get it from your model, and then write down what you did as a check.

3. Yes. Or that the total number of units of all items add up to 1000 (if I recall correctly what the total was). (Although, be careful that you're not just checking that Excel works - if you calculate px = 1 - qx, it's no point then checking that px + qx = 1. In this example, you're calculating one of the items as a balancing figure, so checking that the total = 1000 (while I think it actually got a mark on the marking schedule) is a bit pointless...

4. Yes. But label it in the spreadsheet so that it's clear what it's checking. Don't just have a bunch of cells that say "OK". Either make the check result more descriptive, or have a short label "Check total units over year:" which then ties up to the language you use in the audit trail.

Good questions!

 

Thanks for such quick response.

I also wanted to check that the choice of Pie Chart for Paper 1 Sept 2017 Question 2 (ix) and (x) would be appropriate? The solution spreadsheet shows bar charts.

 

Also few more questions:

1. Do we get marks for formatting? I think I would struggle for time if I take extra time for pretty formats as shown in example model solution.

2. How do you split your time between the model and audit trail of 3 hours?

 

On the Pie charts - the reason for the bar charts is that it allows for a more direct comparison between the three scenarios. You'd have to have three separate pie charts, and you lose the connection between the scenarios. In general, pie charts should be avoided unless its blatantly obvious that they're the only possible solution. In the 9 years I've been involved with CA2, I can think of only 1 paper where the model solution had a pie chart.

There are a few marks for formatting, but it's more along the lines of making things clear, rather than making things pretty. So In the audit trail, you need paragraphs, and bold headings to make it easy to follow the flow. And in the spreadsheet, you need bold headings, and highlighting formulas that don't copy down. Anything beyond that is nice to have. Remember that clarity is much more important than beauty.

As to splitting time, that will depend on you - how quick are you at creating a model in Excel (using keyboard shortcuts, etc), and how quickly do you type. I'd suggest at least 30 minutes planning, 20-30 minutes at the end to read through, and then a rough 50/50 split. If you don't finish the model in that time, do the audit trail as far as you've got, then go back to the model. Have a look at the mark schedule to see which parts of the audit trail are more important - I often see students spend pages producing a comprehensive overview and overly thorough data section, but then don't finish the methodology description which gets more marks.

Hope that helps.

 

Thanks for all this information. It is really helpful. Can I also please check if the CA2 exam paper 1 and paper 2 start at 10 am UK time?

 

Hi a couple more questions on the FAQ section of the CMP materials.
3-4 assumptions are required in the audit trail for Paper 1 (genuine assumptions and not statements relating to parameters)
Question:
1. Are we saying assumptions here would be for example "the model assumes monthly time steps"? Or examples relating to some simplifications of the model if we are short of time?
2. Should we include them in parameter section of the model as a list of all assumptions or only in the audit trail?

 

Hi, is it best to create two separate sections in the audit trail:

1. parameters - only model parameters
2. assumptions - other assumptions made i.e. simplifications in the model etc.

 

Does a chart for data checks score extra marks if not asked for explicitly in the question?

 

Hi, for the exam paper 1 from Sept 2015 we are asked to simulate 0 for a fail and 1 for a pass.
The exam solution simulates 0 for a pass and 1 for a fail in tab "Base no wds".
Am I missing something?

 

In March 2015 paper 1 solution spreadsheet tab AvE, the chart contains a red line drawn across all bars. I've never come across this type of chart before. Was the red line added manually or is there a special method of creating this type of chart?

 

For Paper 2, are there any major differences in what the examiners are looking for when working with Excel model?

 

Does 50/50 split for time also apply to paper 2? Would you first work on excel for 1.5 h and then 1.5 on summary? Or go back and forth?

 

Hi, the April 2017 paper 2 uses some scatter type diagrams. I wouldn't normally choose this diagram but rather a bar type comparison. Would I loose marks? When are scatter type diagrams most appropriate? How do you draw a line + scatter dots like in the solution

 

The IOA's guidance on CA2 states that students should be expected to know INDEX and OFFSET functions. I have not really seen many examples of these functions and have not used them at my work. Am I still ok for CA2?

 

For Paper 2 the IOA says: "Do not simply copy and paste the text given to you in the audit, but amend the content to be appropriate to a senior actuary. Similarly, adjust any text copied from the exam instructions. "

What changes are required apart from not writing in actuarial formulae? Less detail?

 

Paper 1 - What if some of our reasonableness checks don't work and there is not time int he exam to think and correct all issues....? Do I just comment on the result of the check and that it requires further investigation i.e. in "next steps"?

 

Actually, I've seen that actuarial formulae are being used in the Summary Paper 2 (example Paper 2 March 2015 - cash flow modelling - qx are used).

So what does it exactly mean adjust for senior actuary?

For example, don't cover basics such as describing what the symbols mean and keep it high level without too much explanation?

 

Hi Viki2010, I would advise you take the preparation day offered by ActEd. You have some very interesting and important questions there which you can ask to the tutor directly during the prep day. I did the prep day online and found it very useful. I passed CA2 in the recent sitting and I was glad to have taken the prep day and did some of the mock exams and got them marked. It worked for me so I would highly recommend. They do give some pretty good guidance and would answer questions you have during the tutorial. Hope that helps and best of luck

 

Thanks, I've done a prep day and will do the MAP A.

 

Thanks for your advice. I did the prep day but it didn't help me pass this exam.