V
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.
-
We are pleased to announce that the winner of our Feedback Prize Draw for the Winter 2024-25 session and winning £150 of gift vouchers is Zhao Liang Tay. Congratulations to Zhao Liang. If you fancy winning £150 worth of gift vouchers (from a major UK store) for the Summer 2025 exam sitting for just a few minutes of your time throughout the session, please see our website at https://www.acted.co.uk/further-info.html?pat=feedback#feedback-prize for more information on how you can make sure your name is included in the draw at the end of the session.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
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.
Can you please tell me in details what do you mean - I'll try to help you if I can.
Last edited:
V
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....
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....
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.
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.
V
Viki2010
Member
Thanks a lot Darshan. I will stick to the rule "maximum signposting" in DATA, PARAMETERS, METHODOLOGY, GRAPHS.
P
pyuille_1609
Member
This helps! Thanks for asking @Viki2010 and thanks for responding @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.
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.
B
Benoy Soman
Member
Hi Darshan,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.
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
V
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.
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.
@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.
P
pyuille_1609
Member
Following this post, but I agree with @Dar_Shan0209 ,I just say here is a snip of the parameters I'm using. I think if the references are visually shown
Please elaborate... @Benoy Soman , do you mean like a printscreen or a copied-pasted extract from your Parameters tab?
B
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.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?
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.
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.
V
Viki2010
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
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.
V
Viki2010
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.
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.
B
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.
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.
B
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)"
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.
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.
B
Benoy Soman
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.
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.
B
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)"
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?
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.
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.