The Inventory Locator

I designed the Inventory Locator tool to aid the inventory process by automating ordering products and tracking the location of over 79,000 items in one single location.  By creating this tool I cut down 85% of time spent by the team on this project, doubled the amount of inventory we were able to move in one group, and provided a service to other departments in the form of inventory data that was previously unorganized across hundreds of spreadsheets.

 

What it was:

 

Before there was the Inventory Locator tool there was a collection of 122 (and counting) programs stored in individual excel spreadsheets. When each program began our six-level acquisition process my coworkers and I were burdened with hours of clicking back and forth, mass copy-pasting, disorganized lists, and at a high risk of either overlooking products or forgetting outstanding orders. We tracked each program separately using color codes and email chains. A typical shipment size was 2-5,000 products across multiple programs, anything more and the risk of oversight became too great.

One day, I had enough.

I decided no longer would I spend hours scrolling through excel sheets thousands of rows long, no longer would I keep a mental tally of who had responded to my product requests. It was time to tame this beast of a project once and for all.

When I started working for Houghton Mifflin I had no experience using Microsoft Access, but after a year of watching my teammates use tools, build custom queries, and doing quite a bit of quality assurance checks in tools myself I felt that I had a decent understanding of how they worked and how they correlated with Excel Docs. The Inventory Readiness project was our only one without a tool, so I decided I would make one from scratch.

What I did:

 

For weeks I spent my evenings and free time painstakingly combining every single spreadsheet into one massive, all-inclusive access table. When I finished, the spreadsheets were no more, instead, I had 79,155 records all in one nice, neat place.
But what good is that? Consolidating them wasn’t going to help me or my associates with the ordering or the tracking or the status updates. I needed to push further.

For two more weeks, I spent my evenings experimenting with areas of Access I’d never explored. If A connects to B I can see this, if the program is tied to the ISBN’s I can view them this way, if I enter these parameters I’ll wind up with X result. Slowly and with many errors (most notably the tragic data loss of Tuesday the 12th) I formed the shell of something that exceeded my own expectation.

I created a control panel of steps that were color coded in chronological left-to-right order. A user needs only start in the top left, and by the time the bottom right button was clicked the shipment would be complete. All of it. Not just a single program.

It was time to show the boss.

I paraded into her office one sunny Monday morning and presented my tool. She was impressed I had squished so many separate things together, but what really caught her attention were the tools bells and whistles, the extra flair I had experimented with to satisfy my own curiosities. Steps that used to take us hours to accomplish were now solved with the click of a button. Planner codes that previously required a tedious decoding process had been automatically resolved. Lists that used to take time copy/pasting together now automatically exported in one long, complete list that was ready to send. I had her attention.

What it became:

 

With her blessing, I was allowed to work on the tool during work-hours. I designed a user-friendly layout for each of the forums, formatted a tracker that updated live as each user worked through their assignments, I even automated our emails to be pre-typed and addressed, we only need to add the list and press send. When a librarian responded with available products checking them off one by one was a thing of the past! The future was bright, shining, and automated.

With the polished tool, my precious creation, finally complete the day of the demo arrived. And I, someone who feels horribly uncomfortable with public speaking, was ecstatic. The launch went off without a hitch. I cut down on the time spent on Inventory Readiness by 85%, I was even asked to make a sister tool for the rest of the department to use. Several months have passed and the tool has been incorporated into our standard procedures. I was asked to train all relevant staff how to use the tool and publish a detailed user guide for the company to see.