The Model Metrix Approach to Building Better Financial Models
April 14, 2021 | by: David Phillips
An often-overlooked issue that companies face is the lack of a unified financial modeling methodology. Model design is usually up to the style and experience of the builder. Many people stretch the limits of their understanding of spreadsheets and take shortcuts or employ workarounds that did the trick in the past. These factors only drive spreadsheet risk beyond the usual hidden formula errors, broken worksheet links, old versions and copy/paste errors. Spreadsheets inherently create silos of both knowledge and the mechanics of the underlying model. If a spreadsheet owner leaves the company, much of the deep understanding about the model departs with that individual.
At Model Metrix, we recognize that every financial model that drives decision-making and business performance needs to meet the highest standards of accuracy, transparency and efficiency. In order to ensure repeatable and predictable success, we have developed the 5 Layer Design Pattern. This modeling methodology is specific to our platform of choice, Quantrix Modeler, which enables a structured approach. Quantrix is an alternative to spreadsheets for enterprise-level financial and business modeling.
Here’s the sequence in which we apply our 5 Layer Design Pattern:
- Establish model structure: identify dimensions and associated “facts”. Common dimensions include products, locations, cost centers, employees, currencies, etc. The facts are items within dimensions such as widget 1, widget 2, Florida, California, US Dollar, Japanese Yen, etc. We also put these dimensions into the right relationships such as products by sales territory or department by line of business or position to employee.
- Build out logic: in this step, we begin to create formulas for calculating values. Quantrix is different from spreadsheets in that natural language formulas calculate a range of cells rather than individual cells. This results in radically fewer formulas, even as the model grows. Interestingly, formulas can be built without any values in cells. We use the facts, related dimensions and relationships to allow the model to expand and update without needing to revisit the logic.
- Add data: at this point, we can now pull in or copy over data. Oftentimes, we will add sample data to validate structure and logic. After verification, we pull in the full data set.
- Presentation: this is one of the most enjoyable parts of model building. We can now start creating interactive dashboards and reports. With Quantrix, we can add tables, charts, widgets, expressions, custom scripted actions, logos and more for customized reporting. Since the model is well structured, we can snap parts of the model together along common dimensions, add common filters and synchronize views across the whole model.
- Security: this is the final step to model construction. We utilize highly granular role-based permissions to define what users and stakeholders can do and see. Again, structure allows us to present, allow edits/modifications or hide parts of the model based on particular roles.
Our modeling methodology has been tried and tested through the development of hundreds of models. Indeed, it was developed by Steve Bailey, the former head of professional services at Quantrix, and now a principle at Model Metrix.
Oftentimes, we are tasked with re-engineering a spreadsheet model as the basis for building the model in Quantrix. It is often a shock to clients when we reveal the multitude of errors in the original spreadsheet. When we walk these same clients through their new model developed with our 5 Layer Design Pattern, they quickly grasp the underlying purpose, structure and logic of the model. This level of transparency gives them a high degree of confidence that they will be working with a high-fidelity model. Furthermore, the model can be readily understood by new users.
At Model Metrix, we use our proven methodology in every model we build whether it is for a Modeling-as-a-Service engagement or implementing an Integrated Modeling Solution. We also teach the methodology as part of our Quantrix Modeler training engagements. Successful companies realize that there are significant benefits to consistent model design. If you are interested in learning how we can improve model quality in your organization, contact us for an overview and discovery call.