top of page

Week 1

July 1, 2025 - July 7, 2025

Objectives

  • Understand Michigan Solutions and AXP’s operation and dataset structure

  • Begin cleaning and preparing financial data

  • Determine the top pond aides using a matrix to evaluate the performance of the pond aide

​

Key Tools/Skills: Excel cleaning, Performance Metrics​

Pond aide's Performance Matrix ver. 1

Figure 1

Before the Data Cleaning and Data Preparation

Figure 1_edited.jpg

Looking at figure 1, the data are not put together and there are other variables that are not really important for the analysis of the performance of the pond aides. But after cleaning it, based on the figure 2 in the “detailed data” tab, there are color-coded cells which will help you to customize the excel file as much as the next user will. Yellow for manually encoded data and green for automatic or have a formula column. Pink and purple are the final output which will help to analyze who is the top performer pond aide together, by using a metric in the “Parameter Weights” tab, with their computed incentive. I also put an “Instructions and Notes” tab for the instances where the user would have a guide for using this excel file.

Figure 2

After the Data Cleaning and Data Preparation

Figure 2.png

Moreover, in this process, the important stuff that needs to be highlighted is that I made a new column which is entitled as “Scaled Profit” as one of the conditions of the company to give incentives to the pond aides is to meet an ideal biomass and to have a profit that is not negative in value. So, the formula for that column is 

=  IF(OR(Profit<0,Biomass<0),0,Profit)

Lastly, by utilizing statistical tools and methods, I put a Z-score and Standard Deviation formula to assess how well each pond aide performed relative to others and to measure how stable their results were across different ponds.

Figure 3

Version 1 of the Pond Aide’s Ranking

Figure 3.png

Pond aide's Performance Matrix ver. 2

Figure 4

Version 2 of the Detailed Data

Figure 4.png

For version 2 of the pond aide performance ranking, I added a new column and variable which is the “field/sqm” as one of the key performance indicators (KPI) as you cans ee at figure 4. My supervisor wanted it to be presented to their meeting, so I made sure that the data is well presented as you would see in the figure 5. 

Figure 5

Version 2 of the Pond Aide’s Ranking

Figure 5.png

Here, I just used the pivot table and added “Yield/Sqm,” “FCR,” “Profit,” and “Performance Score” in the values tab while the pond aide’s names are in the column tab. To rank the performance score for every worker, I used the formula,

=IF(A5="Grand Total","",COUNTIFS(E$5:E$39,">"&E5,A$5:A$39,"<>"&"Grand Total")+COUNTIFS(E$5:E$39,E5,D$5:D$39,">"&D5,A$5:A$39,"<>"&"Grand Total")+1)

This is used to rank values in a dataset with a tiebreaker, while excluding rows labeled "Grand Total" from the ranking. Moreover, I included a filter in case the user wants to know who are the top performers every cycle. 
 

Lastly, you can see in the right side of the figure 5 that there is an ascending order of ranking.  I used the formula XLOOKUP to determine the pond aide’s name, average profit, average FCR, and average yield/sqm with rank numbers as the basis. This is an example formula for mentioned variables:

=   XLOOKUP(I6,F$5:F$39,A$5:A$39,"None",0,1)

Note that the user needs to change the Cycle number in the filter menu (cell B2), and the pond aides will be automatically ranked—check columns G to I for the results.

bottom of page