Inventory Management in Excel: Download Excel Template with Formulas

Everybody says, “Tracking your inventory can become a complicated task if it’s not managed properly.”

However, I disagree with that the correct statement is –

“Tracking your inventory can become a complicated task if it’s not managed properly with a proper tool.”

Yes, proper tools, gone are the days when people used to use paper, pen, and log sheets (yeah still there are some people using pen, paper, and other stuff). Nowadays, to survive in a competitive environment, you need to use the most efficient tool present in the market.

Here, in this blog, we won’t be talking about the most efficient tool, but yeah, we will be talking about the second most potent tool, and that is excel spreadsheet.

Spreadsheets are the simplest way to manage your inventory and related data like stock, sales, purchases, orders, suppliers, and basic level reports for free!

It is a pre-known fact that Excel spreadsheet have various limitations( as I said, not the best tool but yeah potent enough to help a start-up grow), but it can work for small businesses and start-ups in the beginning. Excel is a low-cost inventory management solution but not the best one.

Well, you might think it’s a dated way to manage inventory, but still, there are some business start-ups with limited funds managing their inventory with a pen, paper, and log sheets. Of course, it takes a lot of man effort and chances of irreparable human errors that can cost you money, and losing money in the initial leg of your business career run is not something that you would like or aspire to do.

So, to reduce the errors and increase efficiency, you can start to control your inventory with the help of an excel. It is a cheap and proficient tool to manage stocks.

Excel is recommended to small scale businesses, especially the ones who have just started up their journey. Though it has its limitations, it’s still better than using pen and paper and log sheets.

With the help of advanced mathematical capacity, the software can comfortably be used to organize and operate different aspects of inventory and business management with ease. You can also use excel with other apps to get through challenging situations in business that require exceptional calculation skills.

Create group sheets, use filter search results, and get the information on the changes in inventory instantly. It’s pretty much easy to figure out the flaws and correct them quickly without wasting much time and money.

You can create your own excel sheet, and here are the things you need to create tabs for.

• Item number
• Item name
• Item price
• Item cost
• Item description
• Item stock
• Item location
• Quantity to reorder
• Quantity sold
• Time to reorder
• Sales quantity
• Total inventory value
• Inventory on hand
• Product active or discontinued
• Available to promise.
  And many more…

The only problem is that the more columns and rows you will create, the more things you will have to monitor and update on excel.

As mentioned before, you can use the formulas of excel to calculate total profit, total cost, total sales, etc.

Here are some of the formulas that you can use to calculate and manage inventory.

SUM

Sum formula can be written as – =sum(column name*column name)

The sum is used to sum up, the figures in two or more cells automatically. This can reduce your headache of summing up the numbers in every cell by using a calculator. Instead, you can just apply this formula and get the sum of the quantities.

Subtraction

Subtraction formula is – = (column name -column name)

This can be used to automatically subtract the sales from the inventory to give the inventory on hand.

This is a beneficial formula to ensure that you have a track on inventory on hand

Sort

You can use sort to sort the content according to the quantities in the cells. You can use the sort option to find products using their quantities, category, color, or cost price. Whenever you need to order the inventory, you can sort the inventory and figure out which products need to be ordered and in what quantities according to their performances.

Rank

Rank’s formula is – =Rank (Cell, Cell: Cell)

Rank can be used to differentiate the fast-moving inventory from the slow-moving inventory. You can also rank the stock based on quantities or cost price and accordingly plan your strategy to manage your inventory.

To learn to create inventory management excel through a video, watch the video given below.

You might be thinking, “I wish I had a ready-made excel spreadsheet.”

Well, your wish is our command. Here is the ready-made inventory sheet template of excel for inventory management, and don’t worry in the article ahead. We will be teaching you how to use this inventory spreadsheet excel with an example.

Just click and download the inventory tracker excel.

We’ve done the back-hand formulating and linking of the datasheets. The sheet is ready for you to enter data so directly, Get started!

Divide and enter all the standard information related to Products, Orders, and Purchases in their respective Sheets, which are already categorized for you. Information like Product Name, SKU, Stock location, Supplier, Current Stock level, Reorder Point, Sales Channel, Purchase Order, Purchase Quantity, etc. can be entered and tracked.

Using formulas that are pre-installed by us, Excel auto-fills, or calculates- Total amount of Order, Purchases quantities to be bought, Stock location, and many more values if they’re entered prior or once the necessary information is entered.

We’ll further show how this is done.

Update your Product sheet daily to record available current Stock level or when there are any changes in standard information like SKUs or suppliers or on the addition of new product(s).

Update your Order sheet when a new order comes in and gets confirmed on any of your sales channels. This could be done on a daily or weekly basis, depending on your business type.

Update your Purchase sheet when a particular product reaches near Reorder point and is needed to be restocked. At his moment, a new purchase order is placed. This could be done biweekly or more, depending on the business.

Uses of Excel Spreadsheet

You’ll find below-mentioned formulas with its applications in the Excel inventory sheet template with formulas preloaded and interlinking all the data!

Interlink the information

Certain information is dependent on other parameters which can be interlinked to cancel out manual calculation every single time. For inventory management using Excel, Selling Price is dependent on the Cost price and mark-up price. The mark-up price remains constant, but the Cost Price for each product varies due to factors like Vendor, Demand of product, etc. Using the ‘SUM’ or ‘SUMIF’ inventory management excel formulas, we have interlinked these three parameters to get the calculated Selling Price on its own. For every product:

Selling Price = Cost Price + Mark-up Price.

Mark-up price is pre-recorded.

Using SUM Formula, you get a calculation for Selling Price automatically, every time you enter the Cost Price.

Formula: “=SUM(G3, H3)”

Syntax: SUM(number1, [number2, …number_n])

Refer information

While keeping a record, the same information gets referred to multiple times. For inventory management, information like ‘Stock location’- which is attached to the SKU- is needed while dealing with

1. Purchasing- to know where to put that stock once it arrives
2. Picking- to see where the product is lying
3. Returns- to know where to put the product back.

When you have 1000+ products on hand, it is infeasible to record two or five or more information like the stock location for each SKU, again and again.

Using formulas like ‘VLookUp’ or ‘HLookUp’ in Excel, we help you duplicate the information as and when needed.

Example:

You want Supplier names from your Product page to the Purchase page, we have used SKU as the foreign key to look-up between the two pages/sheets and get the Supplier’s name displayed on the Purchase page, the very second you type the SKU!

Formula: “=VLOOKUP(D3,PRODUCTS!$D$3:$F$32,3,0)”

Syntax: VLOOKUP(value, table, index_number, [approximate_match])”

Pictorial representation of data

Analyzing data using only numbers and codes can make you go crazy! Excel provides various options for the pictorial representation of your data in the form of graphs, charts, diagrams, Smart Art, etc. This analyzes the data much more organized and easy to interpret.

Highlights when to act upon

At times your products run out of stock, and you are caught off guard! We have fed conditions into the inventory Excel sheet template using ‘Conditional Formatting,’ which signifies an alert for you when a stock count reaches or crosses the Reorder point. This helps in alarming you before the stock runs out without the need for you to go through hundreds of cells each time and avoid any mishap. You can also differentiate columns and rows by colors or gradients based on distinguishing features.

Conditional formatting can be done for complex tasks and interventions also.

Forecasting

Excel allows trend lines to be extended beyond the graph to offer predictions of future activity – and such forecasts can help businesses develop their future strategy.

Formula based forecasting can also be done using Excel’s ‘Forecast’ formula.

Forecasting for a y-value dependent on an x-value:

Formula: “=FORECAST(700, J3: J9, I3: I9)”

Syntax:“=FORECAST(x-value, known_y_values, known_x_values)”

You get the y-value displayed.

If you started with Excel recently, you might not have faced it’s limitations yet! But a single error via Excel can also be an expensive affair.

Forbes states Microsoft’s Excel Might Be The Most Dangerous Software On The Planet – this coming from Forbes, we think you’re starting to understand the criticality of dangers Excel comes with!

Inventory being one of the most critical assets of a retail business, needs to be monitored and controlled carefully. Managing inventory with the excel you might have upgraded from using pen and paper to a ready-made inventory management Excel template. But you need to keep this in mind that you are still using the second-best tool in the market, to reach on top you need to use the best form of inventory management tools.

Nevertheless, let’s go through the flaws and limitations of excel.

1. Managing Orders

• Orders that come in from multiple sources need to be recorded manually in Excel, which delays the order fulfillment process.
• Screening and searching orders become way too monotonous, even with SORT functions.
• Lacks prioritization of Orders as it can be arranged only via Date, Alphabet, etc. by which orders are fulfilled in chronological order, skipping priority, or special orders.
• Order status, stock count, and delivery are never in real-time as it gets updated minutes later. This decreases the Order fulfillment rate as more time is taken to fulfill and process them.

2. Product Categories

• Excel is full of rows and columns, making it a flat-face for analysis. It has a fixed layout that can’t absorb changes and unexpected scenarios, making it an inflexible tool.
• There are around ten parameters about a product- Product Name, Description SKU, Stock location, Supplier, Current Stock level, Reorder Point, Cost Price, Retail Price, Quantity ordered, and more. It is highly inconvenient to keep updating the respective numbers for each product again and again in hundreds of cells.

3. Lost Returned products

• It’s not possible to link a particular returned product to its respective Order in Excel. Separate Sheets for Returns needs to be created, which just shows returns data, and then connecting two sheets via V-lookup might just make sense, but it’s an inefficient way of dealing with Returns.
• When a product is returned, you can treat it in one out of the three ways: fresh stock, refurbishing or discarding. In Excel, storing this data segregated is a lengthy task as you have to sort each product every time into these categories.

4. Formula Fuss

• Choosing between n number of formulas and arranging the data in accordance can take up a lot of time, let’s not talk about doing it multiple times!
• Accurately applying formulas all the time is a tough task mainly because there are high chances of errors, miscalculation, misreading, and duplicating of the data.
• Entering formulas, linking data with each other, and applying various conditions to the data can be a time- & energy-consuming process which needs undue constant attention of the person recording it.

5. Narrow idea only

• No record reviewing or transaction history stored anywhere. If you want to find a transaction that occurred years ago, you need to put in many filters and press various keys to get the data out of Excel.
• No combined information of the whole inventory is available. Excel can’t ever show you the Big Picture perpetually. Eg. Data relating to multiple warehouses or selling channels, complete product range, etc.
• Excel can quickly get too complicated and limits quick data analysis or a clear overview leading to a lack of control.
• There is almost next to no provision for forecasting, reporting, and analysis. Basic mathematical formulas and graphs can’t predict real business scenarios.

Still, if you wish to manage your inventory using an excel template, click the image to get our exclusive inventory to an excel template for free!

Excel is a great inventory management tool, but let’s accept the truth; free things come with limitations and are not the best. Best things have a price, and if you are ready to pay that you can “excel “in your business, like literally.

So if you are a small business, an ambitious start-up, etc. or with less business in hand, you can use excel, and it will help you in growing your business, but what post that? We’re sure you’re building your business into a perpetual one, would you want to limit to simple and basic tools?

Of course not, so here’s a deal. You can download the ready-made temple of excel, and when you feel that you need to move on from excel to something better, you know, like,” the best way to manage the inventory.” You can come back to us and sign up for a 15 day free trial of an automated inventory management software and experience the change yourself.

And yeah, one more thing, you can opt for a 15-day free trial now. Also, there are absolutely no charges, and you can continue using the free Excel template as well.

• https://www.wikihow.com/Create-an-Inventory-List-in-Excel
• https://www.spreadsheet123.com/ExcelTemplates/inventory.html
• https://blog.appsheet.com/how-to-manage-stock-in-excel-three-free-excel-templates