Forecasting Needs for Monthly P&L

Smart CRM & Sales Hub Dashboards & Reporting
1

Hi,

I’d like to describe my forecasting needs.

Currently, our management team aims to implement a monthly forecasted P&L. To achieve this, we have divided our annual expenses into 12 months and split our committed revenue (CA embarqué) equally over 12 months. Now, I need to add a weighted and monthly distributed pipeline to this committed revenue.

I therefore need to have, for each month of the year, my weighted pipeline, which should dynamically update based on newly created opportunities.

At the moment, for each opportunity, we record the following fields:

  • Amount
  • Signature date
  • Contract start date
  • Contract end date
  • Probability (if not provided, we use the default probability from the pipeline stage)

Example:
Let’s say I create a new opportunity for company X, with an amount of €50,000, expected to be signed on 03/28/2025, with a start date of 04/01/2025 and an end date of 05/31/2025. The probability of signing is 50%, as I’m fairly confident.

In my report, I’d like to see €12,500 in April and €12,500 in May. This is because the project is expected to generate revenue in these two months, and with a 50% probability, the weighted amount per month would be €12,500.

I came across the Forecast tool but I’m unsure whether it meets this need. Otherwise, a custom report might be the right approach.

Best regards,
Sami

2

Hey, @SBencheikh, Happy Friday!

Thank you for providing all this information.

A custom report would likely be the best approach. You can create a calculated property to distribute deal revenue across contract months and apply probability weighting. Then, use a custom report to visualize this data on a monthly basis.

I hope this information helps,

Pam

3

Hi @Pam thanks for your reply.

Could you walk me through the creation of this custom report ? I’m not really sure that I’m good enough to do it.

Thanks for your help.

Best regards,
Sami

4

Hey @SBencheikh,
Here is a way to move on with the custom report mentionned above (by @PamCotton) :

  • Create custom properties for your entire calculation. It means at least:
    • months between contract end date and contract start date - number property (in your example here: you shold get 2 for April and May).
    • 1 property per month (for example: “April - weigthed amount”). Use these properties to calculate : weighted amount / number of month between contract end date and contract start date.
      In your example, we should get €12500 in each monthly property.
  • Create a custom report (table) - for each month - select the monthly weigthed amount properties and aggregate the deals as sum to get the overall amounts.

Let me know if this is clearer!
If this answers your question, feel free to mark it as a solution.
Léo Juvin,
HubSpot Expert
LinkedIn | Schedule a meeting