Que vous gériez une petite entreprise ou une plus grande entreprise ; que vous travailliez dans une organisation qui doit suivre des biens ; ou si vous souhaitez simplement conserver un inventaire personnel de vos biens, Google Sheets est l’outil incontournable pour créer des feuilles d’inventaire de toutes complexités.
Dans this article, I will show you how to create an inventory sheet for different scenarios using Google Sheets. These creative techniques will not only be useful but also increase your productivity.
Example Project #1: Personal Pantry Inventory Sheet
If you want to create an inventory of your own belongings, Google Sheets is your best choice, as it is both simple and cost-effective.
Here’s how to get started:
Step 1. Create a new file – Give it an appropriate name, such as “Personal Pantry Inventory.”
Step 2. Customize the first few rows – Depending on your needs, organize your information using columns such as “Product,” “Location,” “Category,” “Stock,” “Units,” and others. Consider being creative with your categories. For example, a pantry inventory would likely include food items and cleaning products. You can use filtering function later to organize them, but it’s important to understand the structure of your categories beforehand.
If you want to use a copy as a starting template, you can download a Google Sheets inventory template here. Crawlan.com
Example Project #2: Inventory for a Small Business
Let’s move on to a more complex scenario by creating an inventory for a small business. In this example, we’ll use a fictional ice cream shop.
Follow these steps:
Step 1. Create a new file – Name it accordingly, such as “Ice Cream Shop Inventory.”
Step 2. Structure your inventory with headers – For small businesses, there are many reasons to use barcodes or SKUs for your products. Create your own system using product IDs as headers. You should also include fields for production and expiration dates, especially for perishable products.
See the screenshot below for an example of how we structured and customized the ice cream shop inventory.
To track updates, it’s recommended to have a “Last Updated” tab.
Pro tip: Conditional formatting is very useful in any inventory setup. By automatically formatting colors or text when stocks are below a certain threshold, you’ll visually know when to take action.
If you want to use a copy as a starting template, you can download a Google Sheets inventory template for small businesses here. Crawlan.com
Example Project #3: Inventory Management
When you need more than just a simple inventory sheet, it’s time to upgrade to a more advanced inventory management system.
In addition to the steps from Example Project #2, you’ll want to add more features to your inventory sheet. This may include additional tabs for sales, stock transfers, orders, and even tracking multiple warehouse locations.
These spreadsheets can become extremely large and complex. Instead of building one from scratch, we’ll focus on the most crucial information and functions your inventory sheet should have.
Data to Collect for Your Inventory Management Sheet:
-
Reference Codes or Stock Keeping Units (SKU) – Alphanumeric codes assigned to products for inventory tracking. The design of your codes requires thoughtful consideration to ensure they make sense. Avoid random letters and numbers, and instead, follow a structured approach. Each variant of a product (e.g., color, size, format) must have a unique SKU to allow analysis of which sets of items perform better than others.
-
Available Stock – The quantity of a specific product readily available for sale. For businesses with multiple warehouses, you may want separate inventory sheets for each location or multiple columns representing a single warehouse.
-
Product Specifications – Use columns for each product variant to make your spreadsheet more intuitive. These columns could include properties such as product color, model, size, style, material, or other relevant information. You can even use a separate tab to create a dedicated specifications sheet for these descriptors.
-
Incoming Supply – To manage the supply chain, track incoming inventory to know how much will be restocked based on the latest order placed with your supplier.
-
Purchases – In addition to incoming stock transfers, you’ll also want to track purchases from each supplier. This is typically done in a dedicated tab.
-
Pricing – Don’t forget about pricing. What price do you sell a product at retail or wholesale? For B2B sales, the volume of products sold affects the price you give to your customers. Let your spreadsheet reflect that.
-
Cost of Goods Sold, Margin, Cost of Goods – You may want your spreadsheet to show you how much it costs to source a particular SKU, your profit margin, or even the margins you achieve per sale.
-
Sales – Your inventory is not complete without regular updates on your latest sales. If you’re selling products, deduct them from your inventory to avoid overselling. Of course, unless you sell pre-order or custom order products.
-
Reports – An additional tab for reports can be useful if you’re creating charts and graphs to visualize your inventory information.
Enhance Your Inventory Sheets with FileDrop
Whether you’re working on a simple personal inventory sheet or a complex inventory management spreadsheet for a large business, the free FileDrop extension can make your document even more comprehensive. Being able to drag and drop files into Sheets is valuable when attaching relevant photos and documents to your SKUs, purchases, and sales.
FileDrop can do a lot, including:
- Drag and drop files from your computer.
- A file library space to manage the files you’ve added and reuse them.
- Add files already present in your Google Drive.
- Convert PDF to text directly from Docs and Sheets using OCR technology.
- Image to text conversion without leaving the application.
- Premium upgrade for even more functionality.
- And many more time-saving features with regular updates!
It’s easy to use! Learn more about our FileDrop extension and discover how you can save time and optimize your processes with our free tool.
Conclusion: Creative Ways to Create an Inventory Sheet on Google Sheets
That concludes our article on creating an inventory sheet that suits your use case. We hope this knowledge helps you grow your business and be more productive in all areas of your work. Feel free to share these Google Sheets inventory templates with your colleagues or team.
At Crawlan.com, we’re constantly building a library of useful content to help you stay productive using digital tools for a digital era of collaboration and connectivity. Be sure to subscribe to our newsletter below to stay informed about our fresh beginner-friendly content!
