Financial models built in spreadsheet with focus on transparency (white-box models) often lack scalability and creates significant spreadsheet risk as it depends on periodic human intervention. Black-box models are less transparent but often allow you to handle high complex operations and are very scalable.
When you are building a detailed financial model, a question that often arises is if it is appropriate to use extremely complex formulae in financial modeling or should it be very simple for the end-user to understand.
A commonly held view is that financial should be very simple in the interest of transparency. While we appreciate the importance of transparency, we disagree with equating transparency with simplicity. Transparency is about keeping the logic of the financial model open for others to interpret.
In this blog, we argue that the decision to keep simple or complex should be driven by key considerations of the inherent complexity of the problem and the level of scalability required. We illustrate this with a simple straight- line depreciation model.
White-box and black-box financial models
When a financial model is easy to explain to an end user, it is referred to as a "white-box model". On the other hand, when the model is very difficult to explain it is referred to as a "black-box model".
Essentially white-box models are transparent while black-box models are opaque.
In an ideal scenario, a model that involves complex calculation, but which can be still explained to a knowledgeable end user in terms of its working is still a white-box model.
However, most financial modelers consider complex modeling logic as an opaque model and generally discourage it.
But why would one prefer complex approach when simpler option exist?
It may appear to be a no-brainer that when you have a simpler option, that should be chosen over a complex alternative. But in the world of financial modeling, what is simpler to build may be very complicated to maintain and vice versa.
Let me illustrate with a simple straight-line method depreciation calculation.
Let us say, we have been given a fixed asset register as shown below. We have various type of assets purchased at various points in time for different amounts. These assets also have different life expectations and annual depreciation.
Exhibit 1: A sample fixed asset register extract
If we have to build a proper depreciation model to calculate depreciation for all years, what would be the right approach?
One of the ways to do it is to build a separate schedule for each of the asset and calculate their respective depreciation, as shown in exhibit 2. Such a model would be highly transparent and easy to understand.
However, one of the major problems in this approach is that if a new asset is purchased, then we would have to create an additional schedule and ensure that the schedule is also linked to all other existing schedules. Since that would involve manual intervention, it creates a huge scope for spreadsheet error later. For instance, a novice analyst may link the additional capex to cash flow statement but forget to link depreciation to the income statement or WDV to the balance sheet.
Exhibit 2: Modeling fixed asset and depreciation with detailed break-down
On the other hand, one could apply a relatively complicated technique in MS Excel that I have shown in exhibit 3. This creates a single schedule for all the assets. So, when a new asset is added to the table, this consolidated schedule would be able to factor that in without the need for any manual intervention. You can download the illustration file from the link.
Exhibit 3: Modeling fixed asset and depreciation directly at aggregate level
(The screen grab shows application of the new dynamic array principle. But it can also be done without that)
The third approach that is possible is to create a separate schedule for each of the asset but use a VBA Macro / script to automatically synchronize every new asset’s schedule into the consolidated total.
While the first approach is very simple to create and understand, the second and third approaches are the ones that can make life simple for the longer future. However, they do require higher level of proficiency in MS Excel, mathematics and / or programming.
You can download the file and play with it to know the difference between the first two approaches.
So, what are the key takeaways?
Although it is good to keep a model as transparent and as simple as possible, they cannot be the corner stone or overriding condition for a financial model.
What is transparent and simple is also often subjective based on the end user’s knowledge. For instance, for an accountant with limited understanding of quantitative principles, even a simple linear regression model can be a black box.
We would recommend the following broad guidelines to choose between white-box and black-box approach
- A black-box can be and should be applied if that is the only way to make the model more scalable and have longer shelf-life.
- A black box model should also be preferred if it can substantially increase the accuracy of the model as compared to a white box approach. However, what is substantial may be subject to the user’s opinion.
- Black-box approach should not be used just for the sake of complicating with no other value addition. Quite often, analysts, in a zeal to protect their IP from others, over complicate even a simple calculation. This creates in a complex model that adds no real value. (IP protection can be handled through features like sheet protection and masking)
- Similarly, over-simplification of a complex problem just to keep the model simple or white box should also not be done. This can lead to significant inaccuracy in the model and may also add a lot of spreadsheet risk.
The real world is too complicated to be modeled with just basic mathematical operators.