Calculate Estimated Weighted Revenue for Opportunities in Microsoft Dynamics 365 for Sales

Posted on: June 13, 2017 | By: Jim Bertler | Microsoft Dynamics CRM

Estimated Revenue is one of the most important fields on the Opportunity form in Microsoft Dynamics 365 for Sales. This field is used to help predict actual revenue and is the basis for the Sales Pipeline within an organization. Many companies estimate the revenue of a project in different ways. People use factors like probability, budget, opportunity close date, and a number of combinations and calculations to project the revenue. Aside from estimated revenue, many companies want to know the weighted estimated revenue, which is often a combination of the estimated revenue based on the probability the revenue will be realized. In this blog, I will present an easy and straightforward method for determining estimated weighted revenue.

First, as best practice we suggest creating your ‘Probability’ field as an option set dropdown with four options of 25%, 50%, 75%, and 90%. This takes much of the guess work out of determining probability. Because this number is largely subjective, by only having four options it keeps an organization consistent in its probability choices. Using 90% for the fourth choice as opposed to 100% is done on purpose because if the probability were 100% then one would theoretically be closing the opportunity as won and not need to put in a probability.

 

 

Next, create a new field on your opportunity form for ‘Estimated Weighted Revenue’. This will be a simple currency field. In Dynamics, with the addition of Calculated and Rollup fields there are many possibilities to do our calculations within the field itself. In this example though we are going to create a Business Rule to calculate our estimated revenue based off our probability. The important thing to note here is that you cannot calculate a currency or numeric value off an option set value. This is why we will use a business rule for our calculation.

Now that we have our new Probability dropdown and Estimated Weighted Revenue currency field on our form we can create a business rule to calculate that estimated weighted revenue. This calculation will be based off Estimated Revenue and probability so you must have a value for estimated revenue already for the calculation to work.

Open a new business rule, give it a name, and add the first condition. For this condition, we will start with the first option in our Probability field which is 25%.

                IF – Probability = 25%

Next add a Set Field Value Action:

                THEN – Estimated Weighted Revenue – (Choose Type = Formula)

                Field – Est. Revenue – (Choose Operator for multiply * ) – (Choose Type = Value) and Input .25

(See Below)

 

 

Next, continue to add conditions for if the probability option set equals 50%, 75%, and 90%. You can click Add and continue to add conditions and branches for however many probabilities you may have. You will do this in the same way the first IF THEN statement was created.

Then, Validate, Save, and Activate your Business Rule. Verify your Estimated Weighted Revenue is for example 75% of your Estimated Revenue if your Probability is 75%. You may also want to consider locking your weighted estimated revenue field now that this is defined by the business rule.

 

 

If you think that a Dynamics CRM system is the next best step for your business, contact Logan Consulting, your Microsoft Dynamics CRM partner of Chicago.

---

---