# Allocating sales quotas

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

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

#### Thread Information

##### Users Browsing this Thread

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

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