|
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.
|