+ Reply to Thread
Results 1 to 4 of 4

Allocating sales quotas

  1. #1
    Registered User
    Join Date
    04-27-2014
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2010
    Posts
    3

    Allocating sales quotas

    Hi, I have a problem I've been working on and I'm not quite satisfied with my solution.

    I have a given sales quota and I need to allocate it to customers based on their past sales, but if less than 10 then no budget. For eg my budget is 50 and last years sales for customers were:

    Customer Sales LY
    cust A 10
    cust B 12
    cust C 5
    cust D 15
    TOTAL 42

    So I won't budget to cust C, and then need to spread the 50 out amongst the other 3. I've looked a straight % increase but because it is a product, I can't use a fractional part and rounding becomes an issue. It leaves a few products unallocated which then have to be manually allocated which I'm trying to avoid.

    What is the best way to do this please?

    I considered using a VBA loop but I'm not sure how that would work. I have also looked at solver and goal seek but again I don't think they are quite right for my needs. I feel like this is something that must come up a lot, but I haven't been able to find a way to do it.

    Any help would be much appreciated please!

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,436

    Re: Allocating sales quotas

    Here is my attempt with solver.

    Column A & B is the raw data.

    Column C determines whether to consider the client for the current year.

    Column D is the percent based on customers this year.

    Column E prorates the target 50 according to the percentages.

    Column F is a clone of Column E the formula is =E2. This formula will be overwritten by Solver. I set up solver as shown in the picture.

    Cells F2:F5 are the best guess we can make for a starting point. With Solver, different solutions are often possible. The closer you start to the "real" solution, the more likely it is that Solver will settle on the best solution.

    Note the restrictions that the answers in F2:F5 are constrained to integers.

    The new percent column is there to compare the result to the original percent

    When you solve, you get something like the picture.
    Attached Images Attached Images
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    04-27-2014
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Allocating sales quotas

    Thanks for taking the time to reply. I haven't used solver before so your explanation was helpful - unfortunately I hit a snag as I have too many variables in my actual dataset for solver to work, apparently there is a limit.

    The concept of solver is exactly what I need but somehow I need to automate it for larger data sets and also I have quite a number of products to 'solve' so I need some way that is less manual. VBA could help but I still have the variable limit

    Thanks again for your time, I appreciate it.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,436

    Re: Allocating sales quotas

    Is it the same layout except more rows? If so, how many rows?

    Provide a sample workbook - maybe solver isn't the solution, but maybe we can come up with something else.

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1