A client had the requirement for the sales managers to be able to set and view the forecast for monthly margin goals for their sales reps. Here is how we created a custom object to achieve this.
The first step in the process was to build out a margin calculator in the company currency (GBP) in the portal.
Calculation deal properties were created to calculate the sum of the margin values for all the line items associated to the deal. Once a value is held in this calculation property the deal is enrolled in a workflow which next looks at the currency of a deal.
As the client works in multiple currencies within their portal, the first workflow action was an if/then branch which looked at the currency a deal was set as.
If the 'Currency' deal property is set as 'GBP' the deal will continue down a branch which stamps the value in the calculation property over to a custom currency-formatted deal property 'Margin in company currency'.
If the 'Currency' deal property is set as a currency other than 'GBP', the deals will move down the relevant branch of the if/then action in the workflow and the value to be stamped to the 'Margin in company currency' property will be pulled from a custom calculation property which divides the unformatted margin by the relevant exchange rate.
Additionally, a calculation deal property 'Margin %' was created which divided the amount in company currency by the margin in company currency and multiplied it by 100 to work out the margin percentage for each deal.
The final deal property to calculate for this project was 'Weighted margin in company currency'.
To achieve this, calculation properties were created for each of the deal stages in the portal which calculated the weighted margin in company currency based on the set percentage probability of a deal in that stage closing, as set by the client. Each of these individual properties had a workflow that stamped the value calculated over to the formatted currency property 'Weighted margin in company currency' once it was known, ensuring that the property updated as a deal moves through the pipeline.
These properties were hosted on the deal left sidebar section 'About this deal', which made the properties viewable on the preview slider when clicked from the deal pipeline or an associated record.
The next step in this project was to create a custom object for 'Margin goals'.
This custom object was designed to only associate with deal records within the portal and contained the following properties:
- Margin goal name - A single line text property that uses a workflow to create a goal name in the format 'Month / Year / 'Margin Goal''.
- Owner - A user property that assigns the margin goal record to the sales rep for whom this margin goal applies to
- Margin goal set by - A user property that sets the sales manager who set the margin goal
- Margin goal close date - A date picker property set to the last day of the month the margin goal has to be met, to be utilised in reporting
- Margin goal - A currency-formatted number property to set the margin goal in GBP
- Margin goal obtained calculation - A calculation property which sums up the margin in company currency of deals associated to the margin goal record that are in the closed won stage
- Margin goal obtained - A currency-formatted number property which is populated by a workflow to stamp across the 'Margin goal obtained calculation' value
- Margin goal obtained % - A calculation property which works out the percentage of the margin goal obtained, calculated by dividing the 'Margin Goal obtained' by the 'Margin goal' and multiplying by 100
- Weighted forecast - A calculation property which sums up the 'Weighted margin in company currency' for all deals associated with the margin goal record
- Weighted forecast - A currency formatted property that shows the weighted forecast in GBP. Additional deal properties have been created to support the margin goal property 'Weighted forecast'
- Forecast submission notes - A multi-line text property where the sales manager or rep can free type notes on this margin goals forecast
Both the margin goal database table and the record left sidebar have been built out to show the key pieces of information needed to be viewed by the sales manager and sales rep on a forecasting call.
The associated deals, whose close dates fall within the month the margin goal is set for, can be viewed on the right hand side of the margin goal record. By clicking on 'preview' for each of the associated deal cards the sales manager and rep will be able to see key financial details - such as the margin in company currency, margin percentage, weighted margin in company currency - along with the last activity date record for the deal and the next step as inputted by the sales rep, this 'next step' property can be updated from the preview bar on a forecast call by the sales manager if required.
Author: Hannah Fisher
HubSpot CRM Platform Consultant
