Ever wondered about the complexities involved in tracking the progress against sales targets in an organization, which is active in multiple territories, selling wide range of products, working on big accounts… If you are looking for an easy solution to this very complex problem, then you are at the right place. In this post, I will explain about my favorite feature in Microsoft Dynamics CRM 2011, that allows you to track variety of targets/goals in multiple dimensions across the hierarchy with automatic rollup of the actuals.
Situation-1: Managing different targets for different Accounts
Let us take the following organizational structure.
Kevin has the target of USD 300,000 for Q2. He wants to distribute it as follows –
Nancy: 90000, David: 120,000, Michael:90,000 for Account1 and 110000 for Account2. He follows the steps below in MSCRM to do this.
1) He opens his goal record, which has been set by his manager. Metric for this goal is set as “Revenue’. This metric gets shipped out of the box with MSCRM.
2) He goes to the “Child Goals” sub-grid and clicks on “Add new goal”. He provides a name of the goal, then sets the owner as “Nancy” and puts the target amount as 90,000. He clicks on “Save & new” to set the target for David in the same way.
3) Based on above goal definition for Nancy and David, system will rollup the actuals and in-progress data purely based on ownership. That means system will calculate the actuals and in-progress for Nancy from all the opportunities which is owned by her.
4) Goals for Michael needs to be set differently, as Kevin wants to set two goals for Michael. So, somehow he will need to tell the system that “Opportunities related to Account1” should rollup into the goal-1 and “Opportunities related to Account2” should rollup into goaL2.
5) To achieve this, Kevin creates two rollup queries. He goes to rollup query grid and clicks on new. He provides a name “Opps coming from Account1” for the rollup query and sets the record type as “Opportunities”. A query control comes up on the form, there he describes the filter as “Potential Customer” equals “Account1”.
6) He saves it and creates the 2nd rollup query “Opps coming from Account2” in similar way.
7) Now he goes to his goals window and adds a new child goal. He gives this goal a name “Michael-Acc1-Q2”, owner=Michael, Target = 90,000 and he scrolls down to find the “Goal Criteria” section. There he provides the Rollup query as “Opps coming from Account1” for Actuals and In-progress fields.
8) In the similar way, he creates the 2nd goal for Michael.
Kevin is done with the goal definition. This way, all actuals and in-progress data rolls up correctly in the goals of his team members. And then the same rolls up into his goal according to the following formulae.
Kevin’s actual = sum of actuals of child goals + Actuals from the opportunities owned directly by Kevin.
Note: In the same above way Kevin can set goals based on territories by specifying proper filters in the rollup queries.
Situation-2: Managing different targets for different Products
If the organization tracks the targets based on the product, it becomes very clear that revenue cannot get aggregated from opportunities directly, as one opportunity can carry many products. In this case, actual and in-progress revenue towards goal would need to be calculated from Opportunity Products (line items). To do this, organization is recommended to first deactivate/delete the “Revenue’ metric or modify this. Then they would need to create new “Revenue” metric as follows –
1) Go to the goal metric grid and click on “new”
2) Give a name “Revenue”
3) Then set “Type” as “Amount” and “amount data type” as “Money”. Save it.
4) Click on Rollup field subgrid and click new to create definition for “Actual(Money)”.
5) Provide the following values, if you are tracking revenue in Opportunities. If you are tracking actual revenue in orders, the select “Source Record Type” as OrderProduct
6) Click “Save & New” and provide definition for “In-progress(Money)” field
7) Save & close. Close the metric and you are ready to set goals based on any dimension including products.
Now that we have setup the right metric to track revenue goals, we can create the goals as follows. Let’s take example where Nancy has target of 90,000 on Product1 and target of 110,000 on Product2. And Targets for David and Michael are same as described in situation-1.
While setting up goals for Nancy, Kevin will create two rollup queries. 1st one will get revenue from Product1 and 2nd one from Product2. Screen shot for Product1 is given below.
Now Kevin will use these Rollup Queries to create goals for Nancy, so that system rolls up amount corresponding to these specific products.
While setting up goals for Michael, Kevin will create Rollup queries for Account1 and Account2. Screen shot for Account1 is given below.
Using these two rollup queries, Kevin can now setup goals for Michael.
Please note that Kevin is using only one metric for revenue for all kinds of goals and not only for product based goals. This is makes the life easier while monitoring the progress against these targets. If organization don’t track target for different products, then they can use the Revenue metric shipped with product, which aggregates data from Opportunities. They can also change the metric to make system rolls up Actual from fulfilled orders and In-progress from open opportunities.
With these capabilities, one can set goals in multiple complex dimensions. As Rollup queries supports all the capabilities of advanced find query, so one can build really complex queries to rollup the data against specific goals. You are welcome to further explore charts and dashboards available to monitor the performance against these goals. Hope you enjoy using this feature.
Cheers,