Create the Perfect Service Bill Format in Excel for Inventory Management

Service bill format in excel for Inventory

hi, in this video i show you how to create a warehouse management sheet It helps you keep track of incoming outgoing and inventory Also you can manage ing to certain time period And now the steps to create a warehouse management sheet Here is the list of items in my inventory I will manage the inventory with these items First of all I customize to make it easy for you to import items You create a drop-down list with a list of SKUs For each SKU you can display a description of it in the cell next to it You use a function called VLOOKUP to display the description Don't forget to use the function called IFERROR when the SKU cell is empty Then if the SKU cell is blank, the description cell will also be an empty cell Next copy the formula to the cell titled UNIT You change the get info column to get the contents of the cell titled UNIT Next I copy the formulas of the column titled SKU down to the cells below it Similarly you copy the formulas from the cells with the title as description and UNIT down to the cells below them Then you reformat the cells to make them easier to see With the above method you will easily create receipts You just need to select the SKU, the information such as description and UNIT will be automatically updated in the next cells Next you copy the formulas for the exports Just like in stock, every time you select a SKU when it comes out of stock, the information like the description and the SKU will automatically update in the next cells. Just like in stock, every time you select a SKU when it comes out of stock, the information like the description and the SKU will automatically update in the next cells. I ended up copying the recipe to the items that were in stock In the next section I show you how to manage items during import and export and check inventory I will be stocking a product with SKU of A001 with a quantity of 100 Next you use the following formula to automatically update the inventory quantity You use a formula named SUMIFS with the following conditions You calculate the total imported quantity of items with SKU of A001 Because I want to track by certain period so I add condition from date to date This function returns 0 because I haven't updated the date yet So I created a drop-down list for the start date I use a list of import and export dates to create a drop-down list I then copied the formula to create a drop-down list of the end date After updating the date you will see the correct updated inventory results After updating the date you will see the correct updated inventory results I will add a new stock receipt with the item SKU is A001 Then I update the end date and you will see the stock has been added 50 more What happens when I export an item with SKU of A001 I output the quantity as 30 You take the quantity entered minus the quantity exported and get the remaining result When you change the date you will see a difference in the amount of inventory In some cases you export more quantity than stock and the result is less than zero That's absurd and I want to warn in that case by highlighting the cell with a different color If the inventory quantity is less than 0 the cell will automatically turn red When you update the correct amount, the cell automatically changes to the normal color When you update the correct amount the cell automatically changes to the normal color At this point you have completed the export import and inventory tracking for items with SKU of A001 At this point you have completed the export import and inventory tracking for items with SKU of A001 You just do the same with the rest of the goods in your inventory You copy the formula to calculate the inventory quantity of each item into the cells below for the remaining items in stock You copy the formula to calculate the inventory quantity of each item into the cells below for the remaining items in stock Above is a simple inventory manager to help you keep track of your inventory In addition it also tells you the amount of inventory for a certain period of time I put the sample file in the description for you to create your own inventory management sheet Thanks for watching don't forget to like and subscribe You will find many useful videos on my channel Good luck