Client Outcome Performance (COPE) Indicators Database & Instructions

Background
Between 2013 and 2015, Freedom from Hunger and the Microcredit Summit Campaign, with the support
of an educational grant from Johnson & Johnson, collaborated with four microfinance institutions (MFIs)
across India, the Philippines, and Peru to test a set of simple health outcome performance indicators,
known as the HOPI,
1
to help assess changes in client well-being over time. While one main obstacle to
understanding changes in client well-being for MFIs is choosing strategically among many indicators,
another obstacle that MFIs face is in timely data-analysis and interpretation. Therefore, with additional
support from Johnson & Johnson, a simple Microsoft Excel-based tool was developed to assist with data
collection, data analysis, and interpretation. While the use of the HOPI inspired the development of the
database, it was ultimately designed to incorporate any chosen client outcome indicator, whether it be
health, women’s empowerment, resilience, assets, etc. This new tool has, therefore, been named the Client
Outcome Performance (COPE) Indicator Database. The construction of this particular database assumes
that the MFI is using Grameen Foundation’s Progress out of Poverty Index® (PPI®).
2

Acknowledgements
Many thanks go to those who made this database possible: Anne-Laure Behaghel, Independent
consultant; Bobbi Gray, Freedom from Hunger; and DSK Rao and Sabina Rogers, Microcredit Summit
Campaign. We would like to thank Johnson & Johnson for investing in this tool as well as our
collaborators whose ideas were important in seeing this tool to fruition: Mark Schreiner of Microfinance
Risk Management, L.L.C. and Frank Ballard and Julie Peachey of Grameen Foundation.
Introduction
This database file is aimed at helping microfinance institutions (MFIs) track and monitor their client
outcome performance indicators.
It is built to
◊ compile clients’ poverty levels using the PPI and other user-defined data collected through a
survey and
◊ provide a general analysis of those indicators as well as a detailed analysis of the possible
correlations between those indicators.
The database contains the following tabs:
◊ My survey questions  where you set up your question-and-answer options
◊ My PPI questions  where you set up your PPI question-and-answer options
◊ My PPI look-up table  where you set up your country’s poverty-likelihood table

(it is an extract from the PPI Simple Data Intake Tool previously
developed by the Grameen Foundation)

◊ Database  where you enter the data you collected through your survey
1Gray, B. (2015). Healthy, Wealthy and Wise: How Microfinance Institutions Can Track the Health of Clients. Health Outcome
Performance Indicators (HOPI) Project Report. Freedom from Hunger. Davis, CA. Available at:

https://www.freedomfromhunger.org/healthy-wealthy-and-wise-how-microfinance-institutions-can-track-health-
clients-health-outcome

2 Learn more about the Progress out of Poverty Index here: http://www.progressoutofpoverty.org/.

3
◊ Control  where you check whether your Database is properly completed
◊ General results  where you will find the full statistical results of your survey
◊ Dashboard  where you will find graphs generated from the General results
◊ Detailed results  where you will find each client outcome indicator broken down by

the variables of your choice

◊ PPI & Food Security  where you will find the breakdown of your clients below a given

poverty line broken down by Food Security status

◊ PPI & Q1 etc.– there is
one tab per indicator

 where you will find a more detailed analysis of each client outcome
indicator for people below a given poverty line
◊ [hidden] Lists  structural sheet—should not be used
Please note that in the entire file, only tan cells are where you can enter your custom
information. All other cells are links or formulas, which means that you should not make

any changes to any cell, row or column unless they are tan. Making changes to non-
colored cells risk breaking a link or a formula, which may result in altered data that does not

reflect reality.
Most tabs are “protected” to avoid mistakenly typing in a cell and changing a formula or link.
Although it is not recommended, if you encounter the need to, you can unprotect each tab
separately, there is no password (“Review”—>“Unprotect sheet”).
Initial Assumptions
 You are using MSExcel 2007 or higher
 You have a PPI questionnaire and a PPI-likelihood table for your country
 You have a survey questionnaire with 1 to 10 questions and 1 to 10 answer options
 You are familiar and comfortable with the Excel Pivot Table basic features.
Prepare Your File For The Analysis
Always use a new and empty file; deleting previous data may lead to errors.
Make sure you enable macros.

  1. On “My survey questions” tab, insert the MFI name at the top of the sheet.
  2. Enter for each question of your survey a generic indicator name (that will appear in the graph titles)
    and enter your question-and-answer options.
     This tab is linked to the “Database” tab to
    allow the data-entry user to select in a
    drop-down menu the answer given by the
    client
     It is absolutely essential that you use the
    drop-down menu to register the survey
    answers so that they are properly coded in
    the database. Copy/pasting may generate errors in statistics.
  3. In “My PPI questions” tab, enter the PPI questions, answer set and score/numeric weights.
     The PPI score for each surveyed person will calculate automatically based on the scores you
    define here.
  4. Go to “My PPI look-up table” and enter the poverty lines that you want to track in the headers of the
    table.

“Database” uses the “Table” MSExcel
feature (version 2007 or higher). As you
enter your survey data row after row,
your table grows, and characteristics
are automatically entered. This
translates in the row changing colors.

4

  1. Copy and paste the poverty likelihoods that you have for your country in the table, and add the
    national benchmarks in the smaller table on the right.
     The calculated PPI score will pick in this table the poverty likelihoods for each poverty lines that
    you have defined.
  2. Enter the data collected during your survey in the “Database” tab. Make sure you enter the Client
    ID since this information is the basis of all further results, because it is the only unique identifier of a
    person. If you do not have specific IDs, you can also number each row in this column from 1 to xxx.
    To add a new row, place your curser in the cell below your last Client ID, enter the new Client ID and
    press Enter. The whole row should change color. If the row does not change colors, check your
    “Table” feature settings: File—> Options—>Proofing—>AutoCorrect Options—>AutoFormat as
    you type—>Include new rows and columns in table.
  3. Go to the “Control” tab to see whether any error messages appear. Return to “Database” to correct
    any mistake that were highlighted.
     This tab makes some control checks on the database entries to ensure the reliability of your
    results.
    Analyze Your Data
    Update of all pivot tables is automatic and occurs when you go to the following:
    ◊ “General results” tab
  4. Selected client outcome indicators
    The first results’ table stems from an exhaustive pivot table (hidden in columns B, C, D) with all
    possible answer options listed there. You may not want to see all results in one graph. First, you can
    simply use the filter on the MFI column (G) to filter out all zero values or simply hide the rows and
    columns you do not want to see on the graph.
    For instance, you may not need to show both percentages, clients who are Food Secure and Food
    Insecure, and just want to show the percentage of food-insecure people in your sample. When you
    hide the rows with the answers you do not need, your dashboard graph will update accordingly.
  5. Estimated share of people below the various poverty lines
    This table shows the same results as at the top of the previous one, but as a stand-alone. Note that it
    is not a pivot table so that it can capture the national benchmark that you entered in the “My PPI
    look-up table” tab.
  6. Food Security Status
    This table controls the chart on the “Dashboard” tab. You can change the results you want to see by
    using the pivot table features.
    ◊ “Detailed results” tab
    This tab presents each survey indicator (PPI likelihood, Food Security status and each user-defined
    indicator + Satisfaction results) in separate pivot tables with corresponding graphs on the right.
    Here you can:
  7. Break down the survey results by (i) Branch/Region, (ii) Age range, (iii) Membership length, (iv)
    Food Security Status and/or (v) Gender.

5
Place your mouse curser inside the pivot table and you will see the Pivot Table fields appear on
the right. Scroll down in those fields to change the column pivot field, which will affect the
breakdown. This tab is built to allow two concomitant breakdowns.

  1. Use the graphs on your dashboard or your reports.
    The graph titles are linked to the bold and underlined table titles. They update automatically with
    the two tan cells below where you select the breakdown that you have set in the pivot table.
    Update those tan cells according to the breakdown you have chosen in the first step, so that your
    graph shows the right title.
    Then copy and paste the graphs that you want to show on your dashboard.
    ◊ “PPI & [indicators]” tabs
    These tabs focus more specifically on the people below the various poverty lines. Each of the outcome
    indicators is presented in a separate tab.
  2. Profile of people below the various poverty lines
    For a given indicator and a given poverty line, the graphs show you the estimated breakdown of
    people below this poverty line according to the answer they gave.

By scrolling down, you will find the table used for the graphs, presenting this detailed breakdown
of your “likely-to-be-poor” population.

  1. Poverty rates
    The poverty rate looks at your total surveyed population and estimates, for each answer, the
    likelihood of those who gave this answer to be poor.
  2. Profile of people above the various poverty lines [advanced users only]
    This table is hidden below the poverty rates. If you need it, you can unhide and use.
    Please keep in mind that it is an “estimation” because it is a weighted average across
    all those who gave a particular answer, where the weight is the percentage that is likely
    to be below the given poverty line.
    For example, one person who has a likelihood of 75 percent to be below a given
    poverty line, and who answered “Yes,” will count in this breakdown as 0.75 of a “Yes”
    answer (and will count as 0.25 of a “Yes” answer in the population living above the
    poverty line).

6

Appendix—Examples of Analysis
Poverty rate
Let us examine the detailed poverty rate of the sources of drinking water:
Column1

Answers–> Piped water
(32 clients)
Tube well or
borehole
(258 clients)
Dug well
(108 clients)
Surface water (4
clients)

Rainwater (1
clients)
Poverty line Number of answers 32 258 108 4 1
$1.25 Poverty Line Poverty rate(<$1.25 Poverty Line) 37% 45% 46% 61% 42%
$2.50 Poverty Line Poverty rate(<$2.50 Poverty Line) 75% 82% 84% 92% 85%
National Poverty Line Poverty rate (<National Poverty Line) 29% 37% 39% 51% 31%
Thirty-seven (37) percent of the people who use piped water are likely to be below the US$1.25 poverty
line; 61 percent of those who use surface water are likely to be below the $1.25 poverty line.
Detailed results
This table below shows the results of 400 clients who answered the question as to whether they treat
water or not. Here, we have chosen to analyze the correlation of water treatment with the food security
status and gender.

You can see for instance that:
 42.3 percent of the 403 people answered that they don’t treat water
 23.1 percent of the total sample are food insecure and don’t treat water
 15.4 percent of the total sample are food insecure females who don’t treat water.
Then by playing with the pivot table columns, you can also see it that way:
Client Name

Female Column3 Total Female Male Column4 Total Male
MFI
TOTAL

Food Insecure Food Secure Food Insecure Food Secure

Doesn’t know 3.8% – 3.8% 15.4% – 15.4% 19.2%
Doesn’t treat water 15.4% 7.7% 23.1% 7.7% 11.5% 19.2% 42.3%
Treats water 19.2% – 19.2% 19.2% – 19.2% 38.5%
Treats water—breakdown by Food Security Status and Gender (403 clients) #answers
Breakdown by Food Security Status What 1st breakdown did you choose in your Pivot Table? 403
and by Gender What 2nd breakdown did you choose in your Pivot Table?
Client Name

Food Insecure Column3 Total Food Insecure Food Secure Column4 Total Food Secure MFI TOTAL
Female Male Female Male

Doesn’t know 3.8% 15.4% 19.2% – – – 19.2%
Doesn’t treat water 15.4% 7.7% 23.1% 7.7% 11.5% 19.2% 42.3%
Treats water 19.2% 19.2% 38.5% – – – 38.5%

Client Name Female Column3 Total Female Male Column4 Total Male MFI TOTAL

Food Insecure Food Secure Food Insecure Food Secure

Doesn’t know 3.8% 15.4% 19.2% – – – 19.2%
Doesn’t treat water 15.4% 7.7% 23.1% 7.7% 11.5% 19.2% 42.3%
Treats water 19.2% 19.2% 38.5% – – – 38.5%

7

Or simply:
Client Name

Female Male MFI TOTAL
Doesn’t know 3.8% 15.4% 19.2%
Doesn’t treat water 23.1% 19.2% 42.3%
Treats water 19.2% 19.2% 38.5%
PPI & each outcome indicator
Let us say you want to analyze whether there is a correlation between the poverty rate and water
treatment. A correlation would suggest that a change in one indicator, will mean a change in the another.
Your “General results” tab shows that
44.7 percent of the surveyed people are
likely to be below the $1.25 poverty line.
These 44.7 percent represent the new
total population you are looking at.
In your detailed PPI results tab, you find that of those living below the
$1.25 poverty line (the 44.7 percent living below the $1.25 poverty line)
the share of them who treat their water is 56 percent. This means, 56
percent of the very poor (total population living below the $1.25 poverty
line) treat their water.
You compare this to your general results, where you see that among the
403 people surveyed (the total population) 38.5 percent answered “Yes”
to the question whether they treat water:

  1. Selected client outcome indicators Treats water
    MFI
    X
    Don’t Know (77 clients) 19.2%
    No (171 clients) 42.3%
    Yes (155 clients) 38.5%
    This indicates that within the 44.7 percent of people who are likely to be below the $1.25 poverty line, 56
    percent would be likely to treat their water. This rate is higher among your total surveyed population
    (38.5%). There is a potential correlation between poverty and the of treating water suggesting that the
    poorer the person, the more likely they treat their water.

Leave a Comment

Your email address will not be published. Required fields are marked *