BEST Framework – Foundation for Modern Financial Modeling
Understanding the paradigm shift from traditional cell-by-cell modeling to dynamic array-powered financial models
Traditional financial modeling in spreadsheets has followed a cell-by-cell, copy-and-paste methodology for decades. While this approach has served the industry well, it comes with inherent limitations: models become unwieldy as they scale, formulas are prone to copy-paste errors, and maintenance becomes increasingly complex as business requirements evolve.
Dynamic arrays (DA) and the LAMBDA functionality fundamentally change this paradigm. Modelers can now create compact, powerful expressions that handle entire datasets as single units. This shift enables financial models to behave more like software applications—robust, scalable, and self-maintaining.
Key Insight: Implementing reliable financial models with dynamic array and Lambdas (DA Model) requires a 360° overhaul of financial modeling practices that are widely followed.
The Need for a 360° Approach
A powerful and scalable formula handling complex logic needs a strong—yet friendly—input architecture to work reliably. And to ensure there is consistency when the complex logic is reapplied, it must be encapsulated in a LAMBDA function.
However, since the LAMBDA can be argued to create a “black box,” its quality and reliability must be assured. This requires applying strong functional programming design principles and ensuring that the Lambdas are thoroughly tested, validated, and bugs, if any, are identified and fixed before they are used.
But these responsibilities cannot be left to individual modelers. The financial modeling function itself must be reorganized with strong governance and organization structure, transforming it from an individual craft into a disciplined organizational process.
Finally, these changes will count for nothing if third parties cannot rely on the model. This requires that modellers still care about making their models auditable, and that auditors modify their approach to focus on validating functions over checking formulae.
This involves more than learning new functions. It requires a different approach that focuses on cohesion between all the elements to achieve the true potential of DA models with far lesser risk.
This guide provides guidance across these aspects to ensure such cohesion in each of the next few chapters.
The BEST Principles
This guide is built around four foundational principles that form the acronym BEST: Balanced, Efficient, Stable, and Thorough. These principles work synergistically to create models that are both powerful and practical.
Balanced
The Balanced principle recognizes that financial modeling with spreadsheet involves constant trade-offs, such as these:
- Pure technical optimization often conflicts with user experience
- Complete transparency can compromise performance
- Future proofing may increase the time to roll out models
The guidance in this document consistently seeks the optimal balance point for each such decision.
Efficient
Models must open quickly and perform calculations within acceptable timeframes to truly support decision-making. Therefore, this guide favours efficient use of human and computing resources.
It discourages practices that favours simplicity in calculations at the cost of creating bloated files with sluggish performance.
Stable
Stability demands that models remain functional despite common user interactions or events, such as updating data, inserting or deleting rows, or sorting and filtering tables. This guide favours approaches that allow a fully built model to automatically accommodate these changes without breaking, preserving its integrity and reliability.
Extended Definition: Stability in dynamic array models extends beyond formula resilience. It encompasses the stability of the underlying data architecture, the consistency of function libraries, and the predictability of model behaviour across different usage scenarios.
Thorough
A scalable and stable financial model cannot have loose ends that depend on end user actions. Therefore, the financial modelers should be thorough with their approach. Financial modelers should not bypass complexity to merely to avoid effort and exploit the full capabilities of modern spreadsheets to handle such complexities.
The guide clearly segregates a financial modeler from the end user.
How BEST Principles Compare to Other Standards
While standards like FAST, SMART, and the ICAEW Financial Modelling Code provide a crucial foundation for good modeling, they were largely formulated before the advent of Dynamic Arrays and LAMBDA. As a result, they focus on best practices for a legacy, cell-by-cell approach. The BEST framework, in contrast, is built exclusively for the modern DA paradigm.
While we align with the spirit of these standards—to ensure financial models are reliable—the context of DA modeling results in significant philosophical and practical deviations. Further, to keep the focus sharp on DA model, we have deliberately ignored topics like use of VBA where we find existing industry practices to be acceptable.
Key Areas of Deviation
Framework for the System and Not the Individual
The key differentiator of BEST framework is that it takes a holistic view of the financial modeling function, treating it as an integrated system of people, processes, and technology. It puts the onus of making the financial model reliable on the system. This is a departure from other standards that focus primarily on the best practices of an individual modeler.
Treats Formula Logic as Sacrosanct and Not Malleable
Legacy standards insist that formulas should be simple enough for any user to understand and modify. The BEST framework takes a starkly different view and insists that a complex formula, especially Lambdas, should not be modified, ever, once approved.
Different Philosophy to Transparency
Other standards place simplicity as the cornerstone of transparency. This guide views transparency as keeping the code accessible and in ensuring that the lambdas are pure i.e. not affected by any value other than what is passed as an argument to it.
Emphasizes Validation over Comprehension
This guide acknowledges that creating formula that satisfy the BEST principles would require writing logics that may be extremely difficult for standard users to comprehend. It emphasizes that standard users and auditors should focus on validating the Lambda by applying it on familiar data sets rather than trying to understand the model logic.
Replaces Ambiguous “Rules” with Clear Prescriptions and Recommendations
Where some standards prescribe a single set of rules that can be broken with justification, the BEST framework provides a clearer, two-tiered framework:
See the BEST Framework in Action
The video below demonstrates the clear difference between traditional approaches and the BEST standard:
Building financial model with Dynamic Array | The BEST framework