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.

Note: 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!

Add SUMCOLS and SUMROWS functions so we don’t have to rely on the ugly, difficult and non-intuitive MMULT function just to add up a column of numbers
Copy the formatting when a formula spills (at the moment, you need to do this manually or pre-format the range you think your formula might spill to)
Array formulas should inherit their number formatting from the source data (at the moment, you need to manually format your dynamic array formulas)
Add a native corkscrew calculation to dynamic arrays so we don’t have to do all this messing around just simulate a corkscrew