tap solutions 818 281 7628 -- For all your website and Microsoft Excel Needs

Excel case studies and solutions
Please click on link to expand topic

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. The manufacturer needed to automate the allocation and balancing of the payments.

Case 3 Solution:

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

Because an insurance company used different data formats and structures, the HR staff had to manually reconcile the reports of more than 1,000 employees. The company needed to find a way to automate while eliminating 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.

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 customer's 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.

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 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 had to manually weed out the spam emails from the legitimate ones 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 processes.

Case 8 Solution:

TAP Solutions wrote and installed a code into the retailer's website so emails sent from the website link would be recognized and kept. Emails from spam and sources outside the website that didn't have the code were kicked out. We also used Excel to delete the junk mail that didn't have the code, sort the information from the emails and distribute it to the appropriate sheets based on the inquiry. 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 worksheets. Each sheet contained one client's information and about 30 data elements. The client had to combine all the information into one sheet so it could be used as a master roster. Copying and pasting each field 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 had some extremely large data files from ongoing customers. This data was uploaded into the main system to update information. The customers 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 Problem : Automatically producing promotional sheets and website data feed

A local importing company used some old, buggy software that could not handle the website data feeds tied to the weekly promotional sheet that went to clients. 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.
Case 13 Problem: 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 Problem: 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 converted the calendar data 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 Problem: 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 sometimes 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 Problem: 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 Problem: 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 Problem 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 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 Problem 19: Creating a preflight checklist

An aircraft charter company needed a simple preflight checklist that each crew member could use on his or her laptop before taking a trip. It had to 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 Problem 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 so they could be uploaded directly into the government's system. What took hours now took 10 minutes with fewer errors.

Case Problem 21: Automating Order Processing

A medical supply company had a website where customers could place orders for needed products. The shopping cart dumped the orders into a very large spreadsheet in an ugly format. The 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.