Cleaning and analysis of a payment database

International NGO · Written recruitment test

Practical exercise

Information Management Officer

Duty station: Gonaïves, Artibonite department (Haiti)
Time allowed: 75 min
Marks: 100 marks
Pass mark: 60 %
Profile: Officer, 2 to 4 years of experience

Instructions

The test lasts 75 minutes. It is marked out of 100; the pass mark is 60.
Write the formulas in English Excel syntax (separator ,).
You may use a calculator or a spreadsheet. Show your workings: a correct result without workings receives only half the marks.
Any outside help (another person, artificial intelligence) is forbidden.
The documents are fictional. Do not make unjustified assumptions; if you make one, state it.

Original practice test, built on the format of written tests used by United Nations agencies and international NGOs. The context and figures are fictitious.

Documents provided

Document 1. Extract from the payment database (sheet "Payments", rows 2 to 21)

A: Household ID	B: Commune	C: Sex of head of household	D: Size	E: Amount paid (HTG)
GON-001	Gonaïves	F	4	6,500
GON-002	Gonaïves	M	7	9,750
GON-003	gonaives	F	6	9,750
GON-004	Gonaïves	F	3	6,500
ENN-001	Ennery	M	5	6,500
ENN-002	Ennery	F	8	9,750
ENN-003	Ennery	F	6	6,500
GON-005	GONAIVES	M	2	6,500
EST-001	L'Estère	F	5	9,750
EST-002	L'Estère	M	9	9,750
EST-003	L'Estere	F	4	0
GON-002	Gonaïves	M	7	9,750
ENN-004	Ennery	F	4	6,500
GON-006	Gonaïves	F	6	9,750
EST-004	L'Estère	F	7	9,750
ENN-005	Ennery	M	3	6,500
GON-007	Gonaïves	F	5	6,500
EST-005	L'Estère	M	6	0
ENN-006	Ennery	F	10	9,750
GON-008	Gonaïves	F		6,500

Document 2. Programme rules

Amount due: HTG 6,500 for a household of 1 to 5 people; HTG 9,750 for 6 people or more.
Only one payment per household (unique ID).
Amount paid 0 = household absent from the distribution.

Question 120 marks

List the data quality problems and state, for each one, how to detect and correct it in Excel.

Your answer

0 words

Question 225 marks

Write the formulas for row 2 for: the amount due (column H), the difference paid minus due (I) and an anomaly flag (J) that displays "Duplicate", "Missing size", "Difference" or "OK".

Your answer

0 words

Question 330 marks

Produce the summary table by commune (without the duplicate): number of households, % of female heads of household, people and average size, households paid and payment rate, amount paid. Give the formula for the amount paid for Ennery.

Your answer

0 words

Model answer

Commune	Households	Female heads	People (known size)	Avg. size	Paid	Rate	Amount paid
Gonaïves	8	6 (75.0%)	33 (7 households)	4.71	8	100%	61,750
Ennery	6	4 (66.7%)	36	6.00	6	100%	45,500
L'Estère	5	3 (60.0%)	31	6.20	3	60.0%	29,250
Total	19	13 (68.4%)	100 (18 households)	5.56	17	89.5%	136,500

Formula: =SUMIFS(E:E,F:F,"Ennery",G:G,1). With a pivot table: source filtered on G = 1, standardised commune in rows. The gross amount paid (HTG 146,250) includes the duplicate of HTG 9,750.

Marking grid

Correct households and female heads (8 marks)
Correct people and average size, GON-008 handled (7 marks)
Correct payment rates (7 marks)
Correct amounts and formula (8 marks)

Your mark for this question / 30

Question 425 marks

List the payment anomalies, calculate the amount overpaid, the amount still owed to households and the amount to be verified. Propose two controls for the next distribution.

Your answer

0 words

Other tests for this profession

Panel interview question for this profession

Describe a data quality problem you discovered and how you resolved it for the long term.

Competency assessed : Data quality and tool improvement

What the panel expects : A precise detection method, a measure of the scale of the problem, a traceable correction, automation or a procedure that prevents the problem from recurring, and attention to data protection.

Possible follow-up questions

How did you measure the scale of the problem?
Who did you involve to correct the data at source?
How did you protect personal data during this work?

Strong answer

In Niger, while preparing a transfer cycle for 4,200 households, I cross-checked the list against the previous cycle's list using a lookup on the ID and the phone number. I found 186 duplicate IDs and 74 phones shared by several households. The potential overpayment was about 9 million FCFA. I presented the results to the programme manager without concluding that there was fraud: field checks showed that 51 phones were genuinely shared between neighbours, and the other cases were double registrations. I built a workbook with data validation, a parameters sheet and an automatic duplicate check before each export, and I wrote a double-validation procedure. In the next cycle, the duplicate rate fell from 4.4% to 0.2%, and the file sent to the payment agent contained only the necessary fields.

Weak answer

"I am very good at Excel; I know all the formulas. When I see duplicates, I delete them with the built-in tool." This answer talks about tools, not results: no measure of scale, no verification, no traceability of deletions and no prevention.

← All tests

Instructions

Documents provided

Question 120 marks

Model answer

Marking grid

Question 225 marks

Model answer

Marking grid

Question 330 marks

Model answer

Marking grid

Question 425 marks

Model answer

Marking grid

Result

Common mistakes that cost marks

Useful references