August 18th, 2007
Building a custom sales pipeline tracker in MS Excel?
oizuki asked:
Hi - I want to build drop-down menus in MS Excel that will allow me to “grade” different sales opportunities as they evolve. For example, if Prospect A is currently just interviewing vendors for a particular purchase, I might classify them as “Gathering Requirements”, which in my book has a 15% probability of turning into a sale. But next month when they are reviewing a contract with us and are negotiating terms, I might regrade the opportunity to “Negotiating Terms” and assign an 80% probability that this will turn into a revenue producing sale. Much of this kind of thing already exists in salesforce.com, but I have some unique needs and want to do this in Excel and want the abilty to create custom pull down lists that, when I select one of the options, has a built in value (e.g. 80%). I’ve tried going to office online and surfing the web for answers but so far am coming up empty handed. Can anyone point me either to an existing spreadsheet that does this or tell me how to do it
Hi - I want to build drop-down menus in MS Excel that will allow me to “grade” different sales opportunities as they evolve. For example, if Prospect A is currently just interviewing vendors for a particular purchase, I might classify them as “Gathering Requirements”, which in my book has a 15% probability of turning into a sale. But next month when they are reviewing a contract with us and are negotiating terms, I might regrade the opportunity to “Negotiating Terms” and assign an 80% probability that this will turn into a revenue producing sale. Much of this kind of thing already exists in salesforce.com, but I have some unique needs and want to do this in Excel and want the abilty to create custom pull down lists that, when I select one of the options, has a built in value (e.g. 80%). I’ve tried going to office online and surfing the web for answers but so far am coming up empty handed. Can anyone point me either to an existing spreadsheet that does this or tell me how to do it
If you liked this post, subscribe to Wpdesigner's RSS feed.
Filed under: Programming & Design
You would have to create the drop down menu in a cell then have another cell check the drop down cell with the “if” function to have it display the value (80%).
just some ideas…with quotes around stuff you might want to look into more.
to get the -drop down menus- you want to look into “data validation” to pull from predetermined a list …gathering req,negotiating terms and so on.
you will probably want to put your list on a separate (possibly hidden so users dont have to see it) sheet. in the column to the right of your list, you can put your %’s. this way on the main sheet you can use a “vlookup” formula to pull the proper % based on whats chosen in the dropdown.
you can use “conditional formatting” to add color and highlight the promising 80% values.
good luck