Paper 1 - Audit Trail - Reference to location of data

Discussion in 'CP2' started by Viki2010, Apr 3, 2019.

  1. Viki2010

    Viki2010 Member

    Hi, is there any value in referencing the cells/columns of each input data used in the model? This isn't in the exam solution examples.
     
  2. Dar_Shan0209

    Dar_Shan0209 Ton up Member

    Hello,

    Can you please tell me in details what do you mean - I'll try to help you if I can.
     
    Last edited: Apr 8, 2019
  3. Viki2010

    Viki2010 Member

    Hi Dar_Shan, thanks a lot for being really eager to help. The exam is coming soon!
    I was looking at Audit Trail documentation from past papers. In some exam solutions, I would see data being described in a more high level than in other exam solutions. In some exam solutions, reference to columns (or sometimes cells) can be seen.

    So I was wondering if I can loose marks by not referencing specific cells in the description of Data section in the Audit Tail.
    Or is it best to be as detailed as possible, just in case....
     
    pyuille_1609 likes this.
  4. Dar_Shan0209

    Dar_Shan0209 Ton up Member

    Hi Viki,

    Pleasure is mine. Exams in a week. The whole point of the audit trail is to be able to provide guidance to the reader without the need for him/her to have a look at the excel itself. With regards to what you wrote above, I don't think the data is described in a high level but the methodology.

    For example, "the interest rates are looked up from the "Parameters" worksheet and stored in Column A. Etc.."

    This is as expected as maximum signposting is a requirement. You need to detail what has been done such that if ever, someone would catchup later. He/she will understand perfectly what has been done.

    I hope this answers your question.
     
    pyuille_1609 likes this.
  5. Viki2010

    Viki2010 Member

    Thanks a lot Darshan. I will stick to the rule "maximum signposting" in DATA, PARAMETERS, METHODOLOGY, GRAPHS.
     
  6. pyuille_1609

    pyuille_1609 Member

  7. Dar_Shan0209

    Dar_Shan0209 Ton up Member

    Hello guys,

    For data, you don't need to signpost. You just say which data has been provided and in which worksheet it is found. But for Methodology and Graphs, the examiners (according to mark scheme) search for a lot of signposting in your audit trail.

    I quote: "To score well in the audit trail, candidates need to describe the modelling steps as well as signpost where in the worksheet the calculation has been carried out. Signposting may be provided by reference to the worksheet, tables or rows and columns of the worksheet. Almost all candidates signposted the calculations by reference to the relevant worksheet and a significant proportion of candidates provided further detail by signposting the relevant cells."

    I hope this one helps.
     
    pyuille_1609 likes this.
  8. Benoy Soman

    Benoy Soman Member

    Hi Darshan,

    Just to build on your point. I usually just include a snip of the parameters I'm using, which I usually keep in a separate tab. I also include the column and row headings in the snip as well. Then under my Parameters section of the audit trail, I just say here is a snip of the parameters I'm using. I think if the references are visually shown in this instance, is that sufficient do you reckon?

    Thanks,
    Benoy
     
  9. Viki2010

    Viki2010 Member

    Hi guys, I just went through some sample solutions to audit trail and the DATA worksheet as you said does not really include the signposting. However, if we are asked to check the data, I would assume that we need to make note where these checks are carried out. Is that right? I would do it just in case.
     
  10. Dar_Shan0209

    Dar_Shan0209 Ton up Member

    Hello guys,
    @Viki : Yes, in the data section, you write which data have been provided. Afterwards, you mention about data checks and data validation.
    @Benoy; I quite don't get your p.o.v, can you please add on what you said.
     
  11. pyuille_1609

    pyuille_1609 Member

    Following this post, but I agree with @Dar_Shan0209 ,
    Please elaborate... @Benoy Soman , do you mean like a printscreen or a copied-pasted extract from your Parameters tab?
     
  12. Benoy Soman

    Benoy Soman Member

    Hi guys, apologies - by 'snip', I was referring to the snipping tool which is essentially the same as a printscreen. So yes, it would be a copied-pasted extract from my Parameters tab. I just figured this approach would suffice, so long as the parameters tab had clear names for each of the parameters as well.
     
  13. Dar_Shan0209

    Dar_Shan0209 Ton up Member

    Hi Benoy,

    Thanks for being much clearer this time. Under the Parameters tab, I would have personally include all the parameters that I would use and define formula for the same that would be used in the whole worksheet. This would be separate from the raw data worksheet. In the audit trail, I would not include snap but I would talk about the different parameters I have put in the worksheet and include any warning messages where any user(s) can change the parameters (for example, I would mark all parameters cells in blue). See, how this is done in the mark scheme.

    I hope you get my point.
     
    pyuille_1609 and Benoy Soman like this.
  14. Viki2010

    Viki2010 Member

    Hi Benoy, I have never seen any model solution using a "snipping pic" except for the goal seek picture in the excel file. I think the audit trail document should only include descriptions and no pictures.
     
    pyuille_1609 and Benoy Soman like this.
  15. Viki2010

    Viki2010 Member

    Some past papers required a candidate to create a new tab with DATA_CHECKS, and link the data to the ORIGINAL DATA. Also fixes were required and a lot of checks. Normally the DATA and DATA_CHECKS would be a separate section in the audit trail. I would then assume that reference to cells, rows etc of data checks in DATA_CHECKS worksheet would be required.
     
  16. Benoy Soman

    Benoy Soman Member

    Hi guys, thanks for your replies, taken onboard!
    It's frustrating because some of the past paper solutions like Sept 2018 only have a brief line on Parameters, whilst the September 2017 solution goes into a lot of detail. And then some papers don't have a parameters section in their audit trail. So just trying to weigh up between time and detail.
     
    pyuille_1609 likes this.
  17. Benoy Soman

    Benoy Soman Member

    Just a follow up question guys on the parameters, aside from describing the parameters included in a separate tab, would you also specify their cells references as well? Or would this be too much detail? e.g. "I've included a parameter for the rate of inflation (cell B6)"
     
  18. Dar_Shan0209

    Dar_Shan0209 Ton up Member

    Hi Benoy,

    Usually key information such as expenses information, level of inflation etc goes in the Parameters tab. The data tab covers only data that has been provided by a source, for example, mortality table, interest rates for 25 years, etc.

    If you get data from a source, better keep it separate from your parameters as you would need to have data checks with the data in an another tab, as mentioned above by Viki. If you have got no external data, put everything in the Parameters tab.

    Whatever you do, document this in your audit trail. Data section of audit trails should include everything that has been provided to you whereas parameters section of the audit trail should just include which parameters are being used throughout your worksheet which can be changed by user. This is why you will see in marking schemes, the purpose for this worksheet is "This sheet sets out all the parameters used in the spreadsheet." and at the end there is a note that "It is to be noted that where named ranges have been defined these are detailed in red italic adjacent to the relevant cells." which is in line with what I mentioned earlier.

    I hope this helps. If you need further assistance, inbox me.
     
    pyuille_1609 and Benoy Soman like this.
  19. Benoy Soman

    Benoy Soman Member

    Hi Darshan,

    Thanks a lot for your help - much appreciated!
    Ah yes I recall from one of the papers we didn't receive external data, so there wasn't a need for a separate data tab in the model.
    So yes, you're right - in this instance everything would go in the Parameters tab. And in the audit trail, we could just note any standalone inputs that have been provided, which have been incorporated as parameters in the Parameters tab.

    Thanks again and will inbox if I have any further queries. Very kind of you to help everyone.
     
  20. Benoy Soman

    Benoy Soman Member

    Just a follow up on this.
    In the audit trail, is it worth also specifying the cell references for the parameters, from the Parameters tab?
     
  21. Dar_Shan0209

    Dar_Shan0209 Ton up Member

    Hi Benoy,

    Glad to help wherever I can.

    With regards to your query above, I have never sign posted exactly for Parameters except for Methodology and Charts Worksheets. The fact that you mention the purpose of the sheet for me is clear. Additionally, I quote from April 2017 Audit Trail Solution:

    " The following data has been provided by the Finance and Actuarial departments of GIL for each line if business, and can be found on the “Parameters” worksheet:
    • Forecast premium volumes for 2018
    • Reserves and Excess Assets as at year end 2017
    • Expected margins for business written in 2018
    • Estimate growth rates for premiums and reserves
    • Current dividend payout ratio
    Also the Solvency capital percentage parameters from the Regulator are given.
    Where named ranges have been defined, these are detailed in red italics alongside the
    relevant cells. The data provided is assumed to be correct but should be independently verified"

    The examiners have not sign posted for Parameters and I follow the same rule. The essential in the Parameters worksheet is to be able to list all parameters that are linked in the calculations such that if an another user wants to change the same, he/she will be able to do that automatically which will be reflected in the calculations.

    Cheers.
     
    pyuille_1609 likes this.

Share This Page