(413) 387-1040
139 B Damon Road, Suite 8, Northampton, MA 01060

Recent Projects 2

DATABASE PROJECTS

A National Metals Testing and Finishing Company

New Database Project

We developed a process router database that tracks and organizes processing steps required for complex metal plating work.  Process revision and general change logging with automated export of formatted Word documents were also included in this project.

A Local Transport Firm

New Database Project

We worked with a local transport firm to consolidate several processes that managed its day-to-day operations (off-the-shelf software, Excel spreadsheets, an Access database, and other sources) into one Access database. The project required creating approximately 40 tables, 45 user forms and subforms, 20 reports, as well as separate forms for database administrators. Also included in the project were training sessions for administrators and detailed documentation of the database.

A Manufacturing Firm

Modifications to Database, Access Training for Database Administrators

Our consultant worked side-by-side with the company’s database administrators to upgrade and modify their current database, in order to add functionality requested by staff. The collaborative effort allowed our consultant to respond to the specific needs of the company and simultaneously train the database admins in the most recent changes. In a related project, one of our VBA consultants created an automated process to export data from the database and create weekly and monthly Excel charts of production data.

A Massachusetts Energy Company

New Database for Communication and Community Relations

This project included community profiles, subforms to track political information, an annual activity tab, as well as functions to display DPW staff, CMA Service Area staff, and visiting teams from selected communities. Modifications made during Phase Two of the project included automatically incorporating contact tracking spreadsheets into the database.

A Local Private College

Conversion of Reporting Application

In order to improve on data-handling methods, we converted an older, DOS-based, legacy reporting application to use an Access front end and SQL server for data processing and back end storage.  The second phase of the project involved re-writing the data import routines to use stored procedures rather than Access queries and VBA code.

A Private College Risk Management Office

Driver Credentialing Application

An online process that allows college students, faculty or staff to submit their driver credentials to an approved vendor that conducts license background checks and stores the credentialing information in a secure database so the the college can verify approved drivers. Drivers must go through the credentialing process in order to drive college vehicles or rent vehicles on college business.

Insurance Claims Database

A database that tracks insurance claims and associated expenses and events.  The database was designed to produce detailed loss runs, automated summaries and detailed reports and replaced previously time-consuming tasks.

Online Waivers Web Application

This database allows students to log in, select a waiver form for a college event, and sign and submit it electronically.  This application replaces the need for students to fill out paper forms for events and send them to the appropriate location.

Schedules of Insurance

A simple form is used to enter the information on each insurance policy as it is renewed in a database, which then generates up-to-date schedules of insurance. This is a useful tool for risk managers and brokers alike.

Regional and Community Banks

We have developed multiple databases for area banks to track excess deposits, report FDIC requirements, manage vendors and track accounts. Some sample projects include:

FDIC Insurance Database

Set insurance limits for specific account types. Import account information, review and validate data, produce totals for FDIC Cap, Total Balance and Amount Uninsured for IRA and regular accounts. Corrects for multiple and joint accounts.

Vendor Management Database

Centralized database for management of vendors: Criticality rating, contract requirements, contact information, oversight requirements, required documents, vendor software products, automated email notifications, data security details.

New Accounts Database

Track new account status, responsible employee, documents required, send reminder emails to employees. Reporting, search, filter by account number, title, status.

Branch Activity Database

Track and summarize monthly activity by branch. Import deposits, loans, transactions in text files from legacy database. Check and modify data, proofing validation, run reports, automatically email reports to one or more recipients.

Error Corrects Database

Import error corrects text file. Run reports for monthly and YTD summary, email to multiple recipients.

Delinquent Loans Database

Import delinquency data from multiple text files. Run reports for month-end, quarter-end, YTD, monthly, quarterly.

Loan Reporting and Charting

Import data for commercial and residential loans, letters of credit, identified reserves. Data proofing, produce monthly or quarterly matrix report with risk-rated balances in multiple categories. Produce GL Entries report (closing entries for Form 13D).

BOI (Board of Investment) Reports

Import consumer and mortgage loan data, preview and email summary reports.

TRAINING

A Local Private College

We have been running workshops and classes for staff since 1995 in a variety of business and educational applications.  We held upgrade classes when the college converted to Microsoft Word from WordPerfect, and we have run upgrade classes for each version adopted by the college since then.  As the training needs of the college have changed in recent years, our classes have focused more on advanced features, such as working with long documents in Word, or data analysis in Excel.   We have recently started coordinating hands-on classroom instruction for staff with online Lynda-training videos, which provide participants with before-class preparation and after-class support and reinforcement.

Douglas G. Peterson & Associates

Douglas G. Peterson & Associates hired us to run a high-impact upgrade workshop when they converted to Office 2013.  In addition to staff at the Greenfield office, they teleconferenced in staff members from New York and Chicago.  GoToMeeting software allowed all participants to view the presenter’s screen, and participate fully in the workshop.  All participants were provided with a printed copy of the workshop contents for later reference.

Franklin County Community Development Corporation

The Franklin County CDC upgraded from Office 2007 to Office 2013 and requested a workshop focusing on Outlook, as well as an overview of the new features in Word, Excel and PowerPoint.  We put together a workshop that concentrated on best practices in Outlook and troubleshooting Outlook problems, as well as an overview of the new suite.  Participants all received documentation containing workshop contents for later use.  We are currently testing the viability of creating a library of instructional videos in common software applications that FCCDC can use with their community business clients.

A Local Private College

We have run staff training classes since 1998.  As staff needs have evolved, our classes have shifted from basic computer training to focused workshops on more advanced topics, such as creating flyers in Publisher, working with queries in Access, or analyzing data with Excel.  When the college began using Google Docs and Google Calendar, we developed a series of workshops to orient staff to the new system, focusing on the collaboration features of Google Docs and a series of best practices, specifically regarding the treatment of confidential information.  Our classes on Google Calendar helped staff understand the variety of visibility and permission levels in the new calendaring system.  We continue to run workshops in other specific areas of interest as needed by staff, such as Mail Merge in Word, and Using Pivot Tables in Excel.

Northeast Foundation for Children

The Northeast Foundation for Children wanted provide training in PowerPoint and Excel so that their computer-savvy staff could go beyond the basics and learn more advanced features in both applications.  We provided a PowerPoint class at their location that focused on tips and shortcuts, animations and transitions, using video in a presentation, advanced printing options, and best practices for giving a presentation.  After an enthusiastic staff response to the workshop, we were invited back the following month to conduct a similar workshop in Excel.  Again, staff members wanted to go beyond spreadsheet basics, so we prepared a class that reviewed formulas and then progressed to more advanced functions, working with multiple spreadsheets, creating and modifying charts, and using absolute references.  In addition to classes and workshops, we have also provided one-to-one training for Northeast Foundation staff in PowerPoint and Adobe InDesign.

A Local Private College

A local private college needed an instructor for a series of basic and advanced Excel 2013 classes for students concentrating in Global Finance.  Our instructor planned the class content with faculty and staff, delivered the classes on three Saturdays in the fall, and prepared and class exercises for students in attendance.  High interest in the subject and excellent reviews from students prompted the college to repeat the fall series in the spring, with plans to repeat the series during the next academic year.

Western Mass. Law Firm – Software Upgrade

A major western Mass. law firm hired us to help with their rollout of Microsoft Office 2013.  Since they were upgrading from Office 2003, users needed to be trained on the new Ribbon layout, as well as the new features beneficial to a law firm, such as the ability edit PDF’s directly in Word 2013.  Because the firm produces and edits a large number of documents, a smooth rollout and transition for staff was crucial.  Our consultants were available to help with the implementation of the new version, which included setting program defaults, creating AutoText and AutoCorrect entries and firm-standard keyboard shortcuts.  In a series of three training sessions for key staff, we presented the changes that they would encounter in the new version, showed off the new features, fielded questions from staff, warned against pitfalls, and distributed documentation that they could use for reference after the training.

Western New England University

Western New England University wanted to offer staff the opportunity to upgrade their Word, Excel and Outlook skills beyond the basics.  They sent us a series of “intermediate” level objectives and asked us to design a training program to meet their goals.  We ran a series of very well-attended classes in all three applications in the fall and spring semester (2013-14) that were enthusiastically received by staff.

OFFICE AUTOMATION

Major Regional Energy Operator

Regulatory Filing

After receiving data in a downloaded Excel spreadsheet, we created summaries and print versions, merging the Excel data into a 31-page Word document. Our programmer reduced a two-day process to 20 minutes.

Notification Letters

We developed a program to sort and reformat data from eight downloaded Excel files, and then merged the data into outgoing letters to applicants for energy generation. Our program replaced an error-prone manual process with an automated process that was faster, more accurate and error free.

Regional Bank in Western Mass.

Certified Letter Form

We automated the creation and printing of certified letter form for bank letters. Since this task is performed 20-50 times a day, the push-button automation procedure was a significant time-saver for bank staff.

Automated Printing of Multiple Documents

Created set of macros to automate printing of letters from bank to customers with options for letterhead, copy paper, blank paper, and multiple copies as well as routing to different printers. used by entire bank.

Loan Application Charting

We created set of 42 separate charts to track loan categories by VP. Since the charts needed to reflect the previous 12 months of data, we added functionality to easily update the date range.

International Bioscience and Lab Reporting Firm

Prepared Lab Reports

We automating a previously manual process for extraction more than 25 pieces of information from lab reports and inserting them into Word templates.

Charting

We developed an automated process to extract data from lab reports, create charts based on the extracted data, and insert charts and data into a Word template for use in court proceedings.

Billing

We simplified the company’s billing by analyzing data and producing a number of reports summarizing data in various categories.

Overseas Manufacturer

Inventory Analysis

We created a process to summarize inventory received from 43 overseas factories. The process calculated inventory purchased and used for a specific time period, total inventory used, and total cost in us dollars (using FIFO pricing). The process included the conversion of foreign date formats into US date formats.

Local Law Firm

Billing Spreadsheet

We created monthly spreadsheets to automate tracking, billing, assigning costs, and calculating personnel allocation within a small law office.