/
Peoplecare Payment Timings Report

Peoplecare Payment Timings Report

Owned by Charles Fox
Last updated: Aug 16, 2021
3 min read

Government mandated Payment Timing report required to be provided to government each 6 month by companies whose turnover exceeds $100M and provides information on payment times to Small Business

The quote for this function (16th August,2021) in google docs https://docs.google.com/document/d/1n3g_SqYvyO8VzoAn9N4o-Av5wLMSnDgXsnmgZ_dg6lQ/edit?usp=sharing

My email that accompanied the quote/ proposal to Lee.Copley@peoplecare.com.au

Hi Lee

I have created the following document which explains the work to be done to produce the Payment Timing Report.

I have done not as a Google Docs document as it will likely be edited and this keeps one version of the truth.

https://docs.google.com/document/d/1n3g_SqYvyO8VzoAn9N4o-Av5wLMSnDgXsnmgZ_dg6lQ/edit?usp=sharing

Please have a look at this and provide feed back. 

  •  I have estimated 2 days to

    •  investigate, 

    • runup a dev VM,

    • import your DB, 

    • create and test the SQL,  

    • provide sample output CSV, 

    • 2 online meetings. 

  • Then 1 day to run the report and that will re-occur every 6 months 

We use the Jira software management system and the above links and your reference documents are stored there.

Cheers Charles

My notes about it 01 August, 2021

small business payment - lysaght people care first thoughts

  1. Screen for small businesses (1-2d)

    1. New Mapping for Suppliers which include the ABN

    2. Above will be used with CVS to ID small business.

    3. Do we read the returned CSV that has ABN that are Small Businesses

  2. Report Payment Timings. Cols V to AG in Example

    1. Prepare a variation of the aged creditor report with aged buckets as below

    2. The above can be used to provide the aggregate figure  required in the Payment Timings Report

The proportion, determined by total number and total value, of small business invoices paid by the entity during the reporting period in each of the following:

  • within 20 days after the issue day

  • 21-30 days after the issue day

  • 31-60 days after the issue day

  • 61-90 days after the issue day

  • 91-120 days after the issue day

  • more than 120 days after the issue day

9th August, 2021 - Meeting with Lee Copley.

Lee already used the Small business ABN system to get a list of Suppliers. That list is 135.

We will call that the SMB Spreadsheet

Given the SMB list exists  and it only took a few hours to create then the suggestion is to write a SQL against the century software database that takes a start_date and and end_date and reads all payments paid for in the specified date range. 

The resultant output will be -

supplier_ID, Supplier_name, Invoice number, invoice data, invoice amount (inc gst) and the payment date.

The time to pay is the difference between the payment date and the invoice date.

This csv can  be imported into the spreadsheet that contains the SMB supplier and a VLOOKUP written to match these and then calc made to create the payment buckets.

The reference documnets

 

Related content