Cash requirement for an Accounts Payables environment.
Link provided for sample data set with desired date results highlighted. https://1drv.ms/x/s!Auu67iC5u960_VhNg5EF3ywaGrCe
To provide an accurate detailed daily cash required forecast for payables, invoices and credits must be calculated to see when a check would actually be cut. Credits cannot be taken before their "due date" and if credits exceeds invoices those due dates will be postponed to a later date when the invoice exceed credits.
Additionally, each set of credits/invoices must respect the supplier relationship.
A PivotTable is where the data currently ends up and works fine, its just the actual pay date calculation that eludes us.
I was thinking an array formula as a possibility, though the calculation time may be excessive. 100's of suppliers on 30k transactions is real data.
Target environment is Excel 2013.
Macro, PowerQuery/Power Pivot are acceptable solution paths.
Link provided for sample data set with desired date results highlighted. https://1drv.ms/x/s!Auu67iC5u960_VhNg5EF3ywaGrCe
To provide an accurate detailed daily cash required forecast for payables, invoices and credits must be calculated to see when a check would actually be cut. Credits cannot be taken before their "due date" and if credits exceeds invoices those due dates will be postponed to a later date when the invoice exceed credits.
Additionally, each set of credits/invoices must respect the supplier relationship.
A PivotTable is where the data currently ends up and works fine, its just the actual pay date calculation that eludes us.
I was thinking an array formula as a possibility, though the calculation time may be excessive. 100's of suppliers on 30k transactions is real data.
Target environment is Excel 2013.
Macro, PowerQuery/Power Pivot are acceptable solution paths.
