Business modelers get jittery when they see volatile functions. These are functions that recalculate each time a cell is calculated, even if the function’s arguments don’t change.

What are some of the volatile functions?

RAND(), NOW(), TODAY(), CELL(), OFFSET(), INDIRECT()

The values can’t be assumed to be the same from one moment to the next. And this worries financial analysts.

Yet financial reports, accounts, and balances change from one period to the next. We have to consider whether the flexibility of dynamic functionality outweighs the costs of making manual updates and related mistakes.

The opposition to volatile functions is usually because they can get clunky and slow down your model. But if you know how to use them intentionally and sparingly, they can accomplish seemingly impossible tasks.

The walkthrough below is based upon a single customer forecast for a company doing nearly $500 million in revenue. It uses OFFSET and INDIRECT.

If this approach worked for them, it might work for you, too. You be the judge.

Watch the walkthrough video. 👆