Automated Excel Inventory Management System
Taylor Gee
The Challenge
As a rapidly expanding company, Nefertem Naturals outgrew their inventory and manufacturing management systems. Their existing systems involved repetitive data entry and did not track product performance or forecast business costs. When this began impacting their ability to grow and scale their business, Nefertem started looking for solutions.
Results
Since implementing SheetLogic’s solution, Nefertem is saving at least 4 hours a month—time they can dedicate to creating new products for their customers.
For a product with just 3 ingredients, Nefertem previously spent 90 minutes on data entry. Now they’re spending just 5 minutes on that task, reducing data entry time by 94%.
The Logic
SheetLogic’s goal was simple: Improve Nefertem’s inventory and manufacturing management systems using Excel. Our objective included developing a user-friendly, automated system that’s flexible enough to grow and scale with the business.
The new system focuses heavily on the user interface, utilizing dropdowns and lookup functions to make data entry as easy and error free as possible.
For inventory management, the products are broken into three tiers:
Level 1: Raw Materials
Level 2: Manufactured Products
Level 3: Bundles, Packages & Gift Sets
Level 1 includes a rolling purchase log that feeds raw material inventory levels.
Levels 2 & 3 include a space to enter sales for each level which are combined to calculate overall raw material demand, inventory, and production needs.
Each level also includes a bill of materials (BOM) which pulls from the previous level. All three levels are combined using PowerQuery to calculate overall totals.
Problem/Solution Breakdowm
Problem #1: Common data entry errors due to overly complex navigation resulting in wasted time and inaccurate inventory data.
Solution: User-friendly interface with color-coded navigation buttons and data validation to prevent common errors caused by data entry.
Problem #2: No way to adjust variables without manually changing each item.
Solution: Variables (such as batch sizes, labor costs, and material costs) that can be easily adjusted as the company and economy changes.
Problem #3: Adding and removing products involved updating multiple sheets.
Solution: Dynamic system that allows users to easily add or remove new products, bundles, and ingredients from a single page with updates that carry throughout the sheet/workbook.
Problem #4: No overall data output to indicate if the company is meeting goals; inadequate data to make growth decisions; no ability to estimate a rolling average cost per unit.
Solution: Summarized metrics to allow the business to quickly view overall performance and make vital data driven decisions.
Nefertem Naturals’ Testimonial
“We looked at manufacturing software first. It was all either much too cumbersome to learn and more advanced than what we needed for our small business or far too simple and unable to handle our needs as a growing business. Working with Sheet Logic allowed us to keep using Excel, but in a much more powerful way. They created exactly the right system for our business.
Working with Sheet Logic was so easy! Any question I had was answered quickly, and I felt confident they understood our needs right from the start. I loved that I could request a virtual meeting anytime I needed to and that the project was completed on time.”
- Brittany H. Owner
Like this project
Posted Oct 7, 2024
Developed an automated inventory management system in Excel, which streamlined data entry and improved product performance tracking and forecasting.
