With today’s high gasoline prices and time at a premium, it is important that you plan your business trips to visit customers, sales prospects, and others to maximize time and cut costs. Using Microsoft Dynamics CRM 3.0 and Microsoft Excel with Microsoft MapPoint, you can create a targeted and efficient plan for your onsite calls in about twenty minutes depending on the number of stops in the trip.
Suppose that you open your Microsoft CRM Workplace and see that Marketing has assigned a new task to you. They want you to introduce a new product line to your active accounts in Southern California with over 500 employees and an annual revenue of at least $7,000,000. For our example, assume that you are based out of Chicago and will fly into Los Angeles International Airport. In Los Angeles you will rent a car and drive to each of your clients ending your sales trip in San Francisco, where you will fly back to Chicago. You want to make sure that you can see all customers in the three days that you have available, so you want to plan your trip to minimize time-consuming routes and backtracking.
To accomplish this, you will want to create a list of your accounts in the region and their locations by using the search capabilities in Microsoft CRM. Then, you’ll export the results from Microsoft CRM to a Microsoft Office Excel worksheet. Finally, you’ll import the data from the worksheet into MapPoint using its import wizard. You will then use a second wizard to help you map your addresses or other data to provide you with the most efficient routes, specific directions to the addresses, and much more.
Apply your situation to the following example, and next time you will have a more efficient and enjoyable sales trip.
Begin by obtaining a list of your accounts, and then select the data on those that you want to work with using the Advanced Find feature. Add information that you will need for the trip, again by using the Advanced Find feature.
Note
If you do not need to use the Advanced Find feature to filter your data, you may export your selected View list immediately to an Excel worksheet by selecting the Dynamic worksheet option. After selecting this option, you can still add columns if needed, and then export the data to Excel.
1.
Open Microsoft CRM and in the Navigation Pane, click Sales, and then Accounts.
2.
In the View list, select Active Accounts to get a complete list of accounts with which to work.
3.
On the Standard toolbar, click Advanced Find to limit the accounts to only those that you are interested in visiting and to get additional columns that are not currently displayed.
4.
On the Advanced Find page, click Edit Columns.
5.
In the Common Tasks area, click Add Columns, and then select the columns that you want to add from the list. For our example, select the following unless they are already displayed:
•
Account Name
•
Primary Contact
•
Main Phone
•
Address 1: City
•
Address 1: Street
•
Address 1: State/Province
•
No. of Employees
•
Annual Revenue
•
E-mail
•
Web Site
6.
Click OK when you are finished adding columns, and then click OK again to return to the Advanced Find page.
7.
On the Find tab of the Advanced Find page, begin in the Query pane filtering your search by leaving the default query Status equals Active as is.
Note: For assistance using the Advanced Find feature, click Help, and then select Help on This Page from the list.
8.
On the Advanced Find page, click Show Details to display Select, if it is not available.
9.
First, you will want to limit your search to accounts in California by doing the following:
•
Click Select, and then select Address 1: State/Province from the list.
•
Click Equals from the list.
•
Click Enter Value to enter CA in the text box.
10.
Next, you can limit the accounts to those with more than 500 employees by doing the following:
•
Click Select, and then select No. of Employees from the list.
•
Click Equals to change the limiting factor to Is Greater Than from the list.
•
Click Enter Value and enter 500 in the text box.
11.
Then, you can limit the accounts further by doing the following:
•
Click Select, and then select Annual Revenue from the list.
•
Click Equals to change the limiting factor to Is Greater Than from the list.
•
Click Enter Value and enter 7,000,000 in the text box.
12.
Click the down arrow in the beginning of each row, click Select Row to select it, and then click Group And.
13.
Click Find.
Note
Data will automatically populate the columns that you added earlier. Click Back to Query to make additional searches as needed, and save your query.
At this point, you are ready to import your Excel worksheet data into MapPoint by following these steps:
1.
Open MapPoint, and then clear any current map by clicking New on the Standard toolbar.
2.
On the Data menu, click Import Data Wizard.
3.
From the wizard, browse to the Excel worksheet that you just saved, and then click Open.
4.
Look at the headings in the row labeled Data type to confirm that MapPoint has correctly determined the types of data contained in your columns. If not, select the correct heading for each column.
5.
Click Finish.
Tip: You may be prompted to indicate what a state or a geographic location that you entered in Microsoft CRM goes with. For example, if you wrote an address in Microsoft CRM that the MapPoint database does not recognize as standard, you will be asked to choose what you were referencing from a list.
6.
On the Choose a map type page, click Pushpin, and then click Next.
7.
On the Select the properties for your Pushpin set page, leave the default in the Pushpin set name box. (You may also select colors and the design of your pushpin or accept the default.)
8.
In the Select which fields to display in balloons for this set box, select the options that you want to display. For this example select:
•
Account
•
Address 1: Street
•
Address 1: City
•
Address 1: State/Province
•
Primary Contact
•
Main Phone
•
E-mail
•
Web Site
9.
Click to select the Display selected field names in balloons check box.
Next, create an optimized route, showing the order of your stops and driving directions. For this example, you will be starting your trip from Los Angeles International Airport traveling to destinations in Southern California where your contacts are located before departing from San Francisco International Airport for your return trip to Chicago.
1.
On the Route menu, click Route Planner.
2.
On the Route Planner pane, in the Type place or address box, type Los Angeles International Airport.
3.
Click Add to Route.
4.
Select Los AngelesInternationalAirport in the Find results list, and then click OK.
5.
In the Type place or address box, type San FranciscoInternationalAirport. Click Add to Route (the airport is automatically entered as your last stop).
Note
If it does not show up with a red box to indicate that it is the last stop, right-click it, and then click Add as End in the Route Planner pane.
6.
Click Select on the Navigation toolbar if it is not already enabled to use the Selection tool pointer.
7.
Drag the pointer diagonally until you've drawn a box around all of the pushpin points that you will visit, and then release the mouse button.
8.
On the Route menu, click Add Pushpins as Stops to add all customers as stops on your route.
9.
Click Optimize Stops to reorder the intermediate stops on your route so that your travel time between the start and end points is the most time-efficient (unless you have a different priority, such as using only preferred roads).
10.
On the Route menu, click Get Directions to generate your route.
11.
In the Route Planner pane, double-click a location to schedule a stop.
12.
In the Schedule Stop window enter 2 hours, and then click OK.
You now have a pushpin map with a route and driving directions to visit the customers that you selected from Microsoft CRM. You can customize the route, such as by changing the order of stops, changing the departure and arrival times at stops, and changing your driving preferences. You can also add stops for recreational purposes or visiting family or friends along the way.
1.
Click More Options in the Route Planner pane to customize your trip information.
2.
On the Profile tab, enter your expected average driving day and breaks. For this example, enter 4.
3.
On the Driving speeds tab, enter the speed that you expect to drive. For this example, leave the default.
4.
On the Fuel tab, enter your expected average fuel consumption. For this example, leave the default.
5.
On the Costs tab, select how you want to calculate your fuel prices, and then enter the expected fuel price. For this example, enter $2.29.
6.
On the Segments tab, select your route preferences for taking the quickest route, the shortest route, or preferred roads. For this example, leave the default, and then click OK.
7.
Because you customized this trip, in the list of messages, click the Route has changed bar to get new driving directions based on your customization.
8.
On the File menu, click Save, name your map Sales Trip So Calif, and then click Save again.
When you have completed your entries, you will see a blue bar at the top of the page giving you a summary of how long the trip will take given your current choices.
•
By clicking a stop, you will see the driving directions to that stop printed above the map. Additionally, you can view your map in various ways (for example, with terrain or data) using the View menu.
•
If you want to check out landmarks, hotels, restaurants, or other things along your route, you can see them by right-clicking and selecting the Find Nearby Places option and adding one of the choices as a stop.
•
Use the Zoom option to see your route in greater detail or to get a larger perspective.
•
For additional convenience, you can print the route and driving directions or even save them as a Web page.
The following map with instructions was generated based on the entries made for this example.
Planning a sales trip is just one of the ways that your integration of Microsoft CRM and MapPoint can assist you in solving business problems. Other possibilities will undoubtedly come to mind as you discover the power of these two applications. For other Sales articles that may interest you, see the Related Links box.
Note
