Consulting

Results 1 to 3 of 3

Thread: Allocating buyers based on expertise and performance

  1. #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. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #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
    Attached Files Attached Files

Posting Permissions

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