Fabrics, trims, and accessories are raw materials for a garment manufacturing business. In a garment factory, the fabric and trim store is responsible for maintaining inventory records. In this post, I have shared one simple excel format for your reference. I have also explained how to make an inventory tracking format in an Excel sheet.
You purchase different types of fabrics, trims, and accessories items for your orders and you also keep inventory for these items. You should keep stock of inventories of raw materials in your store to keep the production on without any break. A high level of inventory cost to the company and fewer inventories may slow down the production rate. To maintain the right inventory level, you should know how much inventory you have in your store of all type of items and how much inventory you are expecting to receive in your store and the expected date of receiving those items.
As a storekeeper, your main responsibility is to maintain a minimum stock of all items (regular items) and control the inventory level by reordering.
How to control your inventory without purchasing inventory management and control software (ERP)?
You need a tool to record all incoming materials, their volume and necessary information against each item. Spreadsheet like Excel sheet is one of the good options for them who don't have software for inventory management and control. Assuming that you still prefer excel sheet for inventory tracking. You can keep record of your inventories in a excel file and retrieve data whenever you need. For this you need to enter inventory record manually.
Note: There are some limitations to using an excel sheet for inventory records. So if you can afford an ERP, better you go for one. Use a secured database.
Some of the disadvantages of using Excel sheets:
Download this template and modify it as per your factory need. You you have any questions you are welcome to write me.
As a storekeeper, your main responsibility is to maintain a minimum stock of all items (regular items) and control the inventory level by reordering.
How to control your inventory without purchasing inventory management and control software (ERP)?
You need a tool to record all incoming materials, their volume and necessary information against each item. Spreadsheet like Excel sheet is one of the good options for them who don't have software for inventory management and control. Assuming that you still prefer excel sheet for inventory tracking. You can keep record of your inventories in a excel file and retrieve data whenever you need. For this you need to enter inventory record manually.
How to make an inventory tracking format in Excel Sheet
- Prepare an excel format for recording data for inventory purchase and inventory consumption
- In the excel template make columns for all necessary information that you need.
- Update your file regularly. I would suggest you to update your inventory tracking file daily.
- Use pivot table for viewing data against particular item. You can use various combinations for viewing data, like- inventory sourcing status of an order.
 |
| Inventory tracking template |
1. Prepare your excel file for data recording.
When you will be preparing your excel format for the factory inventory records, follow these points.- Add columns in the format for as much information as you need. See the image below. I have added x number of columns.
- Use data validation for fields those will be entered repeatedly. Like buyer name, item type, UOM. By doing this you can avoid typing same thing differently. Benefit of using data validation in your template, easier to data sorting while you view reports.
- Use formula for calculations wherever applicable. Like balance quantity of items and item wise amount.
- Add filter to the first row (header row)
- Freeze pane as you need. In the sample template header row and 4 columns are freezed.
- Secure your excel sheet and inventory tracking file. To stop misuse of the inventory tracking sheet you can secure the file by password and also you can protect specific rows and columns if you want.
- You can set conditional formatting for highlighting certain cells, like low inventory level, pending purchase, and delay in sourcing.
Also read: Fabric reconciliation procedure
2. Identify columns (fields) for your record
- First, decide what information you want to maintain the record. Based on that add columns in the format.
- If you prepare your format with unnecessary fields (columns) you have to fill all of them. Filling an excel sheet consume time. Like PO number, Style number, Buyer for the style, Item name, item type, item color, sourcing date, issue date, purchased quantity, issue quantity etc. See the following template for an example.
- Your inventory format is ready for recording fabrics, trims, and other items.
- Don’t think much about the format that you made. Later you can add additional columns if necessary.
3. Data entry and inventory record updating:
- Enter data in the excel sheet.
- Most of the departments are dependent on you for raw materials. Merchandisers, factory owners, sampling departments, and production people need to know the inventory status of a particular item. You are responsible for proving them the right status of the inventory. You can provide right information to them at the right time, only if your inventory record is updated.
- So update your inventory tracking file daily.
- Don’t forget to cross-check your data.
- Delegate data entry job one of your subordinates, if you are busy with other things. When you delegate work to someone remember to provide them instructions on what to do and how to do it.
4. View reports
Analysis of inventory consumption and purchase details- Check stock of inventory
- After few days of data entry you will have lot of data in the inventory tracking sheet. Finding particular information would be a tough job.
- You can see desired data (information) on the same sheet (table) by filtering data in desired columns.
- A pivot template is another option for doing data analysis and viewing reports for the desired information. Prepare pivot table for common report types.
- You can also do post-shipment inventory data analysis.
Download Sample Template
Some of the disadvantages of using Excel sheets:
- There might be a chance of making errors while entering data manually.
- Repeated entry of the same information.
- Limitations in data analysis and report viewing
- Lot of manual data entry work