### Browse by categories

- All
- Collaboration
- Configuration & Data Management
- CPQ and Billing
- Customer Service & Support
- Desktop Integration
- Einstein Analytics
- Email Marketing
- Journey Management
- Mobile
- Mobile Messaging
- Packaging, Uploading & Installing Apps
- Reports & Dashboards
- Sales & Marketing
- Security
- Social Marketing
- Trailhead Challenges
- Additional Products

# 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)

Amnon KruviHi 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 KruviHi 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.

Trude RustadHi,

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

Amnon KruviThe formula already has 2 stages, but you can add more as needed. Trude RustadHm I am afraid i am doing something wrong - I am getting this error message:

Pls Advice

Trude 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 KruviYou forgot to put the stage field in the CASE() function. Trude RustadI 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 KruviYou 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 RustadThank 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 KruviDon'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 RustadThank 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 KruviPerfect, 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 RustadPERFECT - 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 KruviJust 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 RustadThe 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 KruviOh, I see. So that makes it more interesting :) 'Closed Lost',0,
Opportunity.RenewalTotalARRValue__c.CONVERT)

Let's try...

IF (Probability <= 0.30, 0,

CASE(STAGE_NAME,

)

Trude RustadHm, getting this error message on this one:

Warning

Error when encoding row-level formula: Syntax error

Amnon KruviCan you use the field picker to enter the probability field instead? Trude RustadI did - Looks like this:

IF(PROBABILITY <= 0.30, 0,

CASE(STAGE_NAME,

'Closed Lost',0,

Opportunity.RenewalTotalARRValue__c.CONVERT)

Amnon KruviYou missed the closing bracket at the end. Trude RustadSame 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 KruviHmmmm, 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 RustadI 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 KruviDon't mention it, I'm glad you got it working :) Have a great rest of the weekend!

Trude RustadYou too :-)