by Jeff Robson

Corkscrew Calculations using Dynamic Array Functions

If you’ve come to this page expecting information about how to open your wine bottles, you’re going to be very disappointed!  Sorry!

The corkscrew calculations we’re talking about here are those you’d use when calculating a balance sheet account in a financial model.

Accounts Receivable Example

The closing balance in one month becomes the opening balance in the next month, so you get a kind of corkscrew logic to your formulas (if you use your imagination!).

Dynamic Array functions are absolutely amazing and if you haven’t heard about or used these, take a look at our previous video & blog “How to build a financial model without copying & pasting a single formula“.  You’ll be blown away too!

Many people have said it’s not possible to do this kind of calculation using Dynamic Array functions and in one sense, they’re right because you can’t tell a dynamic array function to look back one period (at least not in March 2020).  If you do, it treats your formula as a circular reference.

However, we can simulate this behaviour with a little creativity by calculating the net movements in a balance and summing all of these.

To see how this works, take a look at the video and download the sample file.

Download our free example corkscrew simulation file that uses dynamic array formulasNote: Office 365 is required to use this file (ideally monthly or insider channel)

Help Improve Dynamic Array Functions

There are some great ideas in Excel UserVoice so please support these and help make dynamic arrays even more useful!