Allocating buyers based on expertise and performance

Hello all - I'm working on a problem that has me stuck for a while now so I was wondering if anyone here would lend their expertise and help me out:

• Context: I have purchasing categories that are expected to have a certain workload (in # of purchase orders per year) going forward. I also have a set of buyers that have historically (1) processed X purchase orders per year and (2) dealt with selected categories so have expertise with them
• Problem: How can I allocate the buyers to the purchasing categories while factoring in their performance and historical category expertise?

A sample file is attached for reference but feel free to restructure. Any guidance would be much appreciated!

Attachment 21598

The easiest way I can see is to use a user defined function and select the buyer with expertise in that area, and in case of multiple buyers choose the one with the highest performance

Code:

---

Option Explicit

Function AssignedBuyer(Cat As String, BuyerData As Range) As Variant
    Dim aryBuyers As Variant
    Dim b As Long, perf As Long
    'assigned default error
    AssignedBuyer = CVErr(xlErrNA)
    'make into array
    aryBuyers = BuyerData.Value
   
    perf = 0
   
    'check for buyer with 'Cat' experience, pick max historical performance if multiple
    For b = LBound(aryBuyers, 1) + 1 To UBound(aryBuyers, 1)
        If InStr(1, aryBuyers(b, 2), Cat, vbTextCompare) > 0 Then
            If aryBuyers(b, 3) > perf Then
                AssignedBuyer = aryBuyers(b, 1)
            End If
        End If
    Next b
End Function

---

This does no resource leveling so a buyer could be assigned to multiple categories (e.g. Buyer8) and if there is no buyer with required expertise, then no one is assigned

These constraints / limitations can be resolved but will take more work

Thanks a lot for your input, Paul - very useful.

I think you've visualized the problem a little differently to what I had in mind (which is completely fine and also helpful) but would you have any ideas on how we could implement the following approach?

1. Inputs
   
   • ​Category prioritization (i.e. a ranking of which category is the most critical and which is the least)
   • Category workload (i.e. # of POs expected for that category)
   • Buyer expertise rank by category (i.e. a rank of 1 to 3 of the expertise the buyer has for a particular category)
   • Buyer capacity (i.e. # of POs a buyer can process)
   
   
2. Calculation methodology

• ​Cover each category's workload with the buyer capacity (i.e. allocate multiple buyers to a category if necessary to ensure the workload is covered)
• Try to allocate buyers to categories where they have the most expertise in
  
  • In terms of prioritization, the most critical category should get the buyers who have the most expertise

I've attached an updated file to help visualize the problem - the numbers are all dummies so feel free to make changes if you encounter any logic issues.

Thanks a lot once again for your help!

Attachment 21603