22.20 Government and NGO
Ward offices and camp leads are asked three things: did the application close inside the promised days, did the benefit reach the people on the target list, and was the grant spent on that reach. Events held is the vanity metric. The offices below are fictional. They are not PMC, NMC, or any real scheme's figures. Do not quote them as a government result.
| KPI | What the number means | Who uses it | Action it drives |
|---|---|---|---|
| Closed in 7 days % | Applications closed inside 7 days divided by applications received. | Ward officer | The ones outside 7 days are a named pile, not a percentage to admire. |
| Reach % | Beneficiaries reached divided by the target. | Camp lead, programme officer | Under target with money left: go to the list. Under target with money gone: the spend was not the benefit. |
| Grant utilisation | Rupees spent divided by the grant. | Accounts | 100% spent is not success if reach is 40%. |
Fictional sample rows. One month. Type these rows into a new sheet from cell A1, press Ctrl + T, and name the table tblOffices (Table Design › Table Name).
| Office | City | Applications | Closed in 7 days | Target people | Reached | Grant | Spent | Events |
|---|---|---|---|---|---|---|---|---|
| Ward office | Pune | 100 | 80 | 200 | 150 | ₹1,00,000 | ₹70,000 | 2 |
| Health camp | Nashik | 80 | 72 | 120 | 120 | ₹60,000 | ₹58,000 | 1 |
| Camp | Kolhapur | 60 | 24 | 100 | 40 | ₹80,000 | ₹80,000 | 8 |
| Ward office | Nagpur | 90 | 63 | 150 | 90 | ₹90,000 | ₹45,000 | 1 |
| Camp | Solapur | 40 | 36 | 80 | 70 | ₹40,000 | ₹30,000 | 1 |
| Ward office | Sambhaji Nagar | 70 | 28 | 100 | 30 | ₹70,000 | ₹65,000 | 6 |
Excel formulas for the whole file (type them in empty cells below or beside the table):
| KPI | Excel formula | Result | Number format |
|---|---|---|---|
| Closed in 7 days % | =SUM(tblOffices[Closed in 7 days])/SUM(tblOffices[Applications]) |
68.9% | 0.0% |
| Reach % | =SUM(tblOffices[Reached])/SUM(tblOffices[Target people]) |
66.7% | 0.0% |
| Grant utilisation | =SUM(tblOffices[Spent])/SUM(tblOffices[Grant]) |
79.1% | 0.0% |
Calculated columns (one result per row). Click the first empty column of the table, type the header, then the formula. The Table fills every row by itself.
| New column | Formula in the first row |
|---|---|
| Closed in 7 days % | =[@[Closed in 7 days]]/[@Applications] |
| Reach % | =[@Reached]/[@[Target people]] |
| Grant utilisation | =[@Spent]/[@Grant] |
Expected result per row:
| Office | Closed in 7 days % | Reach % | Grant utilisation |
|---|---|---|---|
| Ward office (Pune) | 80.0% | 75.0% | 70.0% |
| Health camp | 90.0% | 100.0% | 96.7% |
| Camp (Kolhapur) | 40.0% | 40.0% | 100.0% |
| Ward office (Nagpur) | 70.0% | 60.0% | 50.0% |
| Camp (Solapur) | 90.0% | 87.5% | 75.0% |
| Ward office (Sambhaji Nagar) | 40.0% | 30.0% | 92.9% |
One city with SUMIFS. Closed in 7 days % for Pune only:
=SUMIFS(tblOffices[Closed in 7 days],tblOffices[City],"Pune")/SUMIFS(tblOffices[Applications],tblOffices[City],"Pune")
Result for Pune: 80.0%. Change "Pune" to a cell reference (for example $K$1) and add a drop-down, and the same formula answers for any city.
| Good KPI | Vanity metric | |
|---|---|---|
| From the same rows | Kolhapur reach 40.0% and 7-day closure 40.0% | Kolhapur 8 events and 100% of the grant spent |
| Why | Sixty people on the target list were not reached, and 36 applications waited past 7 days. | The event calendar and the "grant fully used" line look like a successful month. |
What the manager does. The Nashik camp lead is the pattern: one event, the whole target list reached, almost the whole grant used on that reach. The Pune ward officer clears the 20 applications that missed 7 days before booking another camp. ₹30,000 of the Pune grant is still unspent, and 50 people on the target list were not reached. That is the week's work. The Kolhapur lead stops the event series. Eight events spent ₹80,000 and reached 40 people. Sambhaji Nagar is the same vanity: 6 events, reach 30 ÷ 100 = 30.0%, closed in 7 days 28 ÷ 70 = 40.0%.
Ravindra Bagale's Tip
Grant utilisation of 100% is not success by itself. Put reach % and 7-day closure next to it, and count events only when they show who was reached.
Ravindra Bagale's Tip – मराठी
100% grant utilisation म्हणजे आपोआप यश नाही. त्याच्या बाजूला reach % आणि 7-day closure ठेवा, आणि events तेव्हाच मोजा जेव्हा ते कोणापर्यंत पोचले ते दाखवतात.
Ravindra Bagale's Tip – हिंदी
100% grant utilisation अपने आप में सफलता नहीं है. उसके बगल में reach % और 7-day closure रखें, और events तभी गिनें जब वे दिखाएँ कि किन तक पहुँचे.
Quick recap
- A KPI has a numerator, a denominator, a user and an action.
- In Excel: a Table, SUM ÷ SUM for totals, calculated columns per row, SUMIFS for one city.
- Never average percentages to get a total.
- Put the vanity metric next to the KPI from the same rows, so you can see what it hides.