Anthony White

Project Write-Up

Project

Business Background

This semester I acted as a mentor for an undergraduate field study team. The company we performed the field study for is Action Strategy. Action Strategy is a small strategic consulting firm that helps its clients develop and execute appropriate visions for their organizations.

Business Pain

One of the tasks Charles Dayton, the president of Action Strategy, asked us to do was evaluate his online tool that he had developed to facilitate organizations, teams, and individuals in the creation, tracking and execution of meaningful goals and work tasks. The tool seems to be very strong in the aspect of listing tasks and measuring completion of those tasks, however, I think that it has some room for improvement in terms of being a tool that inspires creativity and alignment in the process of developing goals with varying timelines.

Business Solution

In hopes of providing some meaningful feedback to Charles, in order to complete this project, and to improve my own setting and measuring of goals, I have selected to make vast improvements upon a simple goal tracker that I previously developed. My VBA application, The CEO Scorecard, makes it possible for one to establish categories that represent the most important things one would like to accomplish and to assign a targeted percentage of completion that one is working towards for each category and for time periods such as yearly, monthly, weekly etc. The actual accomplishment can then be measured and compared to those targets.

Utah County Property Search

My program was designed to be used by lawyers in the research and tracking of parcels of land. The detailed research needed to organize a probate or settle trust disputes can be very time consuming and difficult. The hardest part is the gathering of necessary information in order to make a decision. In Utah County, where the particular law firm I have in mind is located, the land records are available online. Once the information is gathered, it still needs to be evaluated, but the data gathering process if done manually is very time intensive. For example, gathering the data for one individual that has worked in construction and therefore connected to 50 parcels of land, may take 2-4 days to gather all the necessary information. My program automates this process to reduce the time significantly.

Excel Program

Program Write-up

Inventory Tracker and Exporter

Executive Summary

This project comes from a post on getafreelancer.com. It is a business problem that someone posted dealing with shipping and inventory systems. The system is required to query tracking data against the USPS site and collect shipping information on the tracked item. If the item has been delivered, then an inventory chart is updated with quantities of the items in the order.

The input needed from the user is a worksheet with the order details consisting of sizes, colors, and quantities of the items ordered. Then the worksheet is named after the tracking number associated with the order. The tracking number must almost be added to the list of tracking numbers in order for the process to work.

When the procedures are run (via a button) the list of tracking numbers are processed, querying the USPS site one by one, and collecting the order’s shipping class, status, date shipped, and date delivered, if applicable. If the package had been delivered then the order details are added to the worksheet of the running inventory for the business.

Once all the delivered quantities have been added to the running total the inventory worksheet is sorted by color alphabetically and has all zero-quantity rows collapsed and the worksheet is exported as a GIF picture for use in posting online.

Excel File

Project Write-up

Final Project Journal Entries

Executive Summary-

The VBA program that will be discussed involves reconciling journal entries made at my current place of employment. Each day a journal is created to record the general accounts that were affected during the previous day’s business. The daily journals vary in length and amount according to what transactions took place. At the end of the month, the daily journals are compiled into a detail summary spreadsheet. This spreadsheet is compared with the monthly journal entries that summarize all transactions during the month. The process of combining the daily journal entries with the summarized monthly entries takes two to three hours depending on the number of errors.

The process that was automated consists of compiling all daily journals into the summary spreadsheet and comparing the sum of the daily journals with what was uploaded to the journal site. This process takes anywhere from half an hour to an hour to complete, depending on the corrections that need to be made. The automation of this process has reduced the time required for completion because one need only correct potential discrepancies between the two sources of data.

Piano Technician Customer Database

Executive summary

My dad runs a piano tuning business out of his home. He operates a database, but he essentially uses it as a collection of tables since he doesn’t run any queries, forms, or reports. Excel has given him enough functionality for his purposes. He has a table that holds information about his customers: their contact information, address, and piano type. He doesn’t have Access, which would have made the project significantly simpler. However, the challenge of not having Access to work with was a good learning experience.

I created some functionality that can improve the way that Dad interacts with his customers and manages his “database.”

The first was to make all of the customer records searchable by multiple fields. His prior method of using ctrl+F and filters was sufficient, but it wasn’t very fast or thorough.

The second change was to allow my dad to view customer information in a nice form rather than on a long line in the spreadsheet. My dad has special abbreviations and lengthy notes for each customer; a textbox with wraparound function and the ability to tab and do line breaks is more conducive to his needs.

A third feature is the calendar management tab. On this tab, there is list of future dates and a column next to it of the area my dad would like to work in that day. When a customer calls in, he can see the next time he’ll be in the customer’s area and how many appointments he already has scheduled.

The fourth feature was to set up a way of sending simple mass e-mails from the spreadsheet. Using the search functionality, my dad can look up the records of customers who need their piano tuned within a specified period of time. A reminder e-mail, which incorporates table fields in the text, can be sent easily.

A fifth feature is a process that allows my dad to select up to five addresses—any U.S. addresses, in fact—and have the program tell him which route will be the shortest distance among them. This is done by having Excel interface with MapQuest and pull distance data after submitting the addresses selected. The great thing about the application here is that the number of my dad’s appointments is limited, so I could afford to write an algorithm that would look for the best route across all iterations and in any direction. I did this because I feel that the same code could be useful to others, and in areas where there are many one-way roads, the distance from A to B and B to A could be significantly different.

Some of the hurdles that made this project difficult included ensuring that the program was robust and managed unpredictable user input; creating database-like functionality without Access, managing data types, navigating web interfaces, and creating algorithms.

The customer information in the file below has been altered and bears no resemblance to the actual customer list.

Download the write-up
Download the project file

Band Manager VBA Application

Executive Summary

I am a very busy and active performing musician. I play with several bands and groups, host and promote concerts, and record and sell CDs. I track the musicians that I work with, venue contacts, CD sales, and other finances. I created the Band Manager application because I was looking for a way to do all of these things in a single, unified environment.

The application uses a modified relational data model. The advantage to this is that Band Manager supports one to many and many to many relationships between objects. Also, in addition to it's robust processing capabilities, the interface is simple and intuitive. The user interacts with the Band Manager UI and never with the data tables.

The application is launched from a button on the main worksheet upon opening the application. Once the UI is visible, the user can create, read, and update musicians, bands, venues, gigs, and merchandise objects. The user can also create or delete relationships between musicians and bands, venues and gigs, and financial transactions with merchandise and gigs. Lastly, the user may view reports about disbursements and sales.

Full write up availablehere
excel workbook availablehere

Student Hours Report for the Political Science Department

Executive Summary

The Political Science Department allows its professors to hire student assistants (TAs and CAs) to help with grading student assignments and meeting with students. Each student assistant is allowed to work a designated number of hours each week per class. The department chair holds each professor accountable for monitoring the hours that their assistants work. At the end of the semester the Political Science Department's accounting secretary must compile a report that indicates three items: the number of hours a professor was allowed to use for the semester, the number of hours the professor actually used, and the variance between those two numbers.

In the past, the secretary spent eight to ten hours compiling this report because the information needed had to be gathered from separate sources and entered mainly by hand into a Microsoft Access database the department maintains. In addition the report was also prone to the introduction of human error and was usually not finished until months after the semester had ended.

To simplify and significantly speed up the process of creating the Student Hours report, we built a VBA program to run from the Political Science Department’s existing Access database. Using VBA code we are able to manipulate Microsoft Access, Excel, and Internet Explorer to gather data from the user, route Y, and the BYU Student Employment database known as BOB. This information is used to calculate the number of student assistant hours professors are allowed to use for each course over a semester and the variance between that number and the number actually used.

Our program is detailed in two parts. Part I documents how the program automatically generates the end-of-semester Student Hours report. Part II details the process of updating the Political Science Departments database by automatically importing the information from BOB.

Write Up Part I

Write Up Part II

Project File

Final Project ISYS 520

Covey Ogden

ISYS 520-Dr Allen

Executive Summary

This semester I have been working with the director of marketing at BYU Independent Study. BYU Independent Study is an online education university that offers a variety of basic courses to students all over the country.

When a prospective student signs up for an online class, they are supposed to include the school they are currently attending. Using this information, the marketing department can more easily track the success of their advertisements that target specific schools and areas. However, this information does not come to the marketing department in a way that can be easily interpreted. But rather, it comes monthly as a jumbled .txt file with a very long list of the schools, no names or locations, and the course or courses the student signed up for. The problem with this information is that there are no validation checks on the online form that would force the prospective student to pick from a list of schools or put in some sort of standardized school code that is consistent from student to student. What I mean by this, is that a student could type “BYU” or “BYU-Provo” or “Brigham Young University”, etc. as their attended school and the web coding, as it is, cannot understand that these schools are actually the same school. Further, the marketing department does not get the location of these schools sent to them, which is important information. While the new version of the website, which is expected to come out in two years, will be better in validating data, this is what the marketing department is stuck with until then.

So my task was to create a macro that will sift through a lot of these redundancies, find the locations of these schools, and “print” the finding on a nice looking report, which then can be checked for errors. Currently, someone in the marketing department is spending about 8 hours a month doing this manually. I expect this program to cut their time to about 30 minutes or less.

click here for write up

click here for file

click here for example .txt file

Jeremy Heckaman - Viking Pest And Plant Customer/Contract DataBase and Scheduler

Viking Pest and Plant, LLC is a pest control company that offers additional services, such as plant and tree disease diagnosis and treatment, tree pruning and trimming and landscape consulting. The company is owned and operated by my brother, primarily in the St. George, Utah metropolitan area. He principally operates the business out of an office in his home, where he keeps files and customer information, along with any other company documents and information. Customers will sign a contract which stipulates the services requested and the frequency of such services. Based on this information, services are scheduled and payment is collected at that time. Given the limited office space, and proximity to small children, my brother experiences some difficulty keeping his records organized and secured from potential destruction. In addition, scheduling out services cost effectively can be a time consuming and error prone process. Currently he needs to sort through a stack of paper contracts and write down a paper schedule for all customers. This process needs to be repeated periodically consuming unnecessary labor hours. The manual process is expected to become unmanageable as his customer base grows beyond about 250 records. Automation of these tasks will reduce labor needs and enhance his ability to track his customer activity and service requirements.

For my project, I designed a data base in Excel, using primarily VBA forms, which will allow my brother to input customer data, link multiple contracts to a customer record and link multiple service call histories to those contracts. All of this information is input through user friendly forms, and can be recalled and edited. In addition, the data base has a scheduling function that allows my brother to see upcoming services for the next week or month. This allows for safe and secure storage of all files in a safe, central location with easier and more accurate record changes to that information. In addition, it reduces scheduling time and difficulties because my brother only needs to plan a few weeks in advance, instead of planning out many months in advance or periodically reviewing all contracts. This program will also allow my brother to accurately record and report regulatory data, e.g., pesticide usage, required by State and Federal agencies, e.g., The EPA. Lastly, it provides a platform for additional functionality that I plan to program in the future, further increasing its value to my brother’s company.

Click Here for the Complete Report

Click Here to View the DataBase