![Page 1: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/1.jpg)
![Page 2: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/2.jpg)
Spreadsheets – blessing or curse?
by Phil Stott
![Page 3: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/3.jpg)
Overview
1. A little background and history
2. ‘Pros’ and ‘cons’
3. Survey of current practice
4. Top 10 cockups
5. Tips for dealing with cons
6. Suggested ‘standards’
![Page 4: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/4.jpg)
Background and history
• Origin: computerisation of paper accounting worksheets – Question: who put the ‘spread’ in ‘spreadsheet’?
• The concept of an electronic spreadsheet was first outlined in a 1961 paper – 1962-3: Implemented on IBM 1130 /7040 at Marquette
University, Wisconsin using Fortran
– 1968: Implemented on a timesharing IBM 360/67 at Washington Stater University and used to teach finance
– Key calculation algorithm patented in 1970
![Page 5: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/5.jpg)
Background and history (cont.)
• Some historic milestones along the way:
– 1979: VisiCalc on Apple II
– 1981: Invention of the IBM PC
– 1982-3: Lotus 1-2-3 (runs on IBM PCs)
– Early 1990s: Microsoft Excel (runs under Windows 3.x)
– 2005: emergence of on-line spreadsheets
![Page 6: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/6.jpg)
‘Pros’ and ‘cons’ – the ‘pros’:
• Spreadsheets are very easy to use for “end-user development” (EUD):
– Use of spatial relationships
– Allow partial developments
– Use of colours, typefaces, fonts etc (“secondary notation”)
![Page 7: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/7.jpg)
‘Pros’ and ‘cons’ – the ‘pros’ (cont.)
• Why actuaries use spreadsheets: – Control is located closest to the experts
– Flexibility
– Highly visible interface
– Ease of producing reports and graphs
– IT training not required
– Cost
– Facilitates communication
– Supports rapid “what if” thinking
![Page 8: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/8.jpg)
‘Pros’ and ‘cons’ – the ‘cons’
• Cons: – EUDs are difficult to police – Reliability – Limited use – Hard to debug – Alteration of dimensions is major surgery – Collaborative authorship can be difficult – Capacity limits (65,536 x 256 in some packages) – Audit / revision control – Security
![Page 9: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/9.jpg)
‘Pros’ and ‘cons’ – the ‘cons’
• Cons: – EUDs are difficult to police – Reliability – Limited use – Hard to debug – Alteration of dimensions is major surgery – Collaborative authorship can be difficult – Capacity limits (65,536 x 256 in some packages) – Audit / revision control – Security
• Question: how significant are these issues for actuaries?
![Page 10: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/10.jpg)
‘Pros’ and ‘cons’ – the ‘cons’ (cont.)
The dimensionality problem for actuarial projections: 1. Purpose of the run (Statutory, EV, Capital etc) 2. Projected cashflows (premiums, sums insured,
policies etc) 3. Projection periods (months, years, etc) 4. Valuation month 5. Interim / final runs 6. Product lines 7. Grouped data levels 8. Sensitivities, shocks, etc
![Page 11: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/11.jpg)
‘Pros’ and ‘cons’ – the ‘cons’ (cont.)
• One classification of risks: – Mistakes in logic – Incorrect ranges in formulae – Incorrect cell references – Confused range names – Incorrectly copied formulae – Incorrect use of formats and column widths – Accidentally overwritten formulae – Misuse of built-in functions
![Page 12: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/12.jpg)
‘Pros’ and ‘cons’ – the ‘cons’ (cont.)
• One classification of risks: – Mistakes in logic – Incorrect ranges in formulae – Incorrect cell references – Confused range names – Incorrectly copied formulae – Incorrect use of formats and column widths – Accidentally overwritten formulae – Misuse of built-in functions
• Question: how significant are these issues for actuaries?
![Page 13: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/13.jpg)
‘Pros’ and ‘cons’ – the ‘cons’ (cont.)
• Specific error risks for actuarial spreadsheets:
– Incorrect modelling risks
– Inconsistent modelling risks
– Inadequate modelling risks
![Page 14: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/14.jpg)
Survey of current practice
… or “You are not alone!”
![Page 15: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/15.jpg)
Survey of current practice
According to a 2005-06 survey:
• 53.6% of respondents claimed “some expertise”, another 39.3% “high expertise”
![Page 16: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/16.jpg)
Survey of current practice
According to a 2005-06 survey:
• 53.6% of respondents claimed “some expertise”, another 39.3% “high expertise”
but …
![Page 17: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/17.jpg)
Survey of current practice
According to a 2005-06 survey:
• 53.6% of respondents claimed “some expertise”, another 39.3% “high expertise”
but …
• Only 37.7% had ever received “formal classroom instruction”
![Page 18: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/18.jpg)
Survey of current practice (cont.)
• 81.1% typically worked alone
![Page 19: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/19.jpg)
Survey of current practice (cont.)
• 81.1% typically worked alone
but …
![Page 20: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/20.jpg)
Survey of current practice (cont.)
• 81.1% typically worked alone
but …
• 78.3% devoted < 10% of time to testing
![Page 21: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/21.jpg)
Survey of current practice (cont.)
• 81.1% typically worked alone
but …
• 78.3% devoted < 10% of time to testing
• 88.1% devoted no time to documentation
![Page 22: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/22.jpg)
Survey of current practice (cont.)
Frequency of testing:
• “never” ???
• “sometimes” ???
• “usually” ???
• “always” ???
![Page 23: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/23.jpg)
Survey of current practice (cont.)
Frequency of testing:
• “never” 17.1%
• “sometimes” 31.9%
• “usually” 26.7%
• “always” 24.2%
![Page 24: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/24.jpg)
Survey of current practice (cont.)
Frequency of testing:
• “never” 17.1%
• “sometimes” 31.9%
• “usually” 26.7%
• “always” 24.2%
Most common tool for testing spreadsheets:
• ???
![Page 25: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/25.jpg)
Survey of current practice (cont.)
Frequency of testing:
• “never” 17.1%
• “sometimes” 31.9%
• “usually” 26.7%
• “always” 24.2%
Most common tool for testing spreadsheets:
• “use common sense” 67.4%
![Page 26: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/26.jpg)
Survey of current practice (cont.)
Amount of training received p.a. • None ??? • More than 5 days: ???
![Page 27: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/27.jpg)
Survey of current practice (cont.)
Amount of training received p.a. • None 73.0% • More than 5 days: 5.0%
![Page 28: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/28.jpg)
Survey of current practice (cont.)
Amount of training received p.a. • None 73.0% • More than 5 days: 5.0%
Existence of standards and controls:
• No standards ??? • Informal guidelines ???
![Page 29: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/29.jpg)
Survey of current practice (cont.)
Amount of training received p.a. • None 73.0% • More than 5 days: 5.0%
Existence of standards and controls:
• No standards 66.4% • Informal guidelines 23.5%
![Page 30: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/30.jpg)
Survey of current practice (cont.)
Amount of training received p.a. • None 73.0% • More than 5 days: 5.0%
Existence of standards and controls:
• No standards 66.4% • Informal guidelines 23.5%
Are standards followed?
• Always ??? • “Don’t know” ???
![Page 31: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/31.jpg)
Survey of current practice (cont.)
Amount of training received p.a. • None 73.0% • More than 5 days: 5.0%
Existence of standards and controls:
• No standards 66.4% • Informal guidelines 23.5%
Are standards followed?
• Always 6.0% • “Don’t know” 49.1%
![Page 32: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/32.jpg)
Survey of current practice (cont.)
Amount of training received p.a. • None 73.0% • More than 5 days: 5.0%
Existence of standards and controls:
• No standards 66.4% • Informal guidelines 23.5%
Are standards followed?
• Always 6.0% • “Don’t know” 49.1%
Who is responsible in your organisation for managing spreadsheet risks?
• ???
![Page 33: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/33.jpg)
Survey of current practice (cont.)
Amount of training received p.a. • None 73.0% • More than 5 days: 5.0%
Existence of standards and controls:
• No standards 66.4% • Informal guidelines 23.5%
Are standards followed?
• Always 6.0% • “Don’t know” 49.1%
Who is responsible in your organisation for managing spreadsheet risks?
• “Don’t know” 47.5%
![Page 34: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/34.jpg)
Top 10 cockups
… or “It could never happen to me!”
![Page 35: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/35.jpg)
Top 10 cockups
… or “It could never happen to me!”
• #10 … Erroneous exam grades
![Page 36: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/36.jpg)
Top 10 cockups
… or “It could never happen to me!”
• #10 … Erroneous exam grades
• #9 … Fabricated data exposed in court case
![Page 37: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/37.jpg)
Top 10 cockups
… or “It could never happen to me!”
• #10 … Erroneous exam grades
• #9 … Fabricated data exposed in court case
• #8 … $5m actuarial error exposed in court
![Page 38: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/38.jpg)
Top 10 cockups
… or “It could never happen to me!”
• #10 … Erroneous exam grades
• #9 … Fabricated data exposed in court case
• #8 … $5m actuarial error exposed in court
• #7 … MI5 makes 1061 bugging errors
![Page 39: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/39.jpg)
Top 10 cockups
… or “It could never happen to me!”
• #10 … Erroneous exam grades
• #9 … Fabricated data exposed in court case
• #8 … $5m actuarial error exposed in court
• #7 … MI5 makes 1061 bugging errors
• #6 … Genetic research data lost
![Page 40: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/40.jpg)
Top 10 cockups (cont.) • #5 … ‘I think I had a mental blackout!’
![Page 41: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/41.jpg)
Top 10 cockups (cont.) • #5 … ‘I think I had a mental blackout!’
• #4 … The case of the $200m parentheses
![Page 42: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/42.jpg)
Top 10 cockups (cont.) • #5 … ‘I think I had a mental blackout!’
• #4 … The case of the $200m parentheses
• #3 … ‘Houston, we have a $644m (spreadsheet) problem’
![Page 43: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/43.jpg)
Top 10 cockups (cont.) • #5 … ‘I think I had a mental blackout!’
• #4 … The case of the $200m parentheses
• #3 … ‘Houston, we have a $644m (spreadsheet) problem’
• #2 … The $2.6bn minus sign
![Page 44: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/44.jpg)
Top 10 cockups (cont.) • #1 … ‘Bank error NOT in your
favour …’
![Page 45: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/45.jpg)
Dealing with the ‘cons’
• 4 strategies for dealing with the negatives of uncontrolled spreadsheet development:
1. Version control tools
2. Audit tools
3. Automation tools
4. Spreadsheet development standards
![Page 46: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/46.jpg)
Dealing with the ‘cons’ (cont.)
• Special considerations for actuarial processes: – Balance of flexibility and control – Robustness – Reasonableness checking – Removal of key person risk – Automation – Reliability of results – Auditability, transparency and documentation – Repeatability – Reduction in operational risk and capital charges
![Page 47: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/47.jpg)
Suggested standards
• General principles: 1. Primary need: must retain the ‘pros’ of spreadsheets,
while minimising the ‘cons’:- • There are good reasons why actuaries like using spreadsheets
![Page 48: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/48.jpg)
Suggested standards
• General principles: 1. Primary need: must retain the ‘pros’ of spreadsheets,
while minimising the ‘cons’:- • There are good reasons why actuaries like using spreadsheets
2. Must be enforceable:- • Standards are not just “another good idea”, but a business
essential
![Page 49: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/49.jpg)
Suggested standards
• General principles: 1. Primary need: must retain the ‘pros’ of spreadsheets,
while minimising the ‘cons’:- • There are good reasons why actuaries like using spreadsheets
2. Must be enforceable:- • Standards are not just “another good idea”, but a business
essential
3. Must be situational
![Page 50: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/50.jpg)
Suggested standards
• General principles: 1. Primary need: must retain the ‘pros’ of spreadsheets,
while minimising the ‘cons’:- • There are good reasons why actuaries like using spreadsheets
2. Must be enforceable:- • Standards are not just “another good idea”, but a business
essential
3. Must be situational
4. Must be enforced
![Page 51: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/51.jpg)
Suggested standards (cont.)
• Suggested areas where standards are particularly required:
1. Version information
2. Colour coding
3. Restrictions on linking
4. Protection of sheets / cells
![Page 52: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/52.jpg)
Suggested standards (cont.)
• Specific issue for decision – the use of links:
![Page 53: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/53.jpg)
Suggested standards (cont.)
• Specific issue for decision – the use of links:
– Allow unrestricted links
![Page 54: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/54.jpg)
Suggested standards (cont.)
• Specific issue for decision – the use of links:
– Allow unrestricted links
– Allow no links at all (‘Copy values’)
![Page 55: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/55.jpg)
Suggested standards (cont.)
• Specific issue for decision – the use of links: – Allow unrestricted links
– Allow no links at all (‘Copy values’)
– Only allow specific links: • When? (Link into anything, or only link into’official’
sources?)
• Where? (Anywhere you like, or only in specifically earmarked ‘in-link’ sheets?)
• Why? (Simplicity / laziness, or to retain an audit trail?)
![Page 56: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/56.jpg)
Conclusion: blessing or curse?
![Page 57: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/57.jpg)
Conclusion: blessing or curse?
• If you apply appropriate standards for control and balance … clearly a blessing
![Page 58: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/58.jpg)
Conclusion: blessing or curse?
• If you apply appropriate standards for control and balance … clearly a blessing
• If you don’t … ?
![Page 59: Spreadsheets - Actuaries Institute · Background and history •Origin: computerisation of paper accounting worksheets –Question: who put the ‘spread’ in ‘spreadsheet’?](https://reader031.vdocuments.us/reader031/viewer/2022031007/5b8f677009d3f28c298c61d5/html5/thumbnails/59.jpg)
One solution: reliable spreadsheet training