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.
Case 12 Automatically producing promotional sheets and website data feed
A local importing company had some old software that handled the website datafeeds and tied it to its weekly promotional sheet that was sent to their clients. Unfortunately, the software was old, buggy and could not handle the growing business. The process was getting to be extremely time-consuming and frustrating.
Case 12 Solution:
TAP Solutions evaluated the current software with the staff, finding out what they liked about it and what they wished they could do. Then we developed an Excel spreadsheet where the staff could enter the data in one place. The spreadsheet produced the promotional sheet automatically along with the data feed for the website. It completely replaced the buggy, old software.
Case 13 Streamlining project estimates and producing the needed documents.
A local contractor wanted a way to produce an estimate while on a job site. He also wanted the spreadsheet to produce the other needed documents such as contracts and permits automatically.
Case 13 Solution:
TAP Solutions made an Excel spreadsheet that he entered in all the data once, and it automatically spread throughout all the other forms. This way if the contractor needed a form, all he had to do was click on the proper tab, make any tweaks and print it out. It saved a lot of time typing in things over and over.
Case 14 Making a calendar interface with an auto dialer.
An education company used a calendar to keep track of all office appointments. Then two days before the appointments, staff would call every client. If the client didn�t speak English, somebody would call and confirm the appointment in that language. This proved very time consuming, and sometimes it did not get done. The staff decided they needed an automated process, but no one knew how to have their calendar communicate with their auto-caller software.
Case 14 Solution:
TAP Solutions wrote an Excel spreadsheet that was able to accept the calendar data and convert it into a file that could easily be uploaded into the auto-caller software along with time and codes to tell it what language to speak.
Case 15 Streamlining schedules
A large maintenance company had six different departments feeding various scheduled and emergency appointments to the scheduling and planning department. Each department submitted the incoming data in different formats, and sometime critical data was missing. This resulted in scheduling nightmares. Additionally, for legal and reporting reasons, the company needed to keep logs of what was done, when and where.
Case 15 Solution:
TAP Solutions developed a standard input form for each department that had data validation embedded into it. When somebody needed a scheduling report, all they had to do was enter the date range. This pulled in all the data from all the sheets and produced a master scheduling sheet for the date range. The company now could see what needed to be done, which made scheduling more efficient.
Case 16 Standardizing the academic-grading process
A local college wanted to standardize the way professors tracked and assigned grades. The current method was deemed impractical, inaccurate and inefficient.
Case 16 Solution:
TAP Solutions developed a customizable spreadsheet so professors could grade the way they wanted while still keeping all the needed record-keeping information and backup.
Case 17 Eliminating the adding machine
A large maintenance company needed to do an analysis to find out how mechanics were spending their time and how much time was being spent on specific tasks. The clerical staff used an adding machine to figure it out.
Case 17 Solution:
TAP Solutions wrote a spreadsheet that took all the electronic work orders, extracted the needed data and automatically created the dashboards that management wanted and needed.
Case 18 Tracking events more efficiently
A company that ran networking groups tracked events activity and money manually. Everyone involved had a different way of doing things. It was a nightmare for the corporate office to reconcile the events, payments and costs.
Case 18 Solution:
TAP Solutions met with some of the group leaders and management to see what each side needed. Information was downloaded from the ticket purchasing system and produced a spreadsheet that with all the event information. We used a laptop at the event to check in people and gather additional information. After the event, the spreadsheet was emailed to the corporate headquarters. Since all the information was all gathered into that spreadsheet, it was easy for corporate to gather what was needed.
Case 19 Creating a preflight checklist
An aircraft charter company needed a preflight checklist that each crew member could use on his or her laptop before taking a trip. It had to be simple, yet provide a warning if something was not correct or information was wrong.
Case 19 Solution:
TAP Solutions developed a spreadsheet that was simple and quick to use, but nonetheless included logical warnings if something was not correct and/or missing.
Case 20 Certified Payroll
A company that contracts with the government was required to file a certified payroll with the government every pay period. This was very cumbersome because the systems were not compatible, and it took 6 hours each week to get the data in the correct format for the upload into the government's system.
Case 20 Solution:
We came up with a process to take the payroll records from the contractor's payroll system and automatically reformat them into a format that could be uploaded directly into the government's system. This automation turned a 4-5 hour task into a 10 min operation with a lot fewer errors.
Case 21 Automating Order Processing
A medical supply company had a website that customers could place orders on for products they needed. The shopping cart dumped a list of orders into a very large spreadsheet in an ugly format. They customer had to manually, line by line, copy the data into an Excel form, and then print the form for processing. This was very time consuming and created a lot of errors.
Case 21 Solution:
We set up an automated process to copy all the days' data into a master Excel sheet. The sheet then allowed them to select which order they wanted to print in. It would then extract the data and print out the needed forms. This turned hours of work into minutes and reduced the errors dramatically.