Payables Cash Requieents

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
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.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Watch MrExcel Video

Forum statistics

Threads
1,102,470
Messages
5,487,072
Members
407,575
Latest member
Stephen66

This Week's Hot Topics

Top