Use Calculated Fields to Estimate Revenue from Budgets in Microsoft Dynamics 365
Posted on: March 13, 2017 | By: Jim Bertler | Microsoft Dynamics CRM
Keeping track of Expected Revenue can be one of the most important pieces of information in any project. Microsoft Dynamics 365 for Sales does a great job of providing the user with the ability to keep track of financial information for projects. Out-of-the-Box it has fields for vital data like budgets, estimated revenue, and actual revenue. The Opportunity entity is where organizations keep track of new projects, jobs, and sales opportunities from start to finish. This entity comes built in with a process bar so that opportunities can be tracked by stage and the details of each stage can be apparent. With each stage, it is extremely valuable to know the expected revenue. This can tell a company how close or far away a job is from completion and when that expected revenue is transformed into Actual Revenue.
Estimated Revenue is just that though, an estimated amount of money. It is hard to know the exact number until the job is closed and the final invoice is received. Getting a close approximation is important though, and many companies calculate this number in different ways. In our experience, one very good way is to use a calculated field and have the estimated revenue be a percentage of the project’s budget the company hopes to receive. Say for example you are working with a company that has a $1,000,000 budget and you get the opportunity to do work on this project. After the project is complete you can expect the revenue you will receive is 50% of that budget, you would like to set your estimated revenue at $500,000.
In this example, I will demonstrate how to set this number automatically by choosing the percentage, and also how to set this so that if you do not want the number to be calculated you can manually input your own Estimated Revenue.
First we need a field to hold the percentage of budget we think we will bring in as revenue. Let’s call this field ‘Expected Profitability %’. Make this a ‘whole number’ field with a minimum range of 0 and a maximum range of 100. We do not want anyone putting over 100% of budget as expected revenue, but if your organization has circumstances where you can earn additional revenue over the project budget you can modify the field later and increase the maximum value to over 100.
Next, we will need to create a new expected revenue calculated field. There is an out of the box field for expected revenue, but this is a currency field so we will want to create a new one for now. Create this field as data type as currency, but field type as calculated.
In your calculated field settings, you do not need to add a condition in this case. In the Action bar you will want to set the Expected Revenue Calc = budgetamount*(new_expectedprofitabilitypercentage/100).
By using your new expected profitability field percentage divided by 100, it allows for you to show that profitability percentage field as a whole number on your form and not as a decimal. Save your field and publish your changes. This number now gives you the calculation of how much percentage of budget your revenue will be.
Next let’s create a business rule that says if your new expected revenue calc field contains data, then set the Estimated Revenue field to that number.
By creating this rule, it will move the dollar amount to the out-of-the-box Estimated Revenue field which serves two purposes. The first is that the dashboard pipeline which shows your revenue in each stage uses the estimated revenue out-of-the-box field. You have the ability to update the dashboard, but doing this prevents some of that unnecessary updating of dashboards. Second, is that with this business rule, if you do not add anything to your budget field and/or expected probability % field, then nothing will calculate and you can manually put in your own number for Estimated Revenue when that circumstance is needed.
If you think that a CRM system is your next best step for your business, contact Logan Consulting your Microsoft Dynamics CRM partner of Chicago.