We frequently have to model situations where we have material differences in payment lag for different customers or different products.
In this Accounts receivable alternative solution, we look at how we can tackle this.
We're maintaining the assumption that we set up in our previous tutorial that the accounts receivable delay is longer than the model timeline. We have a monthly model, with invoices being paid over four months.
The data we need here is a profile of when, typically, invoices are paid. For the sake of this example, I've broken this down with broad %s. This could be a per customer or per product type calculation that you need to do.
I've used the same structure to model the initial balance profile. The input assumptions are different because we have a longer and profiled payment lag.
The more significant difference is in the profiling of forecast cash receipts.
The underlying function used is the same as our previous example, using XLOOKUP to implement the delay.
We are now using a bigger "2D" block to cascade the calculation and apply the different %s.
In each row, from rows 72 to 77, we apply a different payment lag, from one to six months, using the same XLOOKUP structure as before. We then multiply this amount by the % of invoices paid with that payment lag.
You could adapt this calculation so that the inputs gave the % of revenue and payment lag for a series of customers or product types. I've applied it to all revenue.
In row 78, we add up each of the profile receipts lines. Here, the row totals are crucial information, assuring us that the total receipts add up to the forecast revenue.
From there, we add the forecast receipts to the profile of initial balance receipts to give us the total cash received from invoices. We flow this into the cash flow and corkscrew as before.
I'd love to hear how you have applied this or different solutions you have come up with to the same problem. Leave your comments below.
Sign in or become a Financial Modelling Handbook member to join the conversation.
Just enter your email below to get a log in link.