Interview Exercise – Quality Control Officer

This exercise is designed for Enveritas to gain a better understanding of your analytical skills and abilities in
Excel. It also matches closely to work you may perform on the job. You have 2 hours to complete this exercise.

● Task 1 – Farmer yield analysis (~45 minutes)

● Task 2 – Enumerator scores analysis (~30 minutes)
● Task 3 – Farming practices analysis (~45 minutes)

Context

Enveritas gathers data via farmer surveys to learn more about the sustainability of the coffee supply chain
around the world. Each day, enumerators go out into the field and conduct surveys with coffee farmers to
collect this data. As Quality Control Officer, part of your job will be to make sense of the data collected on a
daily basis, and to follow up when the data does not make sense.

Deliverables

Send back the Excel file with your edits to the tabs (feel free to create as many new tabs as you need, but
please keep it organized). If you get stuck and cannot get to the answer or you run out of time, please write
down what you did (your approach) in the excel so we can see what you attempted.

Time for exercise: maximum of 2 hours.

Interview Exercise – Quality Control Officer

Task 1 - Farmer yield analysis

TASK: Review the attached data set that contains production data from 418 farmers across 4 coffee regions in
Colombia (each row is one farmer). Your task is to use a formula in Column M to convert the farmer production
data into a standardized unit of total annual yield. We define yield as: kilograms of green coffee equivalent
produced per hectare of coffee farmland, in total, during one year. You will need to use the conversion tables
for coffee form and coffee production units in cells O1:T7. The first four correct yield answers are shown in
Column M. Please also calculate the average yield by coffee region and rank the yields from highest to lowest
(include your answers in Column M of “Task 1” and the tab “Task 1_Response”).

GUIDANCE:

Data set: Column A is the coffee region where the farmer is located. Column B is the productive coffee farm
size, in hectares. Column C shows the coffee form sold by the farmer. Columns D/E, G/H and J/K show the
production volumes for their main harvest and second harvest (called “mitaca” or fly crop in Colombia),
depending on the coffee form the farmer sells. Columns F, I and L show the weight unit the farmer used to
report their production for each coffee form, respectively. Column M represents the yield in kilograms of green
coffee equivalent per hectare, which is a standardized unit used to compare farmer yields globally, regardless
of the coffee form they actually sell in.

Coffee forms: Find on the first tab of the excel a table (O1:T3) of conversion rates for different coffee forms,
including Cherry, Dry Cherry, Wet Parchment, Dry Parchment, and Green Coffee. Coffee form is how the
farmer sells his or her coffee. Some farmers pick coffee “cherries” (fruit) from the tree and sell them directly
(form = cherry), others dry those coffee cherries first and then sell as “dry cherry”. Others will do more
advanced processing of their coffee cherries, and sell to their buyers as “wet parchment” (a high
moisture-content form where the pulp has been removed), “dry parchment” (pulp has been removed and the
coffee has been dried out), or the more refined “green coffee” which is ready for exportation (coffee husk has
also been removed). In each step of the process, a coffee bean loses mass, which correspond to the weight
conversions.

Coffee production units: Colombian farmers may report their coffee production in kilograms, or arrobas of
12.5kg, or cargas of 125kg). These conversions are provided in the second table (O5:R7)
Interview Exercise – Quality Control Officer

Task 2 - Enumerator scores analysis

GUIDANCE: Enveritas collects survey data and uses that data to assess the coffee supply chain on a number
of criteria. You can see many of those criteria in Columns A and B of the tab “Task2” in the Excel file.

If the farmers surveyed ‘meet’ the criteria they get a score of 1. If they ‘miss’, they get a score of 0. Each
criteria assessment is based on one or more questions the survey Enveritas conducts with coffee farmers.
Each column (C4-H4) is a different enumerator on the team, eg. Mariana, Juan or Alejandro (not real names).
You will see a score for each enumerator for each criteria. This is the average score that that enumerator
recorded for all farmers they interviewed in that region, for that particular criteria. For example, Valentina
surveyed 30 farmers and 68% of them “meet” the criteria CW1 (Workers have access to clean drinking water
on site) and 32% ‘missed’ the criteria. So, her score for CW1 is 68%.

One of the things we look for to assess data quality is the differences between enumerators on the same team.
We expect the average scores to be roughly similar, since the enumerators are working in the same area and
they are visiting randomly identified farmers based on the GPS coordinates they are given. This relates to the
“law of averages” when a sample is taken for a population. We want to follow up when 1-2 enumerators have
very different scores from their teammates, or there is too much variation in the enumerator scores, versus our
expectations.

TASK: Examine Columns C-H in the tab. Use Column J (‘Discuss?’) with a ‘YES’ to mark whenever there is
too much variation in enumerator scores based on your observation. Then write the names of any particular
enumerator(s) you want to follow up with in Column K (‘Enumerator name(s)’). In Column L, include a brief
explanation – 1 or 2 sentences maximum – for why you would want to follow up with those enumerator(s).
Interview Exercise – Quality Control Officer

Task 3 - Farming practices analysis

TASK: Your task in this exercise is to analyze a dataset related to coffee yield and farm management practices
for farmers located in Ethiopia. Please provide the answers to these questions in tab “Task 3_Response” in
both excel and graph form. You can use any analytics tool to do this, and you can use any method you deem
fit. You are free. If you run out of time, use the blue box on the Task 3 tab to explain your approach.

One of our clients wants to know:

1. What is the average yield for each of the three coffee regions compared to the average yield of all three
regions combined?
2. Which farm management practices are most used among farmers across Ethiopia and in each region?
3. Of farmers that prune, the client wants to know which of the four techniques are most used?

GUIDANCE: The tab “Task 3” is a data set of farmers from three regions: Limmu, Sidama B, and Yirgachefe.
Each row is one farmer. In Column I you will see Yield. In columns D-I you will see a variety of coffee farm
management practices that we ask about in our survey. These include: “disease resistant varieties,” “organic
fertilizers,” “intercropping,” “weeding,” and “pruning techniques.” Most columns are a yes or no answer, but
Column D “Pruning techniques used” can list more than one technique that farmers use. These techniques
include: “branch_removal,” “desuckering,” “topping,” “selective_pruning.”