MBA VBA Projects

Friday, April 10, 2015

Honeywell Discipline and Training Tracker--Andrew W. and Mateus G.

Andrew Walters & Mateus Grahl

Winter 2015

Executive Summary

Honeywell International Inc. is a large (Fortune 100) industrial conglomerate with plant locations in 49 states and over 110 countries. This project is aimed to assist supervisors at a manufacturing plant. The spreadsheet information is modeled after the South Bend, IN location and the Sao Paulo, Brazil sites. The project addresses two major issues at a plant location with a large number of employees: tracking discipline and tracking training for the employee population and is a combining of two separate, but related internship projects that could have benefited from VBA knowledge.

The South Bend site’s current discipline tracking system was a single excel document shared among supervisors on a SharePoint server. The spreadsheet has become cumbersome since hundreds of discipline incidents have been added along with corresponding PDF files being embedded into the spreadsheet. Each cell needed to be manipulated manually along with creation of the physical form for the employees to sign. The supervisors stopped entering in discipline which caused the employees to be able to dispute any escalating discipline due to poor record keeping. There was no training tracking/planning form at either site.

Our task was to streamline the process to help the supervisors cut time in tracking discipline. Once discipline is more under control, the next step is to create and manage training plans for the employees. The system we have built allows supervisors to choose from a list of existing employees and have information automatically populate different required forms—for ease in printing and signing which is required at Honeywell. Once the supervisors have scanned the signed documents back into the system, then can then easily link the scanned PDF files with the click of a button, rather than embed the files into the sheet.

Employee Identification numbers have been randomized to protect information.

Project Management Dashboard

Executive Summary for Project Dashboard

The Project Dashboard project was conceived through discussion with Michael Takahashi, a Project Manager at SRI International, a "nonprofit, independent research center serving government and industry" whose research has led to such products as the computer mouse, medical ultrasound, and cancer drugs[1]. SRI has 2,100 employees, generating $540 million in annual revenue. Michael's role within SRI is to oversee the execution of several projects simultaneously. As such, he keeps a close eye on things like budgets, costs, and deadlines. As you might guess, he is a very busy man.

The problem that needed to be solved was that each week Michael would have to access the "DSS Homepage" through a web browser and click through to each project separately to check on the status of each project through several key metrics scattered throughout mounds of irrelevant statistics. This painstaking process takes considerable time and can be prone to user error (many clicks required). So, the Project Dashboard spreadsheet project was designed to access the site for Michael and pull down relevant data for each project and neatly arrange them in a dashboard.

The Project Dashboard contains a few different elements. First, the dashboard is present on the "Dashboard" sheet, where the user can read instructions, select if charts should be created for the latest data (using check boxes and a combo box), and visually see the data for each project, with data highlighted to warn the user of any alerting signs in the information. It's on the dashboard that the user clicks a button to execute the actions of collecting and displaying the latest data.

Second, the "MyProjects" sheet is simply a place for the user to list the projects that he/she cares about. The user has the option here to record a description to accompany each project.

Third, the "charts" sheet displays the resulting charts that the user has selected to generate for the latest data. Pie charts and/or column charts can be created to display break down of cost types and/or comparison of budget spent vs. total budget for each project, respectively.

Lastly, there is a "hidden" sheet that is, shockingly, hidden from the user's view. This sheet's only purpose is to act as a place for data from the Web to be imported and searched through.

http://files.gove.net/shares/files/15w/crt819/Project_Dashboard_Write-Up.pdf

http://files.gove.net/shares/files/15w/crt819/Project_Dashboard_v1.1.xlsm

[1] SRI International, Accessed April 6, 2015. http://www.sri.com/about

Wednesday, April 8, 2015

A. Schulman ERP System

Executive Summary

A. Schulman is a plastic compound manufacturer with operations around the world. This project was designed to help a production facility in Ohio better track their incoming orders and scheduling. This facility currently does not have an ERP system and for this reason, this workbook was created. Currently, all of the information that the production facility needed was tracked over multiple excel worksheets. This project allows the information to be stored in one workbook and automates some of the tasks. This workbook allows new orders to be entered and scheduled, performance to be tracked, and completed orders to be stored.

The files can be found at:

Monday, April 6, 2015

Translation Assistant

Business Need

I write mobile apps in several languages, and aside from paid translation services, I often use Google Translate, Bing Translate, and similar services. Sometimes I am dealing with 6 or more languages and it becomes very time consuming to translate one phrase at a time, and sometimes I just need a phrase I previously translated. Also, I often need to compare translations between services in order to determine the optimally translated phrase.

Overview

This solution provides a way for a user to quickly translate a phrase or sentence into one or more languages. For my purposes, I included nine language options that I commonly deal with, but the solution could easily be expanded to include dozens of language. I chose to go with nine languages to keep the interface simple and clean.

The solution translates the phrase into the desired languages using both Google and Microsoft translation services. The solution also reverse-translates the phrases, meaning the translated text is sent back to the translation service to be translated back into English. This is useful in determining accuracy of the translated text.

The steps to using this solution are:

1. Enter a phrase or sentence to be translated

2. Selected up to 9 languages in which to translate the phrase and choose “Translate”

3. View the “Translate” worksheet, which shows the original phrase, along with its translation and reverse-translation in its various language, using both Google and Microsoft translation services

4. A history of translated phrases for reference, along with a button to clear the history

What used to take me several minutes of manual effort now takes a few second with this solution. Below is a screenshots that demonstrates how this solution is used.

http://files.gove.net/shares/files/15w/lcope/Project_Writeup.pdf

http://files.gove.net/shares/files/15w/lcope/translation.xlsm

Thursday, April 2, 2015

Piano Assistant

My mother, Alayne, has been teaching piano now for 10 years and continues to run her small little business through her memory and a notepad. She has had a various group of 15 to 20 students at any given time and often has trouble documenting fees, reminding students about lessons, and tracking her schedule. She does not get paid sometimes because people forget or she neglects to document the fees and payments. Also, students occasionally don’t show up because they forget or the time was not communicated. I realized that I could consolidate all of these activities by writing her a user friendly VBA program called APPA. This application, Alayne’s Personal Piano Assistant (APPA), allows my mother to easily manage student information, lesson scheduling, tuition expenses as well as sending automated reminders about lessons and tuition.

http://files.gove.net/shares/files/15w/mfavreau/APPA.xlsm
http://files.gove.net/shares/files/15w/mfavreau/email_Lesson_reminder.txt
http://files.gove.net/shares/files/15w/mfavreau/images.jpg
http://files.gove.net/shares/files/15w/mfavreau/Final_write_up.pdf

Tuesday, March 31, 2015

Boy Scout Advancement Comparison

Executive Summary

As the Advancement Chair for my Boy Scout Troop, I am responsible for tracking merit badges, rank advancements, courts of honor, leadership positions etc. I currently use three different systems to track this information: (1) etrailtoeagle.com, (2) BSA Internet Advancement, and (3) Excel. The website etrailtoeagle.com is a third-party subscription site that parents, leaders and committee members can access and edit. This is a comprehensive site that tracks almost everything that happens in the troop. Unfortunately, it only allows the user to see one scout’s information at a time unless you print a report that is not very easy to read or manipulate. The BSA Internet Advancement is the Boy Scouts of America official advancement site that only the Advancement Chair has access to. The only thing that is entered on this site is rank advancements and merit badges and their respective dates earned. This information is transmitted to the local Boy Scout Council. Finally, in order to view and track all of this information in one location in an easy to read format, I create a separate Excel file that contains a summary of all of the advancement information. Each time I receive advancement information from one of the scouts, I have to update the information in these three places. In addition, because parents and leaders can access and edit etrailtoeagle.com, sometimes merit badges and advancement has been recorded, but I am unware of the change. This problem is faced by almost every Advancement Chair that uses etrailtoeagle.com.

My VBA solution focuses on the scout merit badges and performs three basic steps: