Price monitoring, minimum expenditure basket and weighted survey data

(a) =MEDIAN(FILTER(Prices!$D$2:$D$500,(Prices!$A$2:$A$500=A2)*(Prices!$F$2:$F$500="valid"))). Without FILTER: array formula =MEDIAN(IF((Prices!$A$2:$A$500=A2)*(Prices!$F$2:$F$500="valid"),Prices!$D$2:$D$500)). AVERAGEIFS is not suitable: the rule requires the median.

(b) In C2: =B2*D2 where D2 contains the median price; MEB: =SUM(C2:C7)+38, ideally with the flat rate in a named cell rather than typed into the formula.

(c) Change: =MEB/MEB_ref-1 in percentage format; decision: =IF(ABS(MEB/MEB_ref-1)>10%,"Revise","Maintain"); the absolute value also covers decreases.

(d) =MROUND(0.7*MEB,5).

Good practice: parameters (70%, 10%, 5 USD, flat rate) in a "Parameters" sheet with named cells; no hard-coded values in formulas.

Market C: identical prices for all three traders and equal, to the cent, to the March reference prices; 18 readings entered in 7 minutes, at night, 40 km from the market. These are very strong indications of fabricated data (copying of the reference). The readings must be set to "excluded" status, with a documented justification.

March MEB: 27.00 + 13.50 + 10.80 + 8.00 + 4.00 + 0.50 = 63.80 + 38 = 101.80 USD.

(a) Without market C: 114.235 / 101.80 − 1 = + 12.2%: above 10%, revision; 0.7 × 114.235 = 79.96, i.e. 80 USD.

(b) With market C: 109.22 / 101.80 − 1 = + 7.3%: no revision; the transfer would remain at 70 USD.

Conclusion: the fabricated data change the decision. With valid data, households lose about 12% of purchasing power on the basket; keeping 70 USD would reduce the effective coverage of needs. 80 USD is recommended. Note: with 6 readings (an even number), the median is the average of the two middle values; it should be flagged that the reference now covers only two markets.

40.7% is the simple average of the three sites: (52 + 40 + 30) / 3 = 40.67%. It gives each site the same weight, whereas site 1 has 4,000 households and site 3 only 1,500. With the same sample (150) in each site, site 3 households are over-represented.

Weighted indicator: (4,000 × 52% + 2,500 × 40% + 1,500 × 30%) / 8,000 = (2,080 + 1,000 + 450) / 8,000 = 3,530 / 8,000 = 44.1%.

Formula: =SUMPRODUCT(B2:B4,D2:D4)/SUM(B2:B4). At the level of surveyed households, a weight N/n is calculated per site: 4,000 / 150 = 26.67; 2,500 / 150 = 16.67; 1,500 / 150 = 10, then =SUMPRODUCT(weight,acceptable)/SUM(weight) where "acceptable" is 1 or 0. In a pivot table, a "weight" column is used and the weights are summed instead of counting rows.

Interpretation: the difference (3.5 points) comes from the structure of the sample. The overall figure to report is 44.1%, together with the results by site, which show that site 3 (30%) needs particular attention. Each site result has a margin of error of about ± 7 to 8 points (n = 150).

Sample answer: "Subject: revision of the transfer value. The September MEB, calculated on valid readings from markets A and B, reaches 114.24 USD, against 101.80 USD in March (+ 12.2%). The working group rule requires a revision above 10%. I recommend raising the transfer from 70 to 80 USD (70% of the MEB, rounded), from the October distribution. Additional cost: 10 USD × 2,000 households × 3 months = 60,000 USD, to be covered by reallocation or a request to the donor. The market C readings were excluded: they exactly reproduce the March prices and were entered 40 km from the market; had they been included, they would have masked the increase (+ 7.3%). We are checking the readings for previous months and setting up automatic checks (GPS, timestamps, identical prices). The PDM survey shows 44.1% of households with an acceptable score: the price increase is an additional risk."

The data remain excluded: invented prices cannot be used to set the assistance for 2,000 households, whatever the reason. I tell the head of the sub-office so respectfully, explaining the impact (the data would have deprived households of 10 USD per month).

I distinguish two problems. (1) Access: if the road was dangerous, the enumerator was right not to go; the mistake was not reporting it. The procedure must make it possible to report "market not accessible" without fear, and provide alternatives (readings by phone from identified traders, replacement market). (2) Entering invented prices: this is professional misconduct, to be dealt with through the normal HR channel, in a proportionate and documented way, without involvement of the head of the sub-office, who has a conflict of interest and must recuse himself from the decision.

I check this enumerator's previous readings. I inform my line manager and document the facts, without publicly exposing the person.