Free Inventory Management Software in Excel – Inventory Spreadsheet Template

welcome let’s talk about the inventory and sales manager excel template this template can be useful for you if you are running a business where you actually buy products from your suppliers and sell them to your customers so as illustrated here you place the orders to your suppliers to buy products and then based on the orders that you receive from your customers you sell them to them so in such scenarios there are two types of orders the purchase orders which you placed your suppliers and sale orders which the customers place to you so if you are running a business with such a setup then this template can be very helpful to you this template can help you understand the current inventory levels of each of your products it can identify which products need to be ordered or reordered and also it can instantly tell you if you receive any sale orders then do you have enough inventory to fulfill that sale order and it can also help you understand the different sales and purchase patterns over time and know which customers are your top customers and which suppliers are your top suppliers and also which specific products are performing really well for your business since you can group the products into different categories you can also assess how the different product categories are contributing to the success of your business they can also serve as a history of your orders these are some of the key benefits from the template this template is available for download for free from in Zurich blog spot.com and I’m going to go quickly to the blog and here’s my blog this blog post specifically is about this template and it talks about the features of the template and how to use the template there are two links available to the excel files that you can download the first one is a template without much sample data or dummy data in it but then the second link takes you to an excel file which has sample data in this video we’ll take a look at the sample data so that we can understand how the template functions and also some of the features of the template this is how the template looks when you open and this is the version with the sample data the instructions really simple instructions here the first one is to enter the products in this products table so in this sample I have actually used digital cameras as my products and these are the products I have about 26 or so listed here and this is where you would enter your product information you can enter product description for each product I am not actually using this product description in my template for any calculation or anything like that so this is up only for you to enter any details you want about each product the product category have always used and the product category is a way of categorizing your products so in this example I have used manufacturer the camera manufacturer as a way of categorizing my products since I’m going to have numerous products I would be it would be better for me to group them into categories in order for me to understand the results and the performance of the business more easily then we have reorder point when the inventory level of a specific product hits the reorder point that means that you have to place a new order to replenish your inventory and because you’re going to enter this reorder point for each product now the template will help you in calculating or determining whether the current inventory levels has reached the reorder point or not so you can take smarter decisions about which products to reorder so definitely make sure that you enter your reorder point information here now that’s all we do in this sheet now let’s go to the second step which is entering the order details in the orders and inventory worksheet so you can go there by clicking this link at the top you have lots of information where the template has calculated them based on the information that you have provided based on information that you enter here in this order details table at the top you have the current inventory level so how many products are currently in your inventory right now and as of today and the number of units available and also if there are any products that you have to reorder because they have hit the reorder point and these items are the order line items

that cannot be fulfilled current means as of today we have three line items here where there has been a request from your customer to purchase certain products as a sale order where you need to provide your customers with some specific product but you don’t have enough in your inventory to fulfill that order so there are three such line items where you don’t have enough inventory also since this template allows you to enter orders where you expect things to be delivered in the future so the template also says there are three line items that have future expected dates where the sale quantity is actually higher than the inventory available so it’s telling you that there are some issues you won’t be able to fulfill these order line items so that’s what these two numbers mean it’s very very helpful for you to take action to somehow address the issue with your inventory the this section here is about choosing a specific product among the list of products and this table gets updated with the current inventory so how number of units available right now in your inventory and then the reorder point that you have assigned to the specific product and then also since the template can look into or can accommodate future orders so it’s looking into pending purchase quantity so there are some you have already placed an order to purchase 20 items in the future which you expect to receive in the future and and also it looks at whether you have received any orders from your customers to sell the specific product with the future expected date and in this case for example for this product is nothing this template can give you these pieces of information instantly for all the products that you have in your product portfolio the next item on the top is products where the current inventory is actually less than than or equal to the reorder point which means you have to reorder these products because they have hit the reorder point so you have a list of five here but you can scroll further because you actually have eleven products that you have to reorder so you can scroll through to see all the eleven products okay so now that’s the summary information at the top but here’s where all the information that you have to enter are so the this table is actually a list of line items for all the orders so both purchase and sale orders you would enter them here so let’s go through fields in this table so order number so you can use the order number to identify each unique order and you can use any order numbers that your systems generate for your orders but if you don’t have any such systems you can still enter your own order numbers however you create them the only recommendation is that make sure that they are unique in this sample data I have called all the purchase orders P 1 P 2 P 3 etc and then the sale orders I would be calling them s 1 s 2 s 3 but you could choose however you want to call it but make sure that their each order number is unique and the order date is the date when you make the order to your supplier to buy products or when you your customer makes the order to you to buy products from you and so this is the date when the order is placed this is now the date when the item is expected to ship so the order date order type is purchase or sale as we have seen before the expected date is the date when you expect the inventory to be available so for example in this case on 1st October there was a purchase order made by me to my partner whose supplier X I made my order to the supplier for a specific product of es 25 so the order was for 50 digital cameras and in this case I expect the 50 cameras to be delivered to me by 8th of October so on 8th I will have this these products available to sell so that’s what the expected date means in the case of sale orders so for example if this is an order that a customer place to me then the expected date is the date when I

expect to send that order to my customer so this is the date when the inventory will leave me to my customer so that’s what the expected date means and this is how all the current inventory levels are calculated so this template allows you to have a different order date and an expected date because typically in in real life you actually would place an order and there’s a lead time for the order to be fulfilled so in these cases I’ve in the sample that I have used 7 days as my lead time but you could choose whatever the lead times may be in your scenarios as we’ve talked already the partner is the name of the supplier for purchase orders and it’s the name of your customer if it’s a sale type of order but make sure that you are being consistent when you’re typing your names of partners or customers because this is going to be used later in this template when you try to analyze the performance of your business now next product name it’s pretty straightforward there’s a drop-down menu from the list of products that you entered in the step number one with the list of products quantity this is the number of units and this template can also handle fractional units in this sample data these are digital cameras so these are whole numbers but in your example or in your situation if you’re if you need fractional quantity that should be fine as well now unit price is the you know if your purchase orders this is the amount that you’re paying for each of these cameras to buy it and then in the case of sale orders this is the price that you are selling to your customers at and the amount is just a calculation based on the quantity and the unit price that’s a formula all these last four columns are actually calculated so please don’t type anything there or make any changes because these are formulas written in them and these are going to be used in the next section of the template so please make sure that this these are not modified product category is just what you entered in as product category in the first step your month is used in some of the charts later and inventory availability is very important so this is the number of units that is available as of the expected date so to illustrate this I’m going to go down to the last item here and so for example here I’m going to enter a new product let’s say okay so in this case I’m going to enter ES 20 as my product and as you can see here the inventory availability as soon as I enter ES 20 it says 5 what that means is that as of the date 19th August 2013 which is my expected date I have 5 I will have 5 units in my inventory so that’s what it says and now if this order was for 10 units let’s say from my customer it immediately tells me it becomes red here and it’s a negative number that’s why it turns red and this entire row is highlighted with the red border and this is this this informs me that I have a problem here I don’t have ten items to sell and I only have five so I am going over my inventory which means I have a problem so this tells me that I have to place a new purchase order to my supplier to buy more of the specific product so that I can fulfill this order this is how the instant access to the inventory availability information can be very crucial for you in making the right decisions about purchasing orders and also knowing which sale orders cannot be fulfilled in time so that you can negotiate and work with your customers to reset the expected dates ok so now we have seen all the aspects of the orders and inventory worksheet and you know just like this isn’t this is an Excel table so if you want to add more information you would just start typing in the last row and automatically now this becomes part of your data set and all the calculations will be including this new row as well so make sure that you start entering just after the last row if you want to add more rows ok so now the next section of the template is to actually look at the report so that we can understand how the business is performing so click on the link here to go to your report all information here

is based on order date and not the expected date you would find charts where we use the month and these are based on order date months and not the expected date months ok so at the top you have a list of slicers or filters that you can use to customize these charts and one thing to keep in mind is that please make sure that you only choose purchase or sale as the order type don’t choose both because typically when you choose both that’s not going to be very helpful for you so in this case let’s say I’ve chosen sale because I want to understand the the sales for my business and you can filter by year and the month of the order and also you can also filter by the customers or suppliers basically your partners since I’ve chosen sale I see here that the customers are highlighted and suppliers are not because whenever I make a sale I am selling it to my customers so that data tells me that you know these are my customers who have sold – and this is my list of products so I can filter this report by products and also product categories so the first couple of chops here so this is about the amount so this is the you know money so how much money did I make in sales in each month and the the line here represent represents the cumulative sum so this is actually how much money did I make cumulatively over time and this chart represents the similar thing but for quantity so this is the number of units that I actually sold and how much did I sell cumulatively the next two charts are about product categories again on the Left this is about money so this is about how the different product categories have contributed to your business and also here we have the quantity so the number of units that each manufacturers products have been sold for in each of these months are represented here so this gives you a quick in quick insight into which product categories are performing well and not over time so that you can make the right decisions about buying products this chart represents the breakdown of the total amount of money that you that you’ve made by sales and it breaks it down by the customers that who you sold it to so if you have a good set of customers that you typically sell the products to you can you can take a quick look at which customers are bringing more revenue to your business the last table here this is to identify the best performing products and the worst farming products so there are a couple of levers here so you can sort this table by amount or quantity so I’ve chosen here amount first so you will see that amount is highlighted here and I also can choose descending or ascending so I have chosen here descending that means that the best or the product that is contributed the most to the revenue or most to the sales here in this case will be shown first and you can see here it’s ranked such a way and when I scroll down I will see number 11 number 12 ranked products as well so this helps me identify the best performing products by amount or by quantity which I can just change here and this tells me which product has sold the most number of units and if I want to see the weakest performing products I will do ascending here and this tells me that this product has sold the least number and again I can choose amount to see which product has contributed the least amount of money to my business so this is a very interactive cable right here and you can scroll down to see as many products as you can and that’s pretty much it that’s all you would find in the report sheet the last sheet that we have in this template is a list of terms so again that these things have been covered in my blog post and these things also were also covered in the video earlier but here is a documentation of the different terms that are used in this template and how you should interpret them okay so now if you like this template this template can be downloaded from my blog at in Zara blog spot.com and here are the two links again if you liked this sample data illustration definitely download the actual template without any sample data so this is what the the template without the sample data will look like and I do have one row here in the list of products but please make sure that you start writing over that

Roah for example I may say this is my product and this is my product description and put in your own product category for example here let me say and put in your reorder point this will make sure that all the charts and everything work fine so don’t delete this row by any chance because once you empty this Excel table the charts will not capture all the data correctly so make sure that you start typing over the first row with your own data similarly go to the orders and inventory sheet and then there is a one dummy record here start entering your own information however your order numbers are lined up order number one for example and then put in your own date put in your data expected date partner product names and everything so in this case I would say let’s say your product name is your unit quantity and everything so once you start entering information here all these information pieces at the top will automatically get updated so when you want to add more rows you start writing down again where in the next row and you can see that the template the table now is expanded to include your new row so as you keep entering more rows here the template will take that into account and calculate correctly all the inventory levels so this is how you would use this template and in to enter your order information and one last thing you would have to do is whenever you want to see the report section you always should go and hit the data and refresh all because this is going to when you hit refresh all all these charts will get updated so that is very very important if you don’t hit data refresh all the data will not reflect what you have in your orders table so please make sure that you refresh the data and if you like this template please share it with others and once again this template is available for download for free from injera dot blog spot.com and there are other templates available on my blog as well and if you have any feedback please email me or leave it in the comment section in the blog thanks for watching this video and if you like this video subscribe to my You Tube channel to get updates on my upcoming videos and also feel free to subscribe to my blog to get updates on my blog posts about new excel templates thank you for watching