Sunday, October 14, 2018

Google Sheets Powered PO Generator

Writing purchase orders is one of the least favorite parts of my job. It’s often a repetitive and monotonous task that requires a deep dive into a file cabinet, a massive catalog, or both. I created this spreadsheet tool to make POs a little less onerous. This system keeps track of your orders, allows you to take notes, monitors your budget in different budget codes, and will actually generate the printed PO sheet! Make a copy if you would like to use the sheet. I hope you find this as useful as I do.

Setting up the sheet is easy. Just add your vendor information to the Vendor Tab. As a general rule, anything in the sheet with a yellow background can be edited. Anything with a grey or black should not be edited. These are controlled with functions. Adding vendor addresses is a one-time outlay of work. The address will auto-fill on the POs. I include my own information here, too, because there are certainly times when I purchase something from a vendor not on the bid list (Amazon, anyone?) and need to submit a PO with me as the vendor. You can add vendors as you need them, secure in the knowledge that the address will be ready the next time a PO is generated for that seller.

Also on the Vendor sheet is a budget code table. I have two budget codes as a technology teacher, one for supplies and another for equipment. In order to keep track of your codes, add the total amount you have to spend and the codes themselves in the appropriate columns (yellow only.) You’ll notice that the sheet automatically keeps track of the amount left in each budget code and the supplies code has a function that helps me keep track of per student expenditure.

The “Master Sheet” is where I fill out my orders (yellow cells only.) It's fairly self explanatory. One this thing I need to point out: the first two columns seem redundant but are not! The first column allows me to specify an order on a specific day, the second column is only the vendor name, linked to your vendor sheet. This second column controls the address information and is independent of the date. Both are necessary!

Once your order is recorded on the “Master Sheet” you can generate your PO. Now you have one place for all your PO records and you don’t have to worry about handwriting a PO form. Better yet, next year I have a clear record and can reorder easily. Open the PO Generator tab. You will notice four yellow cells, three with drop down boxes. Select your Vendor, which will populate all of the address fields. Then, select the date the PO was written, which is fed from the first column of the “Master Sheet.” Finally, select the budget code, write in the potential shipping cost, and add your name in the “Requested by:” field.

Your PO Is ready to print! This has made PO writing so much easier for me. I keep notes on my Master Sheet, such as when an order arrives, or if I was unhappy with the product. My record keeping is better and the process is a lot faster. This was designed using the Scarsdale PO form, so you may need to redesign the PO Generator layout. Let me know if you use this and if you’ve made it better!

1 comment: