No minimum order requirement Free shipping over $150 Free 30-day returns Get Started Free

No minimum order requirement Free shipping over $150 Free 30-day returns Get Started Free

The Ultimate Google Sheets Inventory Template for Your Small Business

October 22, 2021 09:04

The Ultimate Google Sheets Inventory Template for Your Small Business

 

The ultimate google sheets inventory template for your small business

Managing inventory can be overwhelming, but our free Google Sheets inventory template has everything you need to make the task a breeze. This template includes formulas and calculators to help you track available inventory, sales, and items purchased, as well as use data to calculate your ideal reorder point. To access our free inventory management template, simply click download below, make a copy, and start tracking your inventory and other relevant order and sales data for your business. 

 

Inventory template download button

 

What Is Inventory Management?

Inventory management covers every stage your business’s inventory goes through, including ordering, storing, packaging, selling, and shipping your goods.

Accounting for your business inventory ensures that you have enough in stock to meet demand, reduces the time needed to process orders, and keeps your customers satisfied with on-time deliveries. But how can you ensure you have enough inventory without overstocking?

You can manage this by calculating the reorder point for your merchandise: that is, the minimum level of inventory at which you need to place a new order to avoid running out. Our spreadsheet can help you do this once you have enough historical sales data.

What Is ABC Analysis in Inventory Management?

The ABC classification system divides inventory into three categories, based on how much it’s worth and how much you have in stock. The idea is to identify which 20% of your products produce 80% of your sales value.

The ABC analysis can be broken down as follows:

 

  • “A” items: Those that account for 10% of your total inventory lines but make up 70% of your annual value.
  • “B” items: Items that amount to 20% of your inventory and 20% of your annual value.
  • “C” items: Goods that add up to 70% of your total inventory but account for only 10% of the total value.

 

By knowing what’s in the most demand, you can also get a better idea of what you can charge for a product, perhaps adding a premium for high-volume items. In addition, you’ll be able to monitor trends: Are products becoming more or less popular, and what inventory purchasing adjustments do you need to make as a result? 

Google Sheets Inventory Template

The Google Sheets inventory template we’ve created provides ample space for business owners to organize their inventory, sales, and purchases. The customizable template also includes a Calculators tab that helps you calculate the ideal amount of safety stock and the reorder point. 

We’ve included formulas, so you don’t have to worry about crunching the numbers. Just input your SKUs, item names, suppliers, category, sales, and purchases, and our spreadsheet will do the rest. If the cells are light grey, that means you need to manually input information. If they’re purple, the information is populated by formulas.

The first tab includes instructions to teach you how to use the template. The other tabs on the inventory management template (Inventory, Sales, Orders, and Calculators) address specific ways you can track and organize your inventory in real-time.

Inventory Tab

Google Sheets small business spreadsheet inventory tab

 

The Inventory tab has space for you to track all necessary information related to your current inventory. This tab includes information such as SKU number, item name, supplier, category, storage location, inventory value, and quantity in stock.

Determine your ideal safety stock and reorder point (the calculator can help with that if you have enough historical data), and the spreadsheet will let you know when it’s time to place another order by highlighting “Available Inventory” in red when stock is running low. 

We’ve included information related to the item cost and markup on the inventory spreadsheet template so you can easily test what retail prices to set based on a markup percentage and set your sale price. If you’d like to consider increasing or decreasing your current markup, simply adjust the percentage under the Markup tab and the retail price will adjust accordingly. 

Sales Tab

Google Sheets small business spreadsheet sales tab

 

Tracking sales is an important part of any business, especially when it comes to managing cash flow. In this tab, you can track not only what items you’ve sold, but input details about your customers and ensure that you’ve shipped the item. If you have not shipped an item, it will be tallied under Quantity Pending Fulfillment (Column I) in the Inventory tab and subtracted from Available Inventory.

We’ve also included color-coding for shipping details and a feature that automatically tracks your total sales. Note that this only measures the item price. It does not include any shipping or taxes.

Orders Tab

Google Sheets small business spreadsheet orders tab

 

Track orders you’ve made from suppliers in the Sales tab. When you start the inventory spreadsheet, you’ll want to input all current inventory here and mark it as received. This will assure that the sheet integrates with the Inventory tab seamlessly and creates an easily digestible inventory list.

Keep an eye on the Total Purchased in the Orders tab compared to Total Sales in the Sales tab to make sure you’re not overbuying.

Calculators

Google Sheets small business spreadsheet calculators tab

 

Our Calculators tab explains two important factors of inventory tracking: safety stock and reorder point.

Reorder Point

The reorder point is the point at which you need to reorder a certain item to ensure your inventory levels are set appropriately and you don’t run out of stock. It’s calculated using the following formula:

 

  • Reorder point = (Average unit sales per day x lead time) + safety stock

 

The lead time you input can either be the supplier’s estimated delivery time, or it can come from historical data if you’ve worked with the supplier before. For example, they might give a 14 day lead time, but, on average, they deliver in 10 days.

Safety Stock

The safety stock refers to an optional amount of inventory you can choose to have on hand at all times in case of delays when restocking, lost shipments, or a spike in sales. You can calculate the amount of safety stock you need using the following formula:

 

  • Safety stock = (max daily units sold x max lead time) - (average daily units sold x average lead time)

 

As you can see, you need a lot of historical data to calculate safety stock. If you’re a small business, the above formula may lead you to overestimate safety stock, especially if your sales aren’t consistent. 

For example, say you sold 5 units one day (max daily units sold) but you sell 1 unit per day on average. The longest your supplier ever took to deliver a product was 20 days, but on average they deliver in 7. Using the above formula, the recommended safety stock is 93 units

If you don’t have historical data to calculate your safety stock or simply feel like the formula is estimating more stock than you’re comfortable holding, you can either:

  • Decide on a fixed number of units to designate to safety stock, say 5 or 10, OR
  • Multiply average daily sales by days of safety stock you’d like to hold. This is what the calculator in our spreadsheet does. For example, you sell 1 unit per day and would like to have 7 days worth of safety stock. In that case, you’d hold 7 units for safety stock.

No matter how you determine the safety stock to hold, it’s useful to have extra on hand in case any unforeseen circumstances occur.

What To Track on Your Inventory Spreadsheet

Tracking your inventory is made easier by the details you provide. Here’s a breakdown of the different items you should consider including on your inventory spreadsheet and why each one is important. 

SKU Number or Serial Number

SKU numbers help you to track stock levels. If you use an inventory management system, you may need to use a certain SKU framework. If you don’t, create top-level categories and sequential item numbers to more easily reference your products. For example, 01 may signify dresses, so a leopard print dress could be given the SKU 0100001.

While an SKU number tracks what’s yours and a barcode tracks the products you carry, a serial number tracks each individual item. So each piece of jewelry or home decor item has its own unique serial number. Because they’re so specific, serial numbers can be used to protect against knock-offs and counterfeit items and to track items that may have been lost or stolen.  

Item Name

Add a detailed item name to differentiate the product from others. If you have different sizes or colors, note that too. You can create separate columns for additional descriptors if you’d like.

Supplier

It’s important to keep track of who’s supplying you with the product and any notes about the supplier. If you’re managing multiple suppliers, it may be valuable to create a spreadsheet with supplier information, order minimums, and order lead time. 

Item Location

Knowing where items are stored is important so you can access them easily to fill orders or count them to confirm how much you have in stock (and whether you need to contact the supplier for more).

Reorder Point

The reorder point is the point at which your inventory reaches a level that triggers the need to order additional units. 

Quantity in Stock

You’ll want to keep track of how many of each item you have in stock. However, this shouldn’t be confused with your available inventory. It’s prudent to subtract sales pending fulfillment and safety stock to understand how much inventory you actually have available to sell.

Safety Stock

Safety stock is your backup or buffer. It’s the inventory you carry in order to prevent stockouts (running out of product) due to fluctuations in customers, product availability, and other factors such as forecast errors. The trick is to calculate how much safety stock you need to have on hand to avoid running out, while at the same time preventing oversupply. Some inventory managers follow a guideline that safety stock should be enough to cover two weeks’ worth of interruption in-stock availability, or 10% to 20% of cycle stock demand. But there are other ways of calculating it, as well.

Cost Per Item

It’s important to know the cost of goods you’re selling. Our inventory tracks the cost of each item and helps determine the retail price. However, you should also be aware of variable costs, traditionally calculated as the cost per unit. The cost per unit is how much money it takes to generate, store, and sell a single unit of a product. 

Retail Price

The price at which you’re selling an item can be used as a comparison point with how much you’re spending to procure it wholesale from the supplier and/or produce it. You can use this to help determine whether it’s profitable to continue carrying it, whether you need to adjust the price in the future, or whether you want to negotiate a lower price from the supplier.

Total Cost

While this isn’t denoted on the spreadsheet, it’s an important metric to understand. The total cost of your inventory is the sum of your cost in ordering goods, costs associated with storing or holding them, and costs necessary to offset any stock shortage. This is a necessary step in optimizing your inventory list.

Google Sheets can provide an effective form of inventory management software because it can allow you to create inventory spreadsheets to track what you're buying from suppliers, what you’re stocking, and what you're selling to your customers. 

 

Inventory template download button

 

Categories:
© 2021 Supplied Inc. All Rights Reserved
Loading...