In this article, we will see the different types of orders and how we can enter them in the Retail Business Manager Excel template.
Retail Business Manager Excel Template – Product Page Support Page
Types of Orders
There are 5 types of orders that can be handled by this template. Before we look at the 5 types, let’s understand that each order will have 2 dates. Order Date and Expected Date.
Order Date is the date when the order is placed. Expected Date is the date when the inventory is impacted.
5 types of orders
- PURCHASE When we purchase products from our suppliers, we do so by entering a PURCHASE order. This order will impact inventory calculations by adding the purchased inventory on the Expected Date.
- SALE When we sell products to our customers, we do so by entering a SALE order. This order will impact inventory calculations by removing the sale inventory on the Expected date.
- QUOTE When we want to provide a sales quote to our customers, we use the QUOTE order type. This order will not impact inventory calculations.
- ESTIMATE When we want to create an estimate of purchase order to our suppliers, we use the ESTIMATE order type. This order will not impact inventory calculations.
- ADJUST Sometimes we may lose products due to damage or expiry or other reasons. We would want to adjust our inventory and then we can use ADJUST order type. We can create an ADJUST order and enter negative quantity values to reduce inventory or positive values to increase inventory as needed. This can be used to adjust our inventory numbers to ensure that the numbers match the inventory on hand.
Now, We will see how we can create them.
Creating a Purchase Order
Let’s start with entering a simple purchase order where we are purchasing 2 products from our supplier ‘ABC Company’. We enter order in two steps. First, we will enter in the Order Headers sheet.
The Order Number should be unique. In other words, each order should be entered in one and only one row. The field should not be blank. We can enter any method of numbering orders. The template does not limit that and does not create any pre-defined order numbers. Here, we have entered ‘P1’ as order number, to reflect that it is the first purchase order we are entering.
The order is placed on May 1st and the products on the order will reach our inventory on 5th May.
- PAYMENT DUE DATE Date when the payment for the order is due.
- OTHER CHARGES Any additional cost on the order. For example, shipping charges.
- ORDER DISCOUNT Any additional order level discount amount. We will be entering product level discounts later.
- ORDER NOTES Enter any notes we would want to appear on the purchase order.
- INVOICE # This number will appear on the invoice for sale orders and is not needed for Purchase orders.
- TAX RATE Tax Rate % applicable for this order.
- PAID AMOUNT Any amount that has already been paid to the supplier for this order.
Now, let’s enter the products or items that are part of the order. We go to the Order Details sheet.
We have entered 2 rows of data. There are only 4 input fields are Order Number, Product ID, Quantity and Unit Discount (optional).
Here, we are ordering 15 units of BR (Boys-Red shirts) and 25 units of GR (Girls-Red shirts). The unit price is auto-populated based on what we had entered in Prices sheet. Let’s see how the calculations work.
For BR, we don’t have any discount. So, Amount before Tax = 15 X 5 = 75. Tax is 10% and hence 7.5. So, Total Amount After tax = 82.5
For GR, we have a discount of 1 per unit. So, Amount before Tax = 25 X (5-1) = 100. Tax is 10% and hence 10. So, Total Amount After tax = 110
This order will now increase the inventory of these two products as of May 5th (expected date).
If you are just creating an estimate and do not want to update inventory calculations, then choose Order Type to be ‘Estimate’.
Creating a Sale Order
Entering a sale order is exactly similar to the purchase order except the Order Type is chosen to be ‘SALE’. In the Order Headers sheet, we have entered a Sale order now where we are selling products to our customer ‘XYZ Company’.
In the Order Details sheet, we enter the products on the order and quantity
This order will now decrease the inventory of these two products as of May 6th (expected date).
If you are just creating a quote and do not want to update inventory calculations, then choose Order Type to be ‘Quote’.
Handling Supplier Return
Let’s say that we received the purchase order and found that 5 units of the product BR were damaged. So, we want to return them.
We will enter a new order PR1 as shown below. Order Type is still ‘Purchase’.
Then, we will add a row in the Order Details sheet where we will enter -5 quantity of product BR.
This will reduce the inventory of the product BR by 5 units.
Handling Customer Return
Let’s say that customer XYZ company returned 5 units of product GR to us. We will enter a new order SR1 as shown below. Order Type is ‘Sale’.
In Order Details, we will enter the quantity as -5.
This order will now increase the inventory of the product GR by 5 units as of May 10th (expected date).
Creating an ADJUST order
After we review the returned products, we realize that the product cannot be sold anymore and should be considered as waste. In order for our current inventory calculations to reflect that, we can enter a new order of type ‘Adjust’.
In the Order Details, we enter the quantity as -5 (negative value as we want to reduce the inventory). To ensure that the order doesn’t impact the finances, we enter Unit Discount the same as Unit Price. This will make the Amounts zero.
This order will now reduce the inventory of GR by 5 units as of May 11th.