Preface
The introduction of Dynamic Arrays and the LAMBDA function mark a significant evolution in modern spreadsheets such as MS Excel, Zoho Sheets, and Google sheets.
​
These functionalities empower financial modelers to build more powerful financial models — models that are transparent, robust, scalable and work like an app. However, this new paradigm demands a different set of best practices compared to legacy scalar modeling techniques. And the absence of a clearly documented framework is a key barrier for its widespread adoption.
​
This guide provides that framework. It is built on four foundational pillars that we call the BEST principles:
​
Balanced: Financial modelers must find optimum balance between conflicting priorities.
Efficient: The model must be fast and responsive enough to aid proper decision making.
Stable: The model must remain functional and reliable when users interact with or update the data.
Thorough: The model should not have loose ends, which creates spreadsheet risk.
​
Each guidance in this first version of the document is a product of first principles thinking, established software design principles, and our extensive experience at Profectus in providing robust Dynamic Array based solutions to the clients over the last five years. Every guidance is accompanied by a clear rationale.
​
But the future version of this document is expected to be driven by feedback, deliberations and contributions from more professionals, worldwide.
​
We refer to this version as a guide and not a standard because it focuses on providing proper principles rather than imposing rigid rules.
​
To that extent, we distinguish our guidance into two parts:
-
Prescriptions: Critical practices that are essential to avoid tech debts and spreadsheet risks.
-
Recommendations: These practices enhance the user experience or performance of the financial models.
The guide also includes discussions of alternative views, presented in the Appendix, to encourage healthy debate and critical evaluation of alternatives.
​
This guide is intended for experienced financial modelers, who are in the best position to determine the appropriate approach for each modeling challenge. We neither insist that dynamic array is the only way forward for future financial modeling, nor do we prescribe when to use them.
​
Instead, for those who choose the modern paradigm, the guide delivers practical guidance on input architecture, design principles, validation and debugging, governance, and audit. Ultimately, it provides the framework needed to fully leverage dynamic arrays and LAMBDA, while addressing the real-world concerns that arise with this new paradigm.