Implement Advanced Facility Location Analysis Models with Excel VBA

This example will involve solving a basic facility location problem (finding the optimal placement of facilities to minimize transportation costs) with several steps:

1. Define the Problem

In a Facility Location Problem (FLP), the objective is to determine the best locations for facilities (such as warehouses, factories, etc.) to minimize costs (transportation, fixed facility costs, etc.) while considering factors like demand and capacity.

Problem Example:

• We have a set of potential facility locations (e.g., cities).
• We also have a set of customer demand points (e.g., demand locations).
• Each customer point has a demand that must be met by the nearest facility.
• We need to find the facility locations that minimize the total transportation cost.

Objective: Minimize the transportation cost

2. Data Collection and Input

To implement this model, you’ll need the following inputs:

1. Facility Locations (e.g., city coordinates or facility positions).
2. Customer Demand Locations (e.g., customer coordinates or demand volumes).
3. Distance Matrix: Calculate the distance between each facility and customer.

Input Data in Excel:

• Sheet1: Contains Facility Locations (columns A, B), where A = Facility ID, B = Facility Location (X, Y).
• Sheet2: Contains Customer Locations and Demand (columns A, B, C), where A = Customer ID, B = Customer Location (X, Y), C = Demand.
• Distance Matrix: Will be computed from the coordinates of the customers and facilities.

3. Choose an Algorithm or Model

There are several approaches to solving the facility location problem:

• Greedy Algorithms
• Integer Linear Programming (ILP): Common for more complex problems.
• K-Means Clustering: Can be applied to solve facility location problems when the number of facilities is fixed.

For simplicity, let’s assume we are solving this problem using a Greedy Algorithm (the algorithm sequentially places facilities and assigns customers to minimize cost).

4. Implementing in VBA

Here’s a basic implementation of a Greedy Facility Location Model in VBA:

Step-by-Step VBA Code:

Sub FacilityLocationAnalysis()
    Dim wsFacility As Worksheet, wsCustomer As Worksheet
    Dim facilityCount As Integer, customerCount As Integer
    Dim i As Integer, j As Integer
    Dim distMatrix() As Double
    Dim totalCost As Double
    Dim facilityLocation As Integer
    Dim cost As Double
    ' Set the worksheets
    Set wsFacility = ThisWorkbook.Sheets("FacilityLocations")
    Set wsCustomer = ThisWorkbook.Sheets("CustomerDemand")
    ' Get the number of facilities and customers
    facilityCount = wsFacility.Cells(Rows.Count, 1).End(xlUp).Row - 1
    customerCount = wsCustomer.Cells(Rows.Count, 1).End(xlUp).Row - 1
    ' Initialize the distance matrix (facility x customer)
    ReDim distMatrix(1 To facilityCount, 1 To customerCount)
    ' Calculate distance matrix (using Euclidean distance)
    For i = 1 To facilityCount
        For j = 1 To customerCount
            distMatrix(i, j) = CalculateDistance(wsFacility.Cells(i + 1, 2).Value, wsFacility.Cells(i + 1, 3).Value, _
                                                 wsCustomer.Cells(j + 1, 2).Value, wsCustomer.Cells(j + 1, 3).Value)
        Next j
    Next i
    ' Initialize total cost
    totalCost = 0
    ' Greedy Facility Location Selection
    For i = 1 To customerCount
        ' Find the closest facility for each customer
        facilityLocation = FindClosestFacility(distMatrix, i, facilityCount)
        ' Calculate the transportation cost for this customer-facility pai
        cost = distMatrix(facilityLocation, i) * wsCustomer.Cells(i + 1, 3).Value
        totalCost = totalCost + cost       
        ' Output the result (customer and assigned facility)
        wsCustomer.Cells(i + 1, 4).Value = "Facility " & facilityLocation
        wsCustomer.Cells(i + 1, 5).Value = cost
    Next i   
    ' Output the total cost
    MsgBox "Total Transportation Cost: " & totalCost
End Sub

' Function to calculate Euclidean distance
Function CalculateDistance(x1 As Double, y1 As Double, x2 As Double, y2 As Double) As Double
    CalculateDistance = Sqr((x2 - x1) ^ 2 + (y2 - y1) ^ 2)
End Function

' Function to find the closest facility to a customer
Function FindClosestFacility(distMatrix() As Double, customerIndex As Integer, facilityCount As Integer) As Integer
    Dim minDistance As Double
    Dim bestFacility As Integer
    minDistance = Application.WorksheetFunction.Max(distMatrix)   
    For i = 1 To facilityCount
        If distMatrix(i, customerIndex) < minDistance Then
            minDistance = distMatrix(i, customerIndex)
            bestFacility = i
        End If
    Next i   
    FindClosestFacility = bestFacility
End Function

Explanation of Code:

1. Input Data:
   • The facility and customer locations are assumed to be in separate worksheets: FacilityLocations and CustomerDemand.
2. Distance Calculation:
   • The function CalculateDistance computes the Euclidean distance between a facility and a customer. This distance is stored in a distance matrix.
3. Greedy Selection:
   • For each customer, the code identifies the closest facility using the FindClosestFacility function.
   • The transportation cost is calculated as the distance between the facility and the customer multiplied by the demand.
4. Output:
   • The assigned facility for each customer is displayed in the Customer sheet, and the total transportation cost is calculated and displayed via a message box.

5. Output and Visualization

After running the VBA code:

• Customer Sheet will be updated with the facility assignment and transportation cost for each customer.
• The total transportation cost will be displayed in a message box.
• To visualize the results, you can use Excel charts (e.g., scatter plot) to plot customer locations and facility locations on the same chart, with lines connecting customers to their assigned facilities.

Example Visualization:

• Scatter plot: Plot customer locations and facility locations with distinct colors.
• Lines: Draw lines connecting customers to their assigned facilities.

You can also use Excel’s Conditional Formatting to highlight the minimum cost facility assignments.

Notes:

1. Complexity: This greedy approach works for small-to-medium-sized problems but may not find the global optimum. More advanced techniques like Linear Programming (via Solver) or Metaheuristics (like Genetic Algorithms) are better for larger or more complex problems.
2. Scalability: For very large problems, this approach could become inefficient, and alternative methods should be considered.
3. Data Input: Ensure that your data (coordinates, demand, etc.) is properly formatted in Excel to avoid errors in calculations.