Purchase Orders and Invoices Tracking Template

Hey Abby! I have one that I created that may suit your needs! I'm going under the assumption that each invoice will be paid in full as it is issued and that we are looking to roll up multiple invoices per vendor. If you instead want to have an amount paid and a total per purchase order, you could add a column to note the amount paid.

It may be helpful to add an automation to automatically update the status to invoice issued when an attachment (the invoice) is added. I also am automatically recording the date at that time so it's easy to see how long an invoice has been open for. Both can be setup under the automation tab and use adding an attachment as a trigger.

If you want to see how much in dollars is pending per vendor you can use a report using this sheet as a source and group by vendor and then by status, which will give you the groupings shown below. You can then summarize the price column using the option to use the sum of the underlying values. This will allow you to see the total amount pending per vendor vs the amount paid. If you only want to see the pending amount, you could filter out all other statuses and forego the second grouping, which would clean up the report visually.

You could also do these totals formulaically on a separate sheet, but that would require you to maintain a list of vendors on that sheet and wouldn't allow you to link directly back to the relevant purchase order if needed. I hope this helped and let me know if I was off base on any of my assumptions and need some additional assistance!

Hey @Abby Davis, I see what you mean by monthly invoices now. Would it be possible to record the invoices as a "flat" sheet (no parent/child relationships) and add a column to track the PO# it is related to? Or do you want to make sure that the POs are on the same page as the payments? My thought is that if you let the monthly payments be separate from the POs themselves, that may allow for a slightly cleaner display of your data, but I think it depends on your process whether that would work well or not.

You could then use the sumifs formula to collect the data to the sheet with the overall payments tracked against it, and depending on what data you are trying to extract point the report at one sheet or the other. Just so I know, is it known up front how many months it will take for a given PO to be paid right off the bat, like a scheduled payment plan? If so we could likely make this hierarchical view work by adding a hierarchy column and some column formulas that allow us to vary depending on whether it is a parent or child task to extract the information that we need.

Also, I assumed that you are attaching an invoice to the sheet, is that the case, or does the line item itself serve as the invoice for you? I get the feeling that splitting up the different components of this solution might be the best way to get the data you are looking for, but am happy to help down whichever path seems better to you and see what we can work out. My initial impression though is it might be easiest to store the PO and invoice information separately, but would love to hear more about how and when invoices are added to your current process to see if I can help you out.