Performance Analysis Tracker - Excel Project (2021)

THE PROBLEM.

There were some performance challenges in the organization as Case Managers (or Service Providers) weren't meeting their quarterly service provision targets which must be 100%.

To meet this target, the total beneficiaries enrolled by Service Providers must be equal to the total beneficiaries served every quarter to meet the 100% target. They document their service provision in data collection tools (forms) which will be entered into the NOMIS database by the Data Entry Officers. Once data entry is done, the data is pulled out into Excel to be analyzed and cleaned up where necessary.

Beneficiaries are divided into 2 categories; Caregivers (CG) and Vulnerable Children (VC).

THE SOLUTION.

As the M&E Analyst, my objective was to know who to hold responsible at every point in time for drawing the organization backward and making it difficult to meet its quarterly target, so that they can be followed up on and ensure that they provide the necessary services in due time for the organization to meet its 100% quarterly target.

I devised a strategy to monitor the performance of the Service Providers in real-time by designing an Excel Spreadsheet that pulls information from 4 different spreadsheets containing lists of beneficiaries enrolled (CG list and VC list), and the list of beneficiaries provided with services (CG list and VC list) in a quarter. The names of the Service Providers handling various beneficiaries are included in the data for all the lists.

I used the COUNTIF function in Excel, using the Service Providers' names as criteria or conditions for counting. The COUNTIF function generated each Service Providers' target from the list of enrolled beneficiaries, and the achievement of the target from the list of served beneficiaries. I also included the simple SUM function where necessary.

No number or metric was hard coded.

I decided to add some professional touch to it by using Conditional Formatting (formula method) to highlight Service Providers who were below 50% in Red, above 90% in Blue, and 100% in Green. Those who achieved 50% - 89% weren't highlighted and considered average or working towards the target. This made it easy to visually analyze and quickly spot certain performances based on the colour code.

This simple performance analysis was shared weekly and it made every Service Provider sit up and push towards achieving a 100% quarterly target.