๐๐บ๐ฝ๐ผ๐ฟ๐๐ฎ๐ป๐ ๐๐ผ๐ป๐๐ฒ๐ ๐: ๐ช๐ฒ ๐ถ๐ป๐ต๐ฒ๐ฟ๐ถ๐๐ฒ๐ฑ ๐๐ต๐ถ๐ ๐ถ๐บ๐ฝ๐น๐ฒ๐บ๐ฒ๐ป๐๐ฎ๐๐ถ๐ผ๐ป *years ago*. ๐๐๐ฒ ๐๐ผ ๐๐ถ๐ด๐ต๐ ๐ฝ๐ฟ๐ผ๐ท๐ฒ๐ฐ๐, ๐ฐ๐ผ๐๐ ๐ฎ๐ป๐ฑ ๐๐ถ๐บ๐ฒ ๐ฐ๐ผ๐ป๐๐๐ฟ๐ฎ๐ถ๐ป๐๐, ๐๐ฒ ๐ฐ๐ผ๐๐น๐ฑ๐ปโ๐ ๐ฝ๐๐ฟ๐๐๐ฒ ๐ฎ๐ป๐ ๐ผ๐๐ต๐ฒ๐ฟ ๐บ๐ผ๐ฟ๐ฒ ๐ผ๐ฝ๐๐ถ๐บ๐ฎ๐น ๐๐ผ๐น๐๐๐ถ๐ผ๐ป๐ ๐๐๐ฐ๐ต ๐ฎ๐ ๐๐ต๐ฒ ๐ผ๐ป๐ฒ๐ ๐๐๐ด๐ด๐ฒ๐๐๐ฒ๐ฑ ๐ถ๐ป ๐๐ต๐ฒ ๐ฐ๐ผ๐บ๐บ๐ฒ๐ป๐๐.
Imagine a capital investment model with columns for months and years, and many rows for revenues, expenses, profit, capital investment, annual cash flow, cumulative cash flowโplus a few rows for NPV and IRR. The image shows a vastly simplified model.
Now imagine implementing all of this in Salesforce for a big oil company. Not just the basic model, but also sensitivity analysis: what-if scenarios with, say, +10% expenses or -10% revenues โ all this requiring recalculations of the entire model for every scenario. Needless to say, triggers weren't cutting it; recalculating everything was slow and even led to timeouts.
Almost a decade ago, we inherited this implementation and needed to fix it.
I suspected there was potential to leverage derivatives for sensitivity analysis to provide a faster and more efficient solution. But due to project and time constraints, we had to implement the entire recalculation approach instead - so there was some frustration because this was a sort of brute force approach.
Ultimately, we used asynchronous Apex to handle the heavy lifting, avoiding timeouts. But it was a pity that we couldn't optimize it further by determining the derivatives for NPV and IRR โ a missed opportunity for a more elegant and faster solution.
For example, with the NPV formula, the derivative with respect to a cash flow would give an estimate of the change in NPV more directly, without recalculating everything. Using ฮNPV โ ฮC_t / (1 + i)^t would approximate the impact of changes in revenues or expenses, significantly speeding up sensitivity analysis (IRR would be more complicated though).
Have you ever faced a scenario where an optimal approach had to be put aside due to constraints? How did you handle it?
