Ravindra BagaleCourses & study guides मराठी Track your progress

27. 20 Industry Projects

27.20 Government and NGO: Fund Utilisation and Cost per Beneficiary

By Last updated:

Example first. Rani reports on six fictional district welfare schemes for a non-profit and wants fund use and cost per person reached.

Government and NGO project — in 30 seconds

What: A Government and NGO tracker: one Table tblSchemes, the calculated column Utilisation, KPI cells, a PivotTable by City, a chart and a one-page dashboard.

Why: This is the shape of a real Government and NGO report: Total Spent (₹ lakh), Overall Utilisation, Cost per Beneficiary, Schemes below 60% are the numbers a manager reads first.

Tiny example: From 6 rows: Total Spent (₹ lakh) = 460, Overall Utilisation = 78.6%, Cost per Beneficiary = ₹1,358.94, Schemes below 60% = 1.

First look at the data (before → after):

Government and NGO project: before and after tables, fictional Blinkit Maharashtra data

Expected result — Calculated column

Scheme Utilisation
School Meals 86.7%
Health Camps 65.0%
Skill Training 94.0%
Water Tanks 50.5%
Library Books 90.0%
Women SHG Loans 80.0%

Expected result — KPI cells (expected results)

KPI Formula Result
Total Spent (₹ lakh) =SUM(tblSchemes[SpentLakh]) 460
Overall Utilisation =SUM(tblSchemes[SpentLakh])/SUM(tblSchemes[BudgetLakh]) 78.6%
Cost per Beneficiary =SUM(tblSchemes[SpentLakh])*100000/SUM(tblSchemes[Beneficiaries]) ₹1,358.94
Schemes below 60% =COUNTIF(tblSchemes[Utilisation],"<0.6") 1
Before table and formula as text

Before — Table tblSchemes (fictional sample rows)

Scheme City BudgetLakh SpentLakh Beneficiaries
School Meals Solapur 120 104 8,200
Health Camps Nagpur 80 52 3,900
Skill Training Pune 150 141 2,100
Water Tanks Sambhaji Nagar 95 48 12,500
Library Books Kolhapur 30 27 5,400
Women SHG Loans Nashik 110 88 1,750

Formula / click path

Utilisation: =[@SpentLakh]/[@BudgetLakh]
Total Spent (₹ lakh): =SUM(tblSchemes[SpentLakh])
Overall Utilisation: =SUM(tblSchemes[SpentLakh])/SUM(tblSchemes[BudgetLakh])
Cost per Beneficiary: =SUM(tblSchemes[SpentLakh])*100000/SUM(tblSchemes[Beneficiaries])
Schemes below 60%: =COUNTIF(tblSchemes[Utilisation],"<0.6")

Do it step by step:

  1. Type the data and press Ctrl+T; name the Table tblSchemes in Table Design › Properties › Table Name.
  2. Add the column Utilisation with =[@SpentLakh]/[@BudgetLakh]; the Table fills it down.
  3. On a KPI sheet type =SUM(tblSchemes[SpentLakh]) and the other KPI formulas from the table below.
  4. Click Insert › PivotTable › From Table/Range, put City in Rows and SpentLakh in Values (Sum).
  5. Click PivotTable Analyze › PivotChart › Clustered Bar, then PivotTable Analyze › Insert Slicer › Scheme.
  6. Move the KPI cells, the chart and the slicer onto a sheet named Dashboard.

The brief. Build a scheme sheet: utilisation per scheme (spent ÷ budget), total spent, overall utilisation, cost per beneficiary in ₹, schemes below 60% utilisation, spending by district city, a chart and a Scheme slicer.

Calculated column formulas:

Column Formula
Utilisation =[@SpentLakh]/[@BudgetLakh]

KPI cells (check your numbers):

KPI Formula Result
Total Spent (₹ lakh) =SUM(tblSchemes[SpentLakh]) 460
Overall Utilisation =SUM(tblSchemes[SpentLakh])/SUM(tblSchemes[BudgetLakh]) 78.6%
Cost per Beneficiary =SUM(tblSchemes[SpentLakh])*100000/SUM(tblSchemes[Beneficiaries]) ₹1,358.94
Schemes below 60% =COUNTIF(tblSchemes[Utilisation],"<0.6") 1

PivotTable (expected):

City Sum of SpentLakh
Kolhapur 27
Nagpur 52
Nashik 88
Pune 141
Sambhaji Nagar 48
Solapur 104
Grand Total 460

Chart and dashboard (one under the other):

  1. Click inside the PivotTable, then PivotTable Analyze › PivotChart › Clustered Bar › OK.
  2. Add a chart title and data labels with Chart Design › Add Chart Element.
  3. Click PivotTable Analyze › Insert Slicer, tick Scheme and click OK.
  4. Insert a new sheet named Dashboard. In it, link a card cell to each KPI (for example =KPI!B2) and format it large and bold.
  5. Cut and paste the chart and slicer onto the Dashboard sheet.
  6. On the View tab, untick Gridlines in the Show group. Select the dashboard area and click Page Layout › Print Area › Set Print Area.

Template and guide: download the practice template Government_NGO_Template.xlsx and read the step-by-step template guide. The template's sample rows may differ from the rows above; the method is the same.

Practice task (try it before you open the answer):

  1. Which city has the highest sum of SpentLakh?
  2. Filter the Table on City = Pune (Data › Filter): what is Total Spent (₹ lakh) for Pune alone?
  3. Add the Clustered Bar PivotChart and the Scheme slicer to your Dashboard sheet.
Answer

Pune (141). For Pune only (1 row): Total Spent (₹ lakh) = 141. The chart shows 6 bars, one per city.

Ravindra Bagale's Tip

Write the definition of Total Spent (₹ lakh) under its KPI card in small text. Two people reading the same dashboard should agree on what the number means.

Loading practice tasks...

  1. Total donations (Easy): Add up Donations in D2:D11.
  2. Average amount (Easy): Average of E2:E11, rounded to 2 decimals.
  3. Rows in Education (Easy): How many rows have Fund "Education"?
  4. Largest donations (Easy): The largest value in D2:D11.
  5. Donations for Education (Medium): Total of D2:D11 where B is "Education".
  6. Education in Delhi (Medium): Total of D2:D11 where B is "Education" and C is "Delhi".
  7. Best in Education (Medium): Largest E2:E11 value where B is "Education".
  8. Value of row 2 (Medium): D2 times E2.
  9. Look up Emma Schmidt (Medium): The Donations for "Emma Schmidt" (use a lookup on column A).
  10. Total value (Hard): Sum of D times E for all rows.
  11. Share of Education (Hard): Share of D2:D11 that is "Education", rounded to 3 decimals.
  12. Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
  13. Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".

See all practice tasks in the Task Hub →