topic Projected ARR Calculations in 💬 RevOps Discussions

Projected ARR Calculations

drafeedie — Sat, 14 Dec 2024 17:49:06 GMT

I am trying to created a projected ARR number for each of our customers. We bill out customers a variable fee each month via Quickbooks and use the integraiton to sync invoices to Hubspot.

Currently we have a roll up custom property that takes the average amount billed to get an MRR number. We then have a calculated field that takes the MRR x 12 to give us the projected ARR.

However, the problem is that the roll up MRR property looks back at ALL invoices to get the average. This skews numbers unfavorably for customers that have material growth or reductions in seat licenses.

We are looking for a way to limit the MRR Rollup calculation to ONLY include the last x number of months in the average calculation so that we bias the more recent invoices and disregard older ones. Anybody have experience doing this, or a creative warkaround to set the average MRR property?

Re: Projected ARR Calculations

BérangèreL — Mon, 16 Dec 2024 10:03:46 GMT

Hi @drafeedie, I hope that you are well!

I'd like to share the solution from @danmoyle on this similar post "Custom Forecast ARR Field and Subscriptions" that might guide you!

Also, I'd love to put you in touch with some of our Top Experts: Hi @HubSpot_Corey, @HFisher7 and @louischausse do you have other suggestions to help @drafeedie, please?

Have a lovely day and thanks so much!

Best,
Bérangère

Re: Projected ARR Calculations

GSriram — Tue, 17 Dec 2024 10:33:46 GMT

Hey @drafeedie

My solution to solve this problem would be to use a tool like Superjoin. It offers a 2-way sync between HubSpot and Google Sheets. I’ve been using it for a similar usecase as yours and it’s been working really well for me. Would recommend it strongly for your workflow too.

You could bring in the Average Amount Billed data to Google Sheets using Superjoin so that you can do the MRR Rollup Calculation accurately on Sheets and send it right back to HubSpot.

Hope this helps! Have a great day :))

Best,
Guru

Re: Projected ARR Calculations

BérangèreL — Tue, 17 Dec 2024 13:07:03 GMT

Hi @GSriram,

Thank you for your contribution in the Community and for your help!

I can see that this is the "Google Sheets Connector" integration from the HubSpot Marketplace. Please let me know if that's not the right one.

Could you please share a concrete example of how @drafeedie could use this integration to solve for this specific use case?
Which formula are you using for the calculation, please?

Thank you and have a great day!

Best,
Bérangère

Re: Projected ARR Calculations

GSriram — Tue, 17 Dec 2024 14:06:32 GMT

Hi @BérangèreL ,

Thanks for following up, and happy to clarify!

Yes, the integration I’m referring to is indeed the "Google Sheets Connector" you've mentioned, listed on the HubSpot Marketplace. Here’s how @drafeedie can use it to solve this specific use case:

1. Pull Recent Invoice Data into Google Sheets:

The connector has a data preview option in which @drafeedie can apply filters and sync the most recent invoices (for example, last 3-6 months) from HubSpot into Google Sheets. The key here is filtering invoices to focus on the last “X” months only.

2. Calculate the Average MRR using a formula:
=AVERAGEIF(DateRange, ">=TODAY()-90", InvoiceAmount)
- Replace DateRange with the column containing invoice dates
- Replace InvoiceAmount with the column containing the billed amounts

- Adjust 90 for a different number of days

3. Send the Accurate MRR Back to HubSpot:
Once the recent MRR average is calculated in Sheets, the connector can sync this value back to the custom MRR property in HubSpot. From there, the calculated ARR property (MRR x 12) will update automatically.

Let me know if you’d like a more detailed step-by-step breakdown! I hope this helps.

Best,

Guru

Re: Projected ARR Calculations

BérangèreL — Tue, 17 Dec 2024 14:13:57 GMT

Hi @GSriram, thank you so much for the details about how to use the integration for this specific use case! ❤️

Would that help @drafeedie? Is that what you were looking for?

Thanks,
Bérangère