9 April | by Jeff Robson and Lance Rubin

Introduction to the co-author

Jeff Robson is the Principal Business Analyst and Founder of Access Analytic. Access Analytic is a Perth-based consulting and training company that specialises in financial modelling, Power BI reporting and data analytics.

Why did Jeff select the topic and why is he passionate about it?

When you see Dynamic Array/s (“DA”) functions, they will blow your mind as you start to think about all the implications for modelling, the countless hours they can save you and the ease with which your models can be extended and adapted.

Topic and context in no more than 3 sentences

DA formulas are self-replicating and can reference all the values in a table throughout the entire workbook.

This makes them perfectly suited for financial modelling as they allow the modeller to rapidly build robust models that can be easily extended (horizontally and vertically) and still comply with financial modelling best practices.

Financial modellers who adopt DA will become more productive by building more reliable models more quickly as a result of significantly less formulas and therefore the risk of errors is greatly reduced.

If you had to teach this topic in a class to school kids what key tips would you give them to focus on

So let’s assume for the moment that school kids don’t even have Excel or Google Sheets, which in reality is probably unlikely as most have started to use these tools but at a basic level only.

Picture for a moment that you need to build one of those really complex Lego Technic constructions like the Porsche 911 RSR below.

Let’s say it will take a kid ordinarily a few hours (assuming no adult assistance) to finish. Great idea for those in lockdown during COVID19 epidemic.

Perhaps with some adult assistance this could go down to say less than an hour and with a lego master perhaps less again to assemble the 1,580 pieces. But it’s unlikely that this could be built by any human in say under 1 minute for so many pieces that are joined together in a complex set of instructions.

Well, for most financial modellers this is exactly what is required to build a model. Following a set of instructions (best practices) and connecting all the pieces together to ensure that the overall model works as intended.

But what if the number of parts (think formulas in a spreadsheet) where reduced from say 1,580 pieces to just 15 pre-assembled blocks that required you to simply connect the blocks together or fill in the assumptions once assembled.

Your time to build the car would easily go down on this basis from hours to minutes or even seconds.

This is fundamentally what DA allows you to do.

By building Excel tables and using the 7 core DA functions you can build models and re-use the formulas and structure (content) in the model to rapidly expand it for re-use in minutes or seconds even.

But you first need to keep an open mind as the formulas used are not like anything you have seen before, but then again you wouldn’t expect anything less from something so powerful and new.

Once you start to play with the tables and functions you will start to see what is possible and how powerful DA can be.

What practical steps can people take now to learn more

Firstly ensure you have access to an up-to-date version of Excel (Office 365: Insider or Monthly channel preferably). If you don’t have the latest version don’t even bother as you simply won’t be able to use the functions as the workbook will simply be full of errors.

If your clients or stakeholders are not on the same version of Excel then be careful using DA as they will not be able to use it.

Find out more using the details below by downloading and use other people’s models to understand how it works. Copying other people is the best way to understand how things work.

Both Jeff and Lance have provided links below to webinars, content and other useful resources that can help you get started.

Where are good places (links) to find out more on the topic

Learn about DA from online resources like YouTube and blog articles. Here are links of some specific useful tips and videos you can start on:

• Don Tomoff’s collection – MS Excel — Introducing Dynamic Arrays and Functions (Available Early 2019 in Office 365)

• Joe Mcdaid - Inside Excel - Episode 4: Dynamic Arrays

• Jeff’s webinar hosted by Danielle Stein Fairhurst - Building a Financial Model with New Dynamic Array Functions

• Jeff’s article – Corkscrew Calculations using Dynamic Arrays

• Lance’s webinar hosted by Danielle Stein Fairhurst - Scenario Modelling for COVID-19 Uncertainty using Dynamic Arrays including how DA work neatly with PowerBI.

• Lance shares a recently published DA model connected to PowerBI - Powerful Dynamic Arrays Model with Monte Carlo Simulation

How important is this skill in the context of learning FM?

At the time of writing this article very few models will contain these functions because they have only recently been released into the “mainstream” and are therefore very new. 

However, as the functions become more widely known, adopted and mature, they will become a critical method for constructing financial models at speed and higher accuracy (lower risk).

Errors in financial models has long been the excuse many use (especially those Excel haters out there) to avoid using Excel as a technology solution. With DA potentially significantly reducing error rates this is game changing.

Financial modellers who are looking to not only maintain their skills but also keep pace with the every evolving technology enhancements in financial modelling would be well placed to learn how to apply DA to their models.

How does all this disruption, AI and automation talk impact this topic

Currently, the disruption and automation in Financial Modelling is just beginning. We both encourage everyone to get on board and begin to learn about these topics and innovations so that they are ready to take advantage of the functions when these become more mainstream and avoid obsolescence.

If you want to find out more and follow the rest of the article series be sure to download the Financial Modelling App

If you want to find more information on financial modelling and content visit the Model Citizn website.