Help Replicating Excel Formula
Need help replicating this Excel Formula. The Anaplan formula we created is not working.
'S01 Time Settings'.Actual Date <= 'S02 Time Admin'.Last Actual Day
PFR Value of Receivables * 0.95
(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
'S01 Time Settings'.Weekdays?
WEEKDAY(START()) = 1
OFFSET('Securitization Master Acct (x5000 Closing)' , -3, 0)
PREVIOUS('Securitization Master Acct (x5000 Closing)' )
0)+(PFR Value of Receivables*0.95) +Cash Collected+'(Cash Sweep to Wells)' +'BALANCING FIGURE FOR x5000'-Excess Funding Sweep<0
PFR Value of Receivables*0.95
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.0
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.0