Excel makes it Easy – Inventory Analytics in Supply Chain Management

Introduction

Effective inventory management is crucial for businesses of all sizes. It ensures that products are readily available to meet customer demands while avoiding overstocking or understocking issues thus optimizing the overall supply chain's efficiency. Excel is a versatile tool for inventory management. Let’s explore how to use Excel to streamline your inventory management process, including essential formulas and a step-by-step guide to creating an Excel interface for inventory management.

Getting Started

Before diving into Excel, it's important to understand your inventory management needs. You should have a clear idea of what products you have, their quantities, reorder points, and any other relevant information. Once you have this data, you can start using Excel to create a user-friendly inventory management system.

10 Key Elements in Inventory Management

1. Product ID

A unique identifier for each product or item in your inventory.

2. Product Name 

The name or description of the product.

3. Purchase Quantity

The quantity of a product purchased in a specific transaction or period.

4. Purchase Cost 

The cost incurred to acquire a specific quantity of a product. 

Formula: 

Purchase Cost = Purchase Price per Unit x Purchase Quantity

5. Average Cost

The average cost per unit of a product in your inventory, considering the different purchase prices.

Formula: 

Average Cost = Total Cost of Inventory / Total Inventory Quantity

6. Sale Quantity 

The quantity of a product sold in a specific transaction or period.

7. Sale Value 

The total monetary value generated from selling a specific quantity of a product.

Formula: 

Sale Value = Sale Price per Unit x Sale Quantity

8. Profit 

The profit generated from selling a specific quantity of a product, calculated by subtracting the total cost from the total revenue.

Formula: 

Profit = Sale Value - Total Cost

9. Inventory Quantity

The current quantity of a product in your inventory.

Formula:

Inventory Quantity = Beginning Inventory + (Purchases – Sales)

10. Inventory Value

The total monetary value of a product currently held in your inventory.

Formula: 

Inventory Value = Inventory Quantity x Average Cost

Inventory Management table has been inserted in Excel sheet including 10 key elements explained above. Now it’s time to insert and other table “Inventory Transactions” with following titles:

a. Code 

b. Product 

c. Type 

d. Price 

e. Quantity 

f. Transaction Value 

The primary objective of this table is to establish a dynamic linkage with Excel formulas in such a way that any updates made to transactions will trigger an automatic synchronization with the "Inventory Management" table. 

Worry not, I'll gladly guide you through the process with ease!

Select rows and columns of Code and Product in “Inventory Management” table and name it Products. 

Now, let’s write a formula to synchronize the inventory management table with the inventory transaction table. This formula helps synchronize the two tables by matching product codes in the inventory transaction table with product names in the inventory management table, ensuring that the product names are correctly associated with their respective codes. 

=IF(C13="", "", VLOOKUP(C13, Products, 2, FALSE))

IF checks a condition and returns one value if the condition is true and another value if it's false. In this case, the condition being checked is whether cell C13 in the inventory transaction table is blank or not. C13 cell being checked in the inventory transaction table. It contains a code. In other words, it means that if there's no code (blank cell), there's no need to perform the VLOOKUP, and the formula will return an empty result.

If C13 is not blank (i.e., it contains a code), this part of the formula performs a VLOOKUP operation.

• C13: This is the value you want to look up in the "Products" column.

• Products: This is the table array where Excel should look for the value.

• 2: This parameter specifies that Excel should return the value from the second column of the "Products" table when it finds a match for the code in C13. In your case, this corresponds to the product name.

• FALSE: This parameter indicates that you want an exact match. In other words, Excel should return the product name only if it finds an exact match for the code in C13.

The formula checks if cell C13 in the inventory transaction table is blank. If it's not blank, it looks up the code in C13 in the "Products" table and returns the corresponding product name.

Now drag and apply the formula to all rows of the column “Product” in “Inventory Transactions” table.  

Next, go towards column “Type” in “Inventory Transactions” table. 

The two types are: 

Purchases--> Acquisition of goods or products by a business or organization from suppliers or vendors. These goods are typically intended for resale or use in the production process.

AND 

Sales--> Represent the transactions in which a business or organization sells its goods or products to customers.

To create the drop down list in Type column, select the cell E13.  In the Excel ribbon, click on the "Data" tab. Under the "Data Tools" group on the "Data" tab, you'll find the "Data Validation" button. Click on it to open the data validation dialog box. In the "Data Validation" dialog box, under the "Settings" tab, set the following:

Allow: Choose "List" from the drop-down menu.

Source: In the source field, enter "Purchases, Sales" without the quotes. This is where you define the options for the drop-down list, separated by commas.

Ignore blank: You can leave this checkbox unchecked unless you want to restrict the user from leaving the cell blank.

Now drag and apply the settings to all rows of the column “Type” in “Inventory Transactions” table.

Select, right click and choose clear contents. 

Use simple multiplication formula in “TransValue” (Transaction Value) column. 

= F13*G13

Drag and apply the formula to all rows of the column. Right click and choose your desire currency in number format.

Now, let’s have a look at the formula 

=CONCATENATE(C13,MID(E13,1,1))

Concatenate is used to combine or concatenate two pieces of text in Excel. So, when you use this formula in a cell, it will result in a concatenated string that combines the product code (from cell C13) and the first character of the product type from “Type” column. This unique identifier tells that the product is purchased or sold. Drag and apply formula to other cells too. 

Let’s name the columns. Select the rows from I13 to I25 and name it Purchases as shown below.

Likewise, name G13:G25 as QtyPurchased and H13:H25 as CostofPurchases.

Incorporate following formulas to other columns of “Inventory Management” table.

The following formula is used in PurchQty (Purchase Quantity) column. 

=SUMIF(Purchases,CONCATENATE(C4,"P"),QtyPurchased)

For each cell in the "PurchQty" column (Purchase Quantity), Excel first creates a unique identifier for the product in cell C4 by concatenating the product code with "P." Then, it searches the "Purchases" range for matching values. It looks for instances where the concatenated product code and "P" match any values in the "Purchases" range. When a match is found, Excel adds the corresponding value from the "QtyPurchased" range to the total.

This formula calculates the total quantity of a specific product that has been purchased by searching through the "Purchases" data and summing up the quantities associated with the product's unique identifier (created by concatenating the product code with "P").

Likewise, the formula used in PurchCost (Purchase Cost) column is

=SUMIF(Purchases,CONCATENATE(C4,"P"),CostofPurchases)

For AvCost (Average Cost) use formula

=F4/E4

The following formula is used in SaleQty (Sales Quantity) column. 

=SUMIF(Purchases,CONCATENATE(C4,"S"),QtyPurchased)

For each cell in the "Sales Quantity" column (SaleQty), Excel first creates a unique identifier for the product in cell C4 by concatenating the product code with "S."

Then, it searches the "Purchases" range for matching values. It looks for instances where the concatenated product code and "S" match any values in the "Purchases" range. When a match is found, Excel adds the corresponding value from the "QtyPurchased" range to the total.

In essence, this formula calculates the total quantity of a specific product that has been sold by searching through the "Purchases" data (which may seem counterintuitive since it's used for sales, but it's based on the unique identifier created by concatenating the product code with "S") and summing up the quantities associated with the product's unique identifier.

Likewise, the formula used in SaleValue column is

=SUMIF(Purchases,CONCATENATE(C4,"S"),CostofPurchases)

Other formulas are:

1. For Profit column

=I4-(G4*H4)

2. For InventoryQty (Inventory Quantity) column

=E4-H4

3. For Inventory Value column. 

=K4*G4

Drag and apply the formulas to desired columns. 

In the end do TOTAL. 

The final Inventory Management and Inventory Transactions Table is shown below.

Whenever any transaction is being made it automatically updates the inventory management table. 

Isn’t it satisfying?

Conclusion

Inventory management is essential for the smooth operation of any business. Excel provides a cost-effective and versatile solution for managing your inventory efficiently. By implementing the formulas and interface tips mentioned in this article, you can create a powerful inventory management system tailored to your business's needs. Excel's flexibility allows you to customize and expand your inventory management system as your business grows, making it a valuable tool for long-term success.