Are your customers buying?
Published: March 10, 2006
On This Page
Quotes are the foundation for orders. A sales manager or executive should periodically analyze quotes that are in various stages (draft, active, and won) across the organization. This information can tell you which customers are ordering your products, and subsequently, which customers are not. It can also provide a barometer of how effective your sales force is at closing.
The following procedure describes how to create a simple query by using the Advanced Find feature in Microsoft CRM 3.0. Then, you can export the results to a Microsoft Excel PivotTable to quickly modify the views and see which quotes are active, won, or still in draft. Because the PivotTable is dynamic, the information will be updated each time you open or refresh the PivotTable.
You must be connected to a server running Microsoft CRM to complete these steps.
Use Advanced Find to find quotes
1. | In the Microsoft CRM 3.0 Web client, click Advanced Find. |
2. | In the Look for list, select Quotes, and then enter the following clauses. To do this, click Select, click Status, click Enter Value, select the following values, click >>, and then click OK. |
3. | Click Find. |
Export the list of quotes to a PivotTable
1. | On the Advanced Find toolbar, click  . |
2. | In the Export Data to Excel dialog box, click Dynamic PivotTable, and then click Select Columns. |
3. | In the Select PivotTable Columns, you must select the following fields: | • | Owner | | • | Potential Customer | | • | Status | | • | Total Amount |
|
4. | Click OK. |
5. | Click Export, and then click Open. |
6. | In the Query Refresh dialog, click Enable automatic refresh. |
Add the fields to the PivotTable
1. | From the PivotTable Field List dialog box, drag Total Amount to the Drop Data Items Here area of the PivotTable. The PivotTable now displays a single total amount of all draft, active, and won quotes. To change the total amount to display currency values, on the PivotTable toolbar in the PivotTable Field list, click Field Settings, in the PivotTable Field dialog click Number, click Currency, and then click OK two times. |
2. | From the PivotTable Field List dialog box, drag Owner to the Drop Page Fields Here area of the PivotTable. The PivotTable now displays quote totals by the user who is assigned to the quote. |
3. | From the PivotTable Field List dialog box, drag Status to the Drop Row Fields Here area of the PivotTable. The PivotTable now displays quote totals by the quote owner and by the stage that the quote is in (draft, active, or won). |
4. | From the PivotTable Field List dialog box, drag Potential Customer on top of, but just to the right of, the Owner column area of the PivotTable. |
The PivotTable now displays all draft, active, and won quotes from last month. With this view, you can see each stage that the quotes are in by the total amount for each owner and potential customer.
Graph the results
1. | In the PivotTable toolbar, click Chart Wizard. |
2. | To display quote activity by salesperson, click the list next to Owner, and then select the salesperson or salespersons that you want. |
Tips
| • | To display details for a quote, double-click on a cell in the chart that displays a total amount. |
| • | To view the graph in pie chart format, on the PivotTable toolbar in the Chart type list click Pie Chart, and then click Finish. |
| • | Click the refresh data icon (!) to update the data in the PivotTable or chart. |
| • | Save the PivotTable file. When you open it again, click Enable Automatic Refresh, and the data will be updated from Microsoft CRM. |
