Consolidating partners' monthly reports and summary table…

United Nations agency · Written recruitment test

Practical exercise · Assistant level

Information Management Assistant

Duty station: Baga Sola, Lac province (Chad)
Time allowed: 60 min
Marks: 100 marks
Pass mark: 60 %
Profile: Assistant, 0 to 2 years of experience

Instructions

The test lasts 60 minutes. It is marked out of 100; the pass mark is 60.
Write the formulas in English-language Excel syntax (comma separator).
You may use a calculator. Show your workings: a correct result without workings receives only half the marks.
Any outside help (another person, shared document, artificial intelligence) is forbidden.
The documents are fictional. Apply only the rules provided; if you make an assumption, 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. "Raw" sheet: October reports received from four WASH sector partners (rows 2 to 10)

Row	A: Partner	B: Locality	C: Activity	D: Unit	E: Value	F: Type of figure
2	Partner A	Baga-Sola	Hygiene kits	people	1,240	monthly
3	Partner A	Bol	Hygiene kits	people	860	monthly
4	Partner A	Liwa	Awareness raising	people	12,400	monthly
5	Partner B	bagasola	Hygiene kits	people	3,150	cumulative since July
6	Partner C	Liwa	Hygiene kits	households	210	monthly
7	Partner C	BAGA SOLA	Awareness raising	people	980	monthly
8	Partner D	Bol	Awareness raising	people	1,500	monthly
9	Partner D	"Bol" followed by a space	Awareness raising	people	1,500	monthly
10	Partner D	Ngouboua	Hygiene kits	people	640	monthly

Document 2. "Ref" sheet: locality reference list (fictional codes) and useful information

A: Official locality	B: Code	C: Estimated population	D: Lookup key
Baga-Sola	LAC-001	42,000	BAGASOLA
Bol	LAC-002	31,000	BOL
Liwa	LAC-003	9,800	LIWA
Ngouboua	LAC-004	14,500	NGOUBOUA

Partner B's September report showed a cumulative total of 2,400 people (hygiene kits, Baga-Sola) since July.
Sector convention for converting households into people: 6 people per household.

Document 3. Sector reporting guidelines (extract)

Each partner reports monthly values, in people, one row per partner, locality and activity.
Localities are spelled as in the reference list; the locality code is mandatory in the consolidated database.
An inconsistent value (for example higher than the population of the locality) is not published before verification with the partner.
People from two different activities are not added together: the same person may have received a kit and attended a session.

Question 130 marks

Review rows 2 to 10 of document 1. For each problematic row, state the problem, the rule concerned and the correction or action.

Your answer

0 words

Question 225 marks

Present the corrected October summary table: people by locality (with its code) and by activity, total by activity. Show separately the value awaiting verification.

Your answer

0 words

Model answer

Locality (code)	Hygiene kits	Awareness raising
Baga-Sola (LAC-001)	1,240 + 750 = 1,990	980
Bol (LAC-002)	860	1,500
Liwa (LAC-003)	1,260	pending
Ngouboua (LAC-004)	640	0
Total	4,750	2,480

Pending: 12,400 people reached by awareness raising in Liwa (partner A), not published. No "all activities" total (4,750 + 2,480) is presented, because the same people may be counted twice (rule 4). Without the corrections, 22,480 people would have been added up (raw sum of rows 2 to 10) instead of 7,230; this is what justifies the check.

Marking grid

Correct values by locality (12 marks)
Correct totals by activity (6 marks)
Liwa value set aside (4 marks)
No all-activities total, with the reason (3 marks)

Your mark for this question / 25

Question 325 marks

Write the formulas to automate the consolidation in the "Raw" sheet: (a) in G2, a lookup key based on the locality (without spaces or hyphens, in capitals); (b) in H2, the locality code taken from the "Ref" sheet; (c) in I2, the value in people (conversion of households); (d) in J2, a check that displays "Check" if I2 exceeds the population of the locality; (e) the total of hygiene kits for code LAC-001, based on columns H and I.

Your answer

0 words

Question 420 marks

Write the email to partner A's focal point (80 to 120 words) about row 4.

Your answer

0 words

Other tests for this profession

Panel interview question for this profession

You have to enter 600 paper forms in three days. How do you organise the work to meet the deadline without losing quality? Base your answer on a real example.

Competency assessed : Accuracy and reliability of data entry under time pressure

What the panel expects : A concrete method (preparing the file, validation rules, batches, checks), figures for pace and error rate, handling of illegible forms, and data protection.

Possible follow-up questions

How do you check your own data entry?
What do you do with an illegible or incomplete form?
Where are the paper forms and the file kept at the end of the day?

Strong answer

During the registration of new arrivals, two of us had to enter 720 forms in three days. Before starting, I added drop-down lists to the file and checks on age, household size and duplicate IDs. We worked in numbered batches of 40 forms, with a break every two hours. At the end of each batch, the colleague re-checked 10% of the other's forms. On the first day, my error rate was 3%; I saw that most errors came from dates, and I changed the column format. It fell to 0.8%. The 26 illegible forms were set aside and checked with the registration team. We finished in two and a half days, with the forms locked away every evening.

Weak answer

"I type very fast, so 600 forms in three days is not a problem. I am careful not to make mistakes." This answer relies on speed alone, with no checking method, no quality figure, and nothing about incomplete forms or confidentiality.

← All tests

Consolidating partners' monthly reports and summary table

Instructions

Documents provided

Question 130 marks

Model answer

Marking grid

Question 225 marks

Model answer

Marking grid

Question 325 marks

Model answer

Marking grid

Question 420 marks

Model answer

Marking grid

Result

Common mistakes that cost marks

Useful references