# Complete Optimization Problem and Solution: small_bank_1

## 1. Problem Context and Goals

### Context  
A small bank is focused on optimizing the allocation of funds between savings and checking accounts for its customers to maximize interest income while ensuring liquidity and regulatory compliance. The bank must decide how much to allocate to each customer's savings and checking accounts based on their total available funds. The interest rates for savings and checking accounts are fixed at 3% and 1%, respectively, and these rates directly influence the bank's income.  

The bank must adhere to several operational rules:  
- Each customer's total allocation to savings and checking accounts cannot exceed their total available funds.  
- Savings accounts must maintain a minimum balance of $100 to avoid fees and ensure account activity.  
- Checking accounts must maintain a minimum balance of $50 for the same reasons.  
- Savings accounts cannot exceed a maximum balance of $50,000 to manage risk and liquidity.  
- Checking accounts cannot exceed a maximum balance of $30,000 for the same purpose.  
- The bank’s total funds across all accounts cannot exceed $1,000,000, reflecting its operational scale.  

These rules ensure that the bank’s decisions are both profitable and compliant with regulatory requirements. The problem is structured to maintain a linear relationship between decision variables and constraints, ensuring computational efficiency and scalability.

### Goals  
The primary goal of this optimization is to maximize the bank’s total interest income from savings and checking accounts. This is achieved by strategically allocating each customer’s funds between their savings and checking accounts, weighted by the respective interest rates. Success is measured by the total interest income generated, which is directly influenced by the interest rates and the allocated balances. The optimization ensures that all operational and regulatory constraints are met while achieving the highest possible income.

## 2. Constraints  

The optimization must adhere to the following constraints:  
1. **Total Funds Constraint**: For each customer, the sum of their savings and checking account balances cannot exceed their total available funds.  
2. **Minimum Savings Balance Constraint**: Each customer’s savings account balance must be at least $100.  
3. **Minimum Checking Balance Constraint**: Each customer’s checking account balance must be at least $50.  
4. **Maximum Savings Balance Constraint**: Each customer’s savings account balance cannot exceed $50,000.  
5. **Maximum Checking Balance Constraint**: Each customer’s checking account balance cannot exceed $30,000.  
6. **Total Bank Funds Constraint**: The sum of all savings and checking account balances across all customers cannot exceed the bank’s total available funds of $1,000,000.  

These constraints ensure that the bank’s allocations are both feasible and compliant with operational and regulatory requirements.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for missing optimization requirements and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE CUSTOMER_FUNDS (
  custid INTEGER,
  total_funds FLOAT
);

CREATE TABLE SAVINGS (
  custid INTEGER,
  balance FLOAT
);

CREATE TABLE CHECKING (
  custid INTEGER,
  balance FLOAT
);
```

### Data Dictionary  
- **CUSTOMER_FUNDS**:  
  - **Purpose**: Tracks the total funds available for each customer to allocate between savings and checking accounts.  
  - **Columns**:  
    - `custid`: Unique identifier for each customer.  
    - `total_funds`: Total funds available for the customer, used to constrain the sum of their account balances.  

- **SAVINGS**:  
  - **Purpose**: Tracks the balance in each customer’s savings account.  
  - **Columns**:  
    - `custid`: Unique identifier for each customer.  
    - `balance`: The balance in the savings account, which is a decision variable in the optimization.  

- **CHECKING**:  
  - **Purpose**: Tracks the balance in each customer’s checking account.  
  - **Columns**:  
    - `custid`: Unique identifier for each customer.  
    - `balance`: The balance in the checking account, which is a decision variable in the optimization.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical banking practices, ensuring realistic interest rates, balance thresholds, and total funds that align with a small bank's operational scale.

-- Realistic data for CUSTOMER_FUNDS
INSERT INTO CUSTOMER_FUNDS (custid, total_funds) VALUES (1, 1000.0);
INSERT INTO CUSTOMER_FUNDS (custid, total_funds) VALUES (2, 1500.0);
INSERT INTO CUSTOMER_FUNDS (custid, total_funds) VALUES (3, 2000.0);

-- Realistic data for SAVINGS
INSERT INTO SAVINGS (custid, balance) VALUES (1, 500.0);
INSERT INTO SAVINGS (custid, balance) VALUES (2, 750.0);
INSERT INTO SAVINGS (custid, balance) VALUES (3, 1000.0);

-- Realistic data for CHECKING
INSERT INTO CHECKING (custid, balance) VALUES (1, 300.0);
INSERT INTO CHECKING (custid, balance) VALUES (2, 450.0);
INSERT INTO CHECKING (custid, balance) VALUES (3, 600.0);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( s_i \) be the balance in the savings account for customer \( i \) (continuous variable).  
- Let \( c_i \) be the balance in the checking account for customer \( i \) (continuous variable).  

#### Objective Function
Maximize the total interest income:  
\[
\text{Maximize } Z = 0.03 \sum_{i=1}^{3} s_i + 0.01 \sum_{i=1}^{3} c_i
\]  
**Coefficients**:  
- \( 0.03 \): Interest rate for savings accounts (business configuration).  
- \( 0.01 \): Interest rate for checking accounts (business configuration).  

#### Constraints
1. **Total Funds Constraint**: For each customer \( i \), the sum of savings and checking balances cannot exceed their total available funds:  
\[
s_i + c_i \leq \text{CUSTOMER_FUNDS.total_funds}[i] \quad \forall i \in \{1, 2, 3\}
\]  
**Coefficients**:  
- \( \text{CUSTOMER_FUNDS.total_funds}[i] \): Total funds for customer \( i \) (from CUSTOMER_FUNDS table).  

2. **Minimum Savings Balance Constraint**: Each customer’s savings account balance must be at least $100:  
\[
s_i \geq 100 \quad \forall i \in \{1, 2, 3\}
\]  
**Coefficients**:  
- \( 100 \): Minimum savings balance (business configuration).  

3. **Minimum Checking Balance Constraint**: Each customer’s checking account balance must be at least $50:  
\[
c_i \geq 50 \quad \forall i \in \{1, 2, 3\}
\]  
**Coefficients**:  
- \( 50 \): Minimum checking balance (business configuration).  

4. **Maximum Savings Balance Constraint**: Each customer’s savings account balance cannot exceed $50,000:  
\[
s_i \leq 50000 \quad \forall i \in \{1, 2, 3\}
\]  
**Coefficients**:  
- \( 50000 \): Maximum savings balance (business configuration).  

5. **Maximum Checking Balance Constraint**: Each customer’s checking account balance cannot exceed $30,000:  
\[
c_i \leq 30000 \quad \forall i \in \{1, 2, 3\}
\]  
**Coefficients**:  
- \( 30000 \): Maximum checking balance (business configuration).  

6. **Total Bank Funds Constraint**: The sum of all savings and checking account balances across all customers cannot exceed $1,000,000:  
\[
\sum_{i=1}^{3} s_i + \sum_{i=1}^{3} c_i \leq 1000000
\]  
**Coefficients**:  
- \( 1000000 \): Total bank funds (business configuration).  

#### Data Source Verification
- **CUSTOMER_FUNDS.total_funds**: From CUSTOMER_FUNDS table.  
- **Interest rates (0.03 and 0.01)**: From business configuration.  
- **Minimum and maximum balances (100, 50, 50000, 30000)**: From business configuration.  
- **Total bank funds (1000000)**: From business configuration.  

This is a complete, immediately solvable LINEAR mathematical model.

## 5. Gurobipy Implementation

```python
#!/usr/bin/env python3
"""
Gurobipy 12.0.2 Implementation for Small Bank Optimization Problem
"""

import gurobipy as gp
from gurobipy import GRB

def small_bank_optimization():
    """Optimize fund allocation between savings and checking accounts to maximize interest income."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("small_bank_optimization")
    
    # Data from the problem
    customers = [1, 2, 3]
    total_funds = {1: 1000.0, 2: 1500.0, 3: 2000.0}
    savings_interest = 0.03
    checking_interest = 0.01
    min_savings = 100
    min_checking = 50
    max_savings = 50000
    max_checking = 30000
    total_bank_funds = 1000000
    
    # CRITICAL: Validate array lengths before loops
    assert len(customers) == len(total_funds), "Array length mismatch"
    
    # 2. VARIABLES
    s = {i: model.addVar(vtype=GRB.CONTINUOUS, name=f"s_{i}", lb=min_savings, ub=max_savings) 
         for i in customers}
    
    c = {i: model.addVar(vtype=GRB.CONTINUOUS, name=f"c_{i}", lb=min_checking, ub=max_checking) 
         for i in customers}
    
    # 3. OBJECTIVE FUNCTION
    model.setObjective(
        gp.quicksum(savings_interest * s[i] + checking_interest * c[i] for i in customers), 
        GRB.MAXIMIZE
    )
    
    # 4. CONSTRAINTS
    
    # Total Funds Constraint
    for i in customers:
        model.addConstr(s[i] + c[i] <= total_funds[i], name=f"total_funds_{i}")
    
    # Total Bank Funds Constraint
    model.addConstr(
        gp.quicksum(s[i] + c[i] for i in customers) <= total_bank_funds, 
        name="total_bank_funds"
    )
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in customers:
            print(f"Customer {i}: Savings = {s[i].x:.2f}, Checking = {c[i].x:.2f}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Execute the optimization
if __name__ == "__main__":
    small_bank_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 132.0
**Execution Time**: 0.18 seconds
**Reliability**: high
**Analysis**: Gurobipy found the optimal solution efficiently with the fastest execution time.

## 6. DOCplex Implementation

```python
#!/usr/bin/env python3
"""
DOCPLEX implementation for small_bank_1 optimization problem
"""

from docplex.mp.model import Model

def small_bank_optimization():
    """Optimize fund allocation between savings and checking accounts"""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="small_bank_1")
    
    # Data from CUSTOMER_FUNDS table
    customer_ids = [1, 2, 3]
    total_funds = [1000.0, 1500.0, 2000.0]
    
    # Business configuration parameters
    savings_interest_rate = 0.03
    checking_interest_rate = 0.01
    min_savings_balance = 100.0
    min_checking_balance = 50.0
    max_savings_balance = 50000.0
    max_checking_balance = 30000.0
    total_bank_funds = 1000000.0
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(customer_ids) == len(total_funds), "Array length mismatch"
    safe_range = range(min(len(customer_ids), len(total_funds)))  # Safe indexing
    
    # 2. VARIABLES
    savings = {i: mdl.continuous_var(name=f"savings_{i}", lb=0) for i in safe_range}
    checking = {i: mdl.continuous_var(name=f"checking_{i}", lb=0) for i in safe_range}
    
    # 3. OBJECTIVE FUNCTION
    total_interest = mdl.sum(savings_interest_rate * savings[i] + checking_interest_rate * checking[i] for i in safe_range)
    mdl.maximize(total_interest)
    
    # 4. CONSTRAINTS
    
    # Total Funds Constraint
    for i in safe_range:
        mdl.add_constraint(savings[i] + checking[i] <= total_funds[i], ctname=f"total_funds_{i}")
    
    # Minimum Savings Balance Constraint
    for i in safe_range:
        mdl.add_constraint(savings[i] >= min_savings_balance, ctname=f"min_savings_{i}")
    
    # Minimum Checking Balance Constraint
    for i in safe_range:
        mdl.add_constraint(checking[i] >= min_checking_balance, ctname=f"min_checking_{i}")
    
    # Maximum Savings Balance Constraint
    for i in safe_range:
        mdl.add_constraint(savings[i] <= max_savings_balance, ctname=f"max_savings_{i}")
    
    # Maximum Checking Balance Constraint
    for i in safe_range:
        mdl.add_constraint(checking[i] <= max_checking_balance, ctname=f"max_checking_{i}")
    
    # Total Bank Funds Constraint
    total_allocated = mdl.sum(savings[i] + checking[i] for i in safe_range)
    mdl.add_constraint(total_allocated <= total_bank_funds, ctname="total_bank_funds")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in safe_range:
            savings_value = solution.get_value(savings[i])
            checking_value = solution.get_value(checking[i])
            print(f"Customer {customer_ids[i]}: Savings = {savings_value:.2f}, Checking = {checking_value:.2f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Execute the optimization
if __name__ == "__main__":
    small_bank_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 132.0
**Execution Time**: 1.24 seconds
**Reliability**: high
**Analysis**: DOCplex also found the optimal solution but took longer compared to Gurobipy.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

import pyomo.environ as pyo
from pyomo.opt import SolverFactory

def small_bank_optimization():
    """Optimize fund allocation between savings and checking accounts for a small bank."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    # Customer IDs and their total funds
    customer_ids = [1, 2, 3]
    total_funds = {1: 1000.0, 2: 1500.0, 3: 2000.0}
    
    # Interest rates
    savings_interest_rate = 0.03
    checking_interest_rate = 0.01
    
    # Minimum and maximum balances
    min_savings_balance = 100
    min_checking_balance = 50
    max_savings_balance = 50000
    max_checking_balance = 30000
    
    # Total bank funds
    total_bank_funds = 1000000
    
    # CRITICAL: Validate array lengths before indexing
    assert len(customer_ids) == len(total_funds), "Array length mismatch"
    
    # 3. SETS
    model.I = pyo.Set(initialize=customer_ids)  # Set of customers
    
    # 4. PARAMETERS
    model.total_funds = pyo.Param(model.I, initialize=total_funds)
    
    # 5. VARIABLES
    model.s = pyo.Var(model.I, within=pyo.NonNegativeReals, bounds=(min_savings_balance, max_savings_balance))  # Savings balance
    model.c = pyo.Var(model.I, within=pyo.NonNegativeReals, bounds=(min_checking_balance, max_checking_balance))  # Checking balance
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return savings_interest_rate * sum(model.s[i] for i in model.I) + checking_interest_rate * sum(model.c[i] for i in model.I)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    
    # Total Funds Constraint
    def total_funds_rule(model, i):
        return model.s[i] + model.c[i] <= model.total_funds[i]
    model.total_funds_constraint = pyo.Constraint(model.I, rule=total_funds_rule)
    
    # Total Bank Funds Constraint
    def total_bank_funds_rule(model):
        return sum(model.s[i] for i in model.I) + sum(model.c[i] for i in model.I) <= total_bank_funds
    model.total_bank_funds_constraint = pyo.Constraint(rule=total_bank_funds_rule)
    
    # 8. SOLVING WITH GUROBI
    solver = SolverFactory('gurobi')
    
    # Optional: Set solver options
    solver.options['TimeLimit'] = 300  # 5 minutes
    solver.options['MIPGap'] = 0.01    # 1% gap
    
    # Solve the model
    results = solver.solve(model, tee=True)  # tee=True shows solver output
    
    # 9. RESULT PROCESSING
    # Check solver status
    if results.solver.termination_condition == pyo.TerminationCondition.optimal:
        print("Optimal solution found!")
        print(f"Optimal value: {pyo.value(model.objective)}")
        
        # Extract variable values
        print("\nVariable values:")
        for i in model.I:
            s_val = pyo.value(model.s[i])
            c_val = pyo.value(model.c[i])
            print(f"Customer {i}: Savings = {s_val:.2f}, Checking = {c_val:.2f}")
        
    elif results.solver.termination_condition == pyo.TerminationCondition.infeasible:
        print("Problem is infeasible")
    elif results.solver.termination_condition == pyo.TerminationCondition.unbounded:
        print("Problem is unbounded")
    else:
        print(f"Solver terminated with condition: {results.solver.termination_condition}")
    
    return model

# Run the optimization
if __name__ == "__main__":
    small_bank_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 132.0
**Execution Time**: 0.89 seconds
**Reliability**: high
**Analysis**: Pyomo found the optimal solution with an execution time between Gurobipy and DOCplex.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 132.00 | 0.18s | N/A | N/A |
| Docplex | OPTIMAL | 132.00 | 1.24s | N/A | N/A |
| Pyomo | OPTIMAL | 132.00 | 0.89s | N/A | N/A |

### Solver Consistency Analysis
**Result**: All solvers produced consistent results ✓
**Consistent Solvers**: gurobipy, docplex, pyomo
**Majority Vote Optimal Value**: 132.0

### Final Recommendation
**Recommended Optimal Value**: 132.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is recommended due to its fastest execution time while still providing the optimal solution.

### Business Interpretation
**Overall Strategy**: The optimal total interest income is $132. This means the bank should allocate funds to savings and checking accounts as per the constraints to maximize interest income.
**Objective Value Meaning**: The optimal objective value of $132 represents the maximum total interest income achievable under the given constraints.
**Resource Allocation Summary**: Funds should be allocated to savings and checking accounts for each customer, ensuring minimum balances are met and total funds do not exceed the bank's limit.
**Implementation Recommendations**: Implement the optimal allocation of funds to savings and checking accounts for each customer. Monitor and adjust allocations as customer funds or bank policies change.