topic Re: Recognized revenue sense check in Tips, Tricks & Best Practices

Recognized revenue sense check

EVersloot — Wed, 22 May 2024 09:32:14 GMT

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

Re: Recognized revenue sense check

trevordjones — Wed, 22 May 2024 14:30:38 GMT

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:

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

3. Calculate next year's revenue:

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.

Re: Recognized revenue sense check

trevordjones — Wed, 22 May 2024 14:35:02 GMT

Hi again @EVersloot ,

I've got you down to two properties:

1.)This year's revenue:

2.) Next Year's Revenue:

Re: Recognized revenue sense check

EVersloot — Wed, 22 May 2024 17:05:59 GMT

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?

Re: Recognized revenue sense check

trevordjones — Wed, 22 May 2024 19:01:06 GMT

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:

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

Now you can calculate the time left in this year:

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

And your revenue for next year:

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

Re: Recognized revenue sense check

EVersloot — Wed, 22 May 2024 20:47:24 GMT

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?

Re: Recognized revenue sense check

trevordjones — Wed, 22 May 2024 20:57:15 GMT

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.

Re: Recognized revenue sense check

trevordjones — Wed, 22 May 2024 21:47:03 GMT

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.

Re: Recognized revenue sense check

trevordjones — Wed, 22 May 2024 21:46:15 GMT

Hi @EVersloot

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

Re: Recognized revenue sense check

EVersloot — Thu, 23 May 2024 07:41:13 GMT

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.

Re: Recognized revenue sense check

trevordjones — Thu, 23 May 2024 14:25:34 GMT

Hi @EVersloot ,

This is your branching logic:

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.

Re: Recognized revenue sense check

EVersloot — Mon, 27 May 2024 08:05:16 GMT

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

Re: Recognized revenue sense check

EVersloot — Wed, 02 Oct 2024 10:31:54 GMT

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