Inventory Control Excel Template Free Download
From This is a Microsoft Excel spreadsheet template that allows small business owners and inventory management professionals to track sales and inventory also be automatically alerted when reordering is necessary. Most Excel based inventory list spreadsheet are just for the inventory managing and tracking, the beauty of this spreadsheets is combine the sales and inventory data into one sheets, it will record and update your inventory level based on your inventory input and sales output data. It will tell you the latest inventory that you have, it will generate view automatic alerts when the inventory level lower than the re-order point. We calculate the Re-order point and get the optimal order quantities by using the build in EOQ model. It help the business owner easy to management their inventory items with the in to an optimal model, and cut down the business cost.
Benefits of Inventory Management Software – Retail Inventory Tracker. How many items you currently have in inventory of each product so that you can take orders from your customers accordingly? If you cannot fulfill orders from your customers on time, you will be losing credibility as a business. Which products are low in inventory (compared to a )? This helps in deciding when and what to buy in purchase orders to your suppliers. Which products are selling well and which products are not?
This will help you decide to buy profitable products and not buy those that are not. What is the profit/loss you make from your business? This is obvious. If you are not turning in profit, you need to improve the business strategy. Who are the best customers and best suppliers?
Inventory spreadsheets made in excel offers a better management and control of the inventory. The options like macros available in excel help in organizing things in a better way, and you can download blank templates and customize for your want. This is a Microsoft Excel spreadsheet template that allows small business owners and inventory management professionals to track sales and inventory also be automatically alerted when reordering.
Building great relationships with the suppliers who bring in most revenue will be helpful. Providing special service to your best customers will likely result in more sales in future.
In order to get to this information easily and quickly, we need some kind of software. There are several sophisticated and expensive cloud based software available to manage inventory and sales for retail businesses. For small and medium size businesses, especially when we are starting up, it is important that any software we choose is easy to use, customize and not expensive.
This is why I am excited to present a free Excel template as a solution. This template is a follow up to the most popular template on indzara.com –. This new template provides several improved features but I have decided to keep the older template online as well. For example, this new template has automatic price population on order line items which the old one doesn’t. There are some users who do not want to auto-populate the prices as they want flexibility to change prices for different customers. For those, the old template would be useful.
Hence, it would be better to have both templates available to our users. Features of Retail Inventory Tracker Excel Template.
Enter Products with Name, Description Starting inventory and Re-order point Let’s see each of the fields in the Products table. ID: Unique identification of product. This has to be unique. Please do not repeat the same ID or leave the field blank.
NAME: Name of the product. DESCRIPTION: Description of the product, as needed in our business. STARTING INVENTORY: This is the quantity of the product we have when we begin using the template. This is entered only once and does not have to be updated daily. RE-ORDER POINT: The quantity of product at which you would like to replenish by ordering. There are a few more columns of product information we can input. Enter Unit of measurement, Product category, Tax information for each product.
UNIT: This is how we measure this specific product. CATEGORY: Product category to which this product belongs. TAXABLE: In our business, if we have products that are not taxable, we can enter NO. If tax is applicable, just leave it blank.
By default, tax will be applicable. PR CUST FIELD: This field is provided as a placeholder for you to enter any information you need at Product level. You can rename the field and use it as needed. The other columns in this sheet are all calculated columns. We will discuss more about this later in this article.
The columns that have Green colored labels are all calculated columns. Please do not edit the formulas in them. Step 4: Enter Product Prices In Prices sheet, we will be entering Purchase and Sales prices.
This information will be used to auto-populate prices in our orders. This will save a lot of time in data entry of orders. Enter product purchase and sales prices in Prices sheet Purchase Price is the price we pay our suppliers to purchase products. Sales Price is the price we sell the products to our customers.
To begin with, let’s assume we start using this template from Nov 1, 2016 to enter orders. We enter each product in this Prices table and enter Nov 1, 2016 as the Effective Date. The Purchase and Sales prices we enter will be the prices effective as of Nov 1, 2016. What if price changes?
The template is designed to accommodate price changes for products. You may have an increase in prices of certain products over time. Not a problem. If price changed for a product from Jan 1, 2017, we will just add a new row, enter the Product ID, Effective date (as 01-Jan-2017) and the new Purchase and Sales prices.
Please note that we have to add new rows whenever prices change, and not to replace the older data. We have to enter both purchase and sales price in each row, even if only one of them changes. Step 5: Enter list of Partners In the Partners sheet, we store the list of our partners. Partners include Suppliers and Customers. Enter customer and supplier information in Partners sheet If a partner is both a customer and a supplier (it is possible in some scenarios), enter the partner only once. Partner ID and Partner Names should be unique. Enter Shipping and Billing address, E-mail address and Phone number.
Enter the primary person of contact for each company in the CONTACT field. This sheet now serves as a nice organized set of data about your partners. We have completed the initial set up now. It’s time to enter our first order. Creating Orders Before we enter our order, let’s learn about the types of orders. You can create 3 types of orders in this template. 3 types of orders Purchase, Sale and Adjust.
PURCHASE: When we purchase products from our suppliers, we enter a PURCHASE order. This order will add the purchased items to inventory on the Expected Date. SALE: When we sell products to our customers, we enter a SALE order.
This order will subtract sold items from the inventory on the Expected date. ADJUST: 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.
For example, we may lose products due to damage or expiry or other reasons. We would want to adjust our inventory accordingly and that’s where we can use ADJUST order type.
Creating a Purchase Order Orders are entered in this template in 2 stages – 1) Order Header and 2) Order Details. Let’s use an example. The products here are shirts for boys and girls. They are available in different colors. In the Order Headers sheet, we enter the following information. Entering a purchase order in 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. Order Date and Expected Date 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. For example, if you place a purchase order on Nov 5th. The supplier says the products will reach your inventory on Nov 25th. Here, Nov 5th is Order Date and Expected Date is Nov 25th. If there is a delay later and the supplier says it will only reach on Nov 27th, then we have to update the Expected Date of our order to Nov 27th. Order Type is ‘Purchase’ and we have chosen our supplier in the Partner Name field. There are additional information we can enter in the Order Header.
Additional information on Purchase Order – Order Header. OTHER CHARGES: Any additional cost on the order. For example, shipping charges. ORDER DISCOUNT: Any additional order level discount amount (not%).
We will be entering product level discounts later. TAX RATE: Tax Rate% applicable for this order. We can have different tax rates for different orders. ORDER NOTES: Enter any notes for your reference to this specific order. Now, we enter the items on the order in the Order Details sheet. Entering order line items in Order Details sheet – Product, Quantity and any discount It is very simple.
Enter Order Number, Product ID, Quantity and any Unit Discount. Here, we have entered a purchase order to purchase 15 units of Boys Shirt in Red color and 10 units of Girls Shirt in Red color. There is a discount of $2 (any currency you use) for each of the 10 Girls shirts and no discounts for the Boys shirts. The template will calculate amounts for each line item.
Let’s understand how the calculations work. Calculations of Amount and tax for each line item in the order Unit Price is automatically pulled over from the Prices sheet. Price chosen will be the one that was effective as of the Order Date of the order. BRD (Boys Shirt – Red color). Amount Before Tax = Quantity.
(Unit Price – Unit Discount) = 15.(20-0) = 300. Tax = 10% of 300 = 30. Amount After Tax = 300 + 30 = 330 GRD (Girls Shirt – Red color). Amount Before Tax = Quantity.
(Unit Price – Unit Discount) = 10.(25-2) = 230. Tax = 10% of 230 = 23. Amount After Tax = 230 + 23 = 253 This purchase order will automatically update the inventory by adding 15 units to BRD and 10 units to GRD. We can view the inventory levels in two places in this template.
One is the Report sheet. Another is the Products table. We will cover these later in the Reporting section below. Creating a Sales Order Entering a sales order is very similar to the purchase order, except that our Order Type is ‘Sale’ now. Entering Sale Order line items with Product and Quantity in Order Details sheet This order will now automatically reduce inventory for each of the products, effective as of Nov 26th (Expected date). Handling Supplier Return If we have a situation where we want to return products back to our supplier due to some reason (example: defective products), we can do so easily.
We will enter a new Purchase order. Tip: For easier identification of return orders, you can enter order number differently. For example, use a prefix of PR for purchase return orders. In our example, after receiving the products on Nov 25th, we notice that there are 5 defective BRD units.
We want to return them. So, on the next day (Nov 26th), we send the products back to the supplier. Customer Return Order – Entering quantity of products returned by customer 4 units of GRD are returned by the customer.
We enter -4 as Quantity. This will be used by the template to add 4 units to GRD inventory, effective as of Nov 30th. Creating an ADJUST order On some occasions, we may find that a product is either expired or damaged locally at the warehouse.
We cannot return it to the supplier, and we cannot sell that to customer too. We need to make sure that our current inventory calculations reflect the true available inventory to sell. This is where we can use the order type ‘Adjust’. In the Order Headers sheet, we first create a new Adjust order. Adjust Order – Entering quantity and discount We enter -1 as Quantity. This will reduce the inventory by 1.
If we want to increase inventory levels without entering a purchase order, we can use an ADJUST order where we enter positive values as Quantity. We enter 35 as Unit Discount (as that is the sales price of the product). This is to zero out the impact on cost. If we are not incurring any additional cost by disposing the shirt, then this method is recommended.
If we incur any additional cost, then we enter the appropriate Unit Discount so that the total Amount after Tax reflects the disposal cost. Business Performance Reporting The template has extensive automated and interactive reporting in the Report sheet. Current Status (Inventory level and Inventory value). Current Status of Inventory – Retail Business The following metrics are displayed to reflect the status as of today. Total inventory (quantity) on hand. Total inventory to Come (ordered from suppliers already and will reach our inventory in future). Total inventory to Go (ordered by customers already and will leave our inventory in future).
Number of Products to re-order (products whose current inventory is at or below its Re-Order Point). Inventory Value (calculated based on current purchase price of the products on hand) The above presents the overall summary of all products together. We would also want to see this information individually for each product. To do that, we go to the Products sheet. Summary Business Performance Metrics for Retail Busines. SALES. Sales Qty: Total of quantity on Sale orders.
Considers returns as well. Sales Amount: Total Order amount on the Sale orders. Includes product level discounts. Does not include tax, order level charges and order level discounts.
Sales Tax: Total tax amounts on Sale Orders. Qty Returned from Customer: Quantity returned by customers.
Discount Amt Given: Total amount of discount given to customers. Other Charges: Total of other charges on all Sale orders. PURCHASE.
Purchase Qty: Total of quantity on Purchase Orders. Considers returns as well. Purchase Amount: Total Order amount on the purchase orders.
Includes product level discounts. Does not include tax, order level charges and order level discounts. Tax: Total tax amounts on Purchase Orders. Qty Returned to Supplier: Quantity returned to suppliers.
Web Template Free Download
Other Charges: Total of other charges on all Purchase orders. PROFIT. Gross Profit: Sales Amount – Cost of Goods Sold. Cost of Goods Sold is the sum of purchase price of products sold. Purchase price is the price of product as of Sale order date. We can view these metrics by month, for 12 months at a time.
Free Template For Inventory Control
Indzara, Thank you so much for this amazing template. I am now try to using my question is if my starting inventory for 1 item ( 10 QTY for example ) and the cost was 10$ then i purchase the same item ( 10 QTY for example ) but i have an additional cost ( shipping ) so the cost must be changed to 12$ why the cost not change automatically also when i make sales for the same item ( 20 QTY for example ) the template tack the last cost so what about the first??? I mean i have two cost for the item waiting for your reply Thanks a lot. I am trying to use your template for my tshirt business. Basically I have 2 tshirt designs that I am selling and I purchase tshirts from my suppliers, with sizes ranging from Small to 4XL, 4 different colors, and 3 different types of tees (hoodies, tshirts, or crewneck). I purchase the shirts as needed, so I don’t really keep a stock unless I order more of a particular size and a particular color. So I guess my question is, would I put the types of designs under the “category” section on the settings tab, or is there a better way to put in this info?
I currently have the type of tee and size in the product category area, and then in the products tab under “product name” I have the color, type of tee, and the size. Choosing how to categorize varies by each business. I would say 3 types of tees can be included as 3 categories. Having Type and Size in the categories would be fine too. When you look at the report, do you feel that the categorization helps in getting good insights about the business?
Which products are doing well? Where to spend more money and where to stop spending? If that is productive, then categorization is working. If not, you can try to change the categorization.
Dear Indzara, First of all I must thank you for a very useful video, the templates and the description provided underneath. I have few queries however, I don’t want to use Purchase Order or Partners. I purchase myself products from the whole sale market and sell the same through my small outlet. The number of products is more than 500 with quantity ranging between 5 each to 2000 each.
This include small stationery items like, pencils, rubber, sharpeners, papers, copies and grocery items like rice, pulses, Flour, etc. I want to just know the items in hand, their value, reorder point, etc.
Can I use these templates with some modification to suit my situation. Thanks and best regards,. Hello Indzara, Thank you so much for making this template. I am now using it and would like to upgrade. The only thing we need is the bar code scanner function but just leave it for now. May I ask a question regarding the report part?
Is it possible that we have a salesperson column that can be made into a report like the partner report? Piano pdf sheet music. Because we need to record every salesperson’s orders and see their performance. I tried to copy and edit the graph using one of the blue columns but failed. Thanks a lot. Hello Indzara, what an amazing template this is.
By way of brief introduction, my name is Chernor from Sierra Leone in West Africa and I am using this template to handle my retail business and hopefully will soon upgrade to the even more amazing RETAIL BUSINESS MANAGER. I really admire your wisdom! My question is, if i choose to enter the shipping charges/cost on orders in the ‘OTHER CHARGES’ column, my basic accounting knowledge says that for Sales Orders it is carriage outwards and not included in the calculation of the gross profit (BUT INCLUDED IN THE NET PROFIT CALCULATION) and since this template ignores net profit, I’m fine with that.
But for purchase orders it is carriage inwards and should form part of the cost of goods sold, hence included in the gross profit calculation. I don’t know how this template handles the other charges in the overall profit calculation, or at least gross profit calculation. Thanks for your time. I would appreciate a response. Dear Indzara, This template is just exactly what I’m searching for. I have a question: We do not keep stocks, we only make the purchase to our suppliers upon receiving an order from our client, therefore I wouldn’t need the inventory function. So do I just leave the inventory columns blank, or can I delete them?
I’ve download both this template and “Inventory and Sales Manager (Free Excel Template) for Small Business”. I like the “Partner Performance” and the “Top and Bottom product performance” from this template, but I also like the “Report” from Inventory and Sales Manager (Free Excel Template) for Small Business, where I can filter by order year. Is there any way I can join these together? Hi Indzara, Just finished watching your video & downloaded your template. It seems like a good template for small retail start ups.I’m not sure how the template will handle when you have many individual customers. I’ll definitely will try it out.
Excel For Inventory Control
My question is if we want to use the Retail Business Manager later after using the Retail Inventory Tracker, will my datas from the Retail Inventory Tracker automatically updates at the Retail Business Manager or do I have to start fresh again? This template is absolutely amazing. We just started a small business and this will be a huge help for me. Thank you for providing it, and such good documentation on how to use it. Here is a request if it isn’t too hard. The supplier we order from is calculating tax on the item as well as shipping and handling. This is throwing the calculation off a small bit for the Total Order Amount in the Order Headers worksheet (as well as in some other areas I would imagine).
I tried to wrap my head around how the total order amount should be handled but so far, I haven’t been able to figure out how to change this calculation. Is this possible to do with the way this is setup? You are welcome. Thank you for the feedback. The report shows customers and suppliers based on what type of orders were placed. It looks at all sales orders and then determines the top 10 partners – and labels them as customers.
It looks at all purchase orders and then determines the top 10 partners – and labels them as suppliers. It is possible to keep suppliers and customers in separate sheets, but that would add one more sheet to maintain. Keeping them together makes certain calculations simpler. Thanks for the feedback. Please let me know if there are any questions.