top of page

Week 2

July 8, 2025 - July 14, 2025

Objectives

  • Evaluate pond, module, and cycle performance based on key indicators: yield per square meter, feed conversion ratio (FCR), and profit.

  • Incorporate months cultured and prevailing weather conditions into performance analysis.

  • Analyze distribution of production costs across different cycles and modules to identify cost-performance trends.

​​

Key Tools/Skills

  • Advanced use of Excel (Pivot Tables, Ranking Formulas, Conditional Logic)

  • Data interpretation and pattern recognition

  • Linking cost structure to farm productivity and profitability

Comprehensive Performance Analysis of Ponds, Modules, and Cycles

To evaluate pond efficiency, I developed a weighted performance scoring system based on four key aquaculture metrics: Profit per Kilo (40%), Yield per Square Meter (30%), Survival Rate (20%), and Feed Conversion Ratio (FCR) (10%). These weights were carefully chosen to balance financial, operational, and biological factors—critical in assessing pond-level productivity and profitability.


Using the raw data, I computed each pond's Performance Score using the formula:

Performance Score = 0.4*(Profit/Kilo) + 0.3*(Yield/Sqm) + 0.2*(Survival Rate) + 0.1*(1/FCR)

This score allows for direct comparison of ponds regardless of size, location, or cycle. I also calculated the Z-score of each pond's performance to evaluate how far each score deviated from the overall average:​​​​​​​

Z-score = (Score - Mean) / Standard Deviation

This helped classify ponds as:

  • Above Average (Z > 0)

  • Average (Z = 0)

  • Below Average (Z < 0)

These analyses were then grouped and visualized using pivot tables by Cycle, Module, and Pond, as seen in Figures 1, 2, and 3. This pivoted view allowed clearer identification of trends across time, locations, and operational groupings. The modular and cyclical breakdown also supported insights into consistent top-performing areas and revealed underperforming sections requiring intervention.

FIgure 1, 2, 3

Comprehensive Performance Analysis of Ponds, Modules, and Cycles

Performance Score per Cycle.png
Performance Score per Module.png
Performance Score per Pond.png

This analysis reflects a strong application of mathematical logic, statistical normalization, and Excel data tools—all crucial for evidence-based decision-making in aquaculture management.

Seasonal and Monthly Production
Trends Assessment

This part of the analysis focused on identifying patterns in shrimp production by assessing seasonal and monthly trends. It required extensive use of formulas due to limitations in Excel and WPS, particularly when handling dates and custom logic for seasonal grouping.

Figure 4

Detailed Data of Seasonal and Monthly Production Trends Assessment

detailed data season analysis.png

To start, I worked on extracting and transforming date-based data in the “Detailed Date for Seasonal Analysis” tab (see Figure 4). I used formulas to derive the Start Month and End Month from the stocking and harvest dates using:

  • =TEXT(D2, "mmmm") for the Start Month

  • =TEXT(F2, "mmmm") for the End Month

To facilitate month-based calculations, I built a mapping table that assigned numeric values to each month (Figure 5). A helper column then used VLOOKUP to retrieve these numeric equivalents.

Figure 5

Mapping Table for Months

mapping table.png

To calculate the range of months a cycle spanned, I used a formula combining LET, SEQUENCE, and TEXTJOIN to list all the months cultured between start and end months, even accounting for wrapping around December to January:

=LET(months,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},

start,H2,end_,I2,length,IF(end_>=start,end_-start+1,12-start+end_+1),

sequence,MOD(SEQUENCE(length,,start,1)-1,12)+1,

TEXTJOIN(", ",TRUE,INDEX(months,sequence)))

Once I identified the cultured months per cycle, I cross-referenced them with climate classifications from PAG-ASA, the Philippines’ weather agency. According to PAG-ASA, the country experiences four main seasonal phases:

  • DJF: December–February (Amihan or cool dry)

  • MAM: March–May (Hot dry/summer)

  • JJA: June–August (Habagat or rainy)

  • SON: September–November (transition period)
     

Using a custom formula, I flagged which monsoon seasons overlapped with each cycle:

=LET(val,J2,
djf,SUM(--ISNUMBER(SEARCH({"Dec","Jan","Feb"},val))),
mam,SUM(--ISNUMBER(SEARCH({"Mar","Apr","May"},val))),
jja,SUM(--ISNUMBER(SEARCH({"Jun","Jul","Aug"},val))),
son,SUM(--ISNUMBER(SEARCH({"Sep","Oct","Nov"},val))),
TEXTJOIN(", ",TRUE,IF(son>0,"SON",""),IF(djf>0,"DJF",""),
IF(mam>0,"MAM",""),IF(jja>0,"JJA","")))

Then, I used a more complex nested IF logic to determine the Primary Season — the season that covered the majority of the months within a given cycle.


Finally, I used Pivot Tables to visualize and summarize the results by season, month, and cycle, showing how production trends aligned with environmental conditions. This revealed, for instance, whether specific modules or ponds performed better during Amihan vs. Habagat, or how the yield varied across months as seen in Figure 6.

Figure 6

Seasonal Analysis of the Shrim Farm's Production 

season analysis output.png

These insights are vital for making data-driven decisions about when to stock and harvest shrimp, as well as adjusting strategies based on seasonal risks like temperature fluctuations and rainfall.

Production Cost Distribution Analysis

To evaluate the overall production cost structure more accurately, I integrated additional financial factors—specifically, the Operational Expenses (OPEX) and loan interest for the year 2025—into the existing pond-level cost dataset.
Initially, the data already contained average values for key input costs such as feeds, fry, chlorine, limes & probiotics, manpower, electricity, pumphouse, and nursery. However, to align with management’s directive to reflect the true cost per kilogram more holistically, I incorporated both monthly OPEX and interest charges based on actual biomass harvested.

  1. OPEX Allocation:
     

    • I started by organizing the OPEX data by month and year to match the harvest closing dates.

    • I then computed the total biomass harvested per month.

    • Using a proportional allocation formula, I assigned the monthly OPEX to each harvest based on its biomass share:

    • From this, I calculated OPEX per kg and integrated it into the Detailed Data sheet using the VLOOKUP function.

​

​

​

​

​

​

​​

​

​

  1. Interest Allocation:
     

    • The loan for 2025 was manually recorded into the dataset.

    • I applied a similar method as with OPEX, distributing the interest per harvest based on biomass and closed harvest date.

    • Again, this was merged into the main dataset using VLOOKUP.
       

  2. Final Analysis:
     

    • Once both cost factors were added, I constructed a pivot table to compute the updated averages of key performance indicators (KPIs) such as cost per kilo, profit, and other metrics—now inclusive of OPEX and loan interest.

    • These are visualized in Figure 7, which illustrates the year-over-year trend in production costs and KPIs.

Allocated OPEX=1,781,125×(Biomass of Harvested pond/Total Monthly Biomass)

Figure 7

Output for the Production Cost Analysis using 100% stacked bar graph

Output 3.png
bottom of page