Almost all business transactions that involve the purchase/procurement of goods or services begin with a Purchase Order on the part of the purchaser, sent to the supplier. The Purchase Order can be a simple phone call between the purchaser and the supplier. In most cases the PO is an elaborate document that acts as an official record of the intent to buy a product or service sent to the supplier by the purchaser.
Because most businesses use some form of Enterprise Resource Planning (ERP) software, a Purchase Order is usually integrated into the system. It forms part of the standard procurement process document flow of Quotation → Purchase Order(PO) → Goods Receipt Note(GRN) → A/P Invoice → A/P Payment.
However, for some, a Purchase Order is a handwritten document or as mentioned earlier, a simple phone call to place an order, okay, but could be better. That is why in this step-by-step guide, you will learn how to create your own Purchase Order Excel Workbook that has database features to store your orders and automated data entry using Excel VBA/Excel Macros. You will learn how to create Excel Tables, create Named Ranges, and use the powerful XLOOKUP function to automate data entry in the PO form.
The skills you will learn here will help you kickstart your business and make a good ‘professional’ impression if you are just starting out. Or perhaps you already have some business mileage but don’t have the resources to invest in a fully integrated ERP software. Or better yet, you are fed up with the manual document processing at your organization and you want to automate the process by making it more efficient and simple (because you are creative) and stand out in your department. Whether you are a beginner at Excel or you have some significant Excel Hours on your belt (well done you), this will give you some useful insights that you can also apply to other areas of your business and daily life.
First, let’s have a quick overview of the template design.
Procurement Template Overview
A Purchase Order Form
This worksheet will have the Purchase Order Form which you can fill out your supplier details and items you want to procure. It will be designed to enable printing and exporting to PDF. Notice it has a ‘SAVE’ button that we will assign a Macro that automatically saves the form data to a database table. This ensures you keep all your PO data for future reference or analytics.
Database Tables
As standard, you need at least three tables to store the most important information. An items database that has all the items or services your organization procures. A suppliers database that has all the information about your suppliers and a PO database table to save your POs after they are raised. The information you will add in the Items and Suppliers tables will be used to automate data entry in the Purchase Order Form.
Picture 4 Purchase Orders Database Table
Here is the video tutorial describing the template overview.
Looking for project-driven supply chain management software?
Current SCM is the first of its kind – supply chain management software purpose-built to support the most complex procurement and materials management projects. With materials management and vendor document requirements uniquely integrated into the order, Current SCM provides a unified, collaborative platform to streamline the end-to-end process of project-driven procurement and materials management.
If you are engaged in any direct procurement, technical procurement, project procurement or third-party procurement, Current SCM will improve your procurement and materials management workflow. If you are engaged in all four, Current SCM will revolutionize the way you do business.
Contact our sales professionals at Current SCM today!