Excel Solutions and Productivity Examples

Case 1 Problem:

The manager was required at the end of every period to evaluate each teams efficiency and productivity that reported to her. The problem was the phone system produced a report of in bound and out bound call information and the corporate main system had the customer information and transactions. She had to manually key in all the information into a spreadsheet which was extremely time consuming. Then when she wanted to do scenario analysis (shifting work around) it was a night mare.

Case 1 Solution:

The solution was we spoke to their IT department and were able to get electronic versions of the files she 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 and seeing the results. This enabled her to balance the work load out and save on costly overtime.

Case 2 Problem:

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

Case 2 Solution:

The solution implemented was we took the flat print file from company ABC and the tab delimitated file from company XYZ, read them into Excel and did a reconciliation which gave both companies the information they needed and a list of discrepancies so they could be addressed. Since there were often more then 65000 transactions we wrote a routine that split the incoming sheets.

Case 3 Problem:

Company ABC was a consulting firm had employees in the field working on many different projects over a billing period. Every time they worked on a clients work they would file out a paper work order. Then at the end of the month, the employees would get all the paper together total it up and turn it in for billing. The office staff would then take the paper work orders and key them into the billing system. Part of the problem was the employees would forget to fill out the papers etc which in turn caused lost billing and revenue.

Case 3 Solution:

We set up two systems using Excel. The first was a master sheet that produced each of the employees individual books which were e-mailed out to the employees every week. This was done so the client codes, billing codes etc were as current as possible. Then the employee would enter the information during the day as they did the work. At the end of the week, they would e-mail the sheet back to the office. The system would then extract the information from all the sheets, reformat and reformat it into a format the billing system could read directly in. This saved all time in having the info re-keyed by accounting and allowed billing to go out faster which improved cash flow.