Week 3
July 15, 2025 - July 21, 2025
Objectives:
-
Develop an inventory template for essential products and equipment.
-
Identify efficient pond configurations for improved shrimp production.
-
Recommend optimal scheduling and pond size utilization strategies.
Key Tools/Skills Used:
-
Conditional logic in Excel formulas
-
Pivot tables for data aggregation
-
Profitability calendar analysis
Inventory Template
To streamline equipment and supply tracking, I created a multi-tab inventory template with the following components:
1. Product Categorization
Products were categorized into: Feeds, Water Treatment, Fry & Stocking Supplies, Equipment & Spare Parts, Fuel & Utilities, Manpower-Related Supplies, Documentation & Testing Tools, and Packaging & Harvest Materials
Users can easily customize these categories by adding or removing items.
2. Vendor and Product Information
This tab provides detailed information for each product:
Product ID | Category | Description | Vendor | Typical Order Time | Shelf Life (days) | Re-order Amount | Unit | Storage Location | Contact Info
It helps streamline procurement and manage supplier details in one place.
3. Inventory Management
This is the core of the system where inputs and formulas are applied. Some key features include:
-
Automated expiration and ETA calculation using:
-
Reorders alerts via:
-
Real-time order ETA using:
4. Order Summary Generator
Using Excel’s FILTER, XLOOKUP, and simple arithmetic formulas, I automated an order list showing only the products marked for reorder. This section includes:
Product | Quantity | Storage | Expiration | Total Cost | Vendor Contact Details
=IFERROR([@Stocking Date] + XLOOKUP([@Product],'Vendor Info'!B:B,'Vendor Info'!G:G), "")
=IF(D9<=H9,"Yes","No")
=IF(I9="Yes", TODAY() + XLOOKUP(...))
Profitability Trends in Shrimp Production: Month and Pond Size Analysis
This component involved analyzing patterns across variables such as:
-
Pond
-
Harvest Month (End Month)
-
Year
-
Pond Area
-
Biomass
-
Profit
-
Profit/Kg
-
Yield/Sqm
Using pivot tables and charts (see Figure 1), I identified seasonal profitability peaks and the influence of pond size on economic performance. A detailed analysis essay accompanies the figure to interpret the findings.
Figure 1
Output for Seasonal profitability with connection with pond size
Efficiency of Shrimp Pond Designs
This section aimed to benchmark physical pond designs and determine which structural features correlate with higher productivity and profitability.
🔹 Data Collection:
I coordinated with the farm manager to collect key attributes per pond:
Pond | Area (m x m) | Total Area | Depth | Shape | With Drainage | Liner Thickness | Distance from Sea | Water Exchange Rate | Pond Elevation Problem | Probiotic/Lime/Mineral Application | History of Disease
However, for analysis, I focused on the most complete and variable fields: Total Area, Distance from Sea, Categorization of Pond, and Pond Elevation Issue
🔹 Data Integration:
I linked these with earlier computed performance metrics: Average Profit/Kg, Average Yield/Sqm, Average Survival Rate, Average FCR
Using pivot tables and bar charts, I visualized correlations between physical pond characteristics and economic performance (see Figure 2). This allowed me to draw comparisons between pond designs and their respective outcomes, laying the groundwork for design-based recommendations.
Figure 2
Output for Efficiency of Pond in terms of Size and Pond Elevation Issue
