Purchase-order tracking system based on Excel 2007: current process and requirements

Current process

In a previous post, I said that we would be developing a new purchase-order tracking system in Excel 2007. I started by interviewing the customers. I sat down with Inbal, our “accounting department,” and this is what she said …

“Today, we have a licenses management solution [Nick named Hendrix]. Every purchase is entered into Hendrix, and the customer then receives an automated e-mail from Hendrix with his payment code and download and activation instructions.”

“When a purchase order arrives, it is entered into the system, so that a license will be issued to the customer. I also manage an Excel sheet with a list of POs to track.”

Here is a sample of the Excel sheet that Inbal uses:

Purchase Order Tracking Sheet

“Purchase orders are not paid immediately; they usually have net or net+30 payment terms. it’s my job to see that all the payment orders are eventually paid,” Inbal added.

“So, every week, I single out the POs that have not been paid, and according to how late the payment is, I either send an e-mail or mail, or I call the customer. When a purchase order is paid, I update the tracking sheet to reflect that, this way I know not to bother the customer.”

Users’ requirements

I then asked her what she would like to the tracking system to do.

“I need to be able to define contact details for both customer and end user,” Inbal said. “This is because sometimes (especially with POs), the customer’s accounting department is ordering the software for their users. So I need to send the download and activation instructions to the user and work with the accounting department on payment issues. In any case, the customer has to be notified about the delivery of the software as well as the user.

“I want to be able to define the due date for each PO (net, net+30, etc.).”

“I want to get a weekly report of all the purchase orders that should have already been paid. If possible, I want to get it by e-mail.”

“When closing an order, I need a place to enter the payment date and some sort of transaction ID (check number, bank transfer ID, etc.).”

And that was it.

If you have any ideas on how to make this system better, please, pretty please … add them as a comment to this post.

Posted on October 18, 2006 by Yoav Ezer 
Filed Under Cogniview, Excel, Methodology

Comments

36 Responses to “Purchase-order tracking system based on Excel 2007: current process and requirements”

  1. mike on July 19th, 2007 3:41 am

    great site

  2. Rajesh on July 16th, 2009 10:26 am

    we should be able to track Monthly P.Os for the same end user. reports should be available customer-wise as well as end-user wise.

  3. stacey Washington on December 31st, 2009 6:22 am

    place an order for StaceyM. Washington 4511 Annunciation St. New Orleans, La. in November of 2009 and the above name person has not received the gift yet.

  4. Ajay Kumar on April 16th, 2010 4:47 am

    hey i have been looking for a order tracking system that is easy and i find this excel one great. could i have a copy also please.

    thanks

  5. Gina on May 14th, 2010 9:41 pm

    Can I get a copy of your current PO XLS tracking system? I think this would be very helpful to us!!!

  6. Jed Eilers on June 10th, 2010 7:19 pm

    I would like some infomation or the sheet
    Thanks
    Jed Eilers

  7. Fazal Rehman on June 29th, 2010 9:46 am

    Hi,
    Please forwarded me Official Purchase Tracker !!!

  8. Greg on September 8th, 2010 6:30 am

    My manager is on me about a solution for our PO tracking problems and this spreadsheet is the quick fix that would make a big difference In my organization right now. PLEASE send me a copy. Thank you.

  9. Darla on October 13th, 2010 9:32 pm

    I also think this would be beneficial to me in tracking PO’s. Would love to have a copy!

    Thanks a Ton!

  10. Frank on November 3rd, 2010 3:16 pm

    Please send me a copy, looking for a way to track PO’s returned to a vendor too.

  11. Rob on December 2nd, 2010 7:34 pm

    Sounds like an elegant solution to a problem we are having as well. May I have a copy please? Thank you!

  12. Logan on December 9th, 2010 1:57 am

    My PO problem is very similar to what you have listed, however, it also comprises of several other unique aspects. I would love to see what you have come up with. From the looks of it, the worksheet has mainly utilized an aray of sort functions and links. I am curious to see if this is all it does.

  13. Jayson Leisenring on December 15th, 2010 4:41 am

    We need help with order tracking and would love to learn more. Please send information to myself and daniel@lauriesbuffalogourmet.com

    Thank you very much,
    Jayson

  14. Kael Smillie on January 10th, 2011 10:42 pm

    I seem to be in the same position as many of the above and I believe if I had a copy it would be a massive help.

    Many thanks

    Kael

  15. Ehsan on January 28th, 2011 11:58 am

    This would be a GREAT help in managing my small company PO. please send me a copy of this tracker. TYVM.

  16. CJ Baker on March 9th, 2011 10:00 pm

    Please send a copy to me as this will help us out a lot. Thanks, CJ

  17. Angela on March 19th, 2011 6:13 am

    That’s exactly what I am looking for to help track po’s

  18. Stefan on April 22nd, 2011 12:01 pm

    I’m very interested, please send me a copy

  19. SHINDE on June 3rd, 2011 9:37 am

    This type of PO Tracking is not giving complete details about the material, supplier, quality level, delivery method, Delay causes. I dont feel that this is good for EPC & construction projects.

    By

  20. Mark on June 15th, 2011 7:53 am

    very interested, please send me a copy

  21. Bill Matthews on June 16th, 2011 1:25 pm

    Excellent, may I have a copy please? Thank you.

  22. Fariz on August 22nd, 2011 6:43 am

    Great idea, I would love to have the copy. Thank you.

  23. Char on August 30th, 2011 11:28 pm

    I want a copy or learn how to set this worksheet up.

  24. ravikiran on September 6th, 2011 9:52 am

    u can mail to this guy for any excel format u need in po tracking

  25. ravikiran on September 6th, 2011 9:53 am

    ravikiranchvs1988@gmail.com

  26. Amy on September 27th, 2011 4:09 pm

    Can I please have a copy of this worksheet.

    Thanks

  27. Mitch on October 15th, 2011 8:52 pm

    Please forward me a copy of this worksheet. Great work!

  28. zeid on October 19th, 2011 7:33 pm

    please forward a copy of this sheet to me. I would greatly appreciate it,

    thank you!

  29. chidambaram on October 21st, 2011 9:51 am

    can i have a copy of the spread sheet.

  30. Chad Heaton on October 28th, 2011 3:12 am

    Could I receive a copy of the spreadsheet?

    Thanks!

  31. Jeremie granat on November 7th, 2011 5:26 pm

    Could I also receive a copy of the spreadsheet! lokks really good :)

    thanks
    jeremie

  32. Velmurugan on November 9th, 2011 8:53 am

    Could you pls send the spreadsheet, it will usefull for my job work.

  33. irwan on December 8th, 2011 6:02 am

    Could I also receive a copy of the spreadsheet! looks really good

  34. Kareem on December 28th, 2011 10:11 am

    Hi,
    I can’t download the tracking excel sheet file. Please how can I download this file to my laptop.
    Thanks.

    Kareem,
    Baghdad, Iraq

  35. Sagar on January 17th, 2012 8:03 am

    I was looking for this particular data from a long time. i will try a let you know my comments on the same. Thanks once again.

  36. Azhar Hussain on January 17th, 2012 5:40 pm

    Hi
    This is Azhar Hussain, and i am working in a restaurant i saw your sheet and read comments and i am impress on your file comments and i am intresting to work on it. So could you please send me a copy of this excel formulated filled sheet.

    regards

    Azhar Hussain

Leave a Reply