Financial Modelling using
Dynamic Array Functions
Best practice financial modelling has always been to enter
your formulas in blocks: enter a formula, then copy this across
and possibly down also, making sure you have your absolute
and relative references set correctly. Advantages:
▪ it is faster to build a model,
▪ easier to enter lots of formulas,
▪ less error-prone and easier to audit
Excel’s dynamic array functions take this concept to a whole
new level because they can be setup to automatically copy
themselves.
What’s great about Financial Modelling using Dynamic Array
Functions?
We only need to type each formula once, and it will
automatically spill (i.e. copy) itself across and down (if
necessary).
By changing one number, our entire financial model can be
extended to incorporate additional periods.
By inserting additional row(s) into our setup tables, we can
automatically add as many new dimensions (products, staff, )
as we want.
Dynamic array functions can also be used to automatically
summarize our detailed calculations and generate all our
financial model reporting using the standard SUMIFS formulas
and others like these.
In addition, dynamic array functions can even be used to
produce simple scenario managers. These have a few
advantages over data tables since all the results are live all
the time plus they’re about 1,000 times faster.
Because all dynamic array functions are processed in arrays
(as you might guess!), they are incredibly fast, even over
millions of calculations.
You can also use them for balance sheet calculations, although
this is a little more complex at present. More details in our
blog on Corkscrew Calculations.
Take a look at the video & the free example file provided to
see how we built an entire financial model without copying and
pasting a single formula!
