Actual vs. Budget

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.

Pull Record Type Using the Data Loader

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.

Upload Template Record Type

The Data Loader will export an 18 digit key like below:

01270000000DUK7AAO

01270000000Dm1bAAC

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:

https://na4.salesforce.com/setup/ui/recordtypefields.jsp?id=01270000000DUK7&type=Opportunity&setupid=OpportunityRecords

01270000000DUK7 is the Record Type ID in the example above.

Here is another example:

https://na4.salesforce.com/setup/ui/recordtypefields.jsp?id=01270000000Dm1b&type=Opportunity&setupid=OpportunityRecords

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:

Report Criteria Record Types

Which can produce a Dashboard component like this:

Graph - Actual vs Plan

This entry was posted in Sales Cloud. Bookmark the permalink.