admin
Do you run a small business and want to keep track of your inventory and business assets?
Maybe you want to know how many products are available, how much stock value you have, or how many office assets your business owns. You may not need expensive inventory software for this. A simple Excel file can help you maintain a basic and useful stock overview.
In this lesson, we are going to create a simple Inventory and Asset Management System in Excel.
This workbook will help you maintain:
Asset items used by your business
Trading items purchased for resale
Item categories
Opening stock quantities
Sold quantities
Remaining stock
Estimated stock value
Category-wise dashboard summaries
Before we begin, remember one important point:
This Excel file is a simple manual monitoring tool. It does not automatically connect with your sales system or online store. You need to update the sold quantity or stock quantity manually using your sales records, preferably every week or month.
Let’s start step by step.
01. Create the Excel Workbook
First, open Microsoft Excel and create a Blank Workbook.
At the bottom of the workbook, you will see the worksheet tabs.
We are going to use two sheets:
DASHBOARD
Data Input
Now your workbook should look like this:
Sheet Name | Purpose |
|---|---|
DASHBOARD | Displays inventory summaries |
Data Input | Used to enter item and stock information |
We will not work on the Dashboard immediately. First, we need to prepare the Data Input sheet because the Dashboard will get its information from there.
Rename the First Sheet
Right-click the first worksheet tab.
Select Rename.
Type:
DASHBOARD
Press Enter.
Create and Rename the Second Sheet
Click the + icon at the bottom to create a new worksheet.
Right-click the new sheet tab.
Select Rename.
Type:
Data Input
Press Enter.
Step 02: Create the Data Input Sheet
Open the Data Input sheet.
In this sheet, we will maintain two separate item lists:
Asset
Trading
What Is an Asset Item?
An asset item is generally used by the business for its own operations. It is not normally purchased for immediate resale.
Examples:
Office laptops
Office chairs
Printers
Desks
Air conditioners
Office equipment
What Is a Trading Item?
A trading item is usually purchased or held for resale to customers.
Examples:
Mobile phones
Stationery
Grocery items
Electronic accessories
Clothing
Toys
Household products
The exact accounting treatment may differ depending on the business and accounting policy. However, for this simple Excel system, we will use Asset and Trading as two practical item groups.
Step 03: Add the Data Input Headings
In the Data Input sheet, create the following headings for the Asset and Trading sections.
For Trading items, we will also add two additional columns:
Step 04: Understand Each Data Input Column
01. Item Code
The Item Code is a unique identification code assigned to each item. It helps you identify an item quickly without depending only on its name.
Examples – AST001/AST002/TRD001
02. Item Name
The Item Name is the name or description of the item. Use a clear and understandable item name so that you can easily identify the item later.
Examples – Office Laptop/Office Chair/A4 Notebook
03. Item Group
The Item Group identifies whether the item is an Asset or Trading item. For this template, use only these two values: Asset / Trading
Examples: Office Laptop – Asset / Wireless Mouse – Trading
04. Category
The Category describes the type or classification of the item.Categories help us create a category-wise summary on the Dashboard.
Examples of Asset Categories: Furniture / IT Equipment
Examples of Trading Categories: Electronics / Clothing
05. Unit
The Unit describes how the item is measured or counted.Use the same unit consistently for each item.
07. Unit Cost
The Unit Cost is the cost of one unit of the item.
Examples:
One laptop costs 250,000
One office chair costs 25,000
One blue pen costs 50
The Unit Cost is used to calculate the total stock value.
Stock Value Formula
Stock Value = Quantity × Unit CostStep 05: Enter Sample Asset Data
Now, enter some sample Asset items into the Asset section of the Data Input sheet.
You can use the following sample data:
Step 06: Enter Sample Trading Data
Next, enter sample Trading items in the Trading section.
Formula for Balance Qty
In the Balance Qty column, enter this formula in the first Trading row:
=E10-G10
Step 07: Create the Dashboard Layout
Now open the DASHBOARD sheet.Create a title:INVENTORY DASHBOARD
We will separate the Dashboard into two sections: Asset Summary | Trading Summary
Then create the following headings:
Step 08: Dashboard Calculation
Now that we have completed the Data Input sheet, we can start building the DASHBOARD.
The purpose of the Dashboard is to convert the detailed item data into a simple summary. Instead of checking every item one by one, you can quickly see the total number of items, available quantity, and estimated stock value for each category.We will create these calculations separately for Asset and Trading items.
Item Count
=COUNTIF(‘Data Input’!D:D,DASHBOARD!A5)
Total Quantity
=SUMIF(‘Data Input’!D:D,DASHBOARD!A5,’Data Input’!E:E)
Total Stock Value
=SUMIF(‘Data Input’!D:D,DASHBOARD!A5,’Data Input’!I:I)
Managing inventory and business assets does not always require expensive software. If you run a small business and need a simple way to understand what you have in hand, an Excel inventory dashboard can be a useful starting point.
With this workbook, you can maintain separate records for Asset and Trading items, organise products by category, record opening quantities, update sold quantities, and calculate the remaining stock and estimated stock value.
The Dashboard makes it easier to understand your inventory at a glance instead of checking every item manually.
However, keep in mind that this is a simple manual inventory monitoring system. It does not automatically synchronise with your sales system, website, POS, or accounting software. You need to update the stock information using your sales records and verify it against your physical stock regularly.
Start with a few items, practise the formulas, and improve the workbook as your business grows. Even a simple, well-maintained Excel file can help you make better inventory decisions and avoid losing track of your stock.
