A few years back we've started using the Sales Hub to keep track of our deals. A setup that was chosen was to have 2 deals for each deal we enter. 1 deal with a close date in current year and 1 deal with a close date on 1st of January of the following year. The reason for this is that we close 1 year software licenses and we report on the close date of each deal on how much of the value is recognized in year 1 vs year 2.
For example:
Total deal value 12.000 USD
Deal 1: 7.000 USD (7 months)
Close date 01.05.2024
Deal 2 5.000 USD (5 remaining months)
Close date 01.01.2025
Due to our growth maintaining 2 deals is becoming a nightmare. From the research I've done over the past few months there is no out of the box solution, such as calculated fields that automatically calculate the value based on the total deal value and close date.
I was thinking to apply the following:
Create custom value fields "recognized year 1" and "recognized year 2"
Filter ACV, CY weighted/unweighted forecast, CY open pipeline on Close date & Recognized Year fields.
The big downside of this is that it still is a manual entry and it's easy to make mistakes, especially when close dates move and the sales rep needs to adjust it or forgets to do it.
Is this a sensible solution to step away from the 2 deals per deal stage? Or are there better ways?
The purpose is to reduce the manual entry for a sales rep and to be able to still recognize value in a certain year.
And this is what each action after the branch looks like (there is only one.) This is really very simple. We need to know when the year ends.
The property is used for this calculated property:
Also, I want to stress that these formulas are going to caclulate based upon the total elapsed time between the Midnight on the close date and Midnight on New Year's Day in miliseconds. The result should be substantially the same, but there does appear to be some rounding error in the HubSpot Calculated Properties. I am actually finding that the most accurate calculation is achieved by calculating this year's revenue in two steps:
Step 1:
Step 2
There appears to be just too much rounding error trying to use a number bigger than 314,500,000 on the first pass.
Also, be careful about your 366 days. That number is only relevant for deals closed between March 1, 2023 and February 29, 2024. After March 1, 2024 we are back to a 365 day year.
When I take a $12,000 amount, and May 22 close date (223 days left in the year), I get the following:
Which is admittedly off from a spreadsheet calculation by $3.48, or about 0.04%, but it seems like an acceptable margin of error.
- Trevor If my post solves your problem, please accept it as a solution.
I tried to keep it simple initially as I didn't think I would be able to use the calculated fields like this. As a last request, how would I adjust the calculation to calculate it back to days?
I'd like to achieve the following result: Number of days left in the year from(incl.) close date/366 days * Annual Contract Value
This year: (366-(days between close date & end of year) * (amount /366)
Next year: (366 - ....... * (amount /366)
I'm guessin this requires another custom property or 2 to complete. What do you think?
That really helps Trevor. Thank you for your efforts on helping me resolve this.
I'm not fully clear what the criteria are that you've used for the branches? Do you filter on the close date between 01/01/2024 and 31/12/2024 for the first branch?
That was actually the purpose of the closed year property. If closed year is 2024 > New Year's Day is 1/1/2025, but you are correct, of course. You could avoid using that calculated property by building your branches such that the close date is between 1/1/2024 and 12/31/2024 instead! Given the limited number of calculated properties, that is probably the better approach.
- Trevor If my post solves your problem, please accept it as a solution.
I've applied your below calulcations and unfortunately the results didn't come out yet. I believe this has to do with the criteria set in the workflow. Can you share what you've added under the branches? Without the right criteria the workflow gives errors.
And this is what each action after the branch looks like (there is only one.) This is really very simple. We need to know when the year ends.
The property is used for this calculated property:
Also, I want to stress that these formulas are going to caclulate based upon the total elapsed time between the Midnight on the close date and Midnight on New Year's Day in miliseconds. The result should be substantially the same, but there does appear to be some rounding error in the HubSpot Calculated Properties. I am actually finding that the most accurate calculation is achieved by calculating this year's revenue in two steps:
Step 1:
Step 2
There appears to be just too much rounding error trying to use a number bigger than 314,500,000 on the first pass.
Also, be careful about your 366 days. That number is only relevant for deals closed between March 1, 2023 and February 29, 2024. After March 1, 2024 we are back to a 365 day year.
When I take a $12,000 amount, and May 22 close date (223 days left in the year), I get the following:
Which is admittedly off from a spreadsheet calculation by $3.48, or about 0.04%, but it seems like an acceptable margin of error.
- Trevor If my post solves your problem, please accept it as a solution.
I've been using your calculations now for a few months and so far it gets me close enough to the result needed. I've did some backwards calculations and to get closer to my Excel calculation it seems that Hubspot calculations are 1 day off.
For example I was expecting a result of 161 days between close date and new year's day, and hubspot returned 159.199 days.
I've tried to adjust the calculation This year revenue - step 1 into: ("Time left this Year - 86400000) * (AMOUNT /315400000), but that did not do the trick.
Afterwards I've moved New Year Day date by one day in the mentioned workflow and that didn't do anything either.
Any suggestions on how to solve for the missing day?
Upon further testing, it would seem that the calculated property won't let you divide the revenue by that large a number (31,540,000,000). It always returns a zero.
As a result, you will need the second calculated property after all.
This Year Revenue (Part 1) is exactly the formula in my screenshot with one less zero. (3,154,000,000)
This Year Revenue (Final) is: This Year Revenue Part 1 * 0.1.
Next Year Revenue is: Amount - This Year Revenue (Final).
Also, please be aware that the amounts are calculated on record save, so build your Calculated properties first, then run your workflow so that the record save after the workflow does the math on all of the calculated properties.
- Trevor If my post solves your problem, please accept it as a solution.