Creating Simple Inventory Report on a Spreadsheet

Simple Inventory Report made easy in Excel Spreadsheet

banner

One of the key issues in supply chain management is inventory management. Inventory is money. This means that we actually hold something in the form of goods, with the purpose of, at some point, the goods will either be consumed or sold.

Visibility of how much of the inventory we hold is the backbone of any inventory management. We really need to know, not only the current stock level, but more profound than that like how is the movement of these items, how many are these items in/out, and so on.

To model the inventory report in a spreadsheet, first, we need to know the basic algorithm in simple Inventory Management. Look at the picture below:

Simple Inventory Visual

In a (mostly) simple inventory management, what we really want to capture is the movement of each item that we are holding. The dark red circle represents the out-flow. Think about when your items are being transported (outbound) from the warehouse to retail stores, warehouses, or even going into production. The dark blue triangle represents the in-flow. Think like the replenishment process (inbound) from the production (or another warehouse) to the warehouse. Anything that adds to your inventory can be categorized as that triangle.

All of these processes, can happen periodically, let’s say each day. Nowadays, most ERP can capture it in every second. For more advanced topics, you might find the item status and the item category, to name a few. To keep things simple, let’s start with a more basic yet foundational in most of the inventory report.

To get the current inventory level, we can just add the logic like this:

Inventory level (t) = Initial Inventory(t-1) + IN(t) — OUT(t)

with t = period

So, for example, the Inventory for 8th August (t = 8) is Initial Inventory at 7th August (Initial Inventory (7)) + How many items goes IN at 8th August (IN(8)) — How many items goes OUT at 8th August (OUT(8)).

The Approach

The report will be based on Excel Spreadsheet. For data handling, we will be using the tabular approach for as long as possible. I recommend you check this post before continuing.

I am a fan of storing each table in a separate worksheet. I don’t know, it just makes it a little bit organized to store each table with specific info in an individual sheet. But, you can actually create them in the same sheet. Stick with your own preferences.

So, in this case, we will have 3 sheets to contain our data — INITIAL tables, IN tables, and OUT tables. With an additional sheet containing our report/dashboard. 4 sheets in total.

For dummy data, I’m using the RANDBETWEEN formula in Excel to generate a random number of quantities, and this amazon product id dataset from kaggle to get the sample of 15 product_id and desc.

Report Design

Initial Sheet

This sheet is just as important as the other sheet. The initial sheet will contain the initial number of stock at the beginning of our period. Unless you have a brand new warehouse or just did some renovation, you will not be having empty stock at the beginning right?

The structure of this sheet will contain two important columns which are product_id, and inital_qty. Here is the description of each column:

• product_id = the unique key (id) of each product.
• initial_qty = total number of quantity at the initial stage.

Initial Sheet Structure

So, let’s fill our data with RANDBETWEEN[100,350] formula.

Initial Sample Data

Another purpose of having this sheet is that imagine when you have a lot of items, with perhaps a thousand records each day (in or out). With an Excel limit of around 1 million rows, you will have a hard time dealing with this issue.

The solution to this problem is to simply break the report into a more compact period of time, for example monthly or quarterly. So we just need to duplicate this template and change the initial quantity with the ending quantity from the previous period.

So, we now know that this sheet serves the purposes which are:

1. To give the information on the beginning stock quantity
2. To provide flexibility in case of splitting the report in a periodic way.

Turn this range into tables by pressing CTRL (Command on Mac) + T in one of the cells, and rename it to whatever you like. I’m renaming it to Initial. Turning this into tables makes our reference clearer and more dynamic because we will often input new data into the tables.

Initial Sheet DONE.

IN/OUT Sheet

The IN Sheet will capture the in-flow data of each individual item. Same with The OUT Sheet will also record the out-flow data of each personal item. The structure of the sheet is also the same, containing 3 columns: date, product_id, and in_qty.

Here is the brief description of each column: (same column name will not explained)

• Date = date for each record.
• product_id.
• in_qty/out_qty = the quantity of the movement of items.

IN Sheet Example

Any duplicate product_id on the same date is allowed, since perhaps you will be having multiple numbers of outbound/inbound in a day.

For this test scenario, you might want to random the IN quantity way bigger than the OUT because there will never be negative inventory (sometimes negative can be interpreted as something, but let’s stick with these rules of thumb).

Turn this into tables, and rename it to IN in the IN Sheet, and OUT in the OUT Sheet.

IN/OUT Sheet DONE.

Report Sheet

The report sheet will contain our final end inventory, with its calculation. There are at least 6 columns in here:

• product_id
• desc = description of product_id
• Init Stock = the initial quantity for each item (source: Initial Sheet)
• IN = the total IN during a given period of time.
• OUT = the total OUT during a given period of time.
• Inv Qty = end inventory during a given period of time.

There are additional touches in this sheet, which are the date picker. In calculating the initial, IN and OUT, we are using SUMIFS to match the selection criteria. Here is the look of this sheet with its function formula:

Structure of Report Sheet

For the date until, enter your specified date to get the inventory report during the given period.

You can turn this into tables, but this is unnecessary.

Report sheet DONE.

Closing Remark

We are done with the simple inventory report-based spreadsheet. All of the tables are stored in a tabular fashion so the next person (or you) would be happy to do some further analysis of the data.

One simple analysis that you can do is to make the data visualization. Like in the picture below.

Simple Visualization to mail directly to your managers

For more advanced topics in the inventory report, you might want to add:

1. The category for each item, like the family product, sub-category product, and whatever.
2. The status for each item, like good, bad, or damage.
3. Multiple warehouses, DC, or facilities.

You can accommodate these by adding a new column to each of the sheets.

Adding data validation in a multiple number of warehouses

That’s it. Thank you for reading. Download the resources (Excel files) here. Support me by giving it a clap, or tip me a cup of coffee. Follow me if you want more content in Supply Chain, Logistics, and Data Analytics.