Wednesday, April 22, 2015

Crime Data Cleanup

The administration building at particular university houses the Office of Compliance. Each year, the university’s Compliance Officer provides a report of on-campus and off-campus crime statistics for the University along with other universities to show comparison. The reports are simple and quick to create, and they vary from year to year depending on which schools are requested to be included in the comparison. Preparing the data, however, is a much more laborious process assigned to a student employee. As many university departments experience, the “student employee” is different every year due to high turnover. So each year a new student must be trained by the compliance officer to clean and prepare the data. Additionally, most student employees lack experience with spreadsheets, so they use manual techniques to cut and paste data. It is not uncommon for rows and columns of data to get mismatched, or for a random university to mistakenly be deleted altogether. This adds hours of work for the compliance officer who must review the data and check for errors. The Excel program created to address this problem uses VBA code to get rid of unwanted data entries and combine multiple excel files (provided online by the US DOE) into one workbook from which reports can then be created. The automated process is complete in just minutes and significantly reduces the potential for errors. Additionally, the workbook includes instructions to complete the process, which reduces the time required to train. The expected reduction in labor costs is hundreds of dollars, and the busy Office of Compliance will be able to allocate labor to other priorities.

Personal Finance Program

Created By: Ethan Lindstrom

Executive Summary

Over the last several years I have had accounts in 4 different banks and have tried 3-4 personal budget software programs. Each of them has had features I like but I have not been completely satisfied with any of the packages, or have run into incompatibility problems with data coming from one of my financial institutions. I decided that building my own personal finance program would alleviate those problems and give me control to build the features that I want.


My new personal finance program imports transactions, checks for overlap in the existing and incoming data, categorizes the incoming transactions, has a user form to allow categorization of items that were not categorized under existing rules, and sums the categories over the specified date range to compare against the budgeted amounts. It also pulls all data for the house I own and rent out to a separate sheet so I can keep track of my gains/losses for that property.

I have included a file that can be used to test the import functionality of the program (Test Transaction Sample.csv).

Monday, April 20, 2015

Symbolic Picture Story Inventory (SPSI) Basic Module

By: Ivan Marchenko

Purposes
This project creates a basis for a program that will later be used to automate Symbolic Picture Story Inventory (SPSI). This inventory is used to analyze the symbolic stories that define the identity of individuals and groups. These stories help understand the logic behind a certain type of choices and behavior.
The method works as follows:
·         Client chooses the main heroes of his/her story: picks a picture that will represent himself/herself and then pictures that represent other significant heroes that should act in the analyzed context (those pictures may be warriors, princesses, animals etc.)
·         Client chooses the environment (roads, mountains, caves, woods, houses, castles, walls, rivers etc.) and arranges the “stage” for heroes to act
·         Client places the heroes on the stage to show the logic of the story
·         Client answers several questions regarding the logic of the story
·         The resulting picture and answers are then analyzed by a psychologist

The basic module of the program allows the user to construct the picture choosing from a categorized menu of pictures. This version contains pictures that are downloaded from the web, whereas the final version will feature the pictures that will be drawn specifically for SPSI and will have options of choosing different positions of the object (limited 3D rotation). SPSI pictures will be black and white and emotionally ambiguous.

Functionality
This version features only the basic functions of the future program. Full version will allow user not only to draw the picture, but also tell the story that is depicted by answering questions. It will also allow the researcher to automatically analyze the picture by calculating object sizes, positions etc. This version allows to do the following:
·         Choose image to insert by specifying the category and the type in the dropdown menu
·         Insert the picture to the drawing area by clicking on it

·         Arrange inserted objects to form the picture

Sunday, April 19, 2015

Automated Email Scheduler for Gmail - Will Matheson

Executive Summary

This project aims to create an email scheduling solution through Microsoft’s Excel VBA for small organizations and individuals who use Gmail as their primary email application. The benefits will help users efficiently communicate with clients, suppliers, applicants, recruiters, contacts, management, direct reports etc. Once the workbook is loaded up with pending drafts, the workbook will need to be scheduled to run as a task in Windows Task Scheduler to be run every hour.
          Relationship management with clients, suppliers, applicants, and coworkers is an important facet of today’s world of business. Employees who remember to maintain contacts with key individuals in a timely manner are able to excel. Email is becoming a critical channel for communication. Automated e-mails with canned messages are commonly used but lack the personal touch of a phone call or customized e-mail. One struggle with writing customized e-mails is making sure the message is sent to the recipient at the right time. During a busy week when work is piling up, it is easy to lose track of time to get an important e-mail out Friday morning and ends up being remembered Friday at the end of day. This can result in delayed projects, lost productivity, and slow turnaround times in communication. This effect is compounded when dealing with overseas contacts whose workday is opposite your own.
          The business need is a tool that can assist users who want to schedule e-mails to send to their contacts at a specific time. This way, users can have a productive episode of drafting e-mails at any time of the day or night and have the messages sent out at scheduled times in the future. The benefit is not letting important communication between the user and their contact be at the mercy of a busy schedule.
          The most commonly used application for workplace e-mail is Microsoft Outlook. A feature to schedule e-mails at a specific time exists for Outlook and greatly benefits employees as they strive to manage relationships and communication pertaining to their work. Google for Work is a growing SaaS solution for small businesses which offers Gmail as an application for e-mails. The same functionality of scheduling e-mails does not exist for Google’s Gmail. There are third party developers who have created Chrome Extensions which do offer scheduling functionality. However, these solutions come with subscription plans and have tiered service levels which can limit the number of scheduled e-mails you can schedule or send in a period of time.

Saturday, April 18, 2015

BYU Tanner Building Room Reservation Export Tool - Daniel Christ

EXECUTIVE SUMMARY
The “business” that my project focuses on is that of Me, Inc. but I plan to share this tool with my classmates and future BYU business students. The first year of my MBA at BYU is almost finished and one of the keys to being successful was working well with your various teams. This meant meeting frequently and this often occurred in one of the Tanner Building team study rooms. To use a study room, you must reserve it on the BYU Marriott School room scheduler web site (http://marriottschool.byu.edu/scheduler). To stay organized, I often would reserve a study room and then immediately create a Google Calendar event noting the room location, date, and length of time. As I looked back on this first year, I have booked nearly 300 room reservations and for each of those, I manually created a Google Calendar event. If I assume my teammates booked a similar amount, that’s another ~1,200 (~300 x 4) room reservations that were made over the past two semesters. We were all consistent in creating calendar events for each reservation.  If we assume each event took approximately 90 seconds to create, that’s 135,000 seconds (1,500 x 90) or 2,250 minutes or 37.5 hours of time spent on simply creating Google Calendar events. Unfortunately, I cannot have this time back but if I created a tool to automate parts of this process, my second year of the MBA program would free up a substantial amount of time for me and my classmates.
The tool that I built allows me to compile all of my upcoming room reservations into a tidy CSV file that I can then easily upload into my Google Calendar. The outcome is having all of my study room reservations turned into individual calendar events that are cleanly placed into my Google Calendar. My tool is driven by a single button on a customized ribbon that quickly allows me to log into the My Reservations web site, extracts all necessary room reservation data from the web site, and then places all of the data into a CSV file that is then ready to be uploaded into Google Calendar. The Web Query information is cleared after the macro runs so it’s ready for its next batch.

Thursday, April 16, 2015

Shipment Receiving Log - Joseph Murphy


Executive Summary

This project is for a client company Northrop Grumman Corporation (NGC), and specifically for a division site located at Hill AFB, UT. Northrop Grumman is an aerospace engineering defense contractor with major military programs such as the F-35 Lightning II Joint Strike Fighter, the B-2 Spirit stealth bomber, and the Minuteman III Intercontinental Ballistic Missile (ICBM).

NGC’s Hill AFB site manages operations for ICBM-related activities at seven main locations throughout the nation. These locations include engineering offices, testing sites, and military grounds.

This particular division of NGC wanted improve the visibility of receiving shipments of orders / purchases.  NGC purchases that are shipped to the sites have a lot of variation; they can be highly technical, expensive pieces of equipment ordered by engineers, or they can be low-cost commodity items such as printer cartridges ordered by facilities management.

Currently employees at the different sites use a clipboard and paper records to track expected incoming shipments and whether shipments arrive or are lost in transit. Information such as delivery courier, tracking number, sender, receiver, dates, location, receiving personnel are recorded for accountability and tracking.

The sponsor of the project wanted a centralized repository for this information, rather than multiple documents for each company site, so that there’s better tracking of whether their expected shipments are actually arriving.

The solution that I created is an Excel document to be used with a user form interface to be used as a shipment receiving log. This log sheet also is able to, with the click of a button, launch the courier’s tracking website to check the status of the shipment. This file can be posted on NGC’s SharePoint drive, so that purchasers can input their purchases and warehouse receiving personnel can expect and track the shipments’ arrival.

This unification of information is critical for the division because they have had a history of delayed shipments, lost-in-transit shipments, and instances where the sender claimed to send the product but did not and NGC was charged for the purchase. Because there has not been a singular communication channel, these cases existed.
 
 

 http://files.gove.net/shares/files/15w/jmur2000/Receiving_Logbook.xlsm