View Monthly forecast

Platform discussions Special workflows & use cases
,
1

Hi all,

Wondering if anyone knows of a way I can view /+ edit a monthly forecast of revenue based on a deal with X duration. For example, if deal value is 1200, deal duration is 12 months, the monthly amount is 100 per month, but I’d like to see what month it starts so ideally if I had a column for each month, the 100 would add to each column depending on some kind of timeline. If 12 months but start date is Feb 12th, then 100 is added to the column for Feb.

Please let me know if this is possible.

2

Hi Shauna,

If I understand correctly you have a {StartDate}, {DealAmount} and {DurationInMonths} column.
Let’s say you create {January}, {February},…{December} formula columns.

You’d want to set up different formulas for each column. Let’s see what it would be for February 2024. Here is the logic I would apply. I’d see if the same day in February than the day of the start date is between the start date and end date of the deal. In other words, if the deal starts Jan 12 I want to know if Feb 12 is within the deal date ranges. If the deal starts Jan 12 and lasts 6 months, the answer is yes. If the deal starts March 20 and lasts 6 months, the answer is no. If the answer is yes, I want the formula column to display {DealAmount}/{DurationInMonths}. If the answer is no, I want to display nothing.

To calculate the date to check, use

FORMAT_DATE(DATE(2024,2,DAY({StartDate})),"YYYY-MM-DD")

Note the 2024,2 in the middle, it stands for February 2024.
We need to compare this date with {StartDate} (>=) and with the end of the deal which is calculated using the EDATE function which allows you to add months to a date, like this: FORMAT_DATE(EDATE({StartDate}, {DurationInMonths},“YYYY-MM-DD”))

So the formula to determine if the date is within the deal or not is:

IF(AND(
FORMAT_DATE(DATE(2024,2,DAY({StartDate})),"YYYY-MM-DD")>={StartDate},
FORMAT_DATE(DATE(2024,2,DAY({StartDate})),"YYYY-MM-DD")
<FORMAT_DATE(EDATE({StartDate},{DurationInMonths},"YYYY-MM-DD"))
),"WITHIN DEAL","NOT WITHIN DEAL")

With the final values, we get:

IF(AND(
FORMAT_DATE(DATE(2024,2,DAY({StartDate})),"YYYY-MM-DD")>={StartDate},
FORMAT_DATE(DATE(2024,2,DAY({StartDate})),"YYYY-MM-DD")
<FORMAT_DATE(EDATE({StartDate},{DurationInMonths},"YYYY-MM-DD"))
),{DealAmount}/{DurationInMonths},"")

You’ll need to adjust the reference to the month twice in the formula for each new formula column.

Want to get rid of the formula column? and write formulas that support multiple lines, variables and can update various columns at once? Take a look at the Advanced Formula Booster at https://mdboosters.com*

3

Thanks @GCavin this is exactly what I was looking for!

1 Like
4

HI @GCavin

I am working on this but noticed that if a deal is 6 months long and starts in Jan, then July or any month after is still being populated with a value even though it shouldn’t have anything past June. Should I add something else for this?

5

Hi @GCavin just checking if you maybe know from the above formula suggested why it wouldn’t stop after the x duration?

6

Please post your formula and indicate the exact values that are not giving you the expected result.

7

The formula for February 2024 I’m using is:

IF(AND(
FORMAT_DATE(DATE(2024,2,DAY({Start Date})),"YYYY-MM-DD")>={Start Date},
FORMAT_DATE(DATE(2024,2,DAY({Start Date})),"YYYY-MM-DD")
<FORMAT_DATE(EDATE({Start Date},{Duration},"YYYY-MM-DD"))
),({Total Sales Value}-{20% of Value}),"0")

If start date is Jan 2024 and the duration is 2 months, I expect the formula for March, April and onwards would be 0, but the value I get for months after the end date is still the monthly value.

8

There is a misplaced parenthesis.

FORMAT_DATE(EDATE({Start Date},{Duration},"YYYY-MM-DD"))

Is incorrect. It should be:

FORMAT_DATE(EDATE({Start Date},{Duration}),"YYYY-MM-DD")
1 Like
9

Thanks for your help @GCavin