Abnormal Returns Tool - Haidyn Knuteson

Executive Summary: Abnormal Stock Returns

From time to time, there are market occurrences which swing cause market over-reactions. This tool helps to gauge how much of those swings are cause by actual market occurrences and what portion is attributable to over-reactions by the market.

A tool like this would be helpful in determining which stocks are overvalued and inflated. An examples when a tool like this would be helpful is illustrated by Google on January 31, 2008. Google planned to release their earnings that day at 4:01 PM but hit the submit button too soon and released earlier that afternoon. This act inherently wasn’t necessarily detrimental but the fact that they missed their earnings was. This sent the market into a frenzy and Google’s stock fell 9% in roughly 4 hours. Much of this swing was due to investors scrambling to protect themselves.

This tool helps to gauge how much of a stock swing is truly due to stock fluctuations and what part is abnormal. 

Links: 

• http://files.gove.net/shares/files/16w/haidynwk/FINAL_PROJECT.xlsm
• http://files.gove.net/shares/files/16w/haidynwk/Abnormal_Returns_Tool_Exec_Summary_Haidyn_Knuteson.pdf

Finding Insurance Pricing Discrepancies

Executive Summary

A recent study found that some medical procedures are priced differently depending on the place where they are offered. A Utah based insurance company wanted to make sure that wasn’t the case in their company so that their members could receive the fairest prices and refer members to the cheapest option. This project takes a table received from a SQL query and separates it into sorted groups through Pivot Table automation, and then compares how much a medical procedure costs in varying places of service, whether a hospital , doctor’s office, urgent care clinic, or an emergency room, and sees which one is the most and least expensive of all available options. Then it calculates the difference in pricing and calculated how much money the members could have saved if they had all been charged the lowest price, and produces summary statistics across all different kinds of procedures. The code was made flexible to process different kinds of medical procedures that use different classifications, and filters data based on the state and insurance plan used. Potential savings to customers and the insurance company of several million dollars were found if the discovered pricing discrepancies were resolved. 

Links

• http://files.gove.net/shares/files/16w/jsee2/Final_Project_Write_Up.docx
• http://files.gove.net/shares/files/16w/jsee2/Excel_Final_Project.xlsm

Red River Medical Reporting

 (Names, locations, and data have all been changed for privacy purposes)

One of my primary responsibilities is to manage all of the reporting for the company.  Red River Medical is a distributorship of orthopedic surgical devices and implants for a parent company called Sandex.  Red River Medical is in Las Vegas, NV and covers territories in Nevada, New Mexico, and Arizona.  One of the challenges Red River Medical faces is working with two independent supply chain systems.  Much of the financial and operation reporting done is used to reconcile and verify information is correct between the two systems.  Other reports are used to calculate commission dollars for sales reps of Red River Medical, sales data, revenue, inventory, and quotas.

My first responsibility was to learn the different reports that were needed and used by the inventory department and the owner’s of the company.  All of the reports are done in Excel, and I quickly realized that the current method of doing reports was extremely inefficient.  While taking the IS 520 class I have applied different VBA concepts to reporting and have made drastic improvements in just a couple months.

The following reports are reports I have created or modified using VBA since being hired in January:

• ·      Daily Report
• ·      Commission Report
• ·      Monthly Purchased Instruments
• ·      Percent to Quota
• ·      Red River Focus Groups YTD

    These different reports use VBA to copy data from downloaded csv files from the parent company database and paste it and organize it on the report templates that have been built.  There are also sub procedures used to organize different types of data into tables and charts to make it easier to read and understand.

• http://files.gove.net/shares/files/16w/aisom2/Executive_Summary.pdf
• http://files.gove.net/shares/files/16w/aisom2/Commission_Template.xlsm
• http://files.gove.net/shares/files/16w/aisom2/Monthly_Purchased_Instruments.xlsm
• http://files.gove.net/shares/files/16w/aisom2/PTQ_TEMPLATE.xlsm
• http://files.gove.net/shares/files/16w/aisom2/Red_River_Foucs_Groups_YTD_QTY_TEMPLATE.xlsm
• http://files.gove.net/shares/files/16w/aisom2/Daily_Reports.zip

Special Project P&L update for Business Firm - Cole Moffat

A Consulting Firm has implemented a new Special Project and hired interns to work on it. This new project does not earn any revenues, but when there is a surge of billable work, the Firm takes some of the interns to fill in the gaps. This way, they can bill clients at the standard rate, pay the interns the same as they always do, keep the difference, and not have to constantly hire and fire their workforce to meet the demand. Interns submit weekly timecards, detailing the things they work on. Paychecks to interns is cash out from the Project, and amounts billed to clients from interns’ work is cash in from the project. The Business Firm wants to know how much this Special Project is making them or costing them, updated weekly.

The VBA program, complete with custom buttons on a new ribbon tab, prompts the user to select a raw data file with the updated timesheet information; imports the data, configures it to fit the template, and appends it to the existing timesheet data; updates/ refreshes pivot tables; uses that updated information to recalculate a Project P&L; displays the last 4 weeks’ data and the grand total since project inception; and saves the updated data as a new file with the data processing date. The Business Firm will know the profitability of their new Special Project in near- real time.

*This is based on a real business with a real project. Due to the sensitive nature of the data, all dollar amounts, hours, employee names, company names, or other personally identifying information has been changed. The Consulting Firm is referred to as the Business Firm (BIZ) and the new project is referred to as Special Project (SP).

• P&L main file with VBA:
  http://files.gove.net/shares/files/16w/cpmoffat/MoffatCole_VBAFinal_ProjectPL.xlsm
• Workbook with New Data to be appended and processed:
  http://files.gove.net/shares/files/16w/cpmoffat/MoffatCole_VBAFinal_RawDataWorkbook.xlsx
• Write up:
  http://files.gove.net/shares/files/16w/cpmoffat/MoffatCole_VBAFinal_WriteUp.pdf

Ben Olpin's Bank Account Tracker

Bank Account Tracking

Executive Summary

My wife and I are very careful with our money, but our credit union’s website does not have the best tools to help us see what is happening with our money in a graphical way. The goal of this project was to create a tool that I could use that would log in to my bank account, extract a set of data, write it to a worksheet and then create a chart that represents that data. This was the bulk of the project and I found ways to make my code do all of those things.

In addition, I wanted a way to keep track of receipts I get by category, so I made a user forms to enter in receipts as well as edit them if I’ve made a mistake. The form gathers the date, store, amount, and category of each receipt, making it easy to keep track of where our money is going. Rather than manually enter the categories, they are validated by checkboxes so the user cannot enter in a category I’m not tracking.

Finally I created a new ribbon titled ‘Financials’ that runs all of this code. The result is a powerful tool that’s both easy to use and looks clean, and I’m very excited about it.

• http://files.gove.net/shares/files/16w/bro22/Bank_Account_Tracking.pdf
• http://files.gove.net/shares/files/16w/bro22/Bank_Account_Tracking.xlsm

Kylie Wiser's Bond Charts and Lead List Generator

Executive Summary

Vivint Inc is a company that is in the smart home technology market. Vivint Inc supplies a variety of services to its customers to help make the customers’ lives easier and safer, such as home security, energy management, home automation, local cloud storage, and high-speed Internet solutions.\

Weekly, the Director of Treasury looks up current bond data on FRED and manually types in the date, YTW, STW, and calculates the price for Vivint Inc’s bond charts. I added the “Pull New Data” tab, which pulls the bond data from FRED, adds calculations and formatting to the pulled data, adds it to the bond charts, sorts the data from newest to oldest, and calculates the price of the bonds.

Meta is a brand new company that engages in fundraising for institutions like schools. It fundraises by having people go door-to-door with brochures of options of services that people may be in need of. If the potential customer is in need of any of the options in the brochures, they check the box. By selecting options, the potential customers are acknowledging that they are aware that their names will be sold to businesses, so that the business can contact them in the future. The proceeds from selling the local businesses the names of people who are in need of their company’s services go to the institution.

An owner at Meta may want to pull a list of specific services relating to a certain area, like auto services, so that they can approach a company with a list of potential customers to buy. I created a Lead List Generator userform that contains a listbox of brochure options that the customer may have selected. The owner could select all services relating to a certain business in the userform and then in the worksheet it compiles a list of all of those customers and their contact information that want those specific services and which of all the services selected they would like. This will make it easy for META to compile a list of potential customers to sell to the businesses.

http://files.gove.net/shares/files/16w/khekking/Final_Project_-_Meta.xlsm
http://files.gove.net/shares/files/16w/khekking/Final_Project_-_Vivint_Inc.xlsm

http://files.gove.net/shares/files/16w/khekking/Final_Project__Write_Up.pdf

Fantasy Baseball Draft Tool

Executive Summary

Fantasy sport leagues over the years has consistently grown in popularity.  One’s success in a league is often determined by how capable they are at drafting the best players for their team.  In order to do this, one needs to have information about the players, and especially look at how they did the season before.  The process of gathering all the stats into of place can be a long and tedious process.  This project was created to easily help fantasy baseball players gather baseball stats from ESPN.com of individual players by position, and then allow the user to run different queries to find players that match the criteria of the players that they are looking for.

http://files.gove.net/shares/files/16w/bchaser/Spreadsheet_Automation_Final_Project.pdf

http://files.gove.net/shares/files/16w/bchaser/520ProjectBaseball.xlsm 

Clark Cannon Mortgage Rates

Final Project: Real Estate Investment Analyzer Introduction. My dad works for a company called City First Mortgage. City 1st is a middleman for selling loans to investors as well as a financier for real estate. In order to comply with regulations and the level of detail involved in approving loans and investments the company has purchased a subscription to the service “Optimal Blue” (OB). OB is a cloud service where an employee will log into the service while sitting down with a potential investor. Company Information. City 1st Mortgage was originally established with $200 by founding Principal Gerret Van Wagoner. City 1st has been in the business of residential and construction lending since its establishment in 1993 and is headquartered in Bountiful, Utah and has over 7 branches around the US. City 1st business activities include: originating and funding residential mortgage loans, soliciting, placing/negotiating residential mortgage loans with other lenders, and rendering services related to origination and funding residential mortgage loans such as— telemarketing, underwriting, taking applications, obtaining verifications and appraisals, and communicating with the borrower. Current Process and Problem. To qualify investors and pull eligible products from the real estate database the loan officer will need a great deal of information. During this process the mortgage broker will take down the investors personal information and input the criteria into excel. Following his entry into excel he will transfer the data into Optimal Blue and then submit the request on the webpage. Following the request he will be returned with anywhere from 0 to 50 “eligible products” the investor is qualified to invest in. Next the broker needs to transfer the data into excel to perform a mortgage analysis on the customer in order to reveal the true cost and potential of the product as it relates to the client’s needs. The analysis is performed in a spreadsheet (which my project is done with) where he plugs in client and product values and a series of overly referenced cells returns some data to help him educate the client. Overview of Project. My proposal is to create a template for the loan officer to enter in the investors criteria that is required when using Optimal Blue to return eligibility and product information. In the ribbon there will be a button that will log into Optimal Blue with the users credentials, they will be directed to an investor entry form, the program will take the data from excel, input the data into the entry screen in Optimal Blue and then submit the criteria. Optimal blue will return a page containing eligible products, the program will then click on the second tab in the window for a better visual of the products info. The program will then import all the eligible products in a sheet called “NewRate”. The program will take the poorly formatted data in “NewRate” and output a clean organized version into the sheet “Input Form” where the investor and loan officer can review investment opportunities and ultimately close more sales from the increased efficiency of obtaining critical information.


http://files.gove.net/shares/files/16w/ccannon6/ExeSummaryVBA.pdf

Budget Vacation Dashboard

My fiancé and I love to travel! We have already looked at flights for trips or family get-togethers on multiple occasions. We are budget conscious and try to remain frugal. Because of this, we spend high amounts of time searching for the cheapest flight. I have created a solution using VBA code to shorten the research time required in this process. My solution allows the user to select multiple destinations from a list of favorites, enter other trip data, set a trip budget and pull flight information from kayak.com. Results are shown in a spreadsheet with within-budget flights highlighted in green. The user can click a button to go online and book the flight as well as track historical prices on a separate tab. 

Write up: http://files.gove.net/shares/files/16w/jhatch24/JakeVacationWriteUp.pdf

File: http://files.gove.net/shares/files/16w/jhatch24/BudgetVacationDashboard.xlsm

Real Estate VBA Project - KSL

Executive Summary

The Business Overview

Durrant Property Investments (DPI) is my father’s company and has been in existence for approximately 22 years.  DPI’s core business model is to invest in real estate in the Provo, UT area, specifically in single family homes and apartment complexes.  These properties are then rented out to BYU and UVU students.  DPI has employed students for years to help manage the properties, collect rent, perform cleaning checks, and upkeep the landscaping of the homes and complexes.

            For years DPI has found deals through building relationships with other real estate professionals in the community.  However, a wide variety of past deals have come from scouring the newspaper, searching for “For Sale” signs, and using the internet.  This last option is the one that is the most efficient, has the greatest amount of information, and is the best way to scan a lot of potential investments anytime and anywhere.  The problem is that it is both time consuming and difficult to first narrow down the options to those homes that meet your requirements and then to open each link to see if the homes meet your criteria.  It is also annoying because many of these properties have been posted multiple times.  It is like trying to find a needle in the haystack.

The System Overview

            My project solves these problems directly, providing a fast, organized, and painless way to rip all of the home data off KSL and then analyze all of the listed properties to find the best deals.  Using a UserForm, that takes the 15 different parameters to create the right URL to find all of the homes that meet the criteria.  The web scraper then opens every link of all the property listings on every single page.  For each of the specific properties, my tool scrapes off the address, price, property type, seller type, bedrooms, bathrooms, year built, square footage, acreage, garage information, basement information, and the description!

            The system then pastes all of the information into the “Home Data” tab with hyperlinks to all of the KSL listings.  After pasting all of the information for the homes that passed the initial filter, they are then sorted and analyzed on the “Dashboard” tab.  Using conditional formatting the top five cheapest homes by price per square foot are highlighted in green.  These top 5 homes are also ranked by price per acre and the conditional formatting of a green checkmark, yellow exclamation point, or red x are shown next to the price/acre, indicating the best deals.  Lastly, if the home is being sold by the owner, the last cell in each of the top rows is highlighted in blue to indicate that there is potential to negotiate a lower price due to the fact that there are no agent commissions for the seller.

Links to Files

• http://files.gove.net/shares/files/16w/dryand/VBA_Project_-_KSL_Homes_Durrant_Ryan.pdf
• http://files.gove.net/shares/files/16w/dryand/Real_Estate_KSL_Project_Durrant_Ryan.zip