14 mins read

How to Create an Inventory Management System in Excel - Part 2

how to create an inventory management system in excel 2
Daphne Blake

By Daphne Blake

Create an inventory management system in Excel for restaurant success

Make your inventory management system work for you. In this article, learn how to create an inventory management system in Excel to boost profits and your reputation.

Why you should learn how to create inventory management systems in Excel

Traditionally, inventory management systems consisted of paper sheets or logbooks, a handful of pencils, and a gaggle of well-meaning but bored employees. It took hours or days to complete, and it almost always resulted in miscounts, bad math, or damaged or lost sheets. Aside from the money lost and the time wasted, mistakes left employees waiting for someone to take the fall. That kind of pressure reduces staff productivity, which further affects your bottom line. And then there's the issue of delayed re-stock orders or missed counting mistakes that result in overstocking perishable items. Those overstocks will almost certainly spoil before you can use them, and even if you can run a promotion to get it all sold to customers, you're doing so at a significant loss of projected income. More wasted money, more stress, more strain on your budget.

It's easy to see how a simple mistake can snowball out of control, damaging your brand's reputation. You may have already experienced this, which is why you're here, seeking new and better methods to improve efficiency and increase your business' profits, all without adding more expenses to an already tight budget. Learning how to create an inventory management system in Excel is the perfect solution to all your inventory problems, but don't just take our word for it. Read on for the details of why this is the right way to handle inventory management and how you can jump in quickly, with minimal effort, and maximum profits. Some of the details might surprise you.

What can Excel do for inventory management?

how to create inventory management systems in excel 1

One mistake on a single paper inventory sheet can cause problems ranging from minor annoyances that take a couple of hours to resolve, to major roadblocks that completely stop business for days, weeks, or more. One mistake can start an avalanche of problems you may not be able to stop. Sometimes, even if you can stop the avalanche, your employees will likely be frustrated and stressed, leading to poor performance and burnout.

Paper is fragile, and humans aren't infallible machines. That means the old inventory management system was destined to fail right from the start. The concept was solid—count your product, track it, make adjustments—but the execution needed a lot of work, and that's where Excel comes in. Microsoft Excel lets you skip the paper inventory logs and checklists and avoid long hours hunched over a desk crunching numbers and re-checking your math. Focusing instead on digital spreadsheets and mobile readiness, Excel is a fast and simple, but powerful tool for food services of all types. Going digital means flexibility, ease of use, and a long list of time and money-saving applications. Learning how to create inventory management systems in Excel unlocks a lot of doors and opens your business up for potential spikes in profit.

Learning how to create inventory management systems in Excel promotes better productivity and encourages efficiency by cutting out the cumbersome aspects of old-style inventory management. No more paper, no more pencils, no more cranky employees racing through the chore just to get it over with. Remove the obstacles instead of trying to power through them. That's what Excel does. Instead of fragile paper sheets, broken pencils, lousy penmanship, and miscalculations through exhausted mental math, you get fast, accurate counts and a user-friendly experience. Happy employees, happy management, and happy profits.

How Excel inventory management works

how to create inventory management systems in excel 2

Excel spreadsheets utilize advanced mathematical formulas that run behind the scenes, keeping your inventory lists up to date and accurate. The program offers the option to organize multiple sheets into intuitive categories, and you can search in an instant or filter by any criteria you set—alphabetical, item type, location in your store, just to name a few. Try doing that with a paper inventory sheet or old logbook. This is efficiency at its finest, and it's all done in seconds with the click of a mouse or tap of your finger.

On top of the benefits listed above, when you add a productivity app to your inventory system, you get the ability to make corrections on the fly. No more having to redraw and reprint paper sheets when someone makes a mistake. Just correct the error right on your computer or mobile device and deploy it to everyone connected. That kind of fast response to errors means no costly overstocking and food spoilage and no risk of running out of supplies. Employees can do their jobs quickly and confidently, knowing that their digital spreadsheets are always correct and that the math will be done for them. Put the right tools in the hands of your staff and you will see reduced stress almost immediately and higher profits on budget day.

How to create an inventory management system in Excel

how to create inventory management systems in excel 3

Once you learn how to create inventory management systems in Excel, it is simple to make a variety of spreadsheets that can cover more aspects of your business, not just inventory. Think about employee records, task lists, tracking of sales and promotions. Add the right apps, and you can do even more with your inventory management system, including receiving instant warnings when mistakes pop up, which helps you improve your food service business' bottom line, employee morale, and your brand's reputation. That said, we're going to focus mainly on the inventory management aspect of Excel spreadsheets since that's the cornerstone of a successful business- Awesome inventory management.

Our article will cover some basic steps on how to create inventory management systems in Excel, but we won't go into the math formulas. While the computation capability of Excel is an essential part of your new inventory management system, that aspect is beyond the scope of this article. There are too many factors unique to each company for us to cover in one article, but we won't just leave you hanging. After you learn how to create an inventory management system in Excel, you can do a quick search for simple math formulas in Excel and add the ones that are suitable for your needs. If it sounds complicated, don't worry! Once you've created your spreadsheet by following this guide, you'll have a good understanding of Excel's layout, and the math formulas will be, easy to plug right in.

1. Open Excel. This is usually as simple as a double click of the icon on your desktop. Due to the wide variety of computers and devices that are capable of running Microsoft Excel, we can't say exactly where your icon will be. However, if yours is not on the desktop, you can try running a search for Excel. Most computers and mobile devices come preinstalled with a search function.

2. Once Excel is open, and before you type anything, take a look at the blank sheet on your screen. You'll notice the letters running along the top, and the numbers running down the left side. The letters refer to columns, and the numbers refer to rows. Each small box is called a 'cell.' Click cell A1, which should be the first small box in the upper left corner of the blank spreadsheet. It should be located directly under the letter A at the top of the screen and to the right of the number 1 on the left of the screen.

3. Depending on your settings, the cell will either highlight a new color, or you'll see a thin frame around the cell. The color will vary and isn't important right now. With your cursor flashing in cell A1, type 'Item' without the quotation marks. This will be the label for this column.

4. Now, look at the next cell over, one space to the right. It should be B1. So, you should still be on the top row, but one column over, directly below the letter B at the top.

5. If you're in the correct spot, type 'Amount' without the quotation marks, which will be the label for that column.

6. Click cell C1, which is one cell to the right from the cell you just typed in.

7. Type 'Cost' without the quotation marks.

And that's it. In about ten minutes' time, you have created a very simple spreadsheet that can be used as your main inventory management system. When you fill this in, using your labels as guides, you'll start to see how this method of tracking inventory is quicker and easier than writing it all down by hand. You can add as many labels as you want or need, including things like applied discounts, spoilage or loss, and the selling price in your store.

Remember, there are no math equations or formulas yet, so this inventory sheet won't be able to do the math for you. As mentioned above, you can (and should) add the proper equations later to make full use of your new inventory management system, but that doesn't mean you can't start using it right now. Filling in the information you already have will speed along the process of tightening up your inventory system once you add your formulas.

How to use your Excel inventory management system

Now that you know how to create inventory management systems in Excel, it's time to learn how to use them. We strongly suggest you create a new sheet for each category you'd like to track. Use the guide above to create the same layout for each. That means one for the freezer foods, one for the refrigerator, one for the panty and dry goods, and one for the spices, at the very least. This is a good time to create inventory sheets for the rest of the business, too. After all, if it cost money to get into your restaurant, it will cost money to repair or replace it, so it should be listed.

Inventory everything

how to create inventory management systems in excel 5

Once you've learned how to create inventory management systems in Excel, you can begin tracking everything—even if you don't have the math formulas in the system yet. If your restaurant is already open, gather your best employees and start counting everything you already have. An important note about this step- even though this is your first inventory with your new inventory management system, be sure to have a separate list for each of these categories. Keeping this separated now will help you fine-tune your budget later. The more categories you have, the easier inventory duties will be, and the faster you can complete purchase orders when the time comes.

1. Consumables first -

Ingredients and components for all your dishes and desserts are the most critical aspect of your restaurant, obviously, so these need to be counted. Anything that can be consumed should go on your first list. That includes the sugar packets on each table, the salt and pepper in the shakers, and the condiments, too. If it can be eaten, put it on the Consumables list. Later, you can separate out the items into different food categories, but for this step, we just want to get all the consumables listed somewhere. The beauty of digital spreadsheets is that you won't ever have to type or write this stuff again—just copy and paste it into a new sheet. But, it has to start somewhere, so get that food counted!br>
2. Cleaning and other supplies -

Paper products—like paper towels for the handwashing stations, napkins, and toilet paper for employees and customers—soaps, scrubs, mops and brooms, all go on this list. Even the buckets need to be counted. Did it cost money to bring into the store? Then it needs to have a place on your inventory list. These items will frequently be replaced, as cleaning chemicals can be harsh, and with daily use, equipment can break quickly. While not expensive individually, these items can add up over time, and if you haven't been tracking each mop you've replaced or each pack of sponges, you could end up wondering where all that money went. Count it, log it, and track it. Your accountant will love you!br>
3. Furniture and fixtures -

Furniture and fixtures often go overlooked on inventory day, and that's partial to blame on the archaic methods most restaurants are still stuck using. It's extra work, and since these items don't need to be often replaced, they get ignored. Now that you know how to create inventory management systems in Excel, you can be sure you won't forget these essential bits. Even though these items seem permanent, the reality is that they can still be damaged, worn out, or even completely broken. If they weren't counted on your inventory sheets, how would you know how much money to set aside for replacement or repair? Count them now, even if you never have in the past, and you'll be well on your way to a more efficient inventory management system.br>
Repeat this process with other sections of your restaurant, if you have more than we've mentioned above. Once you're done, be sure you save each sheet as something easy to understand. We suggest calling each sheet Starting Inventory and then the category name and date. This will help identify it quickly when you need to check your logs. Being able to search in seconds is one of the best time-saving aspects of learning how to create inventory management systems in Excel. br>
TIP- This process can go a lot faster, smoother, and with fewer mistakes by using a mobile inventory system. These apps use mobile devices—like smartphones and tablets—which means no paper, no bad handwriting, and real-time updates that can be shared with everyone.

Transfer past paper inventory sheets to digital format

For established restaurants with a history of past inventories, transfer the last one or two paper inventory counts to a new sheet in Excel. Label each one with the date of the inventory count so you can easily identify them during a search. These older sheets transferred to digital format will help complete any reports or logs you create in the future. It gives you a wider view of your restaurant's history, helping you track trends and predict customer behavior. This step is even easier if you have an inventory management app that can instantly compile the information you're looking for. Another benefit to adding these older sheets is that you can see how much money you're saving by switching to this new digital system. Your numbers should improve drastically, and it's nice to see that on the screen.

Deploy a powerful inventory management system.

Learning how to create inventory management systems in Excel is only the first, but very important, step in developing a streamlined and user-friendly inventory system. When you couple your new inventory sheets with the power and flexibility of an inventory management app, you will be able to see months of data within seconds, generate detailed reports that span months, and organize everything in the blink of an eye. While learning how to create inventory management systems in Excel makes stocking and purchasing preparation much easier than slogging through paper logs, imagine not having to manually search your digital files, either. Connect your new Excel sheets to mobile inventory management systems that allow you to access your data anywhere on any connected device, create and share logs and reports, view real-time counts of your inventory to prevent shortages, and receive error reports instantly before they become much bigger problems. Add some productivity apps to the mix, and you've set yourself and your restaurant up for efficient work, happier customers, and more profits in the bank.

Are you ready to get started?

We've spent quite some time singing the praises of learning how to create inventory management systems in Excel, and we hope we've piqued your interest. If you'd like to learn a little more about putting technology to work for you, read more here.

27 cta content inline and exit intent

Attend a webinar

Our scheduled webinars are a great way to learn more.

Sign Up