How to use the OFFSET function to build collection delays in Accounts Receivable...

To be honest, the OFFSET function always used to scare me: it looks at one cell, but then pulls information from another?  Floating through my model like some assassin just waiting to #REF me when I'm not looking.

In truth, when you know how to use it, it can be very powerful.

One of my favorite (and most simple) use cases is building a "collections delay" for accounts receivable.

In other words: we earn revenue in month 1, but don't actually receive the cash until month 2.

OFFSET helps us build that one month delay.

The syntax works like this:

• start by looking at a cell, what I call the "anchor cell" but then;
• move a certain number of rows away from the anchor cell, and;
• move a certain number of columns away from the anchor cell, and;
• [if I want, specify a range, in case I want to SUM up an area, but this is optional]

So in simple terms: look somewhere, but then give me the result somewhere else.

In the image in this post, I'm asking the OFFSET function to reference the revenue I've earned using an input in Column K, which is number of months.

So I'm asking it to look one month backward, with the assumption that I'll collect the cash in the current month.

To follow the syntax...

• the "anchor cell" is R129
• stay in the same row (by entering 0)
• go to the left one column (the 1 in Column K, entered as a negative to "go left")

So now, if I change the input in Column K, I will change the number of months on my collection delay.

This can easily be applied to when Inventory is sold, AP is paid, or any other place in your model where some kind of "lag" is required and you'd like the option to change that time period.

Next steps: interested in FP&A or Private Equity?  Then sign up for my free email series the Financial Modeling Educator, and go in-depth on Financial Modeling for FP&A and Private Equity Professionals.