Help Replicating Excel Formula

neg177
neg177
in Modeling

Hi, 

 

Need help replicating this Excel Formula. The Anaplan formula we created is not working. 

 

Excel Formula 

=IF((WR20+WR26+WR27+WR29+WR28+WR5*0.95+WR31+WR32+WR35)-WR40>0,IF(WR29+(WR5*0.95)+WR31+WR32+WR28-WR35<0,0,WR5*0.95),0)

 

Our Formula 

IF
'S01 Time Settings'.Actual Date <= 'S02 Time Admin'.Last Actual Day
THEN
PFR Value of Receivables * 0.95
ELSE
if
(Total Eligible Principal + 'Interest Acct (x1001)' + 'Securitization Master Acct (x5000 Opening)' + 'BALANCING FIGURE FOR x5000' + PFR Value of Receivables * 0.95 + Cash Collected + '(Cash Sweep to Wells)') - 'Min. Asset Requirement'>0
then
if
(
IF

'S01 Time Settings'.Weekdays?
THEN
IF

WEEKDAY(START()) = 1
THEN

OFFSET('Securitization Master Acct (x5000 Closing)' , -3, 0)
ELSE

PREVIOUS('Securitization Master Acct (x5000 Closing)' )
ELSE

0)+(PFR Value of Receivables*0.95) +Cash Collected+'(Cash Sweep to Wells)' +'BALANCING FIGURE FOR x5000'-Excess Funding Sweep<0
then
0
else
PFR Value of Receivables*0.95
else
0

Tagged:

Best Answers

  • PhilippErkinger
    PhilippErkinger
    Answer ✓

    Is the formula not valid (anaplan gives you a message) or does it provide the wrong result?

     

    Additional remark: My general recommendation would be to split out the individual parts of the statement into separate line items. For example: This alone should be at least 2 statements. The first one with the calculation, the second one with the True / False check. (Total Eligible Principal + 'Interest Acct (x1001)' + 'Securitization Master Acct (x5000 Opening)' + 'BALANCING FIGURE FOR x5000' + PFR Value of Receivables * 0.95 + Cash Collected + '(Cash Sweep to Wells)') - 'Min. Asset Requirement'>0

    This allows easier debugging of the individual statements.

  • ChrisAHeathcote
    ChrisAHeathcote
    Answer ✓

    @neg177 

    Key to building out nested IF THEN ELSE formulas is to break them out as far as possible.

    Use booleans to inform each query and never hard code a variable. If you can do this then you can be assured that you and another person inheriting the model will fully understand the logic backing up the outcome. 

    Also, structure the nested functions so that it resolves with the most probable outcome first and least probable last. 

    Not only with this improve the efficiency of the formula it will also allow you to be crystal clear in the logic.  

    Finally, aim to understand the logic behind what you are trying to replicate. It is not always preferable to just copy what is done in the excel model. 

    Following these principles you will be able to construct all manor of complex statements. 

Categories