Simple Budget Software

Ryan Oliphant

Background

This program is designed to help college students or young families manage and track their finances overtime. The problem that many college students face is having time to manage and track their money and many are living paycheck to paycheck without much thought of saving. Many students, especially at Brigham Young University, have jobs and are earning enough money but don’t realize where there are spending all of their money and rarely spend the time or effort to manage and save for big purchases or emergencies.

Solution Overview

This program is designed to minimize the time spent having to manage finances while still allowing the user to know where money is spent and to track savings for future financial goals. This is accomplished by pulling balances and transaction history from a bank website and allowing the user to keep track of balance history overtime. The program also uses simple user forms to allow the user to enter in a monthly budget of income and expenses which can then be used to compare with actual numbers at the end of the month using generated charts. 

This program is useful because it allows the user to pull all of their financial information into one worksheet without having to log onto an online bank site and manually go through the information. The program allows the user to see visually where/what their money is being spent (on) each month in order to make improvements.  It is also very useful to track account balances to avoid going under minimum balances or to track savings over time in order to make large purchases such as a house or car, or to pay off debt.

Project (Excel Worksheet)
Implementation Document 

University Impact Fund Database by Besmir Dishnica

Executive Summary

By: Besmir Dishnica

Background

This database is created with the intention to help University Impact Fund (UIF) in its day to day decision making process. UIF will continue to use a Google Doc Spreadsheet where it will record all the new information and data relevant to them. The data fields of this spreadsheet are aligned with Impact Reporting and Investment Standards (IRIS). I decided that it will be important for UIF to have a more sustainable and safe database, that is why I designed and developed a user-friendly Excel Database.

Project impact on UIF

The research database will provide a sustainable foundation for future research in the impact

investing space. UIF will now have a reliable, easy-to-update database and will be able to search

and filter data according to its own interests. Additionally, the user guide provided to UIF will

enhance the sustainability of the database by assisting the organization to maintain the

database.

User Guide 

 Database

Housing Finder

Executive summary

Background and Overview

There has been a great waste of time whenever non-local college students in Orem/Provo needed to go online and look for a room to stay for their school year. Most of the time, they go to craigslist.org because it is simple to use and many people post their ad on the site. The process is very time-consuming because, even though it is simple to use, it still takes several clicks to get the detailed results. Even if they do get the results, additional screening is often required because sometimes it is very easy to forget whether the ad has already been looked at or not.

The project was originally initiated with a personal business problem of not being able to find timely information of housing where pets are allowed in the property (I own a cat).

The project has been proposed to cut down repetitive steps and avoid reading the posts that already has been looked at.

The problems have been solved through the following features that the solution offers:

1.       No need to launch web browser and go to craigslist.org

2.       The solution does not display the same posts twice (or more)

3.       The solution automatically fetch all the detailed information about house listings in the result set (Title, Link, price, description, address, postdate)

This document has been prepared to demonstrate the details of the solution. 

Executive Summary and the rest of Write-up

Project Excel File

Car Value Evaluator

Executive Summary

Classified ads are still among the most popular methods of selling and purchasing used items.  However, there are often discrepancies between sellers and buyers as to making a good deal.  This program was created to aid buyers in being able to identify good deals and thus saving time in their searches for new cars. 

The basis of the program searches local classified car advertisements from the popular website auto.yahoo.com based on a given ZIP code.  The results of the query are then transfered into an excel sheet and key information such as the year, miles and price of the car are extracted.  Customer criteria such as make, model, and price range are taken into consideration.  Listings that meet criteria and seem generally seem like "good deals" will be marked as such colored into one of three categories.  The program will also look for abnormalities in the listing, such as mistakes in mileage, and warn users that a possible problem could exist in a certain listing and further research will still need to be done for more accurate information on a certain listing.  Those listing that do not meet customer criteria or generally seem like "bad deals" will be noted as such in the program and colored and sorted for the user to be aware of.

The end goal has been to improve the experience for buyers of used cars and try to quickly and visually determine if a car is a good deal.  This program will aid in that process and may even help sellers to create listings of higher quality.

http://files.gove.net/shares/files/12f/horrock2/VBA_write_up.pdf

http://files.gove.net/shares/files/12f/horrock2/VBAFinalProject.xlsm

Women in Business Event Check-in Solution

by Courtenay Maynes

Executive Summary

Background

The BYU Women in Business Club has been around for several years, but in the past school year, its membership has grown exponentially. While the leadership is delighted to see this kind of growth, the president of BYU’s Women in Business club, Katherine Poulter, expressed some concerns she had concerning the current method of attendee check-ins at events. For each event, the leadership had to print off a list of members and cross off names as each person came up to the doorway. For the first event of the semester, the inefficiency of this checking-in process caused the event to start 20 minutes later than it was supposed to. Katherine approached me and asked if I would be able to create some sort of program that could use the BYU-issued student ID cards to log members into events. I created a program using Excel UserForms and Macros along with the hardware of a keyboard wedge magstripe reader to improve efficiency for the club and simplify the log-in process.

Overview

I built a system to meet the current need of the Women in Business club of a new way to manage check-ins. The user just needs to click on the check-in button, select which event he or she is checking people in to, and swipe each student’s card as the student walks in to the event. The first of the two sheets in the workbook is the “Members” sheet, which holds each student’s first name, last name, and BYU ID number, as well as a record of which events each student has attended. The second sheet is the “Events” sheet, which keeps track of each event with an ID, title, date, start time, end time, and number of attendees. I’ve added a tab to the ribbon titled “WIB Functions” (WIB stands for Women in Business) and it has four buttons. The buttons include a “Check in” button, “Create New Event” button, “View Members” button, and “View Event Reports” button. The interface is fairly simple to use and is pretty user friendly.

Conclusion

This solution should improve efficiency for the club. Checking one person in takes approximately 3 seconds (due to the length of time it takes to swipe a card—the system itself takes less than a second to process each check-in). Since the club usually has about one hundred fifty people at each event, checking all the attendees in should take less than ten minutes using only one station. I recommend that the club purchases two keyboard wedge magstripe readers and sets up two stations performing check-ins, cutting check-in time down to less than five minutes. Additionally, the system I’ve created provides helpful member attendance information for the leadership to determine how successful each of their events are and where there is room for improvement. Finally, this program is very flexible and I could add RSVP information or other metrics if that’s what the club desires. So far, I have been only asked to manage check-ins for events, so I’ve tried to limit my scope to that.

My writeup is at the following link: http://files.gove.net/shares/files/12f/court101/finalprojectwriteup.pdf

My project is at the following link: http://files.gove.net/shares/files/12f/court101/finalproj.xlsm

BYUSA Blue Crew Email Processing

Executive Summary: BYUSA Blue Crew Email Processing

By: Matthew Sheets

Business Overview: An Introduction to BYUSA Blue Crew

For this project, I choose to do a little volunteer work for an old mission companion, Bryon Anderson. Here’s currently serving with BYUSA Blue Crew and requested my help with a project a little earlier this semester. BYUSA Blue Crew is an organization devoted to providing BYU students with an opportunity to get involved by volunteering at whatever events student leaders may need help with. BYUSA is BYU’s student service organization and is similar to student governments at other universities although there are a few differences.

Problem Description: Email Processing

For some time now, Bryon and others involved with BYUSA Blue Crew have had to do a lot of monotonous work to keep their hefty email list regularly updated. This involves downloading new changes from the internet, formatting those changes into a .txt file, making some changes to the text so that their bulk mailer can correctly process the information, and then uploading it to Listserv (Listserv is a bulk emailing service used by a number of organizations on campus to communicate with student participants).

Implementation Documentation: Process and Solution Descriptions

After going through the process with Bryon and determining what parts could be most aided by automation, I determined that the most helpful part would be helping with automating the formatting portion of the email list creation process, especially after reviewing the steps necessary to download and upload the list automatically and realizing that it likely wasn’t possible using the techniques we learned with the VBA browser.

The completed result consisted of a simple workbook with a number of items on the ribbon for easily processing a saved file. I also included buttons with easy links to the websites necessary for uploading and downloading updates.

Discussion of Learning: What I l Learned Doing This

I feel like overall there was a lot that I learned completing this project. I split it into two groups: (1) principles and (2) particulars. The first group covers overarching principles that I feel I came to better understand while completing this project, things that could apply generally to any coding project, regardless of language, problem, or logic. The second group describes briefly the particular pieces of programming that I needed learn to complete the project, some of which I may not have fully implemented or completed learning, but at least touched on during the progress of the project. Please see the attached document for further detail on these two areas.

Overall, I thought that the project was a great one for learning how to work with folders, to create and reuse methods, to work with workbooks, files, and folders and to just gain confidence throughout the entire process. Thanks again for the project, Dr. Allen! 

• http://files.gove.net/shares/files/12f/msheets/BlueCrewEmailProcessor.xlsm
• http://files.gove.net/shares/files/12f/msheets/MatthewSheetsBYUSABlueCrewWriteup.pdf
• http://files.gove.net/shares/files/12f/msheets/EMAIL_PROCESSING--DO_NOT_ALTER.zip

Ben Graham's Ten Points

Executive Summary

Joshua Lindsay

                  When trying to decide which project to do I focused on something that I could use to talk to employers about that they would find relevant and impressive. I talked to Professor Allen about my interests in finance and how I was looking for a job as an analyst when I graduated. He then suggested that I take the first project we did in this class, the Fallen Angel by Ben Graham, and expand upon that to measure a stock based on all ten hurdles that Graham uses to evaluate whether a stock is a good investor pick or not. I became more interested in Ben Graham’s ten hurdles after reading about when backtesting stocks, evidence shows that concentrating on stocks that meet just 2 or 3 of these hurdles can produce favorable results.

                  This project will use information publicly available to calculate whether it passes or fails each hurdle in order to judge the value of investing in that stock. Below is listed each of Ben Graham’s Ten Points used within this project, followed by a brief description on how that figure is calculated.

Ben Graham’s Ten Points

1. An earnings-to-price yield of twice the triple-A bond yield.
2. A price/earnings ratio down to four-tenths of the highest average P/E ratio the stock reached in the most recent five years.
3. A dividend yield of two-thirds of the triple-A bond yield.
4. A stock price down to two-thirds of tangible book value per share.
5. A stock price down to two-thirds of net current asset value - current assets less total debt.
6. Total debt less than tangible book value.
7. Current ratio (current assets divided by current liabilities) of two or more.
8. Total debt equal or less than twice the net quick liquidation value as defined in No. 5.
9. Earnings growth over the most recent ten years of seven percent compounded - a doubling of earnings in a ten-year period
10. Stability of growth in earnings - defined as no more than two declines of five percent or more in year-end earnings over the most recent ten years.

• http://files.gove.net/shares/files/12f/joshuar2/FinalPaperJoshuaLindsay.pdf
• http://files.gove.net/shares/files/12f/joshuar2/ProjectFinalJoshuaLindsay.xlsm

Eric Petersen - Resume Creator

Resume Creator - Executive Summary

The idea behind this VBA macro is to empower people to quickly create a formatted resume with everything they need to wow that future employer. Many times people do not have the time they need to do this and a tool such as the Resume Creator will give them that boost of time they need to get it done.

This macro has been created with a nice GUI interface in the ribbon so that they can select exactly what they want to add as information. They also can use the GUI to go through a step-by-step wizard to help them select which pieces of information they would like to include on their resume considering sometimes people have many jobs and skills they just cannot list all in one resume.

The idea is simple, this helps one create a resume in a simple GUI format.

Resume Creator - Links

http://files.gove.net/shares/files/12f/erp010/Resume_Creator.pdf

http://files.gove.net/shares/files/12f/erp010/121206-Final_Project_3.0.xlsm

Restaurant Point Of Sale App

Executive Summary

“Spuntinii Point of Sale Application” is a simple point of sale application for restaurants and will be used in my brother’s recently opened fast food restaurant (which goes by the name “Spuntinii”). I have been working with my brother in various aspects of running the restaurant. One of the problems that we face is minimizing food wastage. As the current process of order management is paper based, we are unable to analyze the trend of menu item sales accurately. Another problem is the time spent in keeping track of the daily sales. My brother spends almost half an hour every day going over the sales list and tallying the cash. I have been planning to automate the sales process and was initially inclined to either purchasing a point of sale software or hiring a developer. But after taking up Spreadsheet automation class by Dr. Gove, I was motivated to create a simple point of sale application for the restaurant.

The application uses MS access database and has a simple database design. The application provides a screen (sheet) to place orders by choosing from the existing menu items. Orders that are previously placed can also be viewed from another screen. The restaurant manager can also print an order while placing it or also can access the previous orders screen and print.

Functionality of analyzing the sales trend of all the menu items via 3dColumn charts is also added to the system.

Detailed usage instructions along with screenshots and discussion on various design as well as development aspects are included in the project report document. Future additions to the application would include several chart based analysis options for comparing the sales of various menu items, sales over a period of time etc.

Files:
ZIP file containing the xlsm file, access db & images folder

Project Report - PDF file

 

Email List Clean-up

Background

This project is a useful tool to help remove bad email addresses from your many email lists before sending a large batch of emails. It was built for an existing need of a small non-profit publishing organization. They have several lists of email addresses that are used for different purposes. Sometimes these lists overlap and other times multiple lists are combined to send the same email message. Many of these addresses are no longer valid and some people have requested multiple times to stop receiving emails. Unfortunately, the process of keeping these black listed email addresses was all but effective. Each time and email is sent out to a batch, many return emails come back. It has proven to be a headache to the organization and has frustrated many potential customers.

Solution Overview

This solution addresses the problem of cleaning up email addresses. The spreadsheet can be passed around the organization and still access the same blacklist. The functionality is built into the code of the spreadsheet, but no email addresses are saved to the list. The blacklist emails are stored in an access database that will be stored on a server accessible by the members of the organization. Email addresses can be added to the blacklist database using the spreadsheet. But the spreadsheet doesn’t need to be saved to a central location.

The clean-up functionality takes care of the following problems that have been happening:

•  Badly formed email addresses
• Email addresses that have been blacklisted
• Addresses that have requested to unsubscribe from all emails
• Duplicate email addresses from combining lists

The solution will return a cleaned-up email address list that has removed the types of addresses above. It includes a sheet that categorizes the emails that were removed. The cleaned email list that is returned will be sorted alphabetically and all characters will be in lowercase.

Files

• http://files.gove.net/shares/files/12f/montemca/EmailListCleanUp.pdf
• http://files.gove.net/shares/files/12f/montemca/Monte_McAllister_-_Email_List_Clean_Up_-_Semester_Project.xlsm
• http://files.gove.net/shares/files/12f/montemca/BlackList.mdb

Argus Translator 1.0

Executive Summary

            I have been working at Utah Retirement Systems on the real estate investment team for approximately 6 months. One of my assignments has been to assess the risk within the real estate portfolio of the overall state pension fund. Monte Carlo simulation is one of the tools that I and my team have chosen to use to assess risk.
            Argus DCF is a software platform used to build financial models for various types of commercial real estate properties. A single Argus file contains significant amounts of data related to a single property, which is then used to build a discounted cash flow schedule and determine the IRR associated with those cash flows.

Though it is considered an industry standard software platform, it is not without its weaknesses. One of these is its Monte Carlo simulation engine, which is neither as fast nor as powerful as Excel add-ins such as Risk Solver and ModelRisk. While Argus will export its assumptions and results into Excel, the exported data are in a static format. In order for this exported data to be useful, it is necessary to reconnect the assumptions to the results using formulas in order to create a dynamic model. Only then can the model be used in Monte Carlo simulations. This requires a significant time commitment, often several days, for each property. With a portfolio in excess of 300 properties, the time commitment required to accurately assess portfolio risk is prohibitive.

The program I have designed pulls data from an Access database containing Argus data and then builds a dynamic cash flow model in Excel that is simulation-ready. I will further discuss this program and its functionality in the pages that follow.

• http://files.gove.net/shares/files/12f/dmmalone/Argus_Translator_Report.pdf
• http://files.gove.net/shares/files/12f/dmmalone/Argus_Translator.xlsm
• http://files.gove.net/shares/files/12f/dmmalone/Argus_Translator_Tenant_Worksheet.xltm
• http://files.gove.net/shares/files/12f/dmmalone/Argus_Translator_Totals.xltm
• http://files.gove.net/shares/files/12f/dmmalone/Argus_Translator_Assumptions.xltm
• http://files.gove.net/shares/files/12f/dmmalone/ArgusTestDB.accdb

Chart Builder

Utah Valley University (UVU) is an institution of higher education and is the largest public university in the state. The department for which I work specifically, Institutional Research and Information, is responsible for all surveys administered to students, employees, alumni, and others.

One such survey is entitled “Great Colleges to Work For,” a national survey of full-time employees in higher education. This survey is conducted externally by a company called ModernThink. It is comprised of 60 statements about working at UVU, to which employees respond on a 5-point agreement scale (“Strongly Agree” to “Strongly Disagree”). Because of quirks in the way the data are presented, the reports delivered by ModernThink are almost unusable for our purposes.

In order to remedy this situation, the Graph Builder workbook contains a macro that transfers all of the data from this report to an Excel sheet. Below is a brief overview of the major steps taken by this macro:

  1. Import data from a text file
  2. Extract all important data from the text
  3. Format the data into tables
  4. Produce a chart for each table

After running this macro, the user will have an easy-to-read Excel sheet containing all 60 statements and a chart for each. From this sheet it will be much easier to produce a usable and valuable report.

Full Report
VBA Project
Great Colleges Text File

Submitted by Taylor Lovell

Schedule Meetings

Executive Summary

The final project that I did is for professional firms to schedule meeting times. My project contains a spreadsheet with two user forms. One form is for users to edit or update personnel information. And the other form is for users to input attendees of a meeting, and then form the best time to meet. When there are more than one available times to meet, the user can use the “Next” “Prior” button to navigate and determine the best time themselves. 

• http://files.gove.net/shares/files/12f/wanrui/Final_Project_Write-up_Wan_Rui.pdf
• http://files.gove.net/shares/files/12f/wanrui/VBA_Final_Project_Wan_Rui.xlsm

TRAXI—an Excel-based time-tracking application

It's hard to know how to use your time more effectively without a clear understanding of how much time you are actually dedicating to each of your day-to-day activities. That's where TRAXI can help. TRAXI allows you to track your time using the one item you always have on you—your cell phone.

TRAXI imports the text messages you have sent to a designated phone number, calculates the duration of each of the activities you have logged, cleans and categorizes the data, and generates a report of your time usage for the given period.

That's how TRAXI is akin to a taxi service. It helps you get from point A—how you are using your time right now—to point B—where you want to be.

http://files.gove.net/shares/files/12f/ngb2003/TRAXI_Write-Up.pdf

http://files.gove.net/shares/files/12f/ngb2003/TRAXI.xlsm