• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sales rep region logic

R

Raesu

Member
Hello,


I'm trying to map out queues in SalesForce.com that give leads to sales reps. Reps have overlapping regions by state, and I'm struggling trying to find a logical solution to establish which reps belong to which queues (they can belong to more than one).


I can't post anything on skydrive from work, so here is some text of the direction I'm heading:

[pre]
Code: 
UT	WV	WY	IA	OH	WI	IL	IN	MI
1			1	1	1
1		1
1		1
1		1	1	1	1
1			1				1
1			1				1
1			1	1	1
1
1			1
1
[/pre]
Each line is a sales rep, and 1 means that he covers that state. (I hope it lines up properly). How can I optimize queues (groups of states) so that minimal groups are made, and the number of reps per group doesn't go over ~5?


Any help is appreciated, sorry if this problem is not totally clear.
 
Create a sum for each state. Then conditional format the sum cells to go red when the values is greater than 5. Then you could make operational adjustments in ur business to lower the number of reps in the state.


Also can you please clarify what your asking for.
 
NOTE: I added backticks (`) in your post to help line things up w/ the sample data.


Optimization problems are generally difficult to create a "formula" for, unless one is very skilled in higher mathematics imo.


You might be able to use Solver (chandoo.org/wp/2011/05/11/using-solver-to-assign-item/) if you can clearly define what your limits and variables are. A simple-man approach would be to add conditional formatting or data validation to highlight when you have 5 reps or to limit the column to only 5 reps, respectively. Is there any limit per row as to how many states a rep can visit?
 
I am going to have to agree with Luke but I think with some clarification on what your asking for, we could probably find a solution.
 
Thanks for the responses.


I have a sum line on the bottom, so that I can see states covered by up to 4 reps in this case. I was thinking this would get me close to logically bucketing states but here is the catch.


-Several states are covered by 1 rep. Some by 2, etc. What is not immediately obvious looking at the matrix is how many DIFFERENT reps are covering the state. So as an example, WV has 2 reps and OH has 3 reps. 2 reps cover both areas, so if I group the 2 states WV+OH I have a membership of 3 reps, not 5. I'm thinking maybe a SUMPRODUCT formula could help me get a view of this (combining rows horizontally if that makes sense, while grouping states).


I posted here because this is definitely an optimization problem, and if all else fails I'll just continue trial an error, looking around and making changes until it fits.


However, this would be a good exercise to try and figure out an optimization process. Any help is appreciated!
 
Also to find the minimal number of reps for state. You can just add the number of reps you have then divide by the number of states.
 
Also, I am not reassigning reps or making changes to who covers what. I'm just grouping states together so that about 5 reps belong to each group. Sorry I'm having trouble explaining this properly
 
Also to make it more clear and seeing how some of the reps can covers 2+ states. Can you change your original post so the 1's are first names.
 
I have been doing some dragging and dropping, so here is a good example of my goal here:

[pre]
Code: 
Name   OH      WV	MI     PA      KY
Joe
Jim
Luis
Carlos			1
Pierre	1	1	1	1	1
Sue	1	1	1	1
Derek			1
Ted
Mark	1				1
[/pre]

Those 5 states could be grouped, with only 5 reps to the "Mid West" group. It is a plus when reps cover all states, and a minus when a rep only covers one. If Carlos only covers 1 that mean he gets a lot of lead info for states he does not cover.


The scale of this is obviously bigger than my posts here so dragging and dropping is my last choice here.
 
So for instance on the above example. The 5 reps to be grouped would be carlos Pierre Sue Derek and Mark? Also what do you mean by + and minus? Does this factor into group making?
 
Yes those members would be correct here. The +/- are just factors I'm thinking about when making the groups.


I think I can make some formulas to "score" each rep and use the Excel solver once I read through that article.


I appreciate the help but don't want to hold people up with amiguous posts :S
 
Raesu,


Tackling a small portion of the problem, the formula to determine unique number of reps would be something like:

=SUMPRODUCT(1*((B2:B10)+(C2:C10)+(D2:D10)>0))

Where each range is one state's data.
 
Or if I could somehow sort the states so that similar patterns line up against each other. For example WV and PA would be right next to each other since the 1 pattern is the same.
 
To cloud the issue some more, "what is optimal?"

Is this a good layout? Why/why not? (ignore the geographic position of states for now)

[pre]
Code: 
UT	WV	WY	IA	OH	WI	IL	IN	MI
1	1	1	1	1
1	1	1	1	1
1	1	1	1	1
1	1	1	1	1
1	1	1	1
1	1	1	1
1	1	1	1
1	1	1	1
Or this?

UT	WV	WY	IA	OH	WI	IL	IN	MI
1	1	1	1	1
1	1	1	1	1
1	1	1	1	1
1	1	1	1	1
1	1	1	1	1
1					1	1	1	1
1	1					1	1	1
1	1	1					1	1
[/pre]
If one of these is ideal, solution would be to arrange states in a good order geographically, and then plot the numbers in idea pattern.
 
Hey luke, I think the rep positions are fixed and cant be changed. He is trying to figure out which states can be grouped to make the total reps for that state equal to 5.


It does seem a tad ambiguous. I don't think I can figure this one out. Sorry Raesu!
 
That sumproduct formula will help, just need to figure out exactly how to use it. Why does it need the ">0" at the end?


Geographic location does not matter, it just happens that reps share states that are next to each other. That first sorted table looks good. How did you do that?
 
SUMPRODUCT explanation:

I'm adding each group/column together. Then, for each row, I'm testing if there's at least 1 rep (aka, >0). This gives me a bunch of True/False statements. The 1* bit turns those into 1's and 0's, and the SUMPRODUCT adds those up to get the final count.


The first table was just me doing a block of 4 reps, each having 5 states. I was assuming that you can reassign the reps to make the ideal model, but it sounds like this may be wrong.
 
Thanks for the help Luke and Montrey. I'll play with the sumproduct formula and do a bit of dragging and dropping :X


Raesu
 
Good luck, Raesu! Sorry we couldn't come up with a more complete solution. =/
 
You must log in or register to reply here.
Back
Top