Build a Financial Model using Dynamic Array Functions

See how to apply Excel's dynamic array functions to create a financial model without copying & pasting a single formula!

[WebberJason]

This is the future of modelling! I'm sure that a new wave of best practices and techniques will evolve around dynamic array modelling over time, which take the quirks into account.

[mwaltercpa]

Excel that does the incremental work for you.. I love it!!

[wayneedmondson]

Hi Jeff.. thanks for the video and example of using new dynamic arrays to build a financial model. I think there are some instances where dynamic array functionality adds to overall efficiency and you have given some great examples of how the future might look. Only time will tell which functionality (traditional or new) is better applied in any particular circumstance. It is exciting to have new options and new tools to solve problems. Thanks for sharing your tips and tricks. Thumbs up!

[plumsolutions]

This is fantastic, thanks for sharing!

[moglij]

I have followed along with the video and created the spreadsheet as I went. I have found that the formatting does not "spill". I have tried formating a cell before I enter the formula in it but the format does not extend to the spilled cells.
Therefore, even though I don't need to copy and paste formula, I find I do have to copy and paste formatting.
Am I missing something?

[piero]

Amazing video, it's going to increase the efficiency of my modelling for sure, thanks a lot for sharing it. The only question I have is about the Setup tab, are the information in there something that we copy and paste from another file where we carry out the calculations ? For instance the Monthly Change rate column is hard coded so that is something that probably is coming from another file, correct?

[qwaletee]

Normally for a 1-D array you could use sum(refname#) and odn't need MMULT. However, sum(refname#) on a two-dimensional array gives you a single number sum of everything. Your problem is that you want to aggregate one dimension of a two-dimensional array, i.e., aggregate sum of columns without aggregating rows. Excel is still immature in array processing - it only supports 1- and 2- dimensional arrays, and it has no functions for breaking down arrays, not even anything as simply as referencing portions of an array. Best we have is index, which is awkward and limited. I think we will eventually get a notation to more directly access array elements and dimensions.

[adambalazsschuszter]

Thanks Jeff, astonishing video. Is it possible to get access to the spreadsheet ?

[db]

How did you make the excel add the border / hgihlight the section where you moved the cursor?

[mypage]

Thanks for the video! Would this work if there are assumptions of margins changing from one period to next?