AP Schedule

About

This example is a solution to an issue that I encountered during my time doing accounts payable. The question that came up when revising the list of invoices to pay on our weekly run was of two parts. One what is a quick way to know what to pay, and two how do I remove as much of the manual effort from this as I can.

In this case, the largest issue for us was the tight turn around times for some of the key vendors. PThey provided short terms and given the payment method required great attention to transit times for payment methods.

A copy of the workbook can be downloaded here AP Schedule

Solving the Issue

It is with this that I decided to export our list of vendors from the accounting software.The export looked along the lines the below Vendor Table. The terms for vendors did not export with the software that I had used. I later had added custom column for terms and payment method.

Vendor Table

Payment Method Table

A manual table that is updated with payment methods and average time of transit for the respective method.

Invoice Table

The invoice table is an export of open invoices from the accounting software and is added to the spreadsheet. In my export I knowingly omitted the due date and chose to calculate it based on terms outlined on the Vendor Table. If there was special cases where and required immiediate payment it would typically be paid immediately and would not be part of the run. Special cases were individually tracked and cross referenced when it came time to payment.

Which Pay Run to Use?

The due date for invoices is calculated using the terms defined in the vendor table. To determine if an invoice should be paid I employed a simple formula and logic.

D = Due Date
T = Transit Time
N = Days to Next Pay Run
A = Acceptable Days Late
P = Date to Pay By
C = Pay Run Date

P = D - T - N + A

if C > P then pay

A smiple formula that ensures that payments are recieved on time and within a window of acceptablility to be considered late by the issuer, arrival time being largely influenced by what is considered regular transit time of the method of payment.

In my instance my employer found that 2 days late was acceptable and was used in practice.

Behind the Scenes with Powerquery

The below is a brief and limited view to the queries I am running in Power Query. The below code used to populate the Pay Schedule Table. The end result is either a “Yes” or “No” to indivate wether or not an invoice falls on the upcoming pay run.

let
    Source = Table.NestedJoin(invoice_table, {"vendor"}, vendor_table, {"name"}, "joinvendor"),
    expandTerms = Table.ExpandTableColumn(Source, "joinvendor", {"terms", "payment_method"}, {"terms", "payment_method"}),

    addTermsColumn = Table.AddColumn(
        expandTerms,
        "date_due",
        each Date.AddDays([date_invoice], [terms]),
        Date.Type
    ),

    addTransitTime = Table.NestedJoin( addTermsColumn, {"payment_method"}, payment_method,{"name"}, "paymentmethod"),
    expandTransitTime= Table.ExpandTableColumn(addTransitTime, "paymentmethod", {"days_transit"}, {"days_transit"}),
    
    addPayByColumn = Table.AddColumn(
        expandTransitTime,
        "date_due_modified",
        each Date.AddDays([date_due], -[days_transit] -DaysBetweenPay + DaysLateAcceptable),
        Date.Type
    ),

    addToPayColumn = Table.AddColumn(
        addPayByColumn,
        "to_pay",
        each if DatePayRun > [date_due_modified] then "Yes" else "No",
        Text.Type
    ),
    changeDataTypes = Table.TransformColumnTypes(addToPayColumn,{{"date_due", type date}, {"date_due_modified", type date}})

in
    changeDataTypes

Results

In the workbook we have the pay run scheduled for 2024-09-13 and the following with 2024-09-21 with an acceptable days late of 2 days. The invoice to take note of is invoice 6 from Pets Unlimited. In the current set up the invoice is not paid on the current run.

However, if we advance the the acceptable days late from 2 days to 1 invoice 6 is now on the schedule for payment

A copy of the workbook can be downloaded here AP Schedule

One last note on special case invoiecs that require immediate payment. The format of the tool I had created can easily be modified in a manner that speical case invoices are immediately included on the next pay run. In my instance, it was not needed.