Dynamics Community Forum Thread Details

Slice Opportunity cashflows to month/years to provide sales trend reports

All,

maybe you can help me on this:

Our company defines sales opportunities/projets with project start- and end-dates and allocates estimated cashflows to years slices manually. This is a high effort we dont want to face anymore.

If we assume the "project"/"sales opportunity" starts in March 2017 and ends in August 2019 and we have an overall estimation of cashflow of 300.000€ - is it possible to implemend an automatism/linear calculation similar to the following?:

10 month in 2017 = March - Dec. 2017

12 month in 2018 = Jan - Dec. 2018

8 month in 2019 = Jan - Aug. 2019

Equals 30 month.

300.000 / 30 = 10.000

Means we estimate a monthly cashflow of 10.000 € for this specific opportunity.

In my dashboard I want to provide an evaluation per month for the next 5 years with all opportunities.

Is it possible to do this basic calculation in the opportunity by providing only the start date, the end date and the overall estimated cashflow?

Many thanks in advance!

Regards

Daniel

Hi SDaniel, Thanks for reaching out.

You could use calculated fields for this, but this may only solve part of your problem. You would have to adjust charts and reporting as well to make it all user friendly. For this specific request I would use calculated fields. If it got more complicated (it always does) then a plugin may be necessary to perform some aggregations or complex calculations.

Make sure your Start Date and End Date fields are using the same "Date and Time" format.
Create new field
1. Data Type: Currency
2. Field Type: Calculated
Edit Calculated
1. Conditions: Add any logic you want to limit when this calculation would matter. ex. Opportunities that = Products.
2. Action: Set formula to estimatedvalue/DIFFINMONTHS(new_startdate,new_enddate)
  1. estimated value is the est. revenue field from opportunity.
  2. '/' excel formula for divide
  3. DIFFINMONTHS is formula for count of months between a start date and end date
    1. Requires Date AND time

This will give you a field that does the calculation you need. It updates on every change to the relevant fields. You can use this field in charts etc. Let me know if there are questions.

Quick Links

Leaderboard