Case 1 Problem: Merging Lists From Different Sources Into A Single Spreadsheet
A retail call center manager needed to monitor employee phone usage and determine if any of its staff was abusing their phone privileges. The manager needed to combine the lists of inbound and outbound calls that he had with the customer phone number information from corporate headquarters. The manager needed to find a faster way to merge the data than manually inputting the information.
Case 1 Solution:
TAP Solutions created a spreadsheet using an Excel macro that combined the two sources of information and compared the data. Phone numbers that didn't match up with the customer list were separated out to determine if the employees were abusing their phone privileges. The new spreadsheet also enabled the manager to see the phone activity in each division. This information allowed the company to balance the call load throughout the call center and saved them overtime costs.
Case 2 Problem: Converting PDF Files Into Excel
A manufacturer received orders from its clients in large PDF documents. The company had to print them out, and manually enter the information into the system. The manual process required many hours and contained numerous errors.
Case 2 Solution:
Under the new system, the PDF files were converted into text files which could be read by Excel and turned into spreadsheets. The Excel format enabled the company to directly upload the orders into their processing system. The company could also then analyze the data and import and export the information as needed. Eliminating the manual data entry step resulted in much faster, more accurate order processing.
Case 3 Problem: Automating Accounts Receivable Using Excel
A large manufacturing company did business with a large multi-store retailer. Each shipment to each store created a receivable invoice. There were thousands of receivable invoices each month. When the stores sent a check, they also sent an electronic report of what invoices they were paying. The manufacturer was then manually allocating the payments to clear the invoices and balance the accounts.
Case 3 Solution:
The manufacturer needed to automate the allocation and balancing of the payments. TAP Solutions wrote a routine in Excel that read the customers electronic reports and converted the data into a format compatible with the manufacturer's system. This allowed the payments to be uploaded into the receivables system where they were matched up and cleared automatically. The program also created discrepancy reports of the items that didn't match and enabled the company to address problems much more quickly.
Case 4 Problem: Automating Billing Processes
A consulting firm required its employees to manually fill out monthly billing reports that went to the billing department to be entered into the system by hand. This resulted in a lot of wasted time and paper. Sometimes the consultants forgot to include some information on the report, resulting in incomplete billing for the month, or entries were incorrect resulting in lost revenue.
Case 4 Solution:
TAP Solutions created a master report sheet in Excel that consultants could fill out electronically while they were at the client's office or at the end of the day. The report could be e-mailed in at any time. Then TAP Solutions created an Excel program that combined and re-formatted all the billing reports into a file that could be uploaded directly into the billing system to generate invoices. The Excel programs made it much easier to bill clients and keep monthly reports current. It also prevented errors and saved time by eliminating the manual copying of consultant's reports into the invoicing system.
Case 5 Problem: Synchronizing Data and Finding Errors
A human resources department had a problem keeping its employees' insurance reports in sync with the insurance company's reports because each company used a different data format and data structure. Because of the different formats and structure the HR staff had to manually reconcile the reports of more than 1,000 employees. The company needed to find a way to automate the reconciliation and eliminate errors.
Case 5 Solution:
TAP Solutions wrote an Excel program that matched employee's names and addresses with their insurance packages, and highlighted inconsistencies between the two reports. The new program was also capable of reading both formats for items like dates and abbreviations. After implementing the new program, all the HR staff had to do was quickly glance at the spreadsheet and deal with the discrepancies or issues indicated by the report with the combined the data.
Case 6 Problem: Transferring Excel Data Into Word Files
An online retailer downloaded customer orders into an Excel file. Then an employee created packing slips by copying and pasting all the pertinent information from the Excel file into a Word template. The employee also had to manually change the customers formatting to make it compatible with the company's style. The retailer's manual conversion and correction process was extremely time-consuming.
Case 6 Solution:
TAP Solutions created an Excel program that read the orders, automatically corrected the formats and transferred the data to packing slips. The new process now took seconds to convert the Excel format orders into Word packing slips instead of minutes and sometimes hours
Case 7 Problem: Using Excel To Calculate Estimates
A window blind company owner never knew how exactly how much material he needed for any single project. He would estimate the amount, and put that figure into Excel. When he estimated too much, it cost him money for materials he didn't use. When he estimated too little, it delayed the project's completion because he had to get more material from his supplier. Plus, he used Excel as if it was just a paper chart.
Case 7 Solution:
TAP Solutions used Excel to create a worksheet for each order as part of a workbook for orders to his suppliers. After all the individual orders were entered into the workbook as worksheets, the new Excel workbook calculated how much material was needed for was needed for the entire group of orders. The worksheet saved the company time and money because the owner could order the right amount of material from his suppliers and allocate it correctly to the customers' jobs.
Case 8 Problem: Using Excel To Sort Data
A large retailer received many inquiries by email from a contact form on its website. When the staff went to read the emails, there was a large amount of spam mixed in. The staff member responsible for the incoming emails had to weed out the junk mail and copy the legitimate inquiry information into Excel spreadsheets. The retailer needed to find a way to sort the emails and to speed up the intake and distribution process.
Case 8 Solution:
TAP Solutions wrote and installed a code into the retailer's website. Emails that were sent from the website link automatically carried the code so they would be recognized on the retailer's email processor and kept. Emails from spam and sources outside the website didn't have the code and were kicked out. TAP wrote an Excel macro that went through all the email and deleted the junk mail that didn't have the inquiry code which eliminated the need for manual review. Next the macro sorted the information out from the emails and distributed it to the appropriate sheets based upon the type of the inquiry. The user could then quickly and easily send the inquiries to the appropriate responders throughout the company. The new process saved many hours and greatly improved the company's response time.
Case 9 Problem: Using Excel To Produce Reports For Multiple Stores
A manufacturer received a multi-store report with no page breaks from a vendor. The manufacturer needed separate pages for each store. The manufacturer wanted to stop manually going through the report and putting in page breaks before printing the report.
Case 9 Solution:
TAP Solutions wrote a macro that went through the large report and logically figured out the page breaks according to the store codes and line breaks.
Case 10 Problem: Compiled data over different worksheets.
A client was given a spreadsheet with more than 200 worksheet. Each sheet contained one client's information. There were about 30 data elements on each sheet. Her task was to combine all the information into one sheet so it could be used as a master roster. She didn't want to copy and paste each field, which she estimated would take a week.
Case 10 Solution:
We came up with a formula that combined all the data in seconds, saving her hours and hours of copying and pasting each cell.
Case 11 Problem: Processing repetitive data
A client came to us with some extremely large data files that he got from his clients on an ongoing basis. This data was uploaded into their main system to update information. The problem was the clients often supplied invalid data, causing the updates not to load and causing delays in processing the information.
Case 11 Solution:
We wrote a spreadsheet that reviewed all the data files and showed all the errors before they went into the main system. This allowed them to be cleaned up before the main load, thus saving countless reloads.