On the surface, identifying duplicates in a list of businesses sounds simple. But, as five University at Buffalo School of Management accounting students discovered, when you’re sifting through more than 29,000 records that date back many years, the reality is far more complex.
In the “Data Analysis for Accountants” class last spring, teams of master’s students partnered with UB’s Internal Audit and Financial Management offices to use real data to complete a semester-long project. Each team received a short prompt — for example, “find duplicates in the vendor master file” — and had to devise 10 independent tests to reach their objective.
“To be successful, the students have to get creative and think critically about all the tools they could potentially use,” says Lorrie Metzger, clinical assistant professor of accounting and law, who taught the course. “With published case studies, the data is clean and uniform. But in the real world, data can be messy, so this project pushes them to use all the skills they’ve learned in class.”
In other projects, students developed a scale to evaluate risk factors for various purchase card transactions, created tests to find duplicate payments, and combed through data to find UB employee vendors or transactions with non-UB shipping addresses.
Every project was a win-win — students gained valuable skills essential in today’s business world, while UB’s offices received assistance with large-scale projects and a fresh perspective on their procedures. Kara Kearney-Saylor, director of internal audit at UB, says that as UB prepares to implement a new university-wide procurement system, scrubbing vendor data and evaluating their processes are important steps in the transition process.
“There’s a big future in data analytics, and students who have this skill set will really set themselves apart from others entering the workforce,” Kearney-Saylor says. “If I was looking at résumés, and one candidate had the ability to use tools like IDEA or ACL, that would definitely move them into my ‘to interview’ pile.”
The team working on the vendor project was made up of Joey Chen, Jeanna Huang, Nick Swartz, Wei Wang and Jennifer Wu, all of whom subsequently graduated with their Master of Science in accounting in May 2018. To start, they cleaned and prepared the data using TeamMate Analytics in Excel to standardize the address formats and other fields. From there, they divided responsibilities based on their individual strengths and experience in data visualization, coding, design and other areas.
“The most significant factor in our success was cooperation,” Wang says. “I was lucky to be on a team where every member was willing to share, communicate and be creative.”
To accomplish their goal, the students could use any software or method they chose. Their first few tests were fairly straightforward. In one, they compared the name, address 1 and address 2 fields across vendors, reasoning that if all three were identical on multiple listings, those vendors were the same, even if there was a typo in another field.
But to catch every duplicate, they had to think bigger. In another test, they extracted all of the P.O. boxes, regardless of which field they were in, to find matches between vendors. For one of their last tests, they built a macro to rearrange all of the data alphabetically within each vendor file to identify more duplicates.
“I learned that data can be very powerful, but only if you turn all of the raw data into useful information,” Huang says. “We also enhanced our proficiency with the software we learned in class, as well as our critical-thinking, teamwork and public-speaking skills.”
As the semester ended, the teams presented their work, along with their recommendations for best practices and process improvements, to Metzger, Kearney-Saylor and representatives from UB’s Financial Management team. Kearney-Saylor says the projects help the university improve efficiency and reduce the likelihood of errors.
“I was incredibly impressed with the students,” she says. “They showed great initiative and delivered strong presentations — another important skill in the workplace.”
As a surprise bonus, the vendor duplication project was one of only three student projects nationwide selected to receive the Jack and May Stewart Student Project Competition Award at the Accounting Information Systems Educators Association conference. On June 22, Metzger accepted the award on behalf of her students and presented their work to colleagues from other universities.
“Through this project, our students worked with real data, real business processes and real problems, gaining important skills and providing a service back to UB,” Metzger says. “For other universities, it’s really a model of ‘looking in your own backyard’ to find valuable learning experiences that can benefit everyone involved.”