Consolidating a multi-partner 5W, calculating people…

United Nations agency · Written recruitment test

Case study · Manager level

Information Management Manager

Duty station: Pemba, Cabo Delgado Province (Mozambique)
Time allowed: 120 min
Marks: 100 marks
Pass mark: 60 %
Profile: Manager / coordinator, 5 years or more

Instructions

The test lasts 120 minutes. It is marked out of 100; the pass mark for the interview is 60.
Write formulas with the English Excel function names (the name in another language version of Excel is accepted) and the comma as separator.
Show your workings. The names of locations and partners are fictional.
You may propose Power Query, structured tables or pivot tables; justify your choices.
Any outside help (another person, artificial intelligence) is forbidden.

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. Counting rules for the 5W report (Who does What, Where, When, for Whom)

R1. The same person may receive several types of assistance. To avoid double counting, the number of people reached in a location is the maximum value reported for that location, across all activities, partners and months.
R2. The people reached in a district are the sum of those in its locations; the grand total is the sum of the districts.
R3. Each row must carry the location code (Pcode) from the reference list; a row whose Pcode does not match the reported location is returned to the partner.
R4. The number of women cannot exceed the number of people; values must be numeric.

Reference list ("Ref" sheet)
Location	Pcode	District
Loc A	L-01	Metuge
Loc B	L-02	Metuge
Loc C	L-11	Ancuabe
Loc D	L-12	Ancuabe
Loc E	L-13	Ancuabe

Document 2. Extract from the consolidated "5W" sheet (August and September)

Row	A Partner	B Month	C District	D Location	E Pcode	F Activity	G People	H Women
2	P1	08	Metuge	Loc A	L-01	Food distribution	1200	640
3	P1	09	Metuge	Loc A	L-01	Food distribution	1250	660
4	P2	09	Metuge	Loc A	L-01	Hygiene kits	900	470
5	P2	09	Metuge	Loc B	L-02	Hygiene kits	600	330
6	P3	08	Metuge	Loc B	L-02	Food distribution	800	420
7	P3	09	Metuge	Loc B	L-02	Food distribution	820	4300
8	P3	09	"Metuge␣" (trailing space)	Loc B	L-02	Food distribution	820	430
9	P4	09	Ancuabe	Loc C	L-11	Food distribution	1500	790
10	P4	09	Ancuabe	Loc C	L-11	Hygiene kits	1500	790
11	P5	09	Ancuabe	Loc D	L-12	Hygiene kits	700	360
12	P5	09	Ancuabe	Loc D	L-12	Food distribution	1100	(blank)
13	P6	09	Ancuabe	Loc E	L-02	Hygiene kits	450	240
14	P6	09	Ancuabe	Loc E	L-13	Food distribution	"approx. 300"	160

Document 3. Current organisation of the monthly report

14 partners send their Excel file by email every month, before the 5th; on average 5 send it late.
Each partner uses its own version of the template (columns added, location names typed freely).
An assistant copies and pastes the 14 files into the consolidated workbook: about 3 days of work per month. The dashboard is published on the 15th.
Last month, the published total had to be corrected a week later (rows copied twice).
The coordinator wants a dashboard published by the 10th of the month at the latest, reliable and traceable.

Question 120 marks

Write the formulas that, on the "5W" sheet (rows 2 to 14): (a) count the rows where the number of women exceeds the number of people; (b) flag in column I the rows whose Pcode does not match the location according to the "Ref" sheet; (c) flag in column J the duplicates (same partner, month, location and activity); (d) calculate, on a summary sheet where the location is in A2, the number of people reached according to R1; (e) clean the District column.

Your answer

0 words

Question 220 marks

Carry out the quality check of the extract (document 2): for each problem, give the row, the nature of the problem and the action (correction by you, or return to the partner).

Your answer

0 words

Model answer

Row	Problem	Action
7	Women (4,300) greater than people (820); probably an extra zero	Duplicate of row 8, which has the plausible value (430): delete row 7 after confirmation from partner P3
8	District with a trailing space: not recognised by filters, pivot tables and formulas	Correct with TRIM; drop-down list for the future
7 and 8	Duplicate (same partner, month, location, activity)	Keep only one row
12	Number of women missing	Return to P5; do not estimate it in the official report without flagging it
13	Pcode L-02 (Loc B, Metuge) for Loc E (Ancuabe)	Return to P6; probably L-13, to be confirmed, as the Pcode is used for maps
14	"approx. 300": text, approximate, ignored by calculations	Ask P6 for an exact figure; failing that, row excluded from the calculation and flagged
9 and 10	Same number (1,500 and 790) for two activities: not an error in itself	Check with P4 that these are indeed the same households (consistent with R1)

Corrections made by the information manager are tracked ("QC comment" column and change log); the partner's original file is never modified.

Marking grid

Rows 7 and 8 (outlier, duplicate, space) identified (6 marks)
Rows 12, 13 and 14 identified (6 marks)
Distinction between direct correction and return to the partner (5 marks)
Traceability of corrections (3 marks)

Your mark for this question / 20

Question 320 marks

After the quality check (row 7 deleted, row 13 corrected to L-13, row 14 kept at 300 subject to confirmation), calculate the people reached by location, by district and in total according to R1 and R2. Compare with the raw sum of column G and calculate the share of women for Metuge district.

Your answer

0 words

Model answer

District	Location	Reported values	People reached (maximum)
Metuge	Loc A	1,200; 1,250; 900	1,250
Metuge	Loc B	600; 800; 820	820
Total Metuge			2,070
Ancuabe	Loc C	1,500; 1,500	1,500
	Loc D	700; 1,100	1,100
	Loc E	450; 300	450
Total Ancuabe			3,050
Grand total			5,120

Raw sum of column G (numeric values, rows 2 to 13): 11,640, i.e. 2.27 times the people reached. The raw sum measures assistance delivered, not people.

Women, Metuge: take the women in the rows used for the maximum: Loc A, row 3: 660; Loc B, row 8: 430; i.e. 1,090 / 2,070 = 52.7%. For Ancuabe, the calculation is not possible until P5 has provided the number of women for row 12.

Limitation of R1: the maximum may underestimate the people reached if two partners served different groups in the same location; beneficiary lists or an explicit statement from the partners are then needed.

Marking grid

Maximum per location correct for all 5 locations (6 marks)
District totals and grand total correct (5 marks)
Raw sum correct and difference explained (people versus assistance) (5 marks)
Share of women correct and limitation for Ancuabe or limitation of R1 noted (4 marks)

Your mark for this question / 20

Question 425 marks

Propose an organisation of the monthly process and of the workbook that makes it possible to publish a reliable dashboard on the 10th of the month (document 3). Describe the tools, controls, roles and timeline.

Your answer

0 words

Model answer

Collection: a single locked template (protected structure, fixed headers, structured table), with dependent drop-down lists (district, location, activity) fed by the "Ref" sheet, Pcode calculated automatically, data validation (positive whole numbers, women ≤ people), and a check tab that shows the partner its errors before sending. Better, if possible: an online form (KoboToolbox or equivalent) with the same constraints. Upload to a shared folder, one file per partner per month, named according to a convention ("5W_P1_2025-09.xlsx").

Consolidation: Power Query "From Folder": automatic combination of all files, cleaning (removal of spaces, data types, addition of the source file name), merge with the "Ref" sheet, check rules in columns (duplicates, Pcode, outliers, missing values); refresh in one click. No more copying and pasting: the cause of last month's error disappears.

Analysis and publication: data model or pivot tables on the cleaned table; people reached calculated according to R1 (maximum per location); dashboard with update date, number of partners that reported and methodological note; frozen PDF version archived every month.

Timeline and roles: D1 to D5: data entry by partners; D5: automatic reminder to late partners; D6 and D7: refresh and quality check by the assistant, feedback to partners; D8: corrections; D9: approval by the IM manager; D10: publication. Data received late are included the following month, with a note.

Gains: consolidation work drops from 3 days to a few hours, devoted to quality checking and support to partners. Monitoring indicators: rate of on-time reports, number of errors per partner, correction times. Training of the 14 partners on the template and a one-page guide.

Marking grid

Standardised template with data validation at source (6 marks)
Automated consolidation (Power Query or equivalent) and built-in checks (7 marks)
Realistic timeline and roles to publish on the 10th (6 marks)
Traceability, archiving, methodological note and training (6 marks)

Your mark for this question / 25

Question 515 marks

Partner P4 asks for its 1,500 people in Loc C to be counted twice ("these are two different activities, our donor requires it"). The coordinator asks you to publish 11,640 people reached before a donor conference, because "the response plan target will be exceeded". What do you do? (8 to 12 lines)

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

Consolidating a multi-partner 5W, calculating people reached and automation

Instructions

Documents provided

Question 120 marks

Model answer

Marking grid

Question 220 marks

Model answer

Marking grid

Question 320 marks

Model answer

Marking grid

Question 425 marks

Model answer

Marking grid

Question 515 marks

Model answer

Marking grid

Result

Common mistakes that cost marks

Useful references