INSTITUTE AND FACULTY OF ACTUARIES

EXAMINATION

April 2019 (am)

AUDIT TRAIL

Subject CP2 – Modelling Practice

Core Practices

Paper One

CP2 Paper 1, A2019–1

 Institute and Faculty of Actuaries
Audit trail

Objective

The City Board of Education in a particular city monitors the performance of Schools by
testing all children at the age of 11 in Mathematics. The Board then uses the results of these
tests to calculate the grants that are awarded each year to each school in the city.

We have been asked by the City Board of Education to analyse the test results data that they
have received and to calculate the grants that are to be awarded.

The purpose of this spreadsheet is to calculate the following:

 For each school for which we have data to calculate:

o the percentage of pupils achieving the expected standard
o the percentage of pupils achieving the higher standard
o the rank of each pupil

 The total grant that will be awarded to each school

 The amount that should be awarded per pupil that achieves the higher standard in
order for the Education board to remain within their budget of £75,000.
 Test the data for bias due to the location of the schools

Data

The test results data for the five schools in the city has been provided by the City Board of
Education and can be found on the “raw data” worksheet.

The following data can be found in the parameters worksheet:

 Test maximum score = 60

 The % score required to reach the expected standard = 50%
 The % score required to reach the higher standard = 75%
 The number of pupils in each school = 30
 The base grant available = £50,000
 The % of the base grant awarded based on the school ranking:
o 1st = 40%
o 2nd = 25%
o 3rd = 15%
o 4th = 10%
o 5th = 10%
 The additional grant awarded per pupil achieving the higher standard = £1,000

These parameters are assumed to be correct but should be independently verified.

CP2 Paper 1, A2019–2

Assumptions

 Except for the data items that have been corrected below in the data checks section, there
are no reasons to doubt the accuracy of the data. We assume therefore that the data
provided is correct.
 All the candidates are assumed to have taken the test. There were no absences on the test
date.
 It is possible for a candidate to score the full 60 marks in the test.
 It is possible for a candidate to score no marks in the test.
 The method that is used to calculate the grant being awarded is fixed and will not change.
 There are no additional funds available to be provided in the grant except for the base
grant and the monies available for the grant per pupil.
 The £75,000 grant available next year is fixed and cannot be changed.
 There will be no new schools opening in the city in the next year.
 The % levels that must be scored in order to achieve the expected and higher standard are
fixed and cannot be changed.

Method

“Data Checks”

In this worksheet the data is checked for reasonableness

The sheet “data checks” carries out the following checks on the data using excel functions:

 The average score for each school (row 35)

 The minimum score (row 36)
 The maximum score (row 37)
 The standard deviation (row 38)

These checks highlighted the following possible errors:

 Student A5 has a score of -5. As negative scores are not possible, this has been
assumed to be a data input error and has been changed to be +5.
 Student A19 has a score of 0 – it has been assumed that this is correct and has not
been changed.
 Student C24 has a score of 61. As the test had a maximum score of 60 this is not
possible, so the data for this candidate was changed to have a score of 60
 Student E20 has sex = “G”. This has been assumed to be a data input error and has
been changed to “F”.

“Corrected data”

In this worksheet the data is corrected for any errors identified in “data checks”

CP2 Paper 1, A2019–3

This sheet links through the original data provided on the “raw data” sheet and has the
following changes made to it:

 Student A5 – the score has been changed to +5

 Student A19 – the score for this student has not been changed
 Student C24 – the score was changed to 60
 Student E20 – the sex was changed to “F”.

The data in this sheet is the data that is used in the calculations below. Apart from the
changes detailed above, the data provided is assumed to be correct but should be
independently verified. If any errors are found in this data then the data will need correcting
and the calculations will need revising.

“Normal distribution check” worksheet

In this worksheet the data is compared to a normal distribution

The scores from 0 to 60 are listed in cells A5 to A65.

The number of students in all schools that have achieved each score between 0 and 60 have
been calculated in cells B5 to B65 using the excel count if function.

To check that all students have been counted, the count figures in cells B5 to B65 are
summed in B67. This is checked that it is equal to 150 which is the total number of students
for whom we have data.

This data is then used to produce a chart that shows the distribution of all of the test marks.

Reasonableness checks

The shape of the histogram looks reasonable:

 There is a fixed upper bound which affects the chance of a normal shape - people are
aiming to get a high score
 There are some very low outlier marks
 Roughly there is a normal shape around mark 44, the overall average of all scores is
40.5 so this makes sense (as 40 is being pulled down by the very low outliers)
 Different schools have different average scores and distributions and this will affect
the overall shape of the results

“Analysis” worksheet

In this worksheet the number of students achieving the expected and higher standard for each
school and the rank of each student is calculated.

The score out of 60 for each student in each school is converted to a % in columns D,L,T, AB
and AJ, by taking the student’s score and dividing by 60.

CP2 Paper 1, A2019–4

If this score is greater than or equal to 50% then the student has achieved the expected
standard. This is marked as a 1 if the expected standard is met and 0 if this is not met in
columns E,M,U, AC,AK.

If this score is greater than or equal to 75% then the student has achieved the higher standard.
This is marked as a 1 if the higher standard is met and 0 if this is not met in columns F, N, V,
AD,AL.

The rank of each student for each school is calculated using the excel rank function in
columns G,O,W,AE,AM.

In row 37 the number of students in each school achieving the expected and higher standards
is calculated by summing the expected and higher columns.

This is then converted to a percentage in row 38 by taking the number of students achieving
that standard and dividing by the number of students in the school.

Reasonableness checks

 The calculations look sensible and the number of students achieving the expected
standard is greater than the number of students achieving the higher standard.

“Results” worksheet

In this worksheet the results are summarised and the grant that is to be awarded is
calculated.

The % achieving the expected standard and higher standard is summarised in cells A3 to F8.
This data is pulled through from the analysis sheet.

Using this data, a bar chart is produced to show the % of pupils achieving the expected and
higher standards for each school.

The grant that is to be awarded to each school is then calculated in cells A17 to G26.

The base grant is calculated as follows:

 The rank of each school is calculated in row 18 using excel’s ‘Rank’ function. The
ranking is based on the % of students achieving the expected standard school with the
school achieving the highest % being ranked first and the lowest % ranked last.

 Using these rankings, the % of the base grant that will be awarded to each school can
be calculated in row 19. This is carried out by looking up the ranking of each school
against the table of base grant percentages in the parameters sheet (cells A15 to B20).

 The amount of base grant that is awarded is then calculated in row 20 as the % of base
grant for each school (row 19) x the total base grant available (which is detailed in the
parameters sheet).

CP2 Paper 1, A2019–5

The additional grant to be awarded is then calculated as follows:

 The number of students for each school achieving the higher standard is detailed in
row 23 and is pulled through from the analysis sheet.

 In row 24 the amount of additional grant is calculated as the fixed amount of grant
awarded per student achieving the higher standard (detailed in the parameter sheet
B22) x the number of students achieving the higher standard (row 23).

A stacked bar chart is then produced to show the total grant, split between base and additional
grants, awarded to each school.

Reasonableness checks

 The highest achieving school receives the largest grant

“Sensitivity” worksheet

In this worksheet the amount of additional grant per pupil achieving the higher standard is
calculated based on a total available grant of £75,000

This worksheet is a copy of the results worksheet with the following changes:
 The additional grant amount for higher standard students is linked to the ‘additional
grant – reduced’ amount in the parameters sheet cell B24.
 A goal seek is carried out by setting the total grant available in cell G25 to be £75,000
by varying the ‘additional grant – reduced’ amount in the parameters sheet.
 A check is carried out in cell G27 to check that the goal seek has worked correctly.

Reasonableness checks

 The additional grant amount given for each student achieving the higher standard is
lower in this scenario. This is as expected as the total budget available has reduced
from £111,000 to £75,000.

“T test” worksheet

In this worksheet a t-test is carried out to assess whether the claim that there is bias in the
data from different locations is valid

Method

The null hypothesis for this test is stated in row 5 – the means of the data from the two
locations are equal. We will test this at the 5% significance level.

Group 1 is made up of Schools B, C and D.

Group 2 is made up of Schools A and E.

CP2 Paper 1, A2019–6

The mean for each group is calculated in B9 and C9 using excel’s “Average” function
The variance for each group is calculated in B10 and C10 using excel’s “VAR” function.

The pooled variance is calculated in D10 as:

[(number 1 – 1)*variance 1 + (number 2 – 1)*variance 2]/[number 1 + number 2 – 2].

The number of samples in each group is calculated in B11 and C11 using excel’s “Count”
function.

The t value is calculated as:

P(T,t) = (mean group 1 – mean group 2) /(pooled variance * (1/number1+1/number2))^0.5

The degrees of freedom is calculated as the number in group 1 plus the number in group 2
minus 2 which is 148.

The critical t value is looked up from the tables using the degrees of freedom above. As it is a
two tailed test, the 2.5% value is chosen.

The t value is then compared to the critical value. The t value is greater than the critical value
and so this shows that the test is significant.

Reasonableness checks

 The test is significant and this is suggesting that there is bias in the results as
suggested by the government official.

END OF AUDIT TRAIL

CP2 Paper 1, A2019–7