A lot of people believe there is nothing that cannot be done with Excel. Many small companies that cannot afford ERP use Excel to manage their purchase orders and PO receipts. They also use Excel to check accounts. The seemingly "zero-cost" purchasing management process in fact is often more time consuming and costly due to scattered data and difficult comparison. See if any of the following sounds familiar:

For example: A new purchase record is added to the Excel-based purchase order sheet after the owner gives instructions for a purchase order. However, since Excel itself does not provide an approval feature, the purchase order has to be sent to and confirmed by the owner by email before it can be printed and faxed to the vendor. The signed and returned paper copy of the purchase order will be placed in a folder, returning the process effectively back to one "recalled and handled manually". Purchase orders are sorted manually as needed and invoice deadlines tracked by the human brain. The broken information flow makes it difficult to track purchase orders systematically through receipt and incoming quality control.

As another example: Excel files are used to record receipts. One new file is created each month and one sheet for each vendor. Monthly statements from vendors are checked against information in the PO receipts. The process looks easy, but in fact ends up having data scattered all over the place. In other cases, purchase orders and receipt records, while containing related data, are not connected, and have to be checked by naked eye. One missing receipt or entry may lead to chaos. Identifying a data error may take more time than entering the data.

If you don’t have a Ragic account, click here to install the Purchasing template or click here to install the full templates of Sales Order Management, Purchasing, and Inventory Management. If you’re already a Ragic user, find the purchasing, sales, and inventory module under the Template Suites category in the Install Templates and download it.

Please see the details below for the usage process of the Purchasing Management template.

☞ Purchasing Management

The Purchasing Management template is designed to manage vendor information, purchasing price, and the entire purchasing process. The purchasing process includes a purchase requisition completed by staff (and approval), a purchase order used by the procurement staff, and subsequent receipt and incoming quality control.

The data should be added in the following order:

Products (SO) and Pricelist →(Accounts) → Sales Order → Delivery Note → (Stock Out)

Vendors→Products (PO)(Product Purchasing Price List)→Purchase Requisition→Purchase Order(Purchase items)→PO Receipt→Incoming Quality Control→(Incoming Stock)

Vendors

The vendor sheet keeps complete details on individual vendors. In subsequent procedures, the Vendor No. will be selected from this sheet when a Purchase Order is created.

Vendor No. is an automatically generated serial number that starts with V.Vendor Name and EIN / VAT Number do not allow duplicate values, and are the unique values in this sheet.

Products (PO)

Products (PO) in the Purchasing Management template contains two levels. One level is Products (PO) that focuses on products; and the other is Product Purchasing Price List that focuses on the purchasing prices of the products. (ex. A Products (PO) may be quoted by three vendors, which create one Products (PO) and three Product Purchasing Price Lists.)

The Products (PO) sheet and Inventory in the Inventory Management template and Products (PO) in the Sales Order Management template are multiple versions of the sheet, which means they share the same data source (i.e. one Products (SO) record is one record in Inventory and one record in Products (PO), except Products (PO) displays only Category for Products (PO)). The difference is the subtable Product Purchasing Price List is used to record purchasing prices of the same product for different vendors.

Product Purchasing No. in the subtable Product Purchasing Price List is automatically generated by formulas. It will be Product No.-Vendor No.. When a Vendor No. is selected, Vendor Name and Vendor Contact Person will be automatically imported. Please be sure to enter the Purchasing Price for the corresponding product and vendor. The Date by default is the date on which the record is created.

Product Purchasing Price List

This is used to record a product and the purchasing price offered by a vendor. Product Purchasing No. is Product No.-Vendor No. and a unique value in this sheet.

This sheet is generated from a subtable in the Products (PO) sheet, and so is linked to Product Purchasing Price List subtable in the Products (PO) sheet. One record in Product Purchasing Price List subtable in the Products (PO) sheet is one record in this sheet.

Apart from in the subtable Product Purchasing Price List in the Products (PO) sheet, new records can be created directly in this sheet. Select Product No. to have Product Name, Type, and Specification automatically loaded. Select Vendor No. to have Vendor Name and Contact Person automatically loaded. Product Purchasing No. is assigned with formulas and its value is generated as Product No.-Vendor No.. The Date by default is the date on which the record is created. Finally, please be sure to enter the Purchasing Price.

Purchase Requisition

Purchase Requisition is to be completed by the user to list purchasing requirements (including sales, raw materials, finished goods, semi-finished goods, and packaging materials) for a product.

This sheet consists of two parts:

The first part is to be completed by the user submitting a purchase requisition. Click on the "New+" button to have PR # automatically generated. The Date by default is the date on which the record is created. Created ByDepartment is determined by Created By and automatically loaded from the user information in the system. Priority by default is regular, and is an optional field.

The subtable Purchase Requisition Items below allows multiple products to be added. Select Product No. to have Product Name and Specification automatically loaded. Fill in Qty and, if necessary, provide the Usage Description and select a Suggested Vendor.

When a record is created, proceed to start the approval flow. The approval flow currently requires the approval of the direct supervisor (to be loaded from system user sheet) on the first level and that of a user-selected member of the procurement staff on the second level.

The second part is to be completed by the procurement staff to approve the purchase.

When the approval flow is completed, the Reviewing Officer (signing procurement staff) is required to fill in Reviewing Officer and Review Date in the top right corner and Reviewed Amount in the subtable at the bottom. (The reviewed amount in the subtable will be displayed only after the approval flow is completed.)

Purchase Order

The Purchase Order record can be created by converting from a Purchase Requisition, or by manually creating an order directly on the Purchase Order sheet.

If purchase order is converted from a Purchase Requisition, only From PR # and Required Date (two weeks before the Required Date on the original Purchase Requisition) as well as Product Name, Specification and Qty will be converted here.

Given a purchase requisition may contain multiple products that have to be sourced from different vendors and one purchase order can only be issued to one vendor, if data are added by converting, Vendor and Promised Date here and Product Purchasing No. in the Purchase Items subtable, like in a completely new record, will have to be filled in manually.

When information in a Purchase Order is confirmed, please be sure to initiate the approval flow.

Purchase items

It is used to record all purchase items in each purchase orders. This sheet is generated from the Purchase Items subtable in the Purchase Order sheet, and so is linked to subtable in the Products (PO) sheet. One record in Purchase items subtable in the Purchase Order sheet is one record in this sheet.

All fields in this sheet are read-only as manually creating or updating records should be done directly in the Purchase Order.

This sheet is vital to the subsequent receipt process. Do not delete.

PO Receipt

The PO Receipt record has to be created by converting from a Purchase Order, with relevant information entered manually, such as Invoice No. and Invoice Date as well as Received Qty in the receipt items subtable. Please be sure to check and confirm if Amount is correct.

When receipt details are confirmed, proceed to click on the Confirm Receipt button that the system will update Received Qty and Unreceived Qty in the Purchase Items in the corresponding Purchase Order, and fill in the PO Receipt Timestamp (date and time when the button is executed) on the PO Receipt.

Incoming Quality Control

The Incoming Quality Control record has to be created by converting from a PO Receipt.Click on the Create IQCIQC Created?" on the PO Receipt. One PO Receipt record can be converted to one Incoming Quality Control record. Records cannot be converted twice.

QC by default is the same as Created By. Status and Complete Date will have to be updated or entered manually. Please be sure to fill in for each items in IQC subtable (and confirm the value of Qty Accepted is correct) before filling in Note.

When IQC data are confirmed, please be sure to start the approval flow. Do not click on Confirm RejectCreate Incoming Stock button until the approval process is completed.

Click on Confirm Reject button and the system will update Qty Received and Qty Unreceived in the Purchase Items in the corresponding Purchase Order, and fill in the Return Rejected Items Time (date and time when the button is executed) automatically on the Incoming Quality Control sheet.