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

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

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

Posted by on Wednesday, October 18th, 2006

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.