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