Building a whole financial model using Excel's dynamic arrays
Recently we’ve seen a few experiments where people have seen how far they can get building a whole Excel model out of ‘dynamic arrays’.
It’s a fun experiment but we’re left wondering whether it’s worth the effort. That’s for the reasons outlined at the bottom of this article here: using Excel dynamic arrays.
Our own experiment
Not easily deterred, we had some build work recently where there were a lot of repeated inputs which flowed through the model. It was an opportunity to do something at the very-simple end of working with dynamic arrays (that’s compared to the alternative of a traditional construction which would have used simple links).
Let’s have a look at the differences and challenges that arose – dynamic arrays vs. traditional links and formulas.
Traditional construction: getting ready to delete a row
Imagine we selected a row in a traditional model for deletion (see row 15 below).
Deleting a row
When we delete a row in a traditional model, everything downstream (connected to what’s now missing) gives us an obvious #Ref! error. That’s helpful. We can see – very clearly – what we need to delete next. The next step is to tidy up the downstream calculations by deleting the rows (sitting in the same position in the subsequent calculation blocks) where the #Ref! errors appear (rows 28 and 42 below).
What happens when we do the same thing but with dynamic arrays?
With a traditional construction, modifying the model seems a little simpler. We have to find all the #Ref! errors and delete them.
With dynamic arrays, it seems a little more complex (and, to start certainly, less intuitive).
Here’s what happens after we delete a row in the dynamic arrays version of the model:
The dynamic array formula at cell A5 does a good job of trying to ‘survive’, so we need to fix that up first.
Then we’re going to need to go through the downstream model sections and find where it’s broken (see lines 22 and 36 below) but it all feels a little less obvious than the highlighting we get with the traditional #Ref! errors. In a large dynamic arrays model it would be easy to miss something that’s not working.
Deleting a column
It’s similar when we modify a model by deleting a column.
It feels like there’s a lot more (less intuitive) patching up to do with the dynamic arrays version of the ‘model’.
With the dynamic arrays version you have to find and delete the correct areas of the model.
With the traditional construction you have to find the #Ref! errors (which sit in the same position in the column you deleted upstream) and delete those. That seems like a simpler exercise, less prone to error.
Conclusion: do you really want to build a financial model with dynamic arrays?
The above example illustrates the kind of (slightly awkward and frustrating) challenge you could have working with a model that makes heavy use of dynamic arrays. If you delete something upstream you might decide it becomes harder to spot what’s broken downstream and fix it. Maybe, with time, you’ll decide you could get used to the difference. But, initially, you may wonder whether adapting to the difference is worth it.
For us, when we take into account our prior thoughts about using Excel dynamic arrays, it means we’re not going to rush out and build complete models from dynamic arrays any time soon.
