Excel & Modelling Techniques

Dynamic Arrays Are Powerful. That Does Not Mean Every Model Should Use Them.

After writing an entire handbook on how to translate FAST-style financial models into Dynamic Array logic, I should probably open with fireworks.

New Excel functions. Cleaner timelines. Fewer copy-across errors. Maybe even a small GIF of one elegant formula spilling neatly across 100 periods…

Instead, I will start with possibly the least glamorous sentence in this whole topic:

Some models should probably stay classic.

Not because Dynamic Arrays are bad. Quite the opposite. I like them a lot. Possibly slightly more than is socially healthy.

But financial modelling is not a talent show for Excel functions.

The question is not what Excel can do. Excel can do a lot these days. Sometimes things where one suspects someone in the room should gently have said: “Perhaps not before the credit committee.”

The better question is:

Is this architecture right for this model, this purpose and this audience?

That sounds less exciting than a new formula.

But it is where good modelling starts.

The Small Problem With Elegance

A classic FAST-style model has a reassuring rhythm.

One row. One formula. Copied across time.

It is not glamorous. It will probably never get its own TED Talk. But it has one big advantage: it is familiar.

A reviewer clicks into a period cell and sees the formula. Then clicks next door. Then again. Eventually they are either comfortable or need coffee. Usually both.

That cell-by-cell logic is not perfect. Anyone who has ever searched for one inconsistent formula in the middle of a long timeline knows the feeling: somewhere between model review and archaeology.

Dynamic Arrays solve that particular problem rather beautifully.

One formula in the anchor cell. One spill across the timeline. No quietly different formula in period 47, smiling to itself for three versions.

That is powerful.

But it comes at a price.

The formula no longer lives in every period cell. It lives at the front, in the anchor cell. The later periods show results, not their own formulas.

Transparency does not disappear.

It moves house.

And, as with every move, someone eventually realises that the box labelled “checks” has gone missing.

Cleaner Is Not The Same As Safer

This is the key point.

A Dynamic Array model may look cleaner. Fewer formulas. Less copying. Less visible clutter.

But less visible clutter is not the same as more control.

You can build a model that is elegant and almost impossible to review. That is a bit like a designer chair nobody wants to sit on. Lovely to look at. Quietly threatening in practice.

If the formula only lives in the anchor cell, reviewability has to be rebuilt deliberately.

Not as decoration.

As architecture.

That means clear row checks, sheet checks, a master check, a Dynamic Array guide, a change log and, above all, proper reconciliation against the baseline.

Not roughly. Not “it looks plausible”. Not “but the new formula is much prettier”.

The rule is boring, strict and absolutely necessary:

Same logic. Same results.

If a formula looks elegant but does not reconcile, it is not yet elegant.

It is just confident.

When Classic FAST May Still Be Better

There are several situations where a classic FAST-style structure may simply be the better choice.

The first is external review.

If the model will be reviewed by banks, investors, buyers, auditors or external model reviewers who expect a traditional structure, that matters. Their expectation can be explained. It can evolve. It can be challenged over time.

But it should not be ignored.

A model is not only for the person who built it. It is also for the people who have to review it, use it, negotiate with it, approve it, or open it again in five years when nobody quite remembers why row 148 was “just temporarily adjusted”.

The second situation is manual timeline input.

Prices. Volumes. Capex profiles. Opex assumptions. Overrides. Scenario inputs.

These assumptions often need to be visible exactly where they apply: in the relevant period. If they become spill logic, something practical is lost: the direct link between the assumption and the point in time.

Of course Dynamic Arrays can still consume those inputs.

But the inputs themselves should often remain classic.

Not everything that can spill should spill.

It may not be much of a life philosophy. But for financial models, it is surprisingly useful.

The third situation is team readiness.

If only one person understands the Dynamic Array architecture and everyone else politely nods, that is not progress.

That is a bus factor with modern Excel functions.

A model is not a monument to the best Excel user in the room. It is a working tool. It has to be understood, reviewed and maintained by the right people.

Technical elegance is worthless if it reduces operational safety.

Where Dynamic Arrays Genuinely Help

This does not mean we should stay with classic copy-across logic simply because it is familiar.

Familiar is not the same as good. Sometimes it has just been around long enough to stop being questioned.

Dynamic Arrays are especially powerful when they reduce real structural risk.

Long timelines are an obvious example. In a classic model, one cell in the middle of the timeline can be different from the rest of the row. Sometimes intentionally. Sometimes accidentally. Sometimes nobody knows anymore, but everyone hopes there was a reason.

A well-built Dynamic Array row reduces that risk substantially.

They are also helpful when the timeline needs to remain flexible. If a model’s duration grows or shrinks, a spill-based structure can be more robust than one where formulas have to be manually copied across new periods.

Anyone who has never looked for a forgotten final column has either not modelled much, or has unusually attentive guardian angels.

And then there are the areas where Dynamic Arrays genuinely shine: opening balances, deltas, corkscrews, cumulative values, coupled states and some valuation logic.

Functions such as SCAN and REDUCE can express certain structures more cleanly than classic copy-across formulas.

But again:

A clever formula is not automatically a good model.

A clever formula that nobody can review is just a riddle with a cell reference.

The Case For A Controlled Hybrid

This is why I increasingly think the most interesting answer is rarely “all classic” or “all Dynamic Arrays”.

The better answer is often a controlled hybrid.

Manual inputs remain classic. The presentation remains readable. The model structure remains FAST-style. The outputs remain understandable. Checks remain visible. And Dynamic Arrays are used where they genuinely improve the model.

Not as decoration. Not to prove we know modern Excel functions. Not as a small fireworks display for LinkedIn.

But where they make the model safer, more robust or clearer.

That may mean classic input rows, but Dynamic Array bridge rows.

It may mean visible read-off lines, but an engine in the background where several states are genuinely linked.

It may mean classic review discipline, but stronger structural control over the timeline.

In other words, not old versus new.

Purpose versus architecture.

The Real Modelling Question

So the important question is not: Can I build this row with Dynamic Arrays?

The better question is: Should I build this row with Dynamic Arrays?

And immediately after that: Can I build it in a way another human can understand, review and accept?

That is less spectacular than a new function. But it is much closer to the reality of good modelling.

A good modeller does not only recognise what is possible. A good modeller recognises what is appropriate.

Dynamic Arrays are powerful. I will keep using them. Very happily. Possibly with the slightly worrying enthusiasm of someone who has spent too long staring at the same timeline.

But the best model is not the model with the newest functions.

It is the model whose architecture you can still explain after the excitement has faded, the reviewer has asked the first question, and someone in the background says:

“Can we just quickly check the model?”