Inventory Tool with Pilgrim Place

This year, my team at SOURCE—led by Lead Consultant Rei Imada—was paired with Pilgrim Place, a local nonprofit and senior housing community for individuals who have worked in humanitarian, religious, or charitable organizations. At the start of our client relationship, we agreed to take on an ambitious project: develop a robust online store to sell the handmade items created by Pilgrim Place’s resident artists with the intention of significantly increasing the income flowing into the organization. At the start, our team recognized that a complex inventory tool would be needed to keep track of each and every craft that would be listed and sold on the organization’s new web store. Given my background in financial modeling and Excel, I volunteered to take on the task.

Right off the bat, I realized that the inventory tool should essentially mirror the structure of “Festival,” Pilgrim Place’s annual carnival—comprised of two dozen craft “booths”—during which the items that are hand-made by the resident artists are sold. Such a spreadsheet would allow the organization to easily keep track of each and every item that was listed and sold based on its respective category. Thus, I created twenty-six tabs in the Excel spreadsheet, with each one corresponding to a specific “Festival” booth. Examples of tabs included “Woodcrafts,” “Pottery,” and “Paintings.”

Next, I sought to build out a categorical system in which each item listed would include along with it any relevant information pertaining to that specific craft. Thus, for every “booth” tab, I built out ten columns, with the item “I.D.” category placed in the far left-hand corner. Other labels included columns for “Artist Name,” “Materials Used,” “Item Price,” and “Item Net Income.”

After building out each tab, I decided to create a centralized “Master Sheet” in which any item could seamlessly be added and would automatically be linked to its respective “booth” tab. Using the “filter” function in Excel, I was able to create a sheet whereby any craft—and its corresponding relevant information—could easily be added and would automatically be copied to the tab corresponding to its booth.

Subsequently, I sought to build a “Dashboard” for which any individual could easily search for a specific item or artist and find all corresponding information. I divided this new tab into two main sections. For the first, I built out a search function using Excel’s “VLOOKUP” formula, which linked the “Master Sheet” to the “Dashboard.” After linking each row to the search function, I was able to design the system such that any item can be search by its “Item ID” number and all relevant information—including “Artist Name,” “Materials,” etc.—is automatically populated. Next, I set about building the second component of the Dashboard, which would serve as a click-based search interface for the names of specific artists. Using Excel’s Pivot Table function, I was able to create a list of all artists that is automatically linked to the Master Sheet. Thus, one may click on the name of any artist, and all artwork and net income corresponding to that specific individual is generated. This two-pronged Dashboard provides a user-friendly method of locating all relevant information for any artist or craft item.
Finally, I realized that it would be prudent to integrate a financial projection system into the inventory tool. To do so, I linked the sum of the Net Income stemming from each “booth” tab to a centralized page. Then, I created a series of columns to track each booth’s Net Income by month. Next, I added a Compound Average Growth Rate function, which averages the profit growth rate exhibited over the span of several months. Then, the function uses the derived growth rate to project a future month’s Net Income and present it in an easy-to-understand graph. Such a design will allow Pilgrim Place to make insightful projections to properly forecast their budget for the future.

Creating the inventory tool for Pilgrim Place’s online marketplace was perhaps the most technologically-challenging—yet equally rewarding—task I have completed for SOURCE. I look forward to using the variety of modeling and Excel skills I have acquired to better impact our clients. Truly, there is no substitute for learning-by-doing.

by Josh Dorman