University Student VBA Projects

Tuesday, April 14, 2015

Real Estate Fund Optimization

Executive Summary

Business Description:

I did my final project for the company at which I interned last summer, Key Property Investing. Key Property Investing (KPI for short) has acted as a general partner in the distressed, single family home market to several different private equity shops and hedge funds. Their main function is to find distressed single family home markets and persuade investors to give them money in order to purchase these properties in the investors name, rehab the residence, “stabilize” the property with renters, and to then bundle up several hundred homes at a time to then sell them as asset-back securities to large institutional investors.

System Description:

In order to convince sophisticated investors to give them millions of dollars, KPI has created their own very complex, iterative model that had a very simple, slow macro that maximizes returns of an investor by solving for the ideal number of homes to buy each month for 10 year, or the scope of the investment if shorter. This macro takes approximately 2 hours to run. I created a new macro that very accurately predicts the number of monthly purchases will be the optimal amount, thereby reducing the average optimization time to 54 seconds. Yes…seconds. I then picked 3 of the most influential inputs, the model has over 50 inputs, and created a macro that will run many variations of the model with differing levels in each one to determine how the investment will be most profitable.

Write Up PDF

A Timecard and Sundry Efficiency Measures for IPO Research Projects.

Executive Summary

I work as a contractor for a national consulting firm called Connor Group. Over the last year, I have had two major responsibilities. First, I was in charge of managing research activities on a variety of both public and proprietary data related to initial public offerings (IPOs), where I did some of the research personally and managed other interns who also did some of the research. Additionally, I was put in charge of recruiting and managing new interns as they came in.

My project contained two major parts. First, Connor Group’s HR has a specific Excel template for employees to report their hours weekly, but keeping track of time is left up to employee discretion. My first task was to develop a robust timecard that would allow interns to keep track of their hours on a week-to-week basis. My second task was to take a variety of different time-inefficient processes from the research projects and automate them, so our interns can focus on the parts of the studies that we are actually hiring them to do, rather than on, for example, data entry.

Simplified Grocery Planning and Shopping

Executive Summary

The business that I did my project for is homemaking. While this may seem like a joke to some, homemaking is an intense, very involved job that is stressful, exhausting, and often frustrating. Anything that helps simplify or consolidate some of the many responsibilities of a stay-at-home parent can make a huge difference to that parent. In this case, the specific process that I wanted to expedite is that of grocery shopping and creating a grocery list. It is easy to become overwhelmed when there is a list scribbled quickly onto a post-it note and you can’t remember where everything is in the store or whether you’ve already gotten that item.

This problem is the basis for my project. My project is based heavily on the use of userforms. Clicking a button opens a form that allows the user to select a grocery store item, enter the quantity desired, and then the background programming sorts the item by its department in the store. The departments shown on the spreadsheet are also sorted by location in the store (the specific store is Walmart, since that is the store we commonly shop at, and it is the most uniform grocery store across the country). Another button creates a web query that pulls data from the Bureau of Labor Statistics (BLS). The website includes a list of over 60 grocery items, as well as the national average price for each item, including historical data. Though this will not give the exact amount relative to our local Walmart grocery store, it provides an approximation that can be added to a total. Thus, my wife can have a general idea for how much she can expect to spend on groceries in that trip.

60 is a big number, but it is definitely not an exhaustive list of all grocery items. So, I created a feature (using more userforms) that allows the user to add items and determine where they are in the store. It also allows the user to select and change the department in the store, in which that item is located.

Project Write-up (PDF)

The Real Deal Project (Excel)

Searching books and assign emlpoyees to work

http://files.gove.net/shares/files/15w/lingzhiz/Searching_books_and_assign_emlpoyees_to_work.pdf

http://files.gove.net/shares/files/15w/lingzhiz/Searching_books_and_assign_emlpoyees_to_work.xlsm

In March 2015, one of the Orem public library's staff members had requested that I initiate a VBA project with the intent of streamlining and automating a process via VBA that will ultimately help visitors locate and check-out their desired book or collection from the library's vast book shelves. The project also would assist library administrators to more efficiently scheduling work shifts for library staff, a task currently taking 20 minutes per schedule. Below is a list of the project's objectives:

a. A drop-box list with the names of all of books. When people click the title of books, all information related to the said books, including its complete title, author’s name (s), call#, annotation, and page will pop up immediately in different cells with the proper formatting.

b. A shift schedule. A sheet will be created and named “Worker_schedule”. On this sheet, there are 3 tables. The table is located in the top left side which details the five workdays for the library. The second row of the table provide the number of workers and staff person’s name are displayed. The second table is on the bottom left side. Names of available workers are marked in red. The third table is on right side, it will show the most efficient shift schedule directly when cells in the first table are changed. Based on the city’s needs, I applied the VBA knowledge that I have learned from school to solve their problems. I will introduce the data I got from the Orem city as following.

The city of Orem has a datasheet like the first figure below (until now, they have about 200 rows and 10 columns, but this database is constantly getting bigger). Based on the client's requirements, I will first use VBA macro to reformat the information to another sheet. They require that once the book name or other items are inputted in the datasheet, the macro will automatically update the information accordingly and into another sheet with specific formatting for each row. When users select any book from the database, they will immediately see a table like the second figure below.

Userform for the Actuarial Science degree

The project is not for a business but is intended to be used by the Statistics Department in counseling students who desire to major in Actuarial Science. Many students want to major in Actuarial Science and need help planning out what classes they should take and when but they cannot use MyMap which is the University provided tool for doing this because they are not yet accepted into the major. In order to be accepted into the major a student must pass the first Actuarial exam on probability theory. This exam is covered by the courses Stat 340 and Stat 370 which are well into the program. Instead students must plan on graduating in one of the other Statistics degree programs until they have passed this first exam. The userforms I have created ask a student what classes they have taken, and what major electives they plan to take and then gives them a list of classes they need to take in the coming semesters in order to give them the direction they need in completing a degree in Actuarial Science.

http://files.gove.net/shares/files/15w/judd9/ActuarialDegree1.xlsm
http://files.gove.net/shares/files/15w/judd9/TroysVBAproject.pdf

The Daily Report Generator

Executive Summary

For the past three years I’ve been employed as a Driving Instructor with Revolution Driving Instruction, a driver training company with four offices in Utah. Revolution requires each of its Instructors to send a Daily Report containing information about the hours worked each day and about the students who attended each class to the company’s Internal Auditor. Based on these reports, the Auditor is tasked with compiling the payroll and with verifying that each class and each student’s attendance has been appropriately documented with the government. The problem is that creating these reports on a daily basis is very tedious, especially since the majority of the information is already stored on company-shared Google Calendars and the task is therefore largely one of copying and pasting.

The system that I have built automates the creation of the Daily Reports and does it in a fraction of the time while requiring only a couple clicks and a few keystrokes from the user. This system—the Daily Report Generator—will request some information from the Instructor, allow them to sign in to the appropriate Google Calendar, identify the calendar events that correspond to that Instructor, extract the necessary information, and compile the Daily Report. The Generator will then present an editable draft of the report to the Instructor and provide options for immediately emailing the report to the auditor from within the program, copying the report to a Gmail draft for later attention, rerunning the report in case something was missed, or aborting and discarding the draft. Thus the tedious task of creating a Daily Report, which I have probably performed at least 700 times over the last three years, has been reduced to a couple clicks and a few keystrokes.

Full Write-Up:

http://files.gove.net/shares/files/15w/sjones45/SpencerJones_IS520_FinalProjWriteUp.pdf

Daily Report Generator Workbook:

http://files.gove.net/shares/files/15w/sjones45/FinalProjectSAJ.xlsm

“To Itemize or Not To Itemize- that is the question”

By Calvin Smith

Executive summary: Every year millions of individuals and households are required to file an income tax return. However, much of the general public is unaware of how to optimize their tax return. Tax filing services, automated or provided by an accountant, can be expensive and time consuming. From my experience as a VITA (Volunteer Income Tax Assistance) volunteer for three years, I’ve found that one component of the tax return that can be especially time consuming is determining if it is more beneficial for the taxpayer to itemize deductions or to take the standard deduction. Navigating Schedule A (Form 1040) can be a frustrating experience, even for an accountant. Finding relevant tax guidance that corresponds with each line item and calculating phase outs and limitations can be extremely time intensive. As a result, individuals who may be better off itemizing their deductions often take the standard deduction to avoid the frustrating and confusing forms.

My VBA project provides an easy to use calculator that not only determines which method (standard or itemized) will be most beneficial for the tax payer given their specific situation, but also provides instructions and guidance to help the tax payer enter the correct inputs into the calculator. The VBA project essentially walks the user through all potential deductions from Schedule A and applies the relevant calculations to determine the amount the user can itemize. Then, the program compares the total eligible itemized deductions against the standard deduction and recommends most beneficial method (standard or itemized) of tax filing.

A second potential application of this calculator exists. When tax payers are considering making a major tax planning decision that will impact how they itemize, like whether they should rent or buy a home that will have tax deductible interest, users can run a variety of scenarios to see how that tax planning strategy will impact their itemized deduction. The IRS does not require tax payers to pay more taxes than they are legally obligated to pay. The purpose of this project is not to promote tax evasion, but to help taxpayers structure their financial decisions in a more tax efficient manner and more easily utilize available deductions the IRS has made available.

http://files.gove.net/shares/files/15w/calbro5/VBA_project_Itemize_deductions_4.13.2015.xlsm

http://files.gove.net/shares/files/15w/calbro5/Calvin_Smiths_VBA_Project_write_up.pdf

Online Bank Account Budgeting

Executive Summary

Account withdrawals and deposits, whether they be personal or business, are tracked by banks. If withdrawals are made with debit or credit cards those transactions are listed with the place where they were charged as well. Although most banks give information on where the card was used and how much was charged, many do not categorize the expenditure into personal or business budgets. Sorting through these transactions can be tedious and time consuming, especially if it needs to be done weekly or monthly. This program is designed to automate the majority of the process of categorizing transactions taken from bank statements. With this organizational system small businesses and individuals can easily extract their transaction history from their online account, choose budgeting categories, automatically categorize and sort transactions, and show monthly and running totals in spending for each category and the amount of disposable funds. By using this system the user can be more aware of where their money is going and can find more efficient ways to allocate income.

Home Teaching Automator

Executive Summary

Description of Business

Home Teaching is an assignment given to every male member of the church above the age of 14. Each member is teamed up with one other male member and are assigned one or more ward members to teach. At the end of each month, Home Teachers are to report to the Elder’s Quorum Presidency whether or not they home taught that month. Because there are so many companionships reporting it can be extremely time consuming to manually report this kind of data by calling or texting each companionship. In addition, more mistakes can be made in manual processes. Once you have the reported data, it would also be time-consuming and monotonous to get reliable statistics from it. We found that each ward has their own process of how to assign and report home teaching. This can be a problem since Elders Quorum Presidencies change and ward members come and go. We have come up with a solution to automate the process of assigning and reporting home teaching based on the current ward list so each Elders Quorum can have reliable statistics to help them make the best decisions to help their ward.

Overview of System

The system is made up of 4 macros. Its starts with a ward list that can be downloaded from lds.org, and creates a list of the men and women in the ward. With that list, the second macro creates companionships and assigns members of the ward to home teach. At the end of each month, the district supervisors will turn in their reports of those who were home taught, those who were not, those who were contacted, and those who did not report. The third macro takes that information and compiles a report listing the names of everyone in the ward and their correct status for the month. It then creates month end statistics for each group. Those who wish to be notified of this information can be added to the list of leaders. The fourth macro sends an email with the statistics to each of the members on the leader list.

Travis Irwin, Scott Sefcik

Derivative App

Executive Summary

Learning how to derive functions is a task that most students struggle with. Derivatives are a step away from what most students have seen in their math classes so far, and because they're not solved strictly arithmetically at an undergraduate level, they not only provide new concepts that students must grasp, but they are also not solvable with a normal calculator. There are many websites that teach students the rules for differentiation, but only few that will calculate them. There are no websites that allow students to enter the equations to derive into a calculator in the same format as they are written by hand, or in a textbook. That compounds the problem. Additionally, while there are websites that are dedicated to derivatives, there are few options to find practice problems with answers for students wanting to practice on their own in preparation for a test.

In order to solve this and help students actually learn the material, I implemented a four piece solution into my project that I feel more than adequately addresses and resolves the problem that students face while learning derivatives, as well as provides a template that can be used in a variety of academic settings. The four piece solution that I created encompasses the following parts, and each is explained in detail in the Implementation Documentation section of the write-up:

1. Derivative Calculator
2. Customizable Flash Cards
3. Customizable Quizzes
4. Links to continue learning

Tuesday, April 14, 2015

Blog Archive