Excel Solutions and Productivity Examples.

Case 1 Problem:

The manager was required at the end of every period to evaluate the efficiency and productivity of each team that reported to her. Although the phone system produced reports of inbound and outbound calls, and the corporate main system had customer information and transactions, the manager had to manually key in all the information into a spreadsheet, which was extremely time-consuming. Then when she wanted to do scenario analyses, it was a nightmare.

Case 1 Solution:

Tap Solutions spoke to the client's IT Department, and was able to get electronic versions of the files the manager used. We read all the files into Excel and merged them all together. We set up an analysis sheet which showed her the current situation. It also had a section which allowed her to see the results of moving customers from one team to another. This enabled her to balance the workload and save on costly overtime.

Case 2 Problem:

Company ABC, a large manufacturing company, sold many items to Company XYZ, a retailer. This business produced  many A/R transactions for Company ABC and, in turn, many A/P transactions for Company XYZ. Each month it took many hours for a manual reconciliation to be done between the companies. Each company also had to produce their own separate reports for their purposes.

Case 2 Solution:

Tap Solutions took the flat print file from Company ABC and the tab delimitated file from Company XYZ, read them into Excel, and produced a reconciliation which gave both companies the information they needed and a list of discrepancies to be addressed. Since there were often more than 65,000 transactions, we wrote a routine that split the incoming sheets.

Case 3 Problem:

Company ABC was a consulting firm with field employees working on many different projects over a billing period. Each field employee would need to complete a paper work order for each project, and, at the end of the month, total the work orders and submit them to the office staff for billing. The staff would then key the data from the paper work orders into the billing system. Part of the problem was that the field employees would forget to fill out the paperwork, resulting in lost billing and revenue.

Case 3 Solution:

TAP Solutions set up two systems using Excel. The first system was a master sheet from which individual books were produced and e-mailed weekly to each employee. This was done so the client codes, billing codes, etc., were as current as possible. The employee would enter the information during the day as the work was done, and, at the end of the week, e-mail the sheet back to the office. The second system then extracted the information from all of the employees' sheets and reformatted it so that it could be read directly into the billing system. This saved both time and money, since the manual keying of data was no longer necessary, and faster billing improved cash flow.