Purchase Orders and Invoices Tracking Template

Abby Davis

Hi - Am wondering if there has been a template designed to maintain hundreds of purchase orders along with the associated invoices that get billed every month. A view that would allow me to see which purchase order has been fully invoiced, or which vendors we need to contact to complete the process. I need to know how much we expect to pay (purchase order) and how much has actually been paid (invoices paid).

Any great templates out there??

Jake Kenyon

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!

Abby Davis

Hi @Jake Kenyon - This is an awesome view! I appreciate your help so far. I think mine might be a bit more complicated...I have hundreds of individual Purchase Orders - some will only have 1 invoice, some with have multiple invoices. I have to track the amount left on each PO to determine if all invoices have been received and if i can close out the PO once it is no longer needed. Ive been playing with a template all day and Im using Parent/Children rows but its very "cluttered" in my view since most of them only have 1 total invoice.

I would like to utilize reports after I figure out a feasible template. What am I missing that would help make the spreadsheet more clear and also, I need to filter by requestor and vendor etc.

In the past I had 1 row per PO# and a column for every invoice that went against that PO - but it became a bit much when I had 10-20 invoices for some PO#s.

Any suggestions or thoughts?

Jake Kenyon

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.

Abby Davis

@Jake Kenyon Hey - I'm still thinking my sheet is a bit more detailed. I did take some time to "format" what I have using different colors etc to see if that would help make a difference. Still not 100% excited about this method, but I'm not sure there's a better way with the amount that I need to keep track of.

I have 3 separate sheets that then filter back (link) to a full view sheet so this is one of the sheets where I have 150 PO#s and I've essentially added open invoice lines to each PO# to record once I am given the invoice. We do not know how much each invoice will be - essentially each PO is set up with a limit amount of $ and throughout the timeframe, the vendor can invoice as many invoices as they want to use of that allotted money.

Again - IF there are ideas - I'm always open to hearing them - I want to make this the most efficient method possible as I'll have close to 700 purchase IDs by the end of the year to manage.

aott

https://community.smartsheet.com/discussion/comment/421431#Comment_421431

Hi,

I'm new to Smartsheet and looking for multiple staff to create purchaser orders, boss to approve, they the vendor invoices, staff to add the invoice to the relevant PO, and the boss approves payment

I think the sheet you created would work well. I just don't know where to start creating this.

Thanks!

Abby Davis

@aott I can try to help and send you a blank template of what i currently have!

aott

https://community.smartsheet.com/discussion/comment/436910#Comment_436910

yes please thanks :)

dmaltz

@Abby Davis - If you're willing to share your PO-generating template, I'd love to use it as well! Thanks.

JenO

@Abby Davis - If you're willing to share your PO-generating template, Can I ask for this template too?

SP07

@Abby Davis - if sharing this template, I would also love a copy! It would be perfect for my needs as well.

Sandi Fry

@Abby Davis - If you have a template, I would also appreciate a copy of it. Thank you!!

Sandra Baker

@Abby Davis - I also would love to see the template as I'm trying to duplicate something very similar for a team member.

GeorgeS

@Abby Davis  Can you please share your PO-generating template, I'd love to use it as well! Thanks.

JM2863

@Abby Davis Would love it as well if you have a template to share.

NeilKY

https://community.smartsheet.com/discussion/comment/421851#Comment_421851

@Abby Davis I tried to figure out how to DM you but couldn't :) could you please send me a link to your template as well? this is super neato!