Renewal Forecast Formula - need some help - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Trude RustadTrude Rustad 

Renewal Forecast Formula - need some help

HI, 

I need some help to calculate a Renewal Forecast in a Report

Row level formula. Basicaly want i want  is to add Sum of open opportunities to this formua - then subtrack value of closed lost opportunities

As i am thinking that I want to have a report that forecasts the Renewal rate. Thinking that needs to b something inline with:

(Closed won Sum + Open Opportunities) - (Closed Lost + Downsell+ Sum At risk Opportunities) / Total Renewal Base

Also want to Subtrack At risk opportunities - that is marked by having 30% Probability.

Guess I shoule add a [OR] to the Formula - but I am a bit stuck -

Really appriciate some help :-)

IF(TEXT(STAGE_NAME) = 'Closed Won', Opportunity.RenewalTotalARRValue__c.CONVERT, 0)

Best Answer chosen by Trude Rustad
Amnon KruviAmnon Kruvi
Hi Trude, 

It feels like you want to create one or more formulas that return the opportunity amount of the record is in certain stages, or 0 otherwise. If you only one a single formula, then you can do it as a row-level formula on the report, otherwise, you'll need to create formula fields. 

Your formula would look like this:
CASE(<insert opportunity stage field here>, 
"Closed Lost", 0,
"At risk", 0,
<insert opportunity amount field here>)

Once you have the formula in your report, you just have to sum it up to get your forecast. 

All Answers

Amnon KruviAmnon Kruvi
Hi Trude, 

It feels like you want to create one or more formulas that return the opportunity amount of the record is in certain stages, or 0 otherwise. If you only one a single formula, then you can do it as a row-level formula on the report, otherwise, you'll need to create formula fields. 

Your formula would look like this:
CASE(<insert opportunity stage field here>, 
"Closed Lost", 0,
"At risk", 0,
<insert opportunity amount field here>)

Once you have the formula in your report, you just have to sum it up to get your forecast. 
This was selected as the best answer
Trude RustadTrude Rustad

Hi, 

Thanks for trying to help, will this work with addid multiple stage names in the formula?

Amnon KruviAmnon Kruvi
The formula already has 2 stages, but you can add more as needed. 
Trude RustadTrude Rustad
Hm I am afraid i am doing something wrong - I am getting this error message: 

User-added image

Pls Advice
Trude RustadTrude Rustad

Hi, 

Did this - 

CASE('Closed Won','Value Assessment','Proposal','Negotiation',(Opportunity.RenewalTotalARRValue__c.CONVERT),0)

Error message 

Warning
Error when encoding row-level formula: Incorrect parameter type for function 'CASE()'. Expected Text, received Number
 

Appriciate help here :-)

 

Amnon KruviAmnon Kruvi
You forgot to put the stage field in the CASE() function.
Trude RustadTrude Rustad
I cannot seam to get this one, 

CASE(TEXT(STAGE_NAME)'Closed Won','Value Assessment','Proposal','Negotiation'),(Opportunity.RenewalTotalARRValue__c.CONVERT),0

Still getting an error message
Amnon KruviAmnon Kruvi
You have to follow the pattern I've set out in the original comment, and just put the field names. I can't tell you what to put exactly because it's different if you use a formula field or a report row-level formula. 

Don't add TEXT(), don't add anything except for the field. 
Trude RustadTrude Rustad

Thank you for that. I finally got the formula right - but it sadly does not give wanted result.

It returnes 0, that is not right. ...

Amnon KruviAmnon Kruvi
Don't worry, we'll sort it out :) what is the formula you ended up using? 

The stages appearing in the formula will give you 0, the rest will give you the opportunity amount. So the stages you list should be the low-chance/lost ones. 
Trude RustadTrude Rustad

Thank you for sticking with me - to solve this :-)

Formula is like this:

CASE(STAGE_NAME, 
'Closed Won',0,
'Value Assessment',0,
'Proposal',0,
'Negotiation',0,
Opportunity.RenewalTotalARRValue__c.CONVERT)
Result =0

I wanted to sum up the Open & Closed Won opportunities: Easier would perhaps be to sum up value of all opportunities that is Not Closed Lost.

Basically after that I would like to subtract all opportunities in Probability 30%

But, If we start with the first part of summing up the Opportunity Value of All Opportunities that is not closed lost.

Makes sense?

Amnon KruviAmnon Kruvi
Perfect, thank you :) 
The stages you listed are the ones you want​​​ to count. We need to list the stages you don't want to count, instead - so the Closed Lost stage should return 0, and everything else should return the converted amount.
Trude RustadTrude Rustad
PERFECT - That worked like a Charm.
Any idea of how i can get the value of the opportunities with PROBABILITY 30 - to subtract from this amount
Amnon KruviAmnon Kruvi
Just add those additional stages to the list, and they'll return 0 in the amount just like closed lost. That means their amount won't be counted. 
Trude RustadTrude Rustad
The tricky part is that it is not a stage - Only thing that defines that the opportunity is at risk - is that it is marked with a 30% probability. Can be in various stages. 
Amnon KruviAmnon Kruvi
Oh, I see. So that makes it more interesting :) 
​​​​​​
Let's try... 
IF (Probability <= 0.30, 0,
CASE(STAGE_NAME, 
'Closed Lost',0,
Opportunity.RenewalTotalARRValue__c.CONVERT) 
​​​​​​)
Trude RustadTrude Rustad
Hm, getting this error message on this one:
Warning
Error when encoding row-level formula: Syntax error
 
Amnon KruviAmnon Kruvi
Can you use the field picker to enter the probability field instead? 
Trude RustadTrude Rustad

I did - Looks like this:

IF(PROBABILITY <= 0.30, 0,
CASE(STAGE_NAME, 
'Closed Lost',0,
Opportunity.RenewalTotalARRValue__c.CONVERT)

Amnon KruviAmnon Kruvi
You missed the closing bracket at the end. 
Trude RustadTrude Rustad

Same error

IF(PROBABILITY <= 0.30, 0,
CASE(STAGE_NAME, 
'Closed Lost',0,
Opportunity.RenewalTotalARRValue__c.CONVERT)
)

Error message: 
Warning
Error when encoding row-level formula: Syntax error
 

Amnon KruviAmnon Kruvi
Hmmmm, I just tried this formula in my own report and it seems to compile well enough. Tell you what, let's hop on a quick chat and you can share your screen with me if that's alright?
https://meet.google.com/mxh-qorz-hbg
Trude RustadTrude Rustad

I Finally made it, Missing the Closed lost - need to add that in the Summary formula - then we nailed it !!! 

So the Row level formula looks like this:

(CASE(STAGE_NAME, 
'Closed Lost',0,
Opportunity.RenewalTotalARRValue__c.CONVERT+Opportunity.CrossSellTotalARRValue__c.CONVERT+Opportunity.UpsellTotalARRValue__c.CONVERT))-
(IF(PROBABILITY=0.30, Opportunity.RenewalTotalARRValue__c.CONVERT,0))

The Summary Formula looks like this: 

((CDF1:SUM)-(Opportunity.DownSellTotalARRValue__c.CONVERT:SUM))/Opportunity.Legacy_Contract_ARR__c.CONVERT:SUM

Thank you soo muck Amnon for the patience and help getting me there :-)

Amnon KruviAmnon Kruvi
Don't mention it, I'm glad you got it working :)
Have a great rest of the weekend!
Trude RustadTrude Rustad
You too :-)