Microsoft Excel : Saving you Time and Money and Improving your bottom line
Technology and Productivity Solutions ( TAP
Excel Solutions and Productivity Examples.
Case 1 Problem: Keeping Reports / Data Matched
A human resources department had a problem
keeping its employees’ insurance reports in sync with the insurance
company’s reports because each used a different data format and data
structure. This caused the HR staff to have to manually reconcile the
reports of more than 1,000 employees. This took some serious time! And
numerous errors were missed.
Case 1 Solution:
TAP Solutions wrote an Excel program that matched
employees’ names and addresses with their insurance packages, and
highlighted inconsistencies between the two reports. It also took into
account different data formats: for example, CA and California or
12/31/12 and December 31, 2012. Now, all the HR staff had to do was
quickly glance at the spreadsheet and the errors would be easily seen.
Case 2 Problem:
Can not read PDF docment
A manufacture received orders from their clients in a
large PDF document. They had to print it out, and manually enter it
into their system. It took many hours and there were many errors.
Case 2 Solution:
A solution was to use Excel to read the data. Excel
is able to manipulate the data into a format that their system can
import, and analyze, saving countless hours.
Case 3 Problem: Automating Consultants
A consulting firm required its employees to
manually fill out monthly billing reports, which would then go to the
billing department to be entered by hand. This resulted in a lot of
paper. However, sometimes the consultant would forget to include some
information on the report, resulting in incomplete billing for the month
and lost revenue.
Case 3 Solution:
TAP Solutions created a master report sheet in
Excel that consultants would fill out electronically while they were at
the client or at the end of the day. It 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, making it much easier to bill clients
and keep monthly reports current. It also prevented errors inputting the
Case 4 Problem: Tracking Employees Phone Use.
A retail call center manager needed to monitor
the employee phone use. To do this, the manager needed the list of
inbound and outbound calls as well as customer phone number information.
The manager had the list of calls from the phone system, and the
corporate headquarters had the customer information. So, the manager had
to manually input the info from both sources, which was extremely
Case 4 Solution:
TAP Solutions wrote an Excel macro that combined
the two sources of information, so it was easier to determine if the
employees were abusing their phone privileges; and, the manager was able
to see the phone activity in each division, which in turn allowed them
to balance out the call load, saving on costly overtime.
Case 5 Problem: Reconciling Invoices.
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 retailer sent a check, it also sent an electronic report
of what invoices it was paying. This was then entered by hand into the
manufacturer’s system to clear out the invoices and balance the payment.
Case 5 Solution:
TAP Solutions wrote a routine that allowed the
spreadsheet to be uploaded into the receivables system and match/clear
the invoices automatically. It also identified problems that the user
could quickly address.
Case 6 Problem: Creating Reports From Raw
An online retailer downloaded her orders into an
Excel file. Then an employee created packing slips for her
company’s shipping department by copying and pasting all the pertinent
information into a Word template. Customers did not put things in
the correct format so she also had to correct this. This was extremely
Case 6 Solution:
TAP Solutions created an Excel program that
produced the packing slips with a click of a button and made formatting
corrections automatically. The process now took seconds instead of
Case 7 Problem: Creating Material / Cut List
A window blind owner never knew how much material
he needed for any single project. He would estimate, and put that figure
into Excel. When he estimated too much, it cost him money. When he
estimated too little, it delayed the project’s completion. Plus, he used
Excel as if it were a paper spreadsheet
Case 7 Solution:
TAP Solutions used Excel to create an order
worksheet for each order. Then when all the orders were entered into
sheets, the program calculated how much material was needed for each
order and how much material was needed for the group of orders.
The worksheet saved the company time and money because the owner didn’t
order too much material and didn’t have to constantly reset his cutting
Case 8 Problem: Automating E-mails From
A large retailer received a large amount of
inquiries from its website’s inquiry form. When the staff went to read
the emails, there was a large amount of spam mixed in. It took countless
hours to go through all the emails, weed out the junk and copy the
person’s information into the appropriate Excel spreadsheets.
Case 8 Solution:
TAP Solutions wrote an Excel macro that went
through all the email, eliminated the junk mail and put the appropriate
information into the appropriate sheets. The user then sent the correct
sheet to the correct department.
Case 9 Problem: Reformating Reports.
A manufacturer had a multi-store report with no
page breaks come in from a vendor. Each store had to be its own page.
The manufacturer had to manually go through the report and put in page
breaks before printing.
Case 9 Solution:
TAP Solutions wrote a macro that went through the
large report and logically figured out the page breaks.