HubSpot does a great job of showing you revenue reports for total deal revenue and MRR, but they don't do a great job of showing you the rolling monthly total over the time period of the deal.

For example, if you have a deal or subscription that is $1000/month for a 12 month period you can easily report on:

• total revenue added (total deal amount)
• added MRR

• new subscriptions MRR

• cumulative MRR

• committed ARR

But you can't see the total MRR per month for the duration of the subscription or committed terms length. 

This is a real challenge for many organizations, especially if you're a service-based business that needs to know how much budget you have to work with each month for your staffing and resourcing. You need to have visibility into if that number is increasing and you need to do some recruitment, or if it's going to decrease and you need to plan accordingly. Let's walk through how we've built a custom HubSpot report to solve the resourcing allocation gap. 

Determine Your Resource Forecasting Measurement: Hours, Budget or Something Else

In this example we've used a revenue budget for our forecasting reports and have built them to include committed revenue per month as well as weighted predicted revenue per month, but you can easily do this with hours or any other unit of measurement that's important for your business. 

Build Your Forecasting Metric & Engagement Duration into Your Deals

We're not going to rely on the default HubSpot properties here because we want to have more control to build our forecasting report. For this example we have added:

• Project Start Date
• Project End Date
• Budget per Month (if going with budget/revenue as your forecasting metric)
• Hours per Month (if going with hours as your forecasting metric)
• Probability of Close (if you want to forecast open deals at a weighted %)
• Weighted Monthly Budget (calculated property of Monthly Budget x Probability)
• Weighted Monthly Hours (calculated property of Monthly Hours x Probability)

Making the Data Work: Google Sheets

You can use many different tools to get the data out of HubSpot and into Google Sheets or another data management tool, but you will need a connector to get it out and in the format you want. Your goal will be to push it to your sheet so you have your monthly revenue aligned with the months it will be active for. Good old-school Excel-style report building here. 

Once your data is formatted with the revenue per month, you can build a chart to track the accurate total revenue per month. 

Push that Report to Your HubSpot Dashboard

For this example we wanted to see confirmed revenue per month with forecasted revenue per month. 

The data is all pulled in and interactive so we can see exactly what the numbers are. Based on this report we can see we have a pretty consistent committed budget for March through August but there is potentially a LOT of extra budget coming in that we should be prepared for. Because you can adjust the probability of close for each deal, you can adjust it based on your own predictors such as how far away that close date is, where it is in the pipeline, barriers that might prevent it from moving forward, etc. In this case we wouldn't want to use the default pipeline close rate % because there are some cases where we need to override that stage-based %. 

This report can be cleaned up, labels added, colors changed, etc. to make it look nice and pretty in your portal.

Making it Work for Your Business

While I can see that it would be difficult for HubSpot to 'know' the start or end date of a deal that has "$1000 per month for 12 months" and maybe that's why we can't do the monthly report for those. Or if you have a mix of one-time deals, term-based deals, and recurring revenue that it gets a bit murky. But with this simple workaround, you can still pull that data into your portal and not have to manually maintain your resourcing projections reports anymore. 

If you'd like to chat through building some custom HubSpot reports for your business, just contact us!