Allocating buyers based on expertise and performance

[m14020256]

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!

21598

[Paul_Hossler]

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



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

[m14020256]

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?



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)


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!

21603