Whether you’re managing a small business or a bigger enterprise; working on an organization that needs to keep track of properties; or if you’re simply looking to keep a personal inventory of your belongings, Google Sheets is the go-to for creating inventory sheets of all complexities.
In this tutorial, we’ll show you ways on how to create an inventory sheet for several different scenarios that may be relevant to you and increase your productivity with Google Sheets.
Sample Project #1: Personal Inventory Sheet
If you’re looking to create an inventory of your own stuff, Google Sheet is your easiest and cheapest bet.
For this example, we’ll make an inventory of our fictional household pantry from scratch. Our Pantry Inventory will help us keep our home stocked with what we need.
Step 1. Create a new file. We’ll entitle this as our aptly named Pantry Inventory.
Step 2. Customize the first rows as needed. For this project we’ll organize our information using a couple of columns including Product, Location, Category, Stock, Units, and a few others. We probably don’t need barcodes and SKUs for this complexity yet.
Tip: Be creative! Think about how you’d like to structure your categories. For this example, a Pantry Inventory would likely have food items and cleaning consumables. You’ll be able to use the Filter function later to organize them but it’s important to understand the structure of your categories beforehand.
If you want a copy to use as a starting template, make a copy or download this Pantry Inventory Template.
Sample Project #2: Small Business Inventory
Moving up one notch in the complexity meter, we’ll now try out making an inventory for a Small Business. We will use a retail ice cream shop inventory for this demonstration.
Step 1. Create a new file. We’ll entitle this as our aptly named Ice Cream Inventory. Yum!
Step 2. Structure your inventory with headers. As far as small businesses go, there are many reasons why you’d want to use a barcode or SKU for your products. We’ll use our own system using Product IDs as the header. Do your research on how to make a SKU system that best fits your product line. As our product is perishable, we’ll also note production and expiration dates.
Examine the screenshot on how we structured and customized the inventory for our ice cream business.
As a small business inventory is to be regularly updated, it is recommended to have a Last Updated tab.
Tip: Conditional formatting shines in any inventory configuration. If stocks are running below a certain threshold, automatically formatting fill or text color can visually tell you when to take action.
If you want a copy to use as a starting template, make a copy or download this Ice Cream Inventory Template.
Sample Project #3: Inventory Management
When you no longer need just a simple Inventory Sheet, you’d want to do more technical Inventory Management.
In addition to the steps on our Project #2 with the Ice Cream business, you’d also want to add more functionality to your Inventory Sheet. Some setups would require additional tabs like Sales, Inventory Transfers, Orders, and even tracking multiple warehouse locations.
These spreadsheets can get exceptionally large and overwhelming so instead of creating and dissecting one from scratch, we’ll tackle the most crucial information and functions your Inventory Sheet should have.
Data to Collect for Your Inventory Management Spreadsheet
- SKUs or Stock Keeping Units – alphanumeric codes assigned to products to track your inventory. Designing the coding you use needs a thoughtful approach for SKUs to make sense. It’s best to avoid just random numbers and letters and instead follow a structure. These should be unique for each variant of a product (i.e. color, size, form factor, etc.) so you can run analytics on which sets of items perform better than others.
- Available stocks – quantity of a specific product that is readily available for your business to sell. Businesses with multiple warehouses may want either separate inventory sheets per location or just simply have multiple columns representing a unique warehouse like the approach we did in our example below.
- Product Specifications. To make your spreadsheet be more intuitive, you should use columns for each sort of variant a product may carry. These columns could be properties like product Color, Model, Size, Style, Material, or other relevant information. You can even use a separate tab to make a Spec Sheet dedicated only to those descriptors.
- Incoming Supply. For Supply Chain management, tracking incoming inventory is important so you know how much of your inventory will be replenished based on the most recent order you placed with your Supplier.
- Purchases. Related to incoming supply transfers, you’d also want to track your purchases for each Supplier. This is typically done on its own tab.
- Pricing. Of course don’t forget about your pricing. How much are you selling a product at retail or wholesale? Typically, for B2B sales, the higher volume you’re selling your items, the lower the price you’re passing on to your customers. Let your spreadsheet reflect that.
- Markup, Margin, Supply Cost. You may want your spreadsheet to tell you how much it costs you to source a particular SKU, your mark-up on top of it, and even the margins you make per sale.
- Sales. Your inventory is not powerful without updating it regularly with your most recent sales. If you’re selling products, you have to deduct them from your on-hand inventory so you don’t end up selling more than you have. Of course, that is unless you’re selling pre-order or made-to-order products.
- Reports. An additional tab for reports could be useful if you set-up charts and graphs to visualize your inventory information.
Such inventory management spreadsheets benefit from integrations and automations so you may also want to take a look at that.
If you want a copy to use as a starting template, make a copy or download this Apparel Inventory Template.
Making Inventory Sheets Even More Powerful with FileDrop
Whether you’re just working on a personal inventory sheet or on a massive enterprise inventory management spreadsheet, the free add-on FileDrop can make your document even more comprehensive. Being able to drag and drop files into Sheets is very valuable for when you’d want to attach photos and files relevant to your SKUs or to your purchases and sales.
There’s a lot of things FileDrop can do including the following:
- Drag and Drop files from your computer.
- A File Library space to manage the files you’ve added and use them again.
- Add files already inside your Google Drive.
- PDF to text directly from Docs and Sheets using OCR technology
- Image to text without leaving the app.
- Premium upgrade to do even more with these features.
- + lots of more time-saving features with regular updates!
Using it is simple! Learn more about our add-on by visiting FileDrop and find out how you can save a lot of time by streamlining your processes using our free tool.
Conclusion on Ways to Make an Inventory Sheet in Google Sheets
That concludes our article about how you could make an inventory sheet that’s right for your use case. We hope this knowledge helps you scale up and be more productive in all areas of your business or profession. Please feel free to share all these Google Sheets Inventory templates with your colleagues or team.
At FileDrop, we’re continually building a library of useful content to help you stay productive using digital tools for a digital age of collaboration and connectivity. Make sure you subscribe to our newsletter below to stay updated on our fresh beginner-friendly content!