top of page

Why you should build models using dynamic arrays?

Most models go wrong later in their life due to poor update and maintenance practices. Dynamic array models almost eliminate the need for maintenance, and thus significantly reduces spreadsheet risk.

There were three little pigs: Fifer, Fiddler and Practical. Fifer built its house out of straw while Fiddler built its house out of sticks. Practical was toiling hard to build its house out of bricks. Fifer and Fiddler, having finished their work fast, were mocking Practical for its efforts and went about happily playing and singing. All went fine until the big bad wolf entered and started to “huff and puff” to blow the houses down. Only Practical survived - thanks to its strong brick house.

This allegory can be applied to so many things and I am using it here to talk about the approaches to financial modeling.

But, before I explain that let us understand why most spreadsheet-based financial models contain mistakes.

Unlike other application development projects, financial models do not go through a proper system development life cycle. Often, a lot of care is shown at the beginning to ensure that models that are built do not contain errors. However, in my experience, most of the errors creep in much later due to faulty maintenance or updates. For instance, when new data comes in and it has to be added, somewhere an analyst may miss to copy-paste some section of a model or changing the cell reference to the latest reported year once the new year is out. These mistakes often go undetected as they are not expected to occur in the place – how difficult is it to copy-paste something anyway?

These small human errors are the ones that eventually make the model go horribly wrong. I have seen models in which increasing the cost of goods sold made the equity valuation go up because someone made a small mistake somewhere (long explanation, so I am skipping it).

The way I see it, these mistakes that creep in during the maintenance stage are the big bad wolf that blows the house (the model) down. You need strong structures to protect you from them. For this reason, some companies have completely done away with spreadsheet models and have migrated to SQL, Java, Python, and the like to avoid the entire problem. But if you do not want to leave the familiar spreadsheet environment, then Tables and dynamic array-based models is the one that gives us the architecture that can protect us from this big bad wolf.

But before I explain how it does that, I am going to explain how the three little pig allegory applies to financial models.

Spreadsheets without any best practices are the house of straw

Many beginner-level practitioners look at spreadsheets just as a set of boxes that can do automatic calculations. They design spreadsheets to calculate correct value for the current set of input but hardly factor in other situations. As you can see in Exhibit 1, the tax payable is calculated as profit before tax multiplied by tax rate. While this would give the correct answer when profit is positive, the formula would give wrong answer if there were to be a loss (it would show negative tax instead of zero).

Their design requires too much manual intervention even for small updates. Again, in Exhibit 1, if the next year's reported number comes out, the actual section and forecast section must be manually altered.

These are like the house of straws and these models would collapse very easily as it requires too much human intervention and thus very high scope for error.

Exhibit 1: Poorly designed spreadsheet for financial projection

F.A.S.T, B.P.M, etc leave you with the house of sticks.

We may be offending many financial modeling practitioners, here. But several of the relative reference-based “best practices” frameworks such as the F.A.S.T framework or B.P.M framework or the ones published by ICAEW do not explicitly help you to fail-proof the model a lot. It is not to say these frameworks are bad. But, they are not adequate. Most of the models using relative referencing that experienced modelers build are designed to extremely end user friendly. But the problem is that they are not very machine-friendly. They tell the machine what is being done but do not explain why it is being done. It leaves most of it to the user to ensure what is being done is logical. And this creates a scope for error if the user drops the guard.

For example, in exhibit 2, we have a model where the last reported net cash balance in C6 is linked to cell C17 (which is for the year 2019). Column C contains the last reported year, so the logic of why it is linked to the column is clear to a skilled end-user. But this logic is not built in the spreadsheet. So, when the company comes up with an update for 2020, the last reported cash balance would need to be manually linked to D17. This is a simple task. But when an analyst is exhausted after a 16-hour workday, there is a fair chance that they would miss changing this.

These best practices avoid some creep-in mistakes but cannot prevent everything. So, the big bad wolf may have to huff and puff twice but it would eventually destroy this too.

Exhibit 2: Model designed following common best practices

If a new user does not understand why EBITDA is picked from column D or cash is picked from column C in the valuation section, they are most likely to make mistakes while updating the model.

You may be thinking that I would now say dynamic array models is the equivalent of a house of bricks. But here is a small twist in the plot. There are four pigs in this tale.

Query structure and data model approach are the ones made of bricks

A robust model overcomes the risk of relative referencing by using query structures and the data model approach wherever relevant. So, in exhibit 3, as you can see, the last reported cash balance is not linked directly to the cell, but it is obtained using a lookup function.

So, when the user updates the last reported year, it would automatically obtain the corresponding value. If the user fails to change the year, the mistake can be easily detected by just glancing at the input without having to audit each formula.

Exhibit 3: Model designed using query structures instead of relative referencing

This one has very limited scope for creeping error as it requires very limited intervention. But, any manual intervention is a scope for error and thus it does retain some vulnerability.

Although this approach is very robust, these models also suffer from certain limitations.

  • Spreadsheets would become too slow when one uses too many processes heavy functions like Lookup, Match or Offset

  • It still requires some manual intervention. If we need additional columns, we may have to copy-paste or extend links to cover the new columns.

Models built with Tables and Dynamic array are like houses built with reinforced concrete

Properly built dynamic array models do not require any maintenance work as long as there is no change in the system. So, unless the business of the company changes or their reporting framework is modified (which are rare events) these models can take care of themselves.

And I’ll leave exhibit 4 to do the talking. The clip-in exhibit 4 shows a debt model that automatically updates itself as more and more debts are added - even if they have different rates and payment terms. It also automatically expands the number of columns required. As you can see in the video, once the user updates the input table, the module automatically factors in the new inputs, extends the required number of columns and gives precise output without any troubles.

Exhibit 4: Model designed with Tables and dynamic arrays

Although similar tasks can also be achieved with a data modelling approach without a dynamic array, the later ensures that an end-user need not do anything beyond updating the set of inputs. A simple data model approach would still require copy-pasting new columns when additional years are factored in.

So, this one can not just handle the big bad wolf but it can also easily withstand some tornadoes and some shelling (of course, metaphorically speaking). You just need to ensure that an insider doesn’t intentionally botch it up.

But just like a house with reinforced concrete is going to take more time and resources, models built with tables and dynamic arrays also take more time to get built. I have built many models over the last six months and they take at least 2x the time. And these models also require extensive testing and extensive fail proofing (note the "complete" flag in column L in exhibit 4) because their agility also makes them incorporate incomplete or wrong input right away. Having said that, all these extra efforts are worth it as it significantly saves time and helps us avoid risk during later updates.

So, to conclude, learn from the fable of the pigs. Dynamic arrays is the future of spreadsheet modeling. It may take a while before it is widely adopted, but ignore them at your own peril.

36 views0 comments

Recent Posts

See All
bottom of page