Effective inventory management is vital for business owners, but it can also be useful at home. Whether you want to document home inventory for insurance purposes, keep track of personal possessions that you have in storage, or plan food and beverage quantities for an event, inventory tracking can help you stay organized. Businesses can keep operations running smoothly by managing stock to avoid surplus and tracking inventory to see which products are performing best. To save you time, we’ve collected the top inventory templates for Microsoft Excel, all of which can be downloaded for free. They’re easy to use, can be customized to fit the needs of your business or home project, and are compatible with Excel 2003, Excel 2007, Excel 2010 and Excel 2013. We've also included links to similar templates in Smartsheet, a spreadsheet-inspired work management tool that makes inventory management easier and more collaborative than Excel. With Smartsheet, you can take your inventory tracking to the cloud, so you and your employees can update and access your inventory from multiple locations, without having to email spreadsheets back and forth. You and your team can check quantities on your mobile devices and set automated alerts for when to take inventory and to notify you when quantities reach a certain low, so you can stay on top of re-orders. You can also create a web form to collect order requests, so employees can request a new order or add a new product to the inventory, keeping all the info you need to know in one place. Benefits of Using Inventory Templates The free templates below vary depending on whether you’re managing business assets, personal possessions, food stock, or some other type of inventory. But here are some of the basic benefits individuals and businesses can gain from using templates for inventory management: • View current inventory levels: A template can provide easy access to the quantities of various products currently in stock, which is a vital first step in keeping operations running smoothly. • Manage reordering: Tracking inventory will help you accurately identify when products need to be reordered as well as which items are discontinued. • Create business statistics: View sales patterns, identify best-selling products, and track your top suppliers. • Protect your investments: This applies to home users seeking insurance coverage for personal property as well as companies tracking computer hardware, tools and equipment, or merchandise. Knowing what you have in stock, where it’s located, and what it’s worth will help you maintain assets and know when it’s time to upgrade. • Save time and money: Especially for small businesses, a template allows for easy data entry, quick access to product and order details, and a customizable format that can change along with the business. Basic Inventory Control Template Simple and easy to use, this basic inventory control template is perfect for a small business or any business that doesn’t need a lot of bells and whistles to manage inventory. View inventory by item name, description or ID number as well as unit price. Track the total quantity and value of your current inventory, reorder dates, and discontinued items. Managing inventory is straightforward with this template because of its clean design. As with all of the Microsoft Excel templates included on this page, the columns can be customized for your business, and calculations are completed for you. Asset Tracking Template Keep track of your business assets like computer hardware, equipment that needs to be maintained, and tools with this free asset tracking template. This is a simple asset tracking system intended for smaller businesses, but it allows you to view not only the quantity and value of your assets, but also warranty dates, supplier information, physical location, serial numbers and more. To modify the template, simply remove any columns that you don’t need, or add new columns to increase your level of organization and detail. Home Inventory Template In case of a natural disaster, fire or burglary, having a home inventory spreadsheet can make life a whole lot easier. Going through a traumatic experience and losing personal possessions is hard enough without having to remember what items were lost and how much they were worth. This home inventory template makes it easy to document your home assets, including model and serial numbers, purchase price, and current value. The spreadsheet includes a column for location, so items can be organized based on rooms in your home. There are also fields for insurance information so that you can easily access your policy number and insurance agent. Personal Inventory Template Like the home inventory template above, this template allows you to record personal belongings so that you’re prepared in the event of a natural disaster or other emergency. Effective inventory management is vital for business owners. We’ve collected the top inventory templates for Microsoft Excel. Free Excel Inventory Templates. Cut expenses, manage inventory, and ship effectively by using the Goods web database template. A personal inventory template is also a useful tool for simply tracking personal collections such as media, artwork, books and other collectible items. The template allows you to categorize your personal items, describe their condition, and document their value. Since this is a free excel template already created for you, it’s also a quick way to track your valuables. Equipment Inventory Template If your business is using costly equipment, use this free template for tracking the value, condition and location of your equipment inventory. Keep track of operating costs, depreciation, loan balances and more. Having all of this information easily accessible will help you know when it’s time for an equipment upgrade, which items are being used the most, which are most expensive to operate, and where equipment is being stored. Boost your operational efficiency and save money by accurately tracking your equipment inventory usage and costs. Discover a Better Inventory Management Tool with Smartsheet Smartsheet is a robust collaboration and communication tool in a familiar spreadsheet layout with other views and Sights to help you and your teams work in the ways that work best for you. Smartsheet enables you to have visibility across all aspects of your business, from tracking inventory to managing other business functions. Precise Biometrics uses Smartsheet to. They use Smartsheet in many areas of their business, including the management of their internal product inventory. To keep track of where the valuable samples are going, and to ensure inventory levels remain sufficient, team members enter the number of units they’ve given away and to whom. Built-in formulas automatically deduct the units from the inventory total, and conditional formatting highlights inventory levels when they drop beneath a certain threshold. To learn how Smartsheet can help your business automate collaborative work and help keep your inventory in ship shape. Or try Smartsheet for yourself with a free 30 day trial. There is no obligation and nothing to install. Download your Free Inventory Spreadsheet now! Learn how to manage your inventory, save time and kick-start your business in Excel with this free inventory spreadsheet. Microsoft Excel is one of the most versatile business tools available, and one that most of us already have, even if some of us typically avoid it like the plague. Therefore, utilizing it for and stock control is an easy way to cut costs, save time, and organize inventory, providing you’re willing to learn a few new tricks. While not a perfect fit for large businesses, or anyone with a few thousand different items in their inventory, Excel is excellent for inventory tracking, stock management, sales, data, ordering, and more than a dozen other functions. If you know how to utilize it correctly, you can generate a variety of highly valuable inventory formulas that will help you keep track of stock, sales, orders, and more. The following include five of the best practices for. Organize Based on Sales Quantity The easiest way to use Excel as a stock management system is to organize your data based on sales quantity. This allows you to create a flexible inventory sheet that updates you when you need to order items because you can see sales right at the top. It also keeps your most sold items on the top for easier tracking, so you won’t be scrolling through pages of data to find what you need. There are a number of ways to do this, starting with manual organization on a daily or weekly basis, which can be a pain (no really, please don’t even think about this, your brain will hurt). The easiest way that won’t result in carpal tunnel from hours of manual data entry is the sort function built into Excel. However, you can also use the Rank function, which is slightly more sophisticated. The Sort Function only requires that all of your cells are the same size, that you select all of them, and that you can find the sort function on the top of the page. (This means no merging two cells in one column and three cells in another, design isn't that important). Don’t forget, you’ll have to re-sort each time you update your numbers. You typically want to use Descending order if you want to rank sales from highest to lowest. (As you may have guessed, the sales numbers represented in the image are purely fictional and do not represent hard data of any kind) You can use the same SUM function with Sort to generate income reports by adding a value for the item. Then, when you update your sold items box, Excel automatically does the math for you (yes, it's that easy) to tell you how much inventory you've sold. You can further expand on this by adding sections for profit margins, taxes, and even smaller details like packing and shipping costs, but as you use the same SUM function here rather than advanced quantum mechanics, we won't go into detail on the process. Excel uses * as the multiplication symbol. Your sum should look like this: =Cell*Cell The Rank function allows you to take this process a step further by adding a specific rank (I.E. 1, 2, 3, 4, and so on) to your items. It's also more dynamic because it updates automatically as you adjust numbers. This means that you won't have to manually rank your items each time you add a new sale, so you won’t forget to update your sorting and accidentally purchase all of the wrong items. This formula is slightly more complicated, but still not rocket science (if it is, we want a raise). The formula goes like this: the first number is the cell you want to use, and the second numbers are the cells you want to compare your first cells with. So =Rank (Cell, Cell:Cell). This gives you a very clear numerical ranking for most sold items that automatically updates without you having to sort. This makes it easier to see, at a glance, which items have sold the most. Using Excel to Manage Outgoing Orders with a USB Barcode Scanner The main problem with the above function is that you have to manually enter all of your incoming and outgoing products. When you have a great deal of products, several people entering values, or a busy day, things happen. Most of us don’t have robots to enter our Excel data, so human error is inevitable. For that reason, a barcode scanner is the next best thing (to robots). Most USB and wireless barcode scanners are easy to program to integrate directly into Excel, and you can do so on separate lines or updating a single line. Programming for barcode scanners varies slightly by brand. The standard function for a scanner is to press 'Enter' or ‘Tab’ after adding information to a cell. This means that it will add each new scanned item to the next row or the next column. You can program it differently, usually by finding a scan code for changing the function, but we'll go over a relatively easy way to program using the original multiple line code. How it Works: Each time you scan a code with the USB barcode scanner, it automatically inputs the items SKU into your Excel document. For simplicities sake, we're using an extremely basic SKU, which you would not see in real life because the capacity for error is quite high when using a simple number. You can use anything you like, but you should avoid letter and number combinations that Excel will read as a string (i.e. As text instead of a number), unless you want to spend hours fixing code errors with “directs to empty cell” messages (fun, we know). If you have your scanner set to input the number of items scanned + the SKU number, you can easily generate running totals of ingoing and outgoing items, and all without selling your firstborn son for a magic solution. In order to do this properly, we have to make two assumptions. The first is that unless we assign additional values to the barcode scanner, it won't put in other items such as value, product description, etc. You can most likely program this fairly easily using your barcode scanner codes, but this will differ depending on your software. However, using a barcode scanner is usually a good way to track incoming and outgoing inventory, rather than values, so you don’t necessarily need this information. You can use the SUMIF function to calculate running totals for the same item on different lines. This uses a code that looks like =SUMIF (C5:C7,ES25,D5,D5:D7). This may sound more complicated than your high school trigonometry test at first glance, but it is essentially =SUMIF (Cell Range (usually all the way down the page), SKU To Look For (in this case, ES25), Sales To Add Up). For product remaining, you use the same SUM function used to calculate sales in the previous model. Once you get the hang of it, it’s fairly simple. The best practice is to set the document up with your original SKUS on the top, with your current totals, and use the most right bars for sold and remaining items. You could put them at the bottom, but you’ll end up having to scroll anyway. You can also use a macro function to grab your data and move it to a separate page if you want. The barcode scanner will enter items on the bottom of the sheet, but you will immediately see running totals on the top. You do, of course, have to program your barcode scanner to add scanned inventory into the column of your choice. If you use two different barcode scanners, you can use the same computer for both incoming and outgoing inventory, you just have to program the scanner to add to the 'current stock column', rather than the 'sales column'. This allows you to quickly and easily update your running totals, whether you’re getting shipments, or making sales. Just remember to label which scanner is which; otherwise your inventory will be a mess anyway. Use Office 365 Excel in the Cloud One of the main limitations of the traditional Microsoft Excel program is that there isn't really a feasible way for multiple people to add to the same document at once, unless you count backseat driving. While you can solve this with multiple Excel documents, today's technology presents another solution. Microsoft has their own cloud solution with Office 365, but you can also go for Google Drive or a number of other free online solutions, but keep in mind that free isn’t always better. Office 365 allows for multiple users, real time updates, live backup, and of course, you can have several users editing the same document at once. A cloud inventory management system is especially useful if you have multiple people, multiple locations, or several computers for updating the inventory sheet. The most valuable function is, of course, the automatic updates. Plus, if you download a copy of Microsoft's OneDrive to your computer, you can keep a backup copy directly on your computer to ensure that you can access it in case of an Internet outage. Generating Inventory Reports Inventory reports and analysis charts are relatively easy to generate, providing you have your inventory organized properly. You can choose to create reports on a daily, weekly, bi-weekly, or monthly basis, and create quarterly and yearly reports. The easiest way to do this is by setting up your basic parameters for your inventory and then dragging and dropping the sheet to a new Excel workbook. This automatically copies all of your work to the new one, which gives you a clean inventory page for each day, week, month, or however long you choose to keep records. You can also drag and drop all of your monthly records into one file to set up a yearly inventory, with individual sheets per month for easy organization. You can also generate charts using your data, but it’s up to you to decipher them at a later date. Create Running Inventory Totals Creating running inventory totals is a must if you want to easily keep track of your remaining stock. There are two ways to do it. For smaller businesses, or those with only a few large ticket items to keep track of, single inventory pages are the most convenient, because they allow you to allocate one page to each product. For larger businesses, or for those with more products, you can create a running inventory for sales, how much you've made, and how much product you have, unless going through over 1,000 sheets to see individual product reports sounds like fun. The simplest example is deducting sales from your original stock figure e.g. SUM(D8-E8), replacing the D8 and E8 with specific cell locations that you want to subtract from each other. The best example here is to create an original stock line, and use that to deduct your sales from. This gives you an instant total that updates whenever you change the number in either cell. Importantly, you can update the cell using either a manually entered number or using a scanner. Don’t forget you can use your good old friend Autofill to populate cells using the same formula. Select the cells that contain the data that you want to fill into adjacent cells, then drag the fill handle across the cells that you want to fill. This will stop you having to repeat the formula in multiple boxes (for multiple products). If you want to keep better track of a few large ticket items, you might want to use individual sheets per item, which uses the same functions, except you set up one sheet per item and set your scanner to input different on specific sheets. Conclusion Excel can be an extremely versatile inventory tracking system for businesses, especially if you know how to utilize it to its best effect. While some of the formulas may seem a bit complex if you aren't familiar with using them, it's relatively easy to learn, and allows you a lot more flexibility when managing your inventory in Excel. Plus, they allow you to reduce the manual labor and mind numbing data entry by making Excel and machines like barcode scanners do most of the tedious work for you. However, as the demands of your business grow, you will need to utilize a more sophisticated to ensure you are well-equipped to handle an increase in stock and sales.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2018
Categories |