Thread: Allocating buyers based on expertise and performance

1. 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!

Problem Sample.xlsx

2. 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
'make into array

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
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

3. 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!

Problem Sample v2.xlsx

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•