Success Stories
Company profile: PPL Electric Utilities
One of the largest investor-owned companies in the U.S. utility sector, serving about 1.4 million customers in 29 counties. PPL maintains over 48,000 miles of power lines.
Technical situation:
The Vegetation department oversees all tree trimming operations and the managing of the vegetation contractors. The department was using excel spreadsheets to transfer information back and forth to the contractors. Managing the day to day operations along with the regional PPL Forester approval process for work completed in the field was very difficult.
Solution:
After analyzing the desired process, Tim Afflerbach designed a new hybrid system using MS Access, SharePoint, and Oracle that streamlined the processes/workflows. The new system manages the data flow between Contractors (2 then, 5 now), PPL Foresters/Line Clearing Inspectors, and Veg Administration. The system is comprised of 3 special database applications (for each of the 3 roles above). All 3 applications use multiple Vegetation SharePoint sites as common data points for all secured transactions.
The new process:
-
Contractors import work completed reports into local MS Access DB. (Outside Corp. Firewall)
-
Contractor uploads actuals into SharePoint. (Actuals now inside Corp. Firewall)
-
Forester/LCI approves or denies line items (Notification sent automatically to contractor and Veg Admin for further processing.
-
Actuals saved in an Oracle database to be compared with the Yearly estimated Pre-Plans for cost analysis.
Benefits:
The entire process is now handled by automated workflows. Foresters/LCIs can filter the thousands of rows of data to now see only the line items they need to approve in a custom dashboard. The process is simpler, faster, and with reduced errors. All roles have custom reports and the data is now safe in a secured Oracle database.
Company Profile: Exclusive Transportation for Industry, Inc. (ETI)
ETI (Allentown, PA) was founded in 1977 as a “hot shot” delivery service to handle emergency, same day pickups and deliveries. ETI now handles shipments from 1lb to 50,000 lbs, and they have a fleet of over 40 vehicles which includes vans, straight trucks, tractor trailers, temperature controlled vehicles, roller-beds, flat-beds, lift gates, etc.
Project: Payroll Conversion
Situation: ETI needed to switch their existing on-line Payroll provider to a new on-line Payroll provider.
Problem: The new provider required all data to be sent to them in a special format that was not consistent with their existing on-line system. ETI’s payroll data was only available via on-line reports which did not include necessary database fields and keys to link the payroll and employee data together.
Solution: TGA Technologies used a Microsoft Access database and VBA coding to perform “HTML Web Scraping” to import the raw data from over 50 on-line reports and transfer this information into the Access database. After all the data was imported, it could then be linked, merged, and exported into an Excel spreadsheet which was the format required by the new provider.
Benefits: ETI did not have to re-key all employee data into the new system. This saved ETI many hours of data entry and verifying for keying errors.
Company profile: Rodale, Inc.
A leading U.S. publisher of health and sports related magazines and books. Known for the monthly publications Men's Health and Prevention, the family-owned operation also publishes the popular magazines: Runner's World, Bicycling, and Backpacker.
Technical situation:
The Excel spreadsheet is a critical piece of software to most accounting departments. For a large corporation like Rodale, accountants use many spreadsheets, and their size (and complexity) range from just a few rows to many thousand rows of information. Sometimes, this information needs to be formatted for proper viewing, and other times the data needs to be verified and/or corrected. This Case Study is addressing the need of one accountant’s task of visually going line by line through a few thousand row Excel spreadsheet of inventory data to make corrections where needed. This task was completed every day and would generally take several hours each day to accomplish. She thought there had to be a better way.
Solution:
Tim Afflerbach sat down with the accountant, and through a detailed step by step analysis of the procedure, a custom Excel macro was formulated to exactly replicate her procedure. Along with duplicating her exact procedure, an added benefit was included to color code special conditions that existed in data that needed special attention. Depending on the size of this spreadsheet for any given day, the macro runs in approximately 30 seconds with a click of a mouse.
Benefits:
The benefits are clear. Rodale spent a few hundred dollars to take a job from consuming ‘a few hours per day’ to 30 seconds, with zero errors. This macro can be used as often as needed and with no additional cost.
...The accountant’s morale was also improved.
Company profile: Pennmark Auto Group Inc.
is an automobile retailer. The company is an automobile dealership for Mercedes Benz and has locations in Wilmington, Delaware; Fort Washington and West Chester, Pennsylvania.
Business situation:
With the popularity of Cars.com, Autobytel.com, Autotrader.com, etc., Pennmark’s auto dealerships were overrun with hundreds of dissimilar emails each day requesting information on new and used vehicles. Some of the emails were from past customers, and Pennmark’s sales force needed a way to handle these requests personally.
Technical situation:
The internet manager at Pennmark was spending his entire day viewing each email, determining at which dealership the car was located, if the customer was an existing customer, and entering the details of this potential new customer into a database. He was then forwarding the email to the appropriate dealership’s sales rep or manager.
Solution:
Tim Afflerbach was called in to analyze the current situation and make a recommendation. A custom Access database solution was recommended. Tim Afflerbach designed a software application that would interface directly with Microsoft Exchange Server, monitor the incoming mail for all 8 web sites, add all customer information parsed from a wide range of very dissimilar emails to a prospect/customer database, and forward all email to appropriate dealerships and sales reps.
Benefits:
And, this all happens 24 hours a day, 7 days a week without human intervention.