Inventory Management System Template for Google Sheets

In this post, I will demonstrate how to create a simple yet effective inventory management system. It will strike the right balance between simplicity and functionality, ensuring that it meets your inventory tracking needs without being overly complex.

Create Product List

Let’s begin by creating a straightforward product list. We’ll name this worksheet “Items,” but feel free to choose any name that suits your preference. Here, we’ll include the item ID, product name or description, and pack size. You can add more fields if needed, but for now, we’ll stick with these.

For the item IDs, I’ll input some random values. Now, let’s move on to the next worksheet, where we’ll handle new inventory receipts. This sheet is solely for managing items, so I’ll copy the headers from the previous worksheet.

To enhance the functionality, we’ll include additional fields such as “Quantity” (QT) and any other relevant information you deem necessary. In this section, we want a dropdown menu to select the different item SKUs available.

Data Validation

To achieve this, go to the designated section and select the entire range where you want the dropdown menu to appear. You can accomplish this by pressing Command + Shift + Down (or Ctrl + Shift + Down on Windows) to select the entire area. Next, navigate to the “Data” menu and click on “Data Validation.”

In the Data Validation dialog box, ensure that “List from a range” is selected. If not, choose that option. Then, click on the small icon within the input box and go to the “Items” sheet. Select the list of items you created earlier. It’s recommended to select a larger range to accommodate future additions to the list. You can choose to display a warning or reject any other items not included in the list. Once you’ve configured the settings, save the changes.

Now, when you navigate to the top of the sheet, you should see a dropdown menu where you can select from the available item SKUs.

Item Selection

To add the dropdown functionality and auto-population of information, follow these steps. First, select the cell where you want the dropdown to appear. Then, go to the “Data” menu, choose “Data Validation,” and select “List from a range.” In the input box, click the icon and select the list of items from the “Items” sheet. Make sure to select a larger range to accommodate future additions. Save the changes.

Now, let’s auto-populate the description and pack size based on the selected item. In the cell adjacent to the dropdown cell, enter the following VLOOKUP formula:

excel

=IF(A2="", "", VLOOKUP(A2, Items!$A$2:$C$100, 2, 0))

This formula checks if the dropdown cell is empty. If it’s empty, it returns a blank cell. Otherwise, it performs a VLOOKUP to retrieve the description from the “Items” sheet based on the selected item. Adjust the range Items!$A$2:$C$100 to match your actual range. Drag this formula down to populate the entire column.

Similarly, for the pack size, copy the formula and change the column number to 3:

excel

=IF(A2="", "", VLOOKUP(A2, Items!$A$2:$C$100, 3, 0))

Next, let’s calculate the quantity expanded. Multiply the pack size by the quantity received. For example, if the pack size is in column D and the quantity received is in column E, enter the formula =D2*E2. Drag this formula down to calculate the quantity expanded for each item.

To calculate the total cost, multiply the quantity expanded by the cost per pack. For example, if quantity expanded is in column F and cost per pack is in column G, enter the formula =F2*G2. Drag this formula down to calculate the total cost for each item.

Remember to handle any potential errors, such as division by zero, by using the IFERROR function.

That’s it! You now have a functional inventory management system template in Google Sheets.

This should work in both Google Sheets and Excel. However, in Excel, you would need to use double quotes instead of two double quotes. To apply the formatting, select the cells you want to convert to currency, go to the “Format” or “Number” menu, and choose the currency format. You can also add borders to make the sheet look more organized and professional.

For the sales side, you can duplicate the receiving section and modify it accordingly. Delete the unnecessary columns, such as pack size if it’s not needed for sales. Instead of quantity received, enter the quantity sold for each item. For example, if you sold 45 of one item and 32 of another, enter those numbers in the corresponding cells.

Remember to adjust the formulas accordingly. If you no longer require the pack size, you can remove it from the formula that retrieves the description.

Continue populating the sales section with the necessary information, and adjust it based on your specific business needs. You can rename the section to “Sales” or any other term that suits your purpose.

By duplicating and modifying the receiving section, you can use the same structure and formulas for tracking sales. This way, the description and other relevant information will be auto-populated based on the item sold.

Feel free to customize and format the sheet as desired, adding borders or other visual elements to enhance its appearance.

Cost Per Piece

Assuming that the items are priced per piece, I will input some numbers. The “Price per piece” column represents the individual item price, and the “Total” column is calculated by multiplying the quantity sold by the price per piece. We already have the “Cost per piece” information, so we can omit this column.

This will be the sales side, where the white cells indicate the fields for data entry.

Sales Date

The rest of the cells will be pre-populated with a formula, indicated by a different color. Now, let’s move on to the inventory side. I will copy and paste the existing structure and add a column for the date. To format the date column, select the column and use the format painter to copy the format from the previous date column. Apply data validation to ensure that only valid dates can be entered.

We should also add a date column to the sales side, following the same process. Copy the date column and paste it into the sales section.

Next, we need to populate the inventory list. To do this, we’ll use the UNIQUE function to extract a unique list of items from the receive side. Apply the UNIQUE function to the range starting from column A (excluding the header) down to the last row of data. Use this unique list to populate the description column.

Similarly, copy the formula for the pack size from the receive side and paste it in the inventory section.

To calculate the total quantity received for each item, we’ll use the SUMIFS function. In the inventory section, use the SUMIFS function to sum the quantity column from the receive spreadsheet based on the item name in the inventory section.

For example, for the item “Water,” the SUMIFS function would sum the quantities from the receive section where the item name matches “Water.” This will give you the total quantity received for that item.

Continue using the SUMIFS function for each item in the inventory list.

That’s it! The inventory side should now be populated with the unique item list, description, pack size, and total quantity received for each item.

Quantity Received

Now let’s proceed to calculate the “Quantity Expended” in the inventory section. It will be similar to the previous step. You can drag the formula down to populate the entire column. If there are zeros in some cells, that’s acceptable.

Next, we need to calculate the “Quantity Expended” for each item. This will be done on a per-piece basis. We will use the SUMIFS function again. In the inventory section, use the SUMIFS function with the “Expanded” column from the receive section as the sum range. Lock the range using the F4 key. For the criteria range, select the item column from the receive section. Lock this range as well. Drag the formula down to copy it for all items in the inventory section.

That’s it! You have now calculated the “Quantity Expended” for each item in the inventory.

Quantity Sold

Now, let’s calculate the “Quantity Sold” for each item. Assuming you have better column names for your columns, we’ll use the SUMIFS function once again. In the inventory section, use the SUMIFS function with the “Salt Quantity” column from the sales section as the sum range. Remove the end reference for this column. For the criteria range, select the item ID column from the sales section. Lock this range as well. Click on the item ID column in the inventory section to complete the formula. Copy the formula down for all items in the inventory section.

Next, we can calculate the difference or variance between the “Quantity Received” and the “Quantity Sold.” Let’s call this column “Variance.” Simply subtract the “Quantity Sold” from the “Quantity Received” in the respective cells. Drag the formula down to populate the entire column.

For the “Quantity on Hand” column, it represents the current stock level. It can be calculated by subtracting the “Quantity Sold” from the “Quantity Received.” Update the “Received Quantity” with additional cases, if applicable, and ensure that the formula in the “Quantity on Hand” column is correctly reflecting the updated stock level.

Now, we have the “Quantity Received,” “Quantity Sold,” “Variance,” and “Quantity on Hand” columns populated. These columns contain formulas and will automatically update based on the received and sold quantities.

Additionally, you may want to calculate the cost related to the inventory. However, the information regarding cost is not provided in the given context. If you have the cost information available, you can include a column for “Cost per Piece” and calculate the total cost using the formula: “Cost per Piece” multiplied by “Quantity on Hand.”

Cost

So, let’s now calculate the cost for the inventory. We’ll start with the “Total Cost” column, which will sum up the total cost for each item received. To do this, we’ll use the SUMIFS function again. In the inventory section, use the SUMIFS function with the “Total Cost” column from the receive section as the sum range. Remove the end reference for this column. For the criteria range, select the item ID column from the receive section. Lock this range as well. Click on the item ID column in the inventory section to complete the formula. Drag the formula down to calculate the total cost for each item.

Next, let’s estimate the cost for the “Quantity on Hand” column. Instead of calculating the average price, we’ll use the latest purchase price for each item. To do this, we’ll use a combination of functions. First, compare the item ID in the inventory section with the item ID column in the receive section. Use an IF function to check if they match. Multiply this by the row formula to get the row number of the matching item. Make this an array formula by pressing Ctrl+Shift+Enter (Cmd+Shift+Enter on Mac). This will give you an array with the row numbers for the matching items.

Now, we need to extract the highest (latest) row number from this array. Use the MAX function to find the maximum value in the array, which corresponds to the latest row number. This will give us the row number for the latest purchase of each item.

Next, use the INDEX function to retrieve the cost from the “Cost” column in the receive section. Provide the cost column as the array, and the row number as the index. This will give us the latest cost for each item.

Drag this formula down to apply it to all items in the inventory.

Now that we have the current cost for each item, we can estimate the value by multiplying the current cost by the “Quantity on Hand” for each item. Drag this formula down to calculate the estimated value for all items.

To organize the sheet, you can add a couple of rows for totals and clear the formatting. You can also update the formatting to match the rest of the sheet. Finally, you may consider placing the totals and any additional calculations at a more convenient location, rather than at the bottom of the sheet.

Average Cost

Now, the only thing I want to add is the average item cost. We can calculate this using the AVERAGEIFS function. To get the average cost, divide the total cost for an item by the total quantity. This can be considered a weighted average.

To calculate the weighted average, use the AVERAGEIFS function. In the inventory section, use the AVERAGEIFS function and select the cost column from the receive section. Lock this range using the F4 key. Remove the end reference for this column. For the criteria range, select the item ID column from the receive section. Lock this range as well. Click on the item ID column in the inventory section to complete the formula. Drag the formula down to calculate the weighted average cost for each item.

We can use the existing “Total Cost Received” and “Receive Expended” columns for the calculation. Divide the “Total Cost Received” by the “Receive Expended” to get the weighted average cost. To handle any division by zero errors, use the IFERROR function to wrap the formula. This function will display a specified value (such as zero or blank) if an error occurs.

Finally, drag the formula down to apply it to all items in the inventory.

After completing these steps, you may consider formatting the headers and centering them for clarity.

Inventory Check

We shouldn’t need to modify any formulas at this point. Now, let’s add a new item to the inventory. From the list of items, I’ll add “16-ounce bottles” to the receive section. I’ll enter a quantity of 40 and a cost of $6. The rest of the calculations should update automatically. If we check the inventory section, we can see that the new item has been added, and all the totals and quantities have been updated accordingly.

In the sales or shipping section (whichever you prefer to call it), if we enter a quantity for this item, it should automatically reduce the quantity on hand in the inventory section.

It’s worth noting that sometimes, during a physical inventory check, you may find that the actual stock numbers don’t match what is recorded in the system. To account for this, let’s add another worksheet called “Inventory Check.” In this sheet, I’ll copy the item ID column and paste it in the “Quantity Offset” column. I’ll also copy the description column and paste it in the corresponding column. The “Quantity Offset” column will be used to adjust the quantity on hand.

For example, if we find during the inventory check that the actual quantity on hand for an item is 54 instead of the recorded 56, we can enter -2 in the “Quantity Offset” column for that item. Similarly, if we find that we have 8 more of the “Water 16” item, we can enter +8 in the “Quantity Offset” column.

We’ll need to ensure that the formulas in the inventory section account for these adjustments and update accordingly.

Quantity on Hand

We may also want to include a date column in the inventory check. I’ll copy and paste the column containing the dates, which corresponds to when the check was conducted.

Now, let’s incorporate the quantity on hand and the inventory check adjustments. Currently, the quantity on hand is calculated as the received quantity minus the sold quantity. However, we also need to consider the quantity offset from the inventory check.

To account for the quantity offset, we’ll need to sum up all the adjustments for each item. Keep in mind that the same item may appear multiple times in the inventory check, as multiple checks could have been conducted over time. Therefore, we’ll need to use the SUMIFS function to sum up the quantity offsets for each item.

In the inventory section, add a formula to calculate the quantity on hand. Start with the existing formula (received quantity minus sold quantity) and add the SUMIFS function to incorporate the quantity offsets. In the SUMIFS function, select the quantity offset column from the inventory check section as the sum range. Lock this range using the F4 key. Remove the end reference for this column. For the criteria range, select the item ID column from the inventory check section. Lock this range as well. Click on the item ID column in the inventory section to complete the formula. Drag the formula down to calculate the updated quantity on hand for each item.

Now, we have the updated quantity on hand that takes into account both the received and sold quantities as well as the inventory check adjustments. If we modify the quantity offset in the inventory check section, the quantity on hand in the inventory section will automatically update accordingly.

With this setup, the inventory tab is now fully automated. We can proceed to work on the other tabs, such as receive, sales, and inventory. If we want to add more items to work with, we simply add them to the items list.

Introducing OfficeHabit Shop Google Sheets | Tracker Template

Are you looking for a convenient and efficient way to track your habits and stay organized? Look no further than the OfficeHabit Shop Google Sheets Tracker Template! This template is designed to help you establish and maintain productive habits, whether it’s for personal or professional purposes.

With the OfficeHabit Shop Tracker Template, you can easily monitor your daily habits, set goals, and track your progress over time. The template is built on Google Sheets, providing a familiar and user-friendly interface that allows for seamless tracking and organization.

Key Features:

  1. Habit Tracking: Easily input and track your habits on a daily basis. Whether it’s reading, exercising, meditating, or any other habit you want to cultivate, this template has got you covered.
  2. Goal Setting: Set specific targets for each habit to challenge yourself and stay motivated. The template allows you to define monthly goals, enabling you to track your progress and strive for continuous improvement.
  3. Progress Visualization: Gain valuable insights into your habit-building journey with visually appealing charts and graphs. Track your streaks, view monthly totals, and monitor your overall progress with ease.
  4. Customization: Tailor the template to fit your unique preferences. Personalize the tracker by adding your own habits, selecting emojis, and adjusting goal targets to align with your specific needs.
  5. Accessibility: Access your habit tracker anytime, anywhere, as long as you have an internet connection. The template is compatible with various devices, including desktop computers, laptops, tablets, and smartphones.
  6. Easy to Use: The intuitive design of the OfficeHabit Shop Tracker Template ensures a seamless user experience. You don’t need to be a spreadsheet expert to start using and benefiting from this powerful habit tracker.

Whether you’re aiming to improve your productivity, develop healthier habits, or stay organized with your daily tasks, the OfficeHabit Shop Google Sheets Tracker Template is here to support you every step of the way.

Start tracking your habits and unlocking your full potential today. Visit the OfficeHabit Shop and download the Google Sheets Tracker Template now!

Note: The OfficeHabit Shop Google Sheets Tracker Template is a product offered by OfficeHabit, a trusted provider of productivity tools and resources.

Leave a Comment

Your email address will not be published. Required fields are marked *

Shopping Basket
Scroll to Top