Tips, Tricks & Best Practices

EVersloot
Member

Recognized revenue sense check

SOLVE

Dear all, 

 

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. 

 

Appreciate your input. 

 

Best, 

 

Erik

0 Upvotes
1 Accepted solution
trevordjones
Solution
Recognized Expert | Diamond Partner
Recognized Expert | Diamond Partner

Recognized revenue sense check

SOLVE

Hi @EVersloot ,

 

This is your branching logic:

 

2024-05-23 - 08_40_32 - Set New Year's Day _ Eve and Renewal Date _ HubSpot.png

 

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.

 

2024-05-23 - 08_42_42 - Set New Year's Day _ Eve and Renewal Date _ HubSpot.png

The property is used for this calculated property:

 

2024-05-23 - 08_44_50 - Property settings.png

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:

 

2024-05-23 - 09_51_17 - Property settings.png

 

Step 2

2024-05-23 - 09_52_05 - Property settings.png

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:

 

2024-05-23 - 10_14_18 - Property Editor.png

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.

View solution in original post

0 Upvotes
12 Replies 12
EVersloot
Member

Recognized revenue sense check

SOLVE

Hello Trevor, thank you for your quick 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?

 

0 Upvotes
trevordjones
Recognized Expert | Diamond Partner
Recognized Expert | Diamond Partner

Recognized revenue sense check

SOLVE

Hi @EVersloot ,

 

Yes.  I can get you there with some additional work, but this solution will caculate with greater precision than days. 

 

You'll need to know New Year's Day, for which you need to know the close year:

 

2024-05-22 - 14_08_09 - Property settings.png

You'll use this year in the branching logic of this workflow to set the upcoming New Year's Day for each year:

 

2024-05-22 - 14_45_28 - Set New Year's Day _ Eve and Renewal Date _ HubSpot.png

Now you can calculate the time left in this year:

 

2024-05-22 - 14_53_48 - Property settings.png

Your Revenue in this Year: (HubSpot stores duration in miliseconds and one year is 31,540,000,000 miliseconds)

 

2024-05-22 - 14_55_58 - Property settings.png

And your revenue for next year:

 

2024-05-22 - 14_57_36 - Property settings.png

 By my count that's four calculated properties and one workflow.

 

 

 

 

 

 

- Trevor
If my post solves your problem, please accept it as a solution.

0 Upvotes
EVersloot
Member

Recognized revenue sense check

SOLVE

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?

 

0 Upvotes
trevordjones
Recognized Expert | Diamond Partner
Recognized Expert | Diamond Partner

Recognized revenue sense check

SOLVE
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.

0 Upvotes
EVersloot
Member

Recognized revenue sense check

SOLVE

Hello again Trevor, 

 

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. 

 

Screenshot 2024-05-23 at 09.37.32.png

0 Upvotes
trevordjones
Solution
Recognized Expert | Diamond Partner
Recognized Expert | Diamond Partner

Recognized revenue sense check

SOLVE

Hi @EVersloot ,

 

This is your branching logic:

 

2024-05-23 - 08_40_32 - Set New Year's Day _ Eve and Renewal Date _ HubSpot.png

 

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.

 

2024-05-23 - 08_42_42 - Set New Year's Day _ Eve and Renewal Date _ HubSpot.png

The property is used for this calculated property:

 

2024-05-23 - 08_44_50 - Property settings.png

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:

 

2024-05-23 - 09_51_17 - Property settings.png

 

Step 2

2024-05-23 - 09_52_05 - Property settings.png

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:

 

2024-05-23 - 10_14_18 - Property Editor.png

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.

0 Upvotes
EVersloot
Member

Recognized revenue sense check

SOLVE

Hello @trevordjones , 

 

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? 

 

Thank you in advance for your advice. 

 

best regards, 

 

Erik

0 Upvotes
EVersloot
Member

Recognized revenue sense check

SOLVE

Thank you for your help Trevor.  Your solutions helps me to reduce the time of the team spend on updating multiple deal. 

trevordjones
Recognized Expert | Diamond Partner
Recognized Expert | Diamond Partner

Recognized revenue sense check

SOLVE

Hi @EVersloot ,

 

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.

0 Upvotes
trevordjones
Recognized Expert | Diamond Partner
Recognized Expert | Diamond Partner

Recognized revenue sense check

SOLVE

Hi @EVersloot 

 

I revised the above 0.1, not 0.9 - in case you are looking at these in email.

- Trevor
If my post solves your problem, please accept it as a solution.

0 Upvotes
trevordjones
Recognized Expert | Diamond Partner
Recognized Expert | Diamond Partner

Recognized revenue sense check

SOLVE

Hi @EVersloot ,

 

This is doable if you're willing to use multiple calculated properties to do it:

 

1.) Get the Number of the Current Month:

 

2024-05-22 - 10_05_35 - Property settings.png

2.) Calculate this year's revenue.  (I assumed the revenue starts accruing on the start of the next month, so youre equation might be different.)

 

2024-05-22 - 10_28_26 - Property settings.png

3. Calculate next year's revenue: 

 

2024-05-22 - 10_27_31 - Property settings.png

 

You're on a Professional license, so you only have 5 calcualted properties.  Using 3 might be a big deal for you, but I think this should work.

- Trevor
If my post solves your problem, please accept it as a solution.

0 Upvotes
trevordjones
Recognized Expert | Diamond Partner
Recognized Expert | Diamond Partner

Recognized revenue sense check

SOLVE

Hi again @EVersloot ,

 

I've got you down to two properties:

 

1.)This year's revenue:

 

2024-05-22 - 10_33_23 - Property settings.png

2.) Next Year's Revenue:

 

2024-05-22 - 10_32_17 - Property settings.png

- Trevor
If my post solves your problem, please accept it as a solution.

0 Upvotes