Excel: Inventory Data Management

Overview:

A team of Data Maintenance Officers manage inventory data for a master database and for other businesses ERP systems.
The data being maintained consists of: Suppliers details, products, descriptions, pricing, special quantity pricing, barcodes, promotions.
The supplier would supply the Data Maintenance team with a price list in Excel format containing all of their products and pricing that will be coming into effect.
The data team would:

  • Manually clean the suppliers price file
  • Manually extract the data from each of the systems in to one Excel file.
  • Manually match the suppliers data using formulas.
  • Manually segregate the data to separate sheets for further processing.
  • Manually write in formulas required to process the data.
  • Further process to create a reports showing price changes, discontinued items, new items and compare barcodes.
    This was all handled in Excel and can result from 30 lines to over 400,000 lines of records.

After understanding each step of the process, we were able to automate and streamline over 80% of the process in Access and Excel using VBA.  After implementing the automated process, the data team would:

  • Clean the suppliers price file to a suitable template.
  • In Access, set minimal parameters and press a button to start processing.
  • The result would export across multiple sheets within an excel ‘project’ file, containing the data the original report would contain.
  • Excel add-ins designed for the data task to further assist and streamline checking the results before distribution.

Further Excel macros were designed to split the data out of the main working file into separate files and reports after it has been checked, ready for distribution to each of the clients.
Further Excel macros were designed to prepare each of the clients files in a format ready to import into their ERP systems to update the pricing and product data.

 

(After some years, this was then developed into a web application to incorporate multiple ERP systems, various data sources and the ability to scale – see our post called WebApp: Inventory Data Management v2.0)

Problems:

  • Staff spent a considerable amount of time manually handling data. From cleaning to matching to adding formulas and generating various reports.
  • Often times, data would become corrupted due to incorrectly placed formulas or incorrectly formatted cells.
  • When it came to large data sets, performance of calculations in Excel would slow.  This would cause loss of productivity for the team.
  • Processes were very repetitive and mundane, leading to low staff morale and simple errors.
  • If steps in the process were missed, this would result in bad output at reporting stage often leading to starting the process again from scratch.
  • The task required staff who needed to be trained in advanced excel usage.

Solutions:

  • The solution was to break the tasks up between Access and Excel.  Access was used to process the data and Excel was used to review the output and make corrections. This alone saved days of data processing.
  • Automating the process in VBA cut the procedure down by 80%, greatly reducing the error and data corruption rate.
  • Updated Macros for Excel improved performance and reduced errors.
  • Data integrity checks were implemented at key stages.
  • Macros to generate an import-ready file for the ERP systems according to the import template.
  • Staff morale was greatly improved, their time was then used to benefit other areas of the business and to work on other exciting projects.

Contact US

Fill out the form below, and we will be in touch shortly.
Contact Information
What can we do to help:

Thank you for viewing your sample site!

We take the stress out of websites for you and offer quick, easy web design so you can get your business online as quick as possible.  Start growing and get exposure now!

Below is what you get when you sign up to our site:

  • Unlimited website content updates*
  • Professional email address
  • Online Contact forms
  • Domain name
  • Web Server Hosting
  • Full site maintenance, updates, backups and restore

*Content and images need to be supplied

We also offer additional growth packages to Jet your business to the sky!

  • SEO keyword reviews ($2500 per month)
  • Marketing Content Generation
  • Online Ecommerce Store
  • Online Booking & Appointment System
  • Online Payment System (with Paypal/Stripe/Square)
  • And MORE!

Let us know below if you would like your site started

Lets Get Started!