Learn Ragic
Learn Ragic
Manually logging purchases, filling out shipment forms, and updating stock levels can become tedious and repetitive. This template solves those problems by automating stock calculations during both purchase and sales orders.
To download this template, register for a Ragic account here if you don’t have one. If you already have an account, visit our Template Library, navigate to "Template Suites", and find the "PSI Lite" template to download.
This document will explain the template's structure and how to use it effectively.
Sales Items and Purchase Items sheets are read-only for the purpose of building sheet relationships. No data entry will be done in them.
Data entry in this template should follow this sequence:
Clients and Suppliers → Inventory → Sales Order and Purchase Order
Clients and suppliers are managed within a single sheet in this template. Entries in this sheet are categorized by Status and whether you buy from or sell to each company. When entering Inventory, Sales Order, or Purchase Order, you will only see the relevant clients or suppliers, which are automatically filtered based on the records in this sheet.
Serial Number will be a unique value for this sheet.
The I sell product and service and I buy product and service fields are to distinguish whether this entry belongs to a client or a supplier, so only list of suppliers will appear when you create a Purchase Order, and only list of clients will appear when you create a Sales Order. The same entry could be both a client and a supplier.
When you choose “Yes” in I sell product and service, the relevant fields related to shipping information will appear under.
When you choose “Yes” in I buy product and service, the relevant fields related to billing information will appear under.
This "Inventory" sheet will handle your product name, vendor you purchased this product from, inventory in, and inventory out. Subtables below will record your purchase and sales history, and automatically deduct and increase your inventory according to the purchase or sales status.
In this sheet, the Inventory No. is a unique value automatically generated. The two options under Type — I buy this product/service and I sell this product/service — represent purchasing from suppliers and selling to customers, respectively. If either of these fields is set to "Yes", additional fields for recording the Buying Price or Selling Price will appear accordingly.
When creating an Inventory entry, make sure the Initial Qty is accurately filled in. This value will serve as the baseline for future stock calculation. As you log purchases and sales, the system will add or subtract from this starting amount as your Total Qty.
Note that the Purchasing, Sales, and Inventory Lite module does not include features for warehouse management or product specification tracking. If the same item is stored in different warehouses or comes in various sizes or specifications that require separate stock tracking, you’ll need to create individual records for each.
For example, if you have the same White T-shirt product that needs to be stored in 2 separated locations, Warehouse North and Warehouse South, you will have two entries for the same white T-shirt.
And if your White T-shirt has 2 size measurements, one is M and one is S, you will also need 2 entries for each size.
The Sales Order # in each sales order is a unique value, generated automatically and cannot be duplicated.
Customer-related information is linked to the "Clients and Suppliers" sheet. By choosing a Client ID, related details like the Client Name, Contact Name, Phone Number, will be filled in automatically. Note that the system will only load entries from the "Clients and Suppliers" sheet where the I sell product and service is filled in as Yes.
The subtable below records the sales items for the order. If there are many items, the subtable will automatically expand.
The Product No. field is linked to the "Inventory" sheet. Once selected, the system will automatically load the Selling Price from "Inventory" sheet to Unit Price in this "Sales Order" subtable field. After entering the quantity, the system will calculate the Subtotal and Grand Total. If necessary, you can also add the values in Discount % (Remember to add % for the calculation to work accurately), Tax, Shipping/Handling cost, according to your needs.
After saving the entry and returning to the "Inventory" Sheet, you will notice that this order has already appeared in the "Sales History" subtable. However, the inventory Total Qty will not change until this Sales Order Status is marked as Complete.
To change the Status field as completed and proceed to inventory calculation, you can manually change this field value or you can also use the action button to change the status to Complete in a single click every time a Sales Order is finalized.
After the Status change, you can now see that the Inventory is updated! (24-7 = 17)
The way Purchase Order works is pretty much the same as Sales Order. Each Purchase Order # is unique, automatically generated when you create a Purchase Order entry, and can’t be duplicated.
The Vendor No. is also linked to the "Clients and Suppliers" sheet. Just click and select a vendor, and it’ll automatically fill in the Vendor Name, Contact Person, Phone, and other related info. This link and load will filter data in the "Clients and Suppliers" sheet where I buy products and services is set to Yes.
The subtable below is for recording the purchase items. The Product ID is linked to the "Inventory" sheet, so when you select a product, the Buying Price will automatically populate in the Unit Price field. Just like in the Sales Order, once you enter the Quantity, the system will calculate the Subtotal and Grand Total. You can also adjust the Tax and Shipping Cost as needed.
After saving the data, you can return to the "Inventory" sheet and see that this purchase has also been updated in the Purchase History. However, just like with sales, the inventory quantity won’t change until the purchase is complete (right now, it’s still 17 from the last completed Sales Order).
Once you change the Status of the Purchase Order, then the Total Qty will increase according to the quantity you purchased.
If you would like to understand more about the relationship between each sheet and the tools used to build this template, you can watch the video tutorial below.
