Travel visit ratio presented on a map

[ahmetk]

Dear Excel Gurus,


One of my main task in my job is to follow sales reps visit plan and visit ratio. For a while I have done this through numbers and even had a related post “how to move horizontal date vertically” on this issue (if interested please see the blow link)


http://chandoo.org/forums/topic/how-to-move-horizontal-data-vertically-to-another-page-via-formula


Soon I have realized that just looking at the numbers may sometimes mislead to wrong judgments especially while doing performance management of sales reps distributed among a large scale.


Plain number unfortunately neglects distances which actually lead me to take wrong actions.


Summary: A group of cities may be too far apart in the country and could only be visited certain number of times in a month where another group of cities could all be nearby and can be visited more often. In this incident a sales rep responsible of apart cities will have less number of visits compared to a sales rep with close by cities as he/she has to spent more time on the road. Judging the performance of two by visit ratio only is not fair, distance and region affect needs to be considered.


My goal is to move the visit figures on to a map and observe visit ratio. The more a city is visited, darker its shade should be.


Some of you may seen Google Trend web page: http://www.google.com/trends/explore#q=excel&geo=TR&cmpt=q


I have done a simple example for google trends for word “excel” and copied the link above. What you will see is; the amount of search for word “excel” among cities of Turkey since 2004. This is the same thing I want to do based on visit rate. The look will be same only the data will differ.


I have quite some search on this topic both on chandoo and web but most of the links and solutions I have found seemed either too complicated or mostly VBA involved. That is another problem I have, my managers do not approve VBA usage due to company security regulations. Therefore if possible my solution should be plain excel.


I have copied my study to the following link:


http://speedy.sh/B6Gfx/Visit-Ratio-on-map.xlsx


I would very appreciate if you could kindly share your comments and thoughts with me.


Kind regards


Ahmet Kerestecioglu

 

[NARAYANK991]

Hi Ahmet ,


My comment is very straightforward ; what will the presentation convey , other than the number of visits each region / city has had ?


I do not know whether the workbook you have uploaded has visit data of several salesmen , but just by presenting the visit statistics on a map , how are you going to overcome the problem you have mentioned initially viz. how to judge a salesman's performance only by number of visits without considering the distances travelled ?


Unless your visit data has data of several salesmen , and all this data can be presented on one single map , I don't think your problem can be resolved.


Doing all this in a simple way is very doubtful ; if you exclude VBA , then one way is to use the hotspots technique , of Jordan Goldmeier's , but that too is not so simple to implement.


http://optionexplicitvba.blogspot.in/2012/10/interactive-united-states-2012.html


Narayan

 

[ahmetk]

Dear Narayan,


Thank you for your kind reply.


Eventhough I have been told not to, I guess there is no other way but to use VBA in this issue. Having read many possible solutions they all somehow involve VBA, therefore I guess I have to step backwards and use VBA. Just have to convince my managers


We have 7 regions in Turkey and 3 sales rep covering those areas. Actually it is 5 region that we need to concentrate (in the excel you can see those as sales rep #)


I thought if I can transfer the visit data on to a map, seeing the level of shades and coloring on the map, we can judge their performance better.


The workbook has details of visit plans. From time to time my managers question the number of visits done and complain why certain rep has fewer visits. If I can show them the visit route on the map and present figures, they can get a better understanding of the matter.


Long story short I want to carry those visit figures on to cities. If you can kindly guide one example for a single city, I should be able to do the rest.


Kind regards.


AK

 

[NARAYANK991]

Hi Ahmet ,


There are several articles on this ; you can start from here :


http://www.clearlyandsimply.com/clearly_and_simply/2009/06/choropleth-maps-with-excel.html


Narayan