It is not too uncommon to come across a situation where sales leadership wants to benchmark their performance versus a budget or plan. The trick is how to make a dashboard reflect sales performance against a predefined target? The solution I like to use is to create a "Budget" or "Plan" Opportunity Record Type, and use the Apex Data Loader to upload the sales targets into Salesforce.com.
One benefit of this approach is that now you can then use all the native reporting within Salesforce and avoid exporting the data into MS Excel to do the comparison. Even if the User’s Profile prevents them from creating Plan/Budget Opportunities, you can let them see how they are performing against budget by using a Dashboard.
You can mass upload budget numbers into Salesforce.com using the Apex Data Loader. First you’ll need to use the Apex Data Loader to pull the Record Type ID out of Salesforce.
Once you have the Record Type IDs you can make that a column in your upload template. The Data Loader will then define the Record Type as you insert new records into the database.
The Data Loader will export an 18 digit key like below:
If you’re not good at using the Data Loader, you can also figure out the Record ID by the following method. You’ll notice that the Record ID through this method will only be 15 digits long – don’t worry, it will still work.
Navigate to Setup > App Setup > Customize >Opportunities > Record Type > then in the window to the right click Record Type Label or the Edit button, then look at the URL to find the Record Type ID. You should see something like this:
01270000000DUK7 is the Record Type ID in the example above.
Here is another example:
01270000000Dm1b is the Record Type ID in the example above
Once you have the Plan / Budget Opportunities loaded, you can then run a report summarized by the Record Type:
Which can produce a Dashboard component like this: