Entering Orders in Retail Business Manager
[vc_row][vc_column][vc_column_text]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.
VIDEO DEMO
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.
Additional Fields
- 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.[/vc_column_text][/vc_column][/vc_row]
21 Comments
Hi Just purchased template however, in the order type i can only choose Sales, Purchase or Adjust it does not come up with the Quote or Estimate Options?
Thanks for using our template.
The template can handle 3 types of orders. We might add these types in future releases.
Best wishes
Hello Indzara. I have just bought this template and trying to set it up to use for my small service related business. I do carry inventor and have that set up OK I think. But I also have labour to charge out. I tried just making Labour a “Product”, but the program expects inventory on hand to be able to charge for labour.
Can I use this template for charging labour? If not, do you have another template for a service oriented business?
Thanks for purchasing.
Please try setting ‘Trackable’ to NO for the Labour in the Products sheet.
Please clarify what is the issue when you added Labour product to an order.
For the price to auto-populate, the order should be in order header sheet, the order date should be entered, the product should be in Prices sheet with an effective date <= Order Date. Please email file (support@indzara.om) if there are further questions. Thanks & Best wishes.
Is there an option to add the tax based on category of the product?
The GST taxes vary from product to product right?
Hello
Thank you for your interest in our template.
Different tax rates on the same invoice are not supported in this template.
Best wishes
Okay, is it possible to add two tax components?
like sgst (state tax) & cgst (central tax)
Also can we name/label the tax component on the invoice?
Hello
The same tax rate would apply to all products in one invoice. Suppose an invoice has items which have different GST rates, then it would not calculate the final amount correctly.
Best wishes
I have been using this template with great success but I need to split some of the inventory out for a second online business. How do I adjust the inventory out to still show the correct amount of inventory and not double. I have opened a new template file for the second business and populated it with the items that are sold online in that business.
Thanks for the help.
David Franz
Thanks for using the template. Glad to hear that it has been useful. Please post a review on the product page. https://indzara.com/product/retail-business-manager-excel-template/
You can add the ADJUST order type (with negative quantity) to reduce the inventory in the file for original business. Then, add an ADJUST order type (with positive quantity) to increase the inventory in the file for new business.
Best wishes
Thanks for your help. i was using an old version of excel. Upgraded and the spreadsheet works! Many thanks.
Hi,
I am having the same issue as haries sukita
where I am filling out the order details worksheet, the unit price doesn’t come up / retrieved from the price worksheet. Is this related to what date field cell format should be? please help me too thanks
Thanks for purchasing. Can you please confirm which version of Excel you are using? If you are using Excel 2010 or newer, it should work.
Best wishes.
Hi,
I am having the same issue as haries sukita December 25, 2016 at 7:57 pm – Reply
where I am filling out the order details worksheet, the unit price doesn’t come up / retrieved from the price worksheet. Is this related to what date field cell format should be? please help me too thanks
Thanks for purchasing. Can you please confirm which version of Excel you are using? If you are using Excel 2010 or newer, it should work.
Best wishes.
Hi, In my ” Order Header” find some Error, it shows as below.
Please troubleshoot this Issue.
When i enter the due amount exact same as amount over due it shows me like -2.2203e3( Error)
But if enter less than the amount over due it works, also it works when i enter above the amount it shows ” Over payment”..
Bit confuse, When I enter exact amount it should be 0 in Amount over due rite…?
But first two row working properly and from 5th onwards also ok but in between 2 rows has issue..
Kindly advice me .
Thanks for using the template.
I have emailed you. Can you please reply with the file with specific data that is of issue? I will review and get back to you.
Best wishes.
Hi, In my ” Order Header” find some Error, it shows as below.
DO NOT EDIT GREEN COLUMN CELLS. THEY ARE AUTOMATICALLY CALCULATED.
Amount that has been paid so far Use this field to store any information you would need about Orders Total Order Amount considering all charges, discounts and taxes Order Amount – Paid Amount
PAID AMOUNT O CUST FLD TOTAL ORDER AMOUNT DUE AMOUNT
559.1 559.1 0
68.09 221.28 153.19
561.74 561.74 -2.27374E-13
0 212.18 212.18
88 88 0
89 89 0
66 66 0
27 27 0
67 67 0
Thanks for using the template.
I have emailed you. Can you please reply with the file with specific data that is of issue? I will review and get back to you.
Best wishes.
Hi, I just bought this spreadsheet. As i am filling out the order details worksheet, the unit price doesn’t come up / retrieved from the price worksheet. Please help thanks!
Thanks for sending the file. I can see the price being auto-populated.
Can you please send me screenshots of how it looks in your computer? Also, please check if the dates (Effective date in the Prices sheet, and Order Date in Order Headers sheet) are entered in the correct date format. Date formats vary by language/country.
Please let me know. Thanks.