Entering Orders in Retail Business Manager

Posted on

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

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.

Entering a purchase order
Entering a purchase order

 

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

Purchase Order - Additional Fields
Purchase Order – 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.

Purchase Order - Order Details
Purchase Order – Order Details

 

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’.

Sale Order
Sale Order

 

In the Order Details sheet, we enter the products on the order and quantity

Sale Order - Order Details
Sale Order – Order Details

 

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’.

Purchase Order - Return to Supplier
Purchase Order – Return to Supplier

 

Then, we will add a row in the Order Details sheet where we will enter -5 quantity of product BR.

Purchase Order - Return to Supplier - Order Details
Purchase Order – Return to Supplier – Order Details

 

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’.

Sale Order - Returned by Customer
Sale Order – Returned by Customer

 

In Order Details, we will enter the quantity as -5.

Sale Order - Returned by Customer - Order Details
Sale Order – Returned by Customer – Order Details

 

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’.

Adjust Order
Adjust Order

 

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.

Adjust Order - Order Details
Adjust Order – Order Details

 

This order will now reduce the inventory of GR by 5 units as of May 11th.

17 thoughts on “Entering Orders in Retail Business Manager

  1. Is there an option to add the tax based on category of the product?
    The GST taxes vary from product to product right?

    1. Hello

      Thank you for your interest in our template.
      Different tax rates on the same invoice are not supported in this template.
      Best wishes

      1. 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?

        1. 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

  2. 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

    1. 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

  3. Thanks for your help. i was using an old version of excel. Upgraded and the spreadsheet works! Many thanks.

  4. 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

    1. 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.

  5. 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

    1. 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.

  6. 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 .

    1. 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.

  7. 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

    1. 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.

  8. 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!

    1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *