Inventory Management In Excel

Learn more about what an inventory is, what are its types, the benefits of carrying an inventory management in Excel, and even greater alternatives of it.
Written by

This post is also available in: English Español

Don't want to read?
Listen to the article

Carlos' Opinion -
Inventory Management In Excel.

I am part of a family business that has run for nearly 40 years. 

The business has mainly been “running as we always have done”.

And when it comes to the development and further improvement of a small business, the inner family fights start with the new generations ideas of “better ways of doing things”.

It is not a secret of family businesses, that some of its main struggles are the ideas that we, the second generation, bring up to the table so that the business can change for better.

Or at least, we the second generation, think those ideas are for better.

In my experience, change will bring up discomfort, specially for older generations, since they have been working for years in the same way, and it might take some time to adapt or they might even refuse the new ideas, even if you give projections on income increase or savings. 

How many times have you heard, “if your business don’t adapt or innovate, it will get stuck or disappear”.

Probably, plenty of times.

One of the pain points that I have lived for sure in my own family business is the control of inventory.

We have used from manual scorecards to keep control, to later move on to the inventory management in excel.

Does an inventory management in excel is effective in the long run?

I don’t think so, but it was the quick and low cost solution for a small business to have control on the inventory.

If you are looking for how to do your inventory management in excel, here the team and I, created this article where you can learn about inventory management and how to create an inventory management in excel.

But I would also recommend you, that if you are planning to digitize your business and improve your inventory control processes, then analyze the use and implementation of an ERP System.

I tell you a bit more of my experience.

I needed a solution that helped me with a Point of Sale system, to keep track and control of inventory on an easier way, but also a system that was also connected to an e-commerce platform.

Therefore, here in Mexico I decided to use Bind ERP

As a small business, Bind ERP was an affordable solution that I started to use. 

In just a matter of 2 weeks, I set up all the nearly 170 products of the branch store that I am in charge, and in one week I built up the Shopify store to showcase online the products of my store online and on social media.

Now, everytime I receive new inventory, make a sale or change up the price of a product, the ERP system updates automatically as well on Shopify and Facebook Shop.

As a small business, there are already affordable ERP solutions that can help you have this integrations and help you to save time on updating your Excel Spreadsheets everytime you do a sell, or also to save time on the uploading and managing of products on your e-commerce store. 

So I would recommend, if you prefer to still save up costs and keep using excel, create your inventory template.

But if you are like me, that besides just managing your business locally, and also want to have an e-commerce platform that helps you on the digitalization of your business, then go for ERP systems like BindERP (if you are from Latin America), Odoo & Quickbooks that also have integrations with WooCommerce and Shopify. 

 

What is an Inventory?

We have all heard about inventories in Excel.

And nowadays it seems that whoever does not learn how to use this software is being left behind.

We will not only talk about inventory control in Excel, if you decide to continue reading you will know what an inventory is, what are its types, the benefits of carrying it in the software, and even a great alternative.

A company’s inventory is made up of its raw materials, its products in process, the supplies it uses to carry out its operations, and the products that are already finished.

Something as elementary as a bottle of glass cleaner used as part of a building maintenance program, or something much more complex, such as the combination of raw materials and sub-assemblies in the manufacturing process, can be recognized as inventory.

Carrying inventory can have a positive impact on the company because it gives them operational flexibility, allowing them to produce at a different rate than procurement, which leads them to issue higher volume orders.

Why are business inventories important?

The importance of controlled inventories within the company, monitored and ordered, comes from the need to be able to adequately distribute and supply the material that is available.

Regardless of how the latter may be classified.

You may think that it is not necessary to carry out an inventory for your type of business, but it is a reality that they are designed to help small, medium, or large companies.

And it is that this will make them have complete control of their merchandise, or generate reports of the economic situation of the business.

It should always be kept in mind that, in the case of not having an inventory, this can cause a company to stop production if it does not have what is necessary to supply it.

And, if you don’t count, you’ll never know.

From the moment you can know if you have the raw materials to work with, or inform a customer if you have product availability, it is vitally important to keep inventories.

After all, order in the work area is not only keeping everything in its place, but also accounted for.

Types of inventories.

The classification of inventories tends to be very varied and there are different points of view, which have been expressed by different authors throughout history, that help us to clarify what the types of inventories really are.

Inventories by form.

These are made up of all the materials used to manufacture the products. It is important to understand that at this point only those that have not received any type of manipulation or processing are counted.

These are made up of all those goods acquired by manufacturing or industrial companies and which are in the process of being manufactured. The quantification is made by quantity of materials, labor and manufacturing expenses applied at the closing date.

These are those goods that have been acquired by the manufacturing or industrial companies and have already been transformed to be sold as finished products.

These are those materials with which the products are manufactured, but which cannot be qualified in an exact way. For example, paint, sandpaper, lubricants, among others.

It is constituted by those goods belonging to the companies, commercial or mercantile, that are bought to be sold without any modification. It is a single account for goods available for sale.

Inventories by function.

This is carried out in such a way as to compensate for unplanned downtime risks in production. It also serves as a function of unexpected increases in customer demand.

This is what is required between two adjacent operations, or processes, where their production rates are not synchronized. This is what gives each of the processes the ability to function as planned.

This is made up of those materials that are moving up the value chain. It is composed of those items that have already been ordered but are not yet received.

This type of inventory is taken into account by the number of units produced or purchased, in order to reduce costs per unit of purchase or increase production efficiency.

It is accumulated when a company produces more than the immediate requirements during those periods of low demand, in order to be able to satisfy those of high demand.

From a logical point of view.

This refers to inventories in transit between levels of the supply channel. Other types of inventories that may be considered as pipeline inventories are work-in-process inventories within manufacturing operations.

Raw materials, such as gold, silver, and copper, are acquired both for price speculation and to meet the requirements of the operation. These are carried out in anticipation of seasonal sales.

This type of inventory becomes necessary when seeking to meet average demand over a period of successive replenishments.

This is a type of inventory that can be created in order to protect the variability in stock demand and total replenishment time.

These are those inventories that are carried when something is held for a long time, becomes deteriorated, expires, is lost, or is considered to have been stolen or taken away.

Inventory Management in Excel.

When we talk about Excel we are referring to a computer program that was developed by Microsoft, and that belongs to the Office package (office suite where you can find some other highly useful programs such as PowerPoint and the famous Word).

Excel differs from all other office programs because it allows you to work with numerical data. 

With these numbers you can perform basic arithmetic calculations, and also apply more complex mathematical functions.

And it is even possible to use different statistical functions.

The great popularity of Excel comes from the fact that it facilitates all the work related to numbers, since it also allows these data to be analyzed quickly and easily. This leads to the generation of reports such as graphs or pivot tables.

The category of computer programs in which we find Excel is that one of spreadsheets.

These were developed in 1960 with the purpose of simulating paper accounting worksheets, which would help automate accounting work. 

With the passage of time we can see how electronic spreadsheets have been replacing paper-based accounting systems.

And, although they were originally created with accounting tasks in mind, today they have countless applications, as is the case of inventories in Excel.

What is the need for inventories in Excel?

Inventory control in Excel was given under the need to automate processes which were previously carried by hand, and where you could fill books and books of data that, eventually could reduce the use of time.

It was not until we realized that with a couple of clicks, we can query any data we have stored in the inventory.

Then we began to understand that Excel can be used for more than what was originally sold to us.

Another of the most useful tools is the application of filters.

Where you can ask the program to show you only the data that is stored under a certain characteristic.

Such as, for example, goods that have already been shipped and you only want to view them quickly.

In the past, many people may have believed that the most important requirement to be considered for an important position in a company, regardless of whether it is private or public, was English.

But today, knowing how Excel works is just as important to recruiters.

A person who knows how to master Excel can be considered for places of vital importance where you need to record data from any type of inventory of a company no matter how big it is.

And it doesn’t look like this program will be on its last legs anytime soon.

Advantages of Inventory Management in Excel.

They help to avoid stock out.

That is, it will help us to avoid running out of the basic resources we need to develop our activity. For example, if you need sugar for your cake business, you will know when it will be necessary to get more.

You will be able to prepare promotions.

It may be that, among your products, or references, you find that there is a large quantity of a certain product that is about to expire, or that it is a seasonal item and will soon no longer be in demand by customers. This way you will know when it will be time to carry out a promotional action that will help you get rid of them.

You will know the value of the stock.

One of the main advantages of Excel inventories is that they allow you to know the value of the sum of all your stocks.

You will be able to detect the material that is expired, or in bad condition.

If you make a complete inventory with the help of this program you can always be aware of that material that happens to expire, or simply account for that which has been damaged.

It is a more than complete program to carry out inventories.

Nowadays many professionals agree that Excel should be taught in schools, since it is vital for more processes than we imagine. However, in the area of inventory, it is one of the best tools available.

Disdvantages of Inventory Management in Excel.

Not knowing the program.

We all know that the most important asset in any organization is information. But, when we do not have a person who is specialized in this tool this vital process of the company could be hindered.

Lack of security.

This is a program that is susceptible to be modified without being able to detect the change in a simple way, reason why many assure security problems. Apart from the fact that all this information can be stolen with a copy and paste in the computer file.

Loss of information.

Have you ever wondered what would happen if the program crashed, all the information would be lost? And it is very often seen when it is in use, nothing has been saved, and a dreaded unexpected shutdown occurs. Or the power is cut off to the machine where it is running.

You do not have real-time information.

This implies that you will not have everywhere access to detailed information on sales, items or merchandise, until you make an update and query. It is worse when it comes to having to keep records in more than one branch.

Video on How to Create an Inventory Management in Excel.

The best ways to start building up your own inventory management system in Excel is to follow up by watching.

In the following video, you can find out how to create an inventory management system in Excel from scratch using VBA. 

Websites with Inventory Management Excel Templates.

Or if what are you looking for is a solution that it is already given, there are some templates that could help you out.

Here are some available websites that have inventory management excel templates.

Alternatives to Inventory Management in Excel.

As previously mentioned, there are other alternatives than using the inventory management in excel.

Enterprise Resource Planning systems help also to keep control of inventory.

There are different companies that offer ERP systems.

One of the most known ERP open source systems is Odoo.

With Odoo, you can select the modules you need for your business, including also inventory management.

Another option is Quickbooks, which it is also aimed for small businesses.

Both of those platforms have integrations with e-commerce platforms such as Shopify and Woocommerce.

As well, companies like Bechtle are specialized on Microsoft Dynamics ERP implementation.

QuickBooks Inventory management.

Odoo Inventory management.

Bind ERP as an inventory management alternative for Mexico and Latin American Businesses.

When we talk about Bind ERP we are talking about an all-in-one management system.

This means that it has modules that are used for inventory management, purchasing, sales, finance, and reporting of the company in each and every one of its plans.

This can be hired using different plans, and for which you hire it you will have a robust electronic accounting module, an effective project module, and also a lot of additional functions that will make you see how beneficial is this system that serves as an alternative to inventories in Excel.

We all know that inventory software approaches are usually varied, so some of them do not suit every person’s needs.

However, in this case, you can be sure that this will focus on the aspects of the management of an SME, as is the case of purchases, sales, accounting, among others.

Another point that usually worries about this kind of programs is the fact that they also work by integrating with other programs.

Since, in a technological world as it is today, what does not adapt is of little use to us.

If you are interested in knowing how good it is in this sense, you should take into account that Bind ERP has Zapier, which is a great ally in terms of process automation, and this makes it possible to integrate with more than 1000 applications without having to install it.

Bind ERP’s prices are managed within different quarterly and annual plans that are based on the different stages of life that a company has, as well as the needs that can be witnessed in each of them.

The best of all is that, for those clients who need a little more personalized attention, they have the possibility of contracting additional packages that will satisfy those needs that had not been thought of at the time of using a standard plan.

So, we can now say that the price range of the different plans is from $3,840.00 MXN plus VAT (plan + order in annual subscription), to $16,200.00 MXN plus VAT (plan + growth in annual subscription).

Does it adapt to the budget you have for this task in your company?

The best thing about all this is that not only are we talking about a complete platform, but it is also quite simple to use. Although it has a lot of functionalities, managing everything it offers you will not be a complicated task. Especially if you have interacted with similar ones before.

In case Excel inventories have not been your thing, or you want to innovate with something different, you can try using this alternative.

We assure you that it will have everything you need to keep track of your business and a little more.

More about Digital Business.