Revolutionize Your Excel Forecasts: Dynamic Arrays Unleashed!

⏬More info and copy of the file⏬
Excel Dynamic Array formulas are amazing and can allow you to develop an automatically expanding forecasting model.

We even see what happens when Jeff adjusts it to show 16,000 months!

Jeff flags a few techniques and functions for getting this to work

Presented by Jeff Robson, founder of Access Analytic.

File

00:00 Intro
02:26 Demonstration
07:24 Referencing vertical and horizontal arrays
08:30 Using BYCOL to sum individual columns from an array
10:55 Using * instead of AND
12:20 Calculating Opening and Closing Balances using SUMIFS
15:24 Using + instead of OR
17:12 Calculating Depreciation
17:50 Quickly switch from 8 month to16,000 month forecast!
21:10 Summarise Outputs
22:12 NPV What-if table
23:10 Cashflow and circular references
25:38 Add new items and insert rows wherever you need

[IvanCortinas_ES]

Superb video. Thank you very much Jeff and Wyn!

[Locomaid]

Awesome! I learned that I know NOTHING about Excel after several decades of intensive use… So excited to learn more. Thank you!

[sachin.tandon]

Also, you can use tricks like =HSTACK(0, DROP(D7#+D8#, , -1)), if you need to shift arrays by one cell, but then drop the very last cell, to keep the array the same width. Avoids the querky corkscrew calculations when you want to avoid repeating redundant information. Hope it helps, and great video again!

[malejandrahorvath]

Wow!
Amazing 😎!
Thank you Wyn and Jeff!

[sachin.tandon]

High Quality Financial Modelling! Thanks for sharing and organsing Jeff and Wyn. Wish these functions existed in my PwC C.A. days!

[YouExcelTutorials]

Super cool stuff! There's always something new to learn here. Thanks Wyn and Jeff!

[sachin.tandon]

Also for the B/S - I solved for this a few months ago, as I was thinking about the same thing: Use - D18=SCAN(D14, D15#+D16#-D17#, LAMBDA(a, r, a+r)) for the carry forward, and E14=DROP(D18#, , -1) for the b/f, where rows 14, and 18, are the b/f and c/f rows

[CraigHatmakerBXL]

At the moment, AFAIK, DA's can't do corkscrews. The work around is accumulators. LAMBDA with SCAN seems to be the preferred method.

[henryg]

Very cool 😎

With all those dynamic array formulas being used, I did notice an old school index(match()) though.

I do wonder if the dev time in real-world use would pay off.

[peterbartholomew]

That's a huge step in the right direction! Not many would have the courage to type 16, 000 into the number of periods cell but it made the point!

I feel obliged to make my customary complaint about the use of direct cell referencing. The A1 notation is an abomination that has no place in any computing environment anywhere, at any time. Sometimes I express the sentiment in more direct terms 😄! [Caveat: machine code does use direct memory addresses in situations where performance is everything and meaning/intelligibility takes a back seat]

Since I use defined names for everything else, I extend the courtesy to naming all Lambdas (either using name manager or within LET). That allows
= BYCOL(G54#, LAMBDA(column, SUM(column)))
to become
= BYCOL(amounts#, SUMλ)
which cuts out the intermediate 'column' variable used to pass values by name. SUMλ itself is defined to by
= LAMBDA(x, SUM(x))

As for corkscrews, my function of choice is the SCAN helper function. It provides the running total as a single step, though a bit of 'dressing up' is required to emulate the layout of the traditional corkscrew used for hand calculation. The main calculation is
= SCAN(openingBalance, creditSales+receipts, ADDλ)
where ADDλ is defined by
= LAMBDA(x, y, x+y)

Are you regretting inviting comment😧?

[GeertDelmulle]

Hi Jeff and Wyn,
Dropped in to say that last year autumn I made a conceptually similar DA-model as an investment model for a multi-year programme (several 10s of MioEuros — it was approved). Dynamic investment horizon was an important part of the solution. And of course, if you play your conditional formatting cards right, you can make it look as if you can create ‘tables’ that look like they can dynamically extend sideways. Oh, the look on their faces… :-)
Now, from your video I did learn —or rather: was reminded of— one formula: how to calculate cumulative sums, the way you did (I used the more convoluted MMULT approach). And yet I knew your formula since I used it myself in an Excel Table context so many times before. It just didn’t occur to me to use that very concept in an DA-context. I updated my model. Thanks for that. :-)
Cheers!

[DrAmgadSquires]

Great video! Love how clean your workbook is.
Interestingly, I just created something very similar not two weeks ago to generate dynamic daily cash flow forecasts based on historical data, but with the ability to dynamically select the historical range used as input, the range of dates over which to forecast, and correctly visualizing actuals and projections separately on the same line chart. This required every element of the model having both an actuals and a projection row, where each projection row was based on the actuals row above it.
Needless to say, it took quite a bit of fiddling to make everything dynamic and have the charts look right (in particular, empty actual/forecast cells had to be filled with a dummy "" value so as to leave a gap on the chart, which then required tweaking all dynamic formulas to handle this dummy filler).
It took a while but I'm very pleased with the result, and what is more important, so was my client!

[slryt]

Really excellent concept. As others have said, not sure the dev time would be justified for the corkscrews (and explaining to a client why a simple plus and minus won't do would be fun).

But.... iteration active? 🤨 The very thought brings me out in hives.

[martyc]

Well done 👍 - will be playing with the file tomorrow!, could some of those tricky(referring to prev values) ones be simplified using scan?