Goals vs Revenue for Multi-Year Contracts

[JCrowNM]

We have contracts with multi-year deals. We have the amounts of those deals split out in different fields, e.g. FY24 Amount, FY25 Amount, etc.

I want to compare how much we have booked (closed won deals) vs the annual goal for the year. I can't use the Goal function within HubSpot, the line chart when adding the different FY's vs the yearly goal is more confusing than helpful because the goals need to have a date line comparison.

I was thinking of just adding fields for the FY Goals. But if I add the fields to the Deal object I would have to do something like create a fake deal where the revenue goal is entered but everything else is blank. Then I would have to use this fake deal to compare the revenue to the goal.

So the goal has to be for the our organization, but I am not sure the best way to accomplish this.

Any suggestions?

[HFisher7 · Accepted solution]

Hey @JCrowNM - What HubSpot subscription are you working with to achieve the above?! 

If you have access to an Enterprise subscription you could create a custom object for revenue goals and associate the relevant deals to each yearly revenue goal to see if it has been met? I've done something similar for margin goals, as detailed here: https://hubs.li/Q01NGdb10

Alternatively, if you have Ops Hub and access to datasets you can create a dataset to manage the goal vs revenue calculations. On that thought, I wonder if the formula fields BETA in reports will enable you to calculate a goal: https://knowledge.hubspot.com/reports/create-formula-fields-in-the-custom-report-builder

[HFisher7 · Accepted solution]

Click on the little edit pencil to the right of the formula field, then select 'Currency' from within the 'Format' dropdown.. 

[JCrowNM]

Ok. I figured out that I can aggregate by Average, which gives me the number I started out with. Now I just have to find a way to format the number as currency.

[HFisher7 · Accepted solution]

Click on the little edit pencil to the right of the formula field, then select 'Currency' from within the 'Format' dropdown.. 

[JCrowNM]

Ugh. So simple, yet it very much elluded me. Thanks for all your help. This worked like a charm!

[HFisher7 · Accepted solution]

Hey @JCrowNM - What HubSpot subscription are you working with to achieve the above?! 

If you have access to an Enterprise subscription you could create a custom object for revenue goals and associate the relevant deals to each yearly revenue goal to see if it has been met? I've done something similar for margin goals, as detailed here: https://hubs.li/Q01NGdb10

Alternatively, if you have Ops Hub and access to datasets you can create a dataset to manage the goal vs revenue calculations. On that thought, I wonder if the formula fields BETA in reports will enable you to calculate a goal: https://knowledge.hubspot.com/reports/create-formula-fields-in-the-custom-report-builder

[JCrowNM · Accepted solution]

Just wanted to provide a concise recipe of what I did in case others have a similar need.

1. Signed up for the formula fields beta 🙂

2. Create a new report.

3. Added a new formula field with a static number..

4. Added my FY24 Amount which adds all the values for deals with dollar amounts in  FY24.

5. Added the formula field to the bar chart report with the Aggregate set to average and the format set to Currency.

Voila! Thanks again to @HFisher7 to getting me there.

[HFisher7]

@JCrowNM - Do you have an image of the final report!? I'd be interested to see how you acheived it! DM if you'd rather 

[JCrowNM]

So, I was able to create a static number for the goal in the formula field. The problem now is that the bar chart doesn't seem to want to display unless the field is aggregated. But if I aggregate by, say, sum, it creates a goal number that is far greater than the original number. I thought maybe I could have the goal number divided by like [Deal.Count] but that doesn't seem to be possible. I am also trying to figure out how to make that number formatted as currency.

[JCrowNM]

I have Sales and Marketing Professional subscriptions. I will take a look at the formula fields.

[JCrowNM]

Hannah,

Thanks for the feedback. Originally I thought that you had to work with a field that was already created. But it looks like you can just add a value. I need to figure out how to get it to display on the report but it looks like it might work. I will keep you updated.

[PamCotton]

Hey @JCrowNM Happy Tuesday,

Let us know if you need any help. @HFisher7 thank you for the helpful information provided.

Kindly,

Pam

[JCrowNM]

Yes. The formula field got me part of the way there, but still having an issue with the bar chart doesn't seem to want to display unless the field is aggregated. But if I aggregate by, say, sum, it creates a goal number that is far greater than the original number. I thought maybe I could have the goal number divided by like [Deal.Count] but that doesn't seem to be possible. I am also trying to figure out how to make that number formatted as currency.