These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Friday, December 12, 2014

Caleb Flint - Forecasting Automation

I. Executive Summary
Description of Business. Zarbee’s, a natural pharmaceutical company founded in Draper, Utah, is experiencing significant growth, largely due to the number of new products the company is launching in a number of major retailers nationwide. The forecasting department of the company previously utilized a separate excel file for each of the SKUs it attempted to forecast, with each file requiring a number of manual tasks that had to be completed each week and each month before the forecasting analysis was performed. As the number of SKUs proliferated, the amount of time dedicated to simply setting the files up each week significantly increased and overwhelmed those in the forecasting department.
Overview of System Built. My final project addressed these issue by automating many of these setup tasks through two simple buttons on the ribbon of the main forecasting file. The first button, to be used weekly, is designed to automatically open 100+ files and make formatting and average formula changes. This macro was built to be robust enough to account for foreseeable formatting differences in the different files, such as differences in the number of columns and placement of the reference cell. The second button, to be used monthly, adjusts the summary formulas of hundreds of cells to reflect the addition of the previous month’s actual sales data. The value of the solution lies in the combination of its simplicity for the end user and its robust code. The user of the code estimated that the weekly button will save him 2 hours per week and the monthly button about half a day per month. 

Dive Meet System

Business Description
                During my time at BYU I have been a part of the diving team. We would hold competitions regularly throughout the season against schools across the country. In order to run a diving meet, we would usually need three people to help with announcing, recording judges’ scores, and summing up divers’ scores. In order to find the number of points achieved by a dive, each dive has a specific degree of difficulty that gets multiplied by the sum of the middle three judges’ scores. Divers perform six dives during a meet, so these six dive scores get added up to get a final score for a diver that is compared against the other divers to see who wins.

Business Issue
                At BYU, diving meets have been run on pencil and paper with calculators as the most sophisticated piece of technology. Because of this, divers and spectators would have to wait about 20 minutes before knowing the results of a meet. There would also sometimes be errors in the manual calculations. In the past this has caused the need for adjustments after results have already been announced.

Solution
                My system is able to effortlessly run a diving meet without having to worry about miscalculations or waiting for the results. The system uses user forms to easily input divers’ lists and judges’ scores once the meet has started. The results are then automatically calculated accurately. This saves the pain out of running a dives meet, and makes it extremely easy. The system accomplishes the four main tasks of running a diving meet:
1)      Entering diver info and dives
2)      Setting the order of the divers in a random order
3)      Entering judges’ scores
4)      Calculating the results

Josh Bates - Stock Screener


There are several key indicators that somebody should look at before deciding to spend several hours investigating and analyzing a particular stock.  The system I built is meant to help me grab an industry, perform a key indicator analysis and rank all of the stocks in a given industry.  Once I have the rankings, the next step is to pull financial sheets so I can begin the process of analyzing the stocks.  There is also a function that will allow me to pick a specific stock and pull financial data on that stock.  

http://files.gove.net/shares/files/14f/jbates2/Final_Project_Writeup.pdf 

Value Investing Game


Executive Summary

There are many programs out there to learn how to invest and pull information from online sources.  Most of these programs however are focused in individual stocks that are publicly traded on the U.S. stock exchanges.  I want to learn more not just about stocks but indices that are traded globally.  I am going to be starting my first job in a couple of months and I want to understand the principles of value investing, particularly in these global stocks and indices.  When I sign my paperwork about how I want to invest my savings, I want to have a good idea where and why.


Therefore, I created an investing tool to create value portfolios in equities.  The program is created to create global value portfolios, compare them, and track them over time.  In order to better learn the global equities, I decided to create a game.  The game is used mostly as a simple benchmark while the program itself is meant to help the user familiarize themselves with stocks and indices.


*the project itself was too large to be uploaded but was submitted to LS and send to TA's email

Queuing Simulation

Description

I plan on going into a career in government program evaluation/performance auditing. As public agents, it is crucial that government provide its services as efficiently and effectively as possible. Program evaluators strive to improve a program’s performance by measuring current outcomes and establishing realistic benchmarks. A useful tool to measure performance is through simulations. In this case, the model simulates line conditions and service at a government agency.

Model Solution

The model simulates lines conditions at a government agency, where clients arrive and wait in line to be assisted by servers. The model can simulate several aspects of a government queue, such as length of service, number of servers available, time between arrivals, and hours of operation. The model takes these user inputs into consideration and then reports relevant statistics.
The model is event-based. It uses the user inputs to schedule events. First, it schedules the arrival of the first client. Second, the client checks to see if any server is available. If a server is available, the client receives service. If no servers are available, the client waits in line. At the same time, the next client’s arrival is scheduled. Third, the first client leaves the system and the second client arrives. Fourth, the counters and statistics update.


Thursday, December 11, 2014

Executive Summary:
Generally, business executives do a substantial amount of travelling. However, I’ve noticed that many of them do not plan their own travel but instead, have their secretaries do it for them. Although there is nothing wrong with this, secretaries then are left with the burden of planning a trip or several trips within a short period of time. In my own life I have also noticed how time consuming trip-planning can be. For this reason, I have created a macro to help provide a brief overview of the costs of travelling.


My project focuses on two main sources of travel, driving and flying. The user can enter his data and know exactly how much a trip will cost and how long a trip will take. As was mentioned in my proposal, the information will return the price of the flight and pull the cheapest flight. Additionally, the overall cost of driving will be calculated and compared to the flight price and whichever is less expensive will be shown in a message box. The project consists of a simple userform in which users can enter all of their relevant information and then easily select a button to pull in the information from several web sources. The final product is a basic table with the pulled data and is simple enough for anyone to understand. In addition to the table, users can also request an email, by clicking another button, with the link associated to the flight information gathered from the web. From this email they can immediately go to the website and purchase plane tickets to their destination.

Cost of Living Comparison


I have spoken with many students that are facing decisions as to where to start their career after graduation. I personally received three offers and took all the time I could to research the companies, locations, and compensation packages in order to come up with a decision that would best suit my family and me. Ultimately, I accepted the offer with the lowest salary. It was $8,000 lower than the highest salary, but I still feel I got the best deal because of the location among other factors important to my family.

The company I accepted an offer with has recently decided to pause its active recruiting at Brigham Young University. This is because there is not very good turnover of interns to full-time students. Recruiters feel this is due to the appearance of a lower offer value on paper than other companies. However, what the students don’t take into consideration is how far that salary goes in that location compared to other companies in their respective locations.

This Cost of Living Comparison tool provides users with a central location to select a target city and salary along with as many comparison cities as they would like in order to make a good decision. The tool displays what kind of a salary the user would need in the selected comparison cities in order to have the same lifestyle as in the target city with the given salary. It also displays comparisons for expenses in different categories like food, housing and utilities compared to the national average.

I feel this tool will really help its users to make a more educated decision on the acceptance of an offer based on factors other than the pure salary figure.


Chandler Egbert 


http://files.gove.net/shares/files/14f/echandle/ChandlerEgbert-Final_Project_Report.pdf

http://files.gove.net/shares/files/14f/echandle/CostOfLivingCalc.xlsm

Acquire - The Vocabulary Study Program

Executive Summary
The language learning industry is a quickly growing market, with companies like Rosetta Stone reaching annual revenues of over $250 million. An essential part of mastering a new language is building a working vocabulary to both understand and be understood in the new language. Research suggests that language learners may need to be exposed to a new word up to 16 times before that word is learned. The Acquire vocabulary learning program provides a systematic way for the language learner to take control of his or her vocabulary study. Users can review vocabulary by topic, difficulty, or last-reviewed date, or they can study a random combination of words. The user determines the vocabulary studied, including giving priority to more difficult words and tracking the date that each word was last studied to ensure words are not neglected. Lastly, at the end of each study session, Acquire sends the list of the day’s vocabulary to the user’s email so the language user can study their vocabulary from their smartphone throughout the day.

Project Description and Write-up

Acquire - Vocabulary Study Program


Grocery Tracker

Author: Morgan Young

Description of Business

Buying groceries is a commonplace occurrence in everyday life. It’s even commonplace to shop at different stores to find the exact product or product price that you want. However, it’s not exactly easy to know which of all of the stores contains the cheapest product.

The Grocery Tracker project was created to help people keep track of their every-day grocery purchases. Grocery stores are largely apprehensive toward allowing people to monitor their prices due to the enormous variability of prices from store to store (even within the same chain). This project allows the user to enter in prices paid for different products. The program will keep track of all of the entries and then display the lowest price for a given product when prompted.

Overview of System

There are several functions within Grocery Tracker which allow a user to manage product prices from the different stores. Here is a list of them:

Add Items

In order to add items to the Grocery Tracker, simply click on the 'Grocery Tracker' tab, then select 'Add Product.' A form will then appear. Fill out the form, and press 'Save.'

Manage Items

Several options exist to customize the addition of products. Within the 'Add Product' form exist several buttons that allow a user to manage types, brands, metrics, stores, and cities. These come in handy when trying to identify which store has the cheapest product. Basic create, edit, and delete functions are supported. NOTE: though a management item may be deleted from the list, it will still appear within the list of products so as to preserve accuracy.

Find the Lowest Price

In order to find the lowest price of a product on Grocery Tracker, click on the 'Grocery Tracker' tab, then select 'Find Lowest Price.' A form will then appear. Two filter options exist: 'Product Name' and 'Product Type.' These filters work alone or together. As the filter is populated, results begin to show up in the 'Lowest Price Results' list. Click on one and see things like the location, store name, brand, metric, and whether the item was on sale! Further price comparison tools exist (see 'Compare all Prices for a Given Product')

Compare all Prices for a Given Product

 The user may also access the pricing for a whole product. This can be accomplished by pressing the 'Compare Other Options' button. This will take the user to a screen whereupon exists a graph displaying all of the different options for a given product. This screen also displays the same information in a list view. This allows the user maximum flexibility to choose the best option when price is not the only deciding factor.

Links

Henderson Sales Report


Henderson Sales Report

By Jason Kruger

 

Business Description

The Sales Representatives at Henderson, a regional truck and auto part retailer, are required to submit a Monthly Sales Report.  These reports are all turned in to the Sales Manager and VP of Sales.  The Sales Representatives are to report on who they visited or called, what they talked about, and highlight anything that they feel needs to be brought up to the Sales Manager or VP of Sales.

 

Business Issue

Because Henderson is a smaller regional company, the owners have not invested in a lot of technology.  This leaves a lot of functions to be done with paper and pencil.  The Monthly Sales Report is one of these functions.  To fill out these reports takes close to 20 hours to fill out correctly.  This is a lot of time, especially to a sales representative who is paid on commission.  The more time they take to fill out these reports, the less time the sales representatives have at visiting customers.

 

Solution

To help the sales representatives spend less time writing their reports, I decided to create a way so the sales representatives could fill the reports out on a computer in order to save time.  I did this by creating a form in excel that they can use to put in their information for the month.  This form allows them to quickly fill in their report by using dropdown menus, list boxes, and check boxes to fill in a majority of their information.  They only need to type in one or two bits of information to create one line in their report.  Most of the sales representative that would use this form would not have a lot of technical knowledge so I created the workbook so everything could be down in the form.  Also the only option in the ribbon they have access to, is a button that will bring up the form.  Also in the form I have automated the formatting and printing of the report all in one button.  After they are done inserting their information then they click the print button and select their printer and print.


By using this form the sales representatives at Henderson can save time filling out reports and more time taking care of customers.
 
 
 
 

Final Project: Family Cash Flow/Budget

Final Project: Family Cash Flow/Budget

Executive Summary

Family Cash Flow description: This last summer my wife and I found ourselves in a situation in which we needed a VERY specific cash flow/budget to see how much money we needed to make and budget in order to make ends meet. Between graduate tuition and having our first baby this year, we need a lot of cash on hand at very specific moments, and yet we have lower-income paying jobs that pay us bi-weekly. As I designed a cash flow to meet our needs, I discovered that the most accurate cash flow system was to show how much cash we have on hand every pay period, but this is complicated by the fact that many cash outflows, or expenses don’t occur in even number of weeks, but rather by month (like rent). So I created a rather complicated cash flow/budget that accounts for which months have three rather than two pay periods and accurately shows how much cash we have on hand and will need for 12 months out.

The Problem: While accurate and necessary, our cash flow budget take a long time to update due to the large number of manual tasks, such as figuring out which pay periods have monthly expenses, since they do not all fall evenly. Also, if there were significant changes to our estimates of monthly expenses, this is time-consuming to update and easy to mess up.

The Solution:  For my final project, I wrote several macros and created new worksheets and tables that automate all of the above listed tasks. This is broken down into the following steps:

1.       I created a macro that updates today’s date, the last pay-date, deletes old columns, creates new columns for at least a year out, enters in needed formulas, and reformats the spreadsheet as needed.
2.       I created separate worksheets to allow the user to know exactly where to ender the following data: Income estimates, Investment income/liquidations, Recurring expenses (fixed and variable), one-time expenses, savings, and current bank balances.
3.       I wrote macros for each of these worksheets that deletes out old cash flow/budget information, reads the new or updated information in each worksheet, and enters it into the appropriate row and column. This includes reading dates and deciding by logic which columns to enter the data into depending on a specific date, or if it is a monthly or bi-weekly cash flow.
4.       I created a macro that assists the user in entering in current bank account and credit card information to determine how much cash we have on hand. This assists the user in entering the information without error while looking at other sources for the data.
5.       Lastly and most importantly, I created a ribbon with buttons for each of the above macros that will allow my wife to easily and simply update the cash flow/budget too so she is not dependent on me to determine if an expenditure is within our budget.


Jensen Budget

Stephen Jensen

Executive Summary

For my personal project I decided to work on my personal budget. I designed this project to help alleviate two pain points I have, first categorizing my spending and second analyzing home and car loans.

Keeping a budget is something that I take very seriously. I really try to keep track of where I am spending money and for areas where I can save. To track my spending, I use a VBA code to categorize each transaction I make. In the past, I would spend three to four hours a month manually entering each transaction into each designated category. Needless to say this process is tedious and frustrating.

Along with tracking my spending, I am also concerned about getting a car loan and home loan. I currently have a car loan and will be looking to get a home loan in the next two to three years. Being able to quickly crunch the numbers for different loan amounts, down payments, interest rates, and terms is something that I would find very useful now and in the future. I also am very interested to see how much interest I will save over the life of the loan by making extra payments each month on my loan.

The first part of my system is designed to ease the annoyance of manually categorizing each of my transactions. Now all I need to do is download my credit card transactions from my bank and put them in a sheet in my workbook. Then simply click the button “Input Transactions” and the code will automatically place each transaction in the desired month and category. This code will save me three to four hours each month and allow me to track my spending month to month.

The second part of my system allows me to quickly analyze loans based upon the loan amount, down payment, interest rate, and terms. This system allows me to enter these four inputs and then runs a sensitivity analysis on each input. That is it shows me how the monthly payment and total interest paid varies if each input were to increase or decrease. I also included an amortization schedule based off of those four inputs. The amortization schedule also allows me to enter an extra payment amount, how many months I will make that extra payment and the number of months remaining on the loan then calculates how much interest I will save. This will allow me to analyze loans quickly and intelligently.


This system will be very helpful for me to keep track of my spending and analyze home and car loans.

http://files.gove.net/shares/files/14f/jensen48/Jensen_Final_Project.xlsm
http://files.gove.net/shares/files/14f/jensen48/Jensen_Final_Write_Up.pdf

COVER SONG FINDER - Find the best songs to cover for your YouTube channel.

These days, the best way for an independent musician to get noticed is on YouTube. On YouTube, a musician can gain subscribers and build an audience to release content to and eventually profit from.

Most musicians want to write and release their own content. The problem is doing this does not attract an audience. A musician must first “cover” other popular songs and hope they will then show up in search results of users searching for popular artists. Once an artist is randomly found, if their content is good, a user will subscribe to their channel. Now when the artist wants to release original music they have an audience to receive it.

By analyzing data found on billboard.com, YouTube.com, and other sources, this project is a tool for musicians to quickly identify the songs to cover that will bring the most exposure to their YouTube channel.

Luncheon Coordination

Executive Summary
               
The Business Career Center within the Marriott School has a Recruiter Support Team of which I am a part. This Recruiter Support Team is tasked with handling all interactions and preparations with recruiters in order to facilitate their Marriott School experience while on campus. As part of that experience we offer a complimentary luncheon where faculty/staff from the Marriott School will be present to meet and network with the recruiters.
              
 A large portion of my responsibility on this team is to facilitate this luncheon experience: the coordination and invitations, the hosting, and all preparation/ordering food, etc. Just months prior to understanding more about VBA coding I was completing each of these tasks manually taking hours each day. However, now I have designed two programs; one to automatically combine all lunch orders from recruiters and staff/faculty into one easy-to-read spreadsheet and the other to combine the schedules of recruiter visits with their associated faculty/staff. In these processes of combination, I also pull the contact information of all recruiters into another file where it can be stored for use by the rest of my Recruiter Support Team.

Once I have the associated faculty for each recruiting visit, my program will send automatic emails to the linked faculty/staff members and invite them to lunch with their assigned recruiters on the day the recruiters will be on campus.

(All 9 files after the project write-up must be downloaded for the full effect of the code to take place--the 2nd and 3rd are where the code is located and the first 6 after the project write up should be opened when running the code)

http://files.gove.net/shares/files/14f/apoll11/Final_Project_Write_Up.pdf
http://files.gove.net/shares/files/14f/apoll11/MBA-U.G.-Faculty_Luncheon_Confirmation.xlsm
http://files.gove.net/shares/files/14f/apoll11/Menu_Selections_Download.xlsm
http://files.gove.net/shares/files/14f/apoll11/FacultyStaff_Cafe_Zupas_Menu.csv
http://files.gove.net/shares/files/14f/apoll11/MBA_Schedule_Data.xlsm
http://files.gove.net/shares/files/14f/apoll11/Qualtrics_Faculty_Menu_Download.xlsm
http://files.gove.net/shares/files/14f/apoll11/Recruiter_Contact_Information.xlsx
http://files.gove.net/shares/files/14f/apoll11/Recruiter_SignIn.csv
http://files.gove.net/shares/files/14f/apoll11/U.G.__MBA_Schedule_Data.xlsm
http://files.gove.net/shares/files/14f/apoll11/U.G._Schedule_Data.xlsm

Customer Complaint System



Executive Summary

Taffy Town Inc. is a small family owned business located in Salt Lake City, Utah. The company has been in business for almost 100 years. The company was known as the Glade Candy Company until 1995 when the company changed its name to Taffy Town to reflect its decision to discontinue all of its product lines other than taffy.  Since 1995, Taffy Town has positioned itself as a leader in gourmet taffy. The company primarily does business in the United States, although in recent years it has picked up some key international accounts. The company prides itself in using the finest ingredients and in processing the taffy in a manner that keeps the product fresh and soft. Taffy Town currently produces over 60 flavors and a variety of standard assortments.

For my project, I created a system that will help Taffy Town manage customer complaints. The system uses Excel as the user interface and Access to store the data. When a complaint is received, the employee receiving the complaint will open the application by clicking on the shortcut on his or her desktop. The employee will enter the required information, including the customer’s description of the complaint. The employee also has access to a chart summarizing historical complaints in case he or she needs to reference that information. After the employee records the complaint, an email is sent to the appropriate individual(s) in the company. The email includes the complaint information along with a chart showing historical complaints.

When the individual receives the email, he or she will take the necessary action to resolve the complaint. Once the complaint is resolved, the employee will record the resolution in the customer complaint system. At the end of each week, the sales manager will use a report to review all complaints that have not been resolved within 5 days. He will also review complaints that have been resolved and change the status of the complaints from open to closed.

The customer complaint system will help Taffy Town better understand complaint trends by providing a centralized location to store all complaint data. The system will further help Taffy Town by creating an instantaneous way for employees to be made aware of complaints that resulted from process failures in their respective departments. Additionally, the system will provide a framework for holding the respective managers accountable for resolving process failures that led to the complaint. Finally, the system provides a set of reports to enable employees to quickly view complaint data and to follow up on unresolved items.

 File Links



Write up
Excel
Database
Image
 

Blog Archive