A new knowledge base article has been published on our support portal.
Scenario Overview
In this scenario we have an Advertising company selling TV ads for a period of time with the need to calculate monthly pro-rata amounts . These TV ads were represented as Opportunities in the CRM system where you had a campaign start and end date along with an budget amount.
We need to calculate on a pro rata basis the amount for each month given the number of days in the month that the campaign would run for. Each month will be added as an Opportunity Product record.
N52 Formula Manager Solution
The Formula Manager solution works like this:
- A formula is created which executes whenever an Opportunity is created or updated.
- It checks to see if either of the following fields are changing before it will continue its execution, Campaign Start Date, Campaign End Date and Budget Amount
- Using the SmartFlow capability and multiple functions we can break the formula down into a series of steps similar to a standard CRM workflow:
- Step 1: Ensure that the Campaign Start Date, Campaign End Date and Budget Amount fields contain data otherwise display an error
- Step 2: Find any existing records and delete them. Executes a fetch-xml statement to find the records.
- Step 3: Calculates the overall Number of Months involved
- Step 4: Calculates the overall Number of Days involved
- Step 5: Calculates the Amount of revenue per day
- Step 6: Loops over the number of months where it will create one Opportunity Product record per month
- The calculations for the Amount and No Days are taking into account 4 key instances via the case statement. This case statement needs to calculate the correct values based on what month we are working on within the DoLoop:
- Instance 1: First month of the DoLoop and there is just 1 month involved
- Instance 2: First month of the DoLoop and there are more than 1 month invloved
- Instance 3: Last month of the DoLoop
- Instance 4: Neither the First or Last Month of the DoLoop