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

7 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 !!!

Leave a Reply