Managing thousands of school devices is one of the greatest IT challenges for schools—especially for those that are investing in new tech-heavy initiatives or growing quickly.
So how does a K-12 IT technician keep track of it all? Well, one of the most cost-effective solutions for school stock management would be to use an inventory spreadsheet in a platform such as Google Sheets or Microsoft Excel.
Such solutions are admittedly low-tech, but they can be effective if used properly. Read on to see how best to use a spreadsheet software for managing inventory.
Will Excel Work for School Inventory Management?
Smaller schools, or those lacking in spare funds, may find themselves considering Excel or Google Sheets as a basic asset-tracking option. It can definitely be a viable solution in these cases, provided you understand the limitations of homegrown inventory management solutions when compared to specialized inventory management software. The lack of automation is one of the most significant drawbacks of Excel, which means users must manually initiate actions like submitting help desk tickets when an asset changes. Excel also doesn’t feature built-in analytics, which gives visibility into a district’s IT performance.
That said, templated or self-made asset management spreadsheets have been — and can continue to be — used effectively in smaller schools and those that have a limited number of assets to manage.
Pros and Cons of Using Excel for Inventory Management
Like any other option in this world, the decision of whether or not to use an Excel-based inventory management tool requires weighing its pros and cons. But keep in mind that even if you start out using Excel, you may likely need to implement a dedicated solution at some point as the number of devices grows — and how soon that almost inevitable switch occurs should be factored into your decision as well.
Pros of Managing School Inventory in Excel
- Free and accessible to all team members
- Offers useful features like tabbed pages, pivot tables, formulas and automatic calculations
- Automatic calculations on existing inventory show when new assets need to be ordered (This capability assumes the inventory is kept current in the Excel sheet)
- Formulas can be inserted to show statistics (However, these statistics must be created by hand, making them prone to human error)
Cons of Managing School Inventory in Excel
- No automation – An Excel spreadsheet isn’t automated, so it needs frequent attention to keep your inventory current. An ideal inventory management solution should include automation features that make tracking more manageable.
- Limited visibility over assets – Spreadsheets provide limited visibility over assets. This limitation directly affects your school’s ability to verify that maintenance, replacements, and deployments occur on a proper schedule.
- Manual data entry – Admins often use reports based on inventory data to justify the purchase of new assets. The need for manual data entry by multiple users creates the possibility of human error, causing administrators to under- or over-order assets for the next school year.
- Not very scalable – Spreadsheets were never designed to manage the stock inventory of a school district. The manual processes that it requires will eventually result in an unsustainable number of data errors.
- Not self-documenting – Spreadsheets require users to create their own documentation, which can increase workloads. This task will have a lower priority than many action items, resulting in a long delay and making it difficult to train new IT hires.
Despite these disadvantages, an Excel sheet may still be the best solution for schools with limited budgets. Following best practices in building spreadsheets will maximize the chances for this solution to succeed.
What to Include in an Inventory Management Spreadsheet
Inventory and facilities managers can use free Excel inventory templates, but you can also buy fully built-out asset-tracking templates or create your own.
Most free templates consist of worksheets with cells that primarily contain data and formulas, while paid templates will often include more thorough layouts that include ActiveX controls, automatic formulas, charts, and preset external data ranges.
A workbook will likely be a critical aspect of an inventory management system because it stores all of its worksheets in one file, typically in xlsx format. All Excel operations take place within the workbook.
Inventory management templates allow users to begin the data entry process on a worksheet, which can include the following columns:
- Product name
- Product details
- SKU
- Serial number
- Current stock
- Reorder point
You can modify the template by adding other columns such as item name, sales quantity, whether the product has been discontinued, and total inventory value. Excel has no limit on the number of columns a spreadsheet can have, but it does become more difficult to manage inventory as the number of columns increases.
You can also use your pivot tables in your template to summarize other tables. Pivot tables work by aggregating the table’s individual terms within discrete categories, which can include statistics like averages and sums. A pivot table then applies an aggregation function to the grouped values.
Bear in mind that using Excel spreadsheets for inventory management leaves room for error because users must enter data manually. As a result, inventory managers must frequently review the worksheet for errors.
Tips for Using Excel for Inventory Management
Excel spreadsheets require a lot of manual updates and reviews to keep their content current. However, the following best practices will greatly improve your experience when using Excel instead of inventory management software.
1. Avoid Mistakes in Data Entry
Errors are particularly troublesome when it concerns K-12 budgets. Avoid issues by implementing processes around who adds or updates data, how frequently reviews are done, and when full-scale audits will happen to catch errors before real trouble is caused.
2. Update Spreadsheets as Changes are Made
It’s important to keep Excel files as current as possible. Since Excel can’t provide real-time information, it can’t be a reliable source of information regarding inventory statuses. The only workaround in this situation is for all asset managers and team members to update inventory spreadsheets as soon as possible when updates occur.
3. Use the Cloud
Excel’s version control is limited to offline sheets, which prevents multiple users from saving on top of each others’ spreadsheet updates by only allowing one person to use a spreadsheet at any given time. While this is effective, it also severely limits collaboration between users. Using Excel in the cloud via OneDrive allows technicians to invite multiple people to edit sheets in real time while preventing them from overwriting each other’s changes.
4. Consolidate Data
Schedule time at the end of each month or quarter to consolidate the data in your Excel spreadsheet, making it easier to read and compare with prior months/quarters. Adding the data for the reporting period to a pivot table can resolve the issue of Excel not being able to analyze historical data from just a spreadsheet. This process is time-consuming, but it does allow you to track deployments, updates, and asset lifecycles over time.
5. Conduct Audits
Excel requires users to enter data and asset histories manually, which inevitably results in human error. As a result, determining inventory lifecycle estimates and reordering assets can be inaccurate. An audit policy that tracks devices on paper in addition to the Excel document allows managers to review the inventory at the end of each day to correct any errors. However, it also requires additional effort to maintain the spreadsheet while doing the double-work of using a paper system and retaining your paper trail.
6. Back Up Your Data
Make regular backups in the event of data loss, whether it’s to the cloud or an on-premise server. A recent version of your inventory sheet should always be available in case it’s needed for a restore, so backing up spreadsheets at the end of each work day may be necessary. It also provides the added benefits of synchronizing data across all of your devices, so you can edit it from anywhere.
7. Know When it’s Time to Upgrade
As stated earlier, using Excel for inventory management will work, but only if you have a realistic understanding of Excel’s limitations in this area. Excel will struggle to meet a school district’s needs once it reaches the size of a medium-sized business, due simply to the time it’ll require to maintain the spreadsheet manually.
As your inventory grows, it’s critical to be aware of your and your team’s ability to keep your spreadsheet up to date. Once the process becomes too time-consuming, the time your team saves with a proper inventory management software will far outweigh any low software costs of using Excel.
Top Features of an Effective Inventory Management Software
Each K-12 asset manager has specific needs for their inventory management tool, but there are several consistently top-rated features to look for in your next inventory software solution:
- Real-time updates and analytics – See all assets, their lifecycle status, and their location details at all times. API-driven Integrations also allow the software to generate a live view of inventory levels by location.
- Track inventory value – Asset values can be tracked based on their age, intended lifespan, and condition. A good inventory management system should be able to update accounting data in real time.
- Inventory auditing tools – Having a high or unlimited number of available SKUs will be useful as your school and its initiatives continue to grow. Software that includes easy auditing tools, such as device barcodes and barcode scanners, will also keep inventory data sheets updated in real time.
- Streamline reordering processes – Software levels can be tracked against your ideal inventory list, and app automations can instantly generate purchase orders for approval and submission, guaranteeing school stock levels never run low for devices and related assets.
Best Inventory Management Solutions
Simply said, the best inventory management tool is the one that works for your school in its current state. Excel inventory management templates for school asset management are specifically designed for use in K-12 schools, which can help you implement an Excel solution very quickly. And if an Excel template is what your school needs right now, then use one!
However, if your school would benefit from an inventory management software that meets all of your inventory management needs (plus some facilities management and help desk ticketing tools as well), then asset management software from Incident IQ may be something for you to consider. Schedule a demo to learn how a comprehensive support platform can transport your district’s IT support.
