You need to sign in to do that

Don't have an account?

# Help with Complex Formula

I need some help writing a complex formula to estimate Sales Gross Profit. Logically, the formula will work like this:

If it is a new order on a new contract, the estimated Sales GP will be the Monthly Recurring Reveue (MRR) times the number of months of the contract times 10.5%

If is is a new order on an existing contract, the estimated Sales GP will by the MRR times the number of months left on the contract times 10.5%

If it is a contract renewal, the estimated sales GP wil be the MRR times the number of months on the contact times 30%, but this third result for renewals only is capped at $125K.

There is a picklist where the user selects the type of order (New order(new contract), New order (Existing Contract), Renewal)

The user will put in the number of months on the contract, and the number of months left on the contract where appropriate.

I've tried nested IF and also CASE statements, and it gets too complicated too fast.

Can someone help?

Steve :-/Can you post the formulas that you have tried to use (even if they don't work) it will help with the field and object references.

Either that or post the complete list of the Objects, Fields, Datatypes, and Vaules that need to be evaluated in your formula

JayB

Well, here's the problem I'm running into. I can develop formulas that work, except that I keep running up against two errors: picklist data type mismatches, or the 5000 compilied character limitation. I've made significant progress since my first posting, and I'm very close. Let me explain the login and if you can just suggest an approach, that would be helpful. Also, I can't find any documentation on what I can do to try to limit the size of the compiled formula (shorter field names?) I do understand the problem is that the compiler inserts full formulas wherever a formula field is referenced.

Here are the relevant fields:

Contract Opportunity Type: a picklist with either New Contract, New Order (Existing Contract), Renewal

Contract Term; a picklist with 12, 24, 36, 48, 60 as the choices

Months Remaining on Contract: a numeric field entered by the user.

Contract GP percentage: a formula to set the GP multiplier based on contract term.

Various currency fields for different types of services, for the user to enter the monthly revenue per service.

Est Sales Gross Profit for New Contracts, Est Sales GP for New Order (Existing Contract), Est Sales GP for Renewal: all calcualted

Est Sales Gross Profit for all iines of services. Calculated across multiple record types, including some not discussed here. It is a simple sum formula. More on this later.

Here is the logic:

If it is a new contract, take the contract term in months. If it's 12, set the GP multiplier to 10%, if it's 24, set the GP multiplier to 10.5% If it's anything else, set it to 11%. (I used a CASE statemetn to get this and it worked.) To get the Est Sales Gross profit, sum the monthly service revenues, multiply by the contract term and then multiply by the gross profit multiplier.

If is a new order being added to an existing contract, look at the original contract length. Again 12 month =10% GP multiplier, 24=10.5%, everything else is 11%. Now sum the monthly service revenues, multiply by the number of months remaining on the contract, and multiply by the GP multiplier.

If it a contract renewal, multiply the length of the contract by the MRR, and use a GP percentage multiplier of 30%. If this number is greater than $125,000, set the Est Sales Gross Profit to $125,000. Otherwise use the calculated result.

I can make all this logic work using CASE statements. I can even work around most compile length problems using workflow rules that look at the picklist decisions and set hidden fields to the correct number fo I can use that number in my calculation.

Here's where it all falls apart:

Est Sales Gross Profit for all iines of services. We have other record types for other lines of service. We use one field to calculate the final Estimated Gross Sales Profit number. Each record type has this field, and contains a simple sum of the gross profit calculation for each record type. We can do it this way because lines of services cannot be combined on a record type. This is the field that breaks when I try to implement all of the above, because the field reference to Contract GP pulls in too many sub formulas.

thomast

So what if you make the Contract Gross Profit itself a number field set by workflow, so that its subformulas aren't called by the master Est Sales Gross Profit?

And while it sounds like you're already converting the contract length picklist to a number with workflow, you could also consider making it a number field with validation rule (OR(Length__c < 12, Length__c > 60, MOD(Length__c,12) > 0)) to enforce the desired values, and then work with it directly.