Production Optimization of Refill Drinking Water Using Linear Programming and Excel Solver: A Case Study of AMIL Depot in Madura - Maulidiansyah; Rohimatul Maisyaroh (CV. Laskar Karya, 2025)
In the era of data-driven decision-making and operational efficiency, the optimization of production processes has become vital for sustaining competitive advantage. AMIL, a leading refill drinking water brand in Madura, Indonesia, is striving to enhance its production profitability amidst increasing consumer demand and resource constraints. This study presents a linear programming-based approach using Microsoft Excel Solver to optimize the production planning of two key product variants 19-liter and 15-liter gallon refill bottles at the AMIL depot. By formulating an objective function that maximizes daily profit and incorporating constraints such as production capacity, raw material limitations, and market demand, the model identifies the optimal product mix. The results indicate that producing only the 19-liter variant under the given demand scenario (25 units/day) yields a maximum daily profit of IDR 150,000, while the 15-liter variant, under the same demand, yields a lower return of IDR 125,000. The optimal production ratio is thus determined to be 2:0 in favor of the 19-liter gallon. This study demonstrates the practical utility of Excel Solver as an accessible and effective decision support tool for small to medium-scale manufacturing operations. The findings offer actionable insights for resource allocation, cost minimization, and strategic production planning in the refill water industry. Furthermore, this approach may serve as a replicable framework for other enterprises seeking to optimize operational performance through linear programming.