Monday, April 21, 2014
Searching cars in Provo on Craigslist has never been this easy!
Executive summary
Searching cars on craigslist has never been fun for most of us. The functionality of the search function on craigslist is sufficient, however, we cannot search through all the result easily. Also, it is impossible to keep different model’s search result together. This is project is aimed to solve this problem. It provides a clean and efficient user interface to search through craigslist Provo to find the perfect car for you. It transfers all the searching criteria you specify in the system and collect all the matching records from craigslist. Then it displays in a very organized view. Moreover, you can keep searching different types of cars and the system will save them into one spreadsheet. This way, you can compare the price, location, different model and make very easily through excel sort function.
List of uploaded files
Thursday, April 17, 2014
Automated Qualtrics to Eventbrite Registration for the WIB Club
Executive Summary
Business
The Marriott School Women in Business Club (Undergraduate) currently has 270 club members and is a two-time winner of the Bateman Outstanding Marriott School Club Award. The Women in Business (WIB) Club provides opportunities for women to connect with fellow classmates and professionals, learn about opportunities for women in the Marriott School, and gain skills that are valuable in the workplace as well as in other aspects of life.
I served as the Club’s president last school year (2012-13) and in various in years prior. As such, I have a vested interest in the Club’s progression and image. Those driving factors are what led me to the idea for this project.
Problem
Every year the Marriott School and Women in Business Club co-host the Major Shopping Event. The event typically has over 400 registrants. The event's purpose is to attract freshmen and sophomore women to consider earning a Marriott School degree. Thus, registration is structured to screen out those who are not seriously considering a business degree. Those who are already in the Marriott School can attend by bringing a friend who might seriously consider earning a Marriott School degree or by volunteering to market before the event and help during the event. The registration process verifies that these requirements are met.
In the past, the Club has used both Qualtrics and Eventbrite systems for registration. Qualtrics because of its ability to display different questions based on responses. Eventbrite has been used to enhance the users experience by sending a confirmation email right after registration and a reminder email the day before the event. Another major reason for using Eventbrite is because of its great check-in list and process on mobile devices. As you can image, this makes checking in 400 plus people very easy on the day of the event.
However, the Club has found that sometimes people only get registered on one system, not both. The Qualtrics link is publicized for registration. Two different methods for completing registration through Eventbrite have been implemented. One, after answering the last question the system redirects them to Eventbrite. Two, a link to Evenbrite is provided and the user is asked to register through Eventbrite and then say that they did on Qualtrics before the Qualtrics survey will submit.
However, sometimes the redirect takes too long or people forget to come back and registrants don't realize that they didn’t complete their registration. Also, since some registrants bring a friend, the friend must too fit the attendance criteria. Sometimes a friend will get registered on Eventbrite even though they haven’t been entered into and verified through Qualtrics.
Solution
I built a program that receives user feedback for credentials and survey or event titles, downloads a table of those who have registered on Qualtrics and finds that data necessary to register on Eventbrite. The program also downloads a table of those who have been registered on Eventbrite. In the future I will add the functionality that registers attendees from Qualtrics to Eventbrite.
This solution will eliminate confusion for registrations while still providing an enjoyable experience for registrants in receiving the confirmation and reminder emails from Eventbrite. Furthermore, check-in will continue to be quick and easy on the day of the event. The Club and Marriott School will no longer need to worry about students being registered on one system and not the other.
Dallin Lemich's Executive Summary
My project is for Skyline Motors,
Inc., a GM dealership in Rawlins, WY. I
spent the summers of 2011 and 2012 working there as the Finance & Insurance
(F&I) Manager, which mostly entailed 1) arranging financing for customers,
2) overseeing the legal and regulatory paperwork and requirements for each car
deal, and 3) selling aftermarket products, packages, and insurances. As part of the process, one tool that I used
with nearly every transaction was called a menu. This menu was used to present the products
and insurances to the customer and to finalize the lender choice decision. It
simply lays out a few options—or baskets of the products available— and shows
the customer how certain selections will affect their monthly payment.
The
need for this project arose when the dealership switched insurance product
providers from Zurich Insurance Group, through whom the menu software was
available, to GM Financial, who doesn’t currently offer anything of the
sort. The menu is a very useful tool in
the sales process, so when I asked the dealer whether he had a project I could
work on for this assignment, this idea came right out. We basically just used the old Zurich menu as
a template for the new one, at least as far as presentation and aesthetics
go. However, there were a few minor
changes that he and I came up with throughout the planning process.
The
system I created in Excel simply takes information about a car sale transaction
and certain products to be offered to the customer—input through three user
forms—and formats it in a clean, presentable way. In the process, the system calculates a few
things such as sales tax to be charged, the customer’s monthly possible monthly
payment under five scenarios including without any added products, and how much
each added product will add to the monthly payment.
My files are at the following urls:
Excel file --> http://files.gove.net/shares/files/14w/dlemich/IS_520_Final_Project.xlsm
Write-up --> http://files.gove.net/shares/files/14w/dlemich/VBA_Final_Project_Write-up.pdfJST BI Blitz!
By Sean Fisher, Tahna Black, Joseph Burwasser
The Brigham Young University Information Systems program currently provides students taking their introductory courses with opportunities to explore business intelligence. Unfortunately, this capability is only provided through Microsoft SQL Server and students must either install this software on their personal computers or use the school’s desktops, which are limited in quantity. We have provided a solution to this situation: enabling students to utilize the basic business intelligence functions by including them in a downloadable workbook called JST BI Blitz.
The objective of this project is to provide business intelligence algorithms for free to students in a form doesn’t require Microsoft SQL Server.
These algorithms include:
- K-Nearest Neighbors(KNN)
- Naive Bayes (NB)
The workbook displays the results of these algorithms in an intuitive manner which can be easily analyzed.
Aside from the algorithms, this workbook aims on providing other Business Intelligence-related capabilities to users. A customized ribbon includes the following options:
- Cleaning the data before it’s analyzed (removing rows with blank values)
- Partitioning the data into a training and a validation set
- Tutorial for effectively utilizing the workbook
With the Blitz workbook, students are able to gain experience with statistical analysis in a convenient manner on their own laptops. Students can learn to build BI models without any additional software or financial strain.
Project Files:
Multi-Function Employee Management System
Executive Summary
Multi-Function Employee Information Management System provides a new way to
install, update, and organize employees in management efforts. The system has 7
sub-systems including Employee’s Basic Information, Transfer Record, Work
Experience, Health Information, Injury Record, Penalty Record, and Dimission. The HR can easily find the employee’s information and records and search for specific employee’s records by using this Multi-Function Employee Information Management System.
The system was built within a Main Page which contains the 7 sub-systems. Each
sub-system will show up when you click the sub-system title on the Main Page. For example, when you click “General Information” subtitle on the Main Page, the
sub-system will show the general information for the company which contains the
status of the company (number of current employee, new hire, number of employee
who left the company, etc. ) and business unites of the company. When you click
Employee Business Unite subtitle of the main system, you will see the name of each business unit is listed on the left side of the system. You can click any business unit, and the related sub-business unit will show up in the middle of the system. The employee’s name who relates to this sub-business unite will also show up. You can also click “Detail” on the right corner to review the employee’s general information. You can also click “Injury Record” subtitle to review each employee’s injury record information. For example, you can type in “Lucy” as a name and click “Search” to search the injury record information of Lucy, such as what her illness was, when she had this illness, where she got this illness, and how much it cost. You can also add employee’s injury records to the Injury Record subsystem by filling out the empty spots. In addition, you can save and export the records by simply clicking “Save” and “Export”. The information will automatically be saved in your computer. In addition,
you can click “Transfer Record” to see employee’s transfer information from one business unit to another business unit. You can also add employee’s transfer record to the Transfer Record subsystem. In this subsystem, you can see the name of the employee, the transfer time and reason, the old and new position, and the old and new business unit. Also, there is a penalty record subsystem available to record each employee’s penalty. You can see the reason and date this penalty happens, and you can also add records to it. In conclusion, this system is built to help the company to find, search, add, delete, save, and export employee’s information in a more effective way. This system will reduce the time spent on employee’s records and increase the information accuracy.
Multi-Function Employee Information Management System provides a new way to
install, update, and organize employees in management efforts. The system has 7
sub-systems including Employee’s Basic Information, Transfer Record, Work
Experience, Health Information, Injury Record, Penalty Record, and Dimission. The HR can easily find the employee’s information and records and search for specific employee’s records by using this Multi-Function Employee Information Management System.
The system was built within a Main Page which contains the 7 sub-systems. Each
sub-system will show up when you click the sub-system title on the Main Page. For example, when you click “General Information” subtitle on the Main Page, the
sub-system will show the general information for the company which contains the
status of the company (number of current employee, new hire, number of employee
who left the company, etc. ) and business unites of the company. When you click
Employee Business Unite subtitle of the main system, you will see the name of each business unit is listed on the left side of the system. You can click any business unit, and the related sub-business unit will show up in the middle of the system. The employee’s name who relates to this sub-business unite will also show up. You can also click “Detail” on the right corner to review the employee’s general information. You can also click “Injury Record” subtitle to review each employee’s injury record information. For example, you can type in “Lucy” as a name and click “Search” to search the injury record information of Lucy, such as what her illness was, when she had this illness, where she got this illness, and how much it cost. You can also add employee’s injury records to the Injury Record subsystem by filling out the empty spots. In addition, you can save and export the records by simply clicking “Save” and “Export”. The information will automatically be saved in your computer. In addition,
you can click “Transfer Record” to see employee’s transfer information from one business unit to another business unit. You can also add employee’s transfer record to the Transfer Record subsystem. In this subsystem, you can see the name of the employee, the transfer time and reason, the old and new position, and the old and new business unit. Also, there is a penalty record subsystem available to record each employee’s penalty. You can see the reason and date this penalty happens, and you can also add records to it. In conclusion, this system is built to help the company to find, search, add, delete, save, and export employee’s information in a more effective way. This system will reduce the time spent on employee’s records and increase the information accuracy.
Consultant Tools
Executive Summary
Partners In Leadership, Inc. is a widely respected international leadership training and management consulting company. Founded in 1989, the company is now the world’s premier provider of Accountability Training Services. All training services are based on books (most notably The Oz Principle) written by the company’s founders, which focus on creating greater accountability.
The Excel Consultant Tools file I created will allow Partners In Leadership Consultants to assess how a company’s employees view accountability within the three tracks. Before doing a workshop or training session, consultants will send a Google Form link to the employees. The form will ask a series of 46 questions (on a scale of 1 to 10) that are related to the three accountability tracks. The Consultant Tools file will then scrape the data collected by the form online and put it in an Excel spreadsheet. With this data, pivot tables and charts will be automated so that the consultant can evaluate where the company is struggling with accountability. The objective is to give the consultant some direction in their training preparation.
Download the Project Write Up: VBAProject.pdf
Download the Excel file: ConsultantTools.xlsm
Partners In Leadership, Inc. is a widely respected international leadership training and management consulting company. Founded in 1989, the company is now the world’s premier provider of Accountability Training Services. All training services are based on books (most notably The Oz Principle) written by the company’s founders, which focus on creating greater accountability.
- The Self Track Training helps people embrace their own accountability for achieving organizational results.
- The Culture Track Training accelerates the change within organizational culture to produce the results the organization needs now.
- The Others Track Training teaches people to effectively hold others accountable for results in a positive, principled way so that they successfully deliver on expectations.
The Excel Consultant Tools file I created will allow Partners In Leadership Consultants to assess how a company’s employees view accountability within the three tracks. Before doing a workshop or training session, consultants will send a Google Form link to the employees. The form will ask a series of 46 questions (on a scale of 1 to 10) that are related to the three accountability tracks. The Consultant Tools file will then scrape the data collected by the form online and put it in an Excel spreadsheet. With this data, pivot tables and charts will be automated so that the consultant can evaluate where the company is struggling with accountability. The objective is to give the consultant some direction in their training preparation.
Download the Project Write Up: VBAProject.pdf
Download the Excel file: ConsultantTools.xlsm
Wednesday, April 16, 2014
AMEX to Certiflex
Business
Description
The accounting firm
that this project was created for has many clients that need book keeping work
done for them. The firm uses CertiflexDimensions (Certiflex) as their
accounting software. Client journal entries can be uploaded from Excel into Certiflex
if formatted correctly. The firm will often need to create these journal
entries from bank statements and credit card statements.
Business
Issue
Employees at the firm
will copy client credit card information onto Excel and associate each entry
with a Certiflex account. This is a very tedious and time-consuming process for
employees, as client’s card information may easily consist of thousands of
entries a month. As employees enter information, they will make errors that go
unnoticed and are hard to find.
Solution
I decided to automate
this process as much as I could in order to save the company time/money as well
as make the data entry process as error-free as possible. This project is split
into four parts:
(1) displaying instructions for
the user to follow;
(2) importing the data from AMEX
online using the client’s username and password;
(3) reformatting the data, matching
a Certiflex account number with each entry, and presenting a quality review
interface where the user can easily check that the program matched the correct
account with each entry make any necessary changes; and
(4) formatting the data for
Certiflex upload.
As most of the
clients use American Express (AMEX) cards for business I decided to start with
a macro for AMEX cards. This project has the capacity to expand into more uses
and pull card information from other card companies.
Automating an Accounting Process
Purpose
The goal of this project is twofold: (1) Provide a link between an online sales database and the accounting system. (2) Using information retrieved from those databases, create, format and print payment statements to accompany monthly checks sent to merchants.
The goal of this project is twofold: (1) Provide a link between an online sales database and the accounting system. (2) Using information retrieved from those databases, create, format and print payment statements to accompany monthly checks sent to merchants.
Company Background
The selected company is an online deal site similar to Groupon and Living Social with a main focus on the Utah market. Just prior to my employment at the company, it was acquired from the former owners by its primary creditor due to solvency issues.
The business model is simple (1) customers purchase discounted deals on the website and present the certificates to the merchants to receive the products (food, spa treatments, carpet cleanings, etc.). (2) Each month, the company sends a portion of the sales proceeds to the merchant to compensate them for the services offered. This payment is calculated based off the number of sales and a predetermined split per sale. All of these transactions are processed and stored on the website.
Problem
All transactions are tracked in an online database maintained by a third party, and the only access to this data is through preexisting web reports which are then manually transferred to the accounting system. This results in the accountant manually entering hundreds of entries each month. Past turnover has resulted in inconsistent treatment of monthly entries as well. Overall, the manual process resulted in a time consuming process that only muddied the economic picture due to inconsistencies. It can only be imagined that the poor state of the accounting information system was a contributing factor in the solvency issue faced by the former owners.
Solution
In order to automate the process as much as possible, I have create procedures in MS Excel which process the reports from the web portal, perform the necessary manipulations and calculations, and saves the output in a format and file type that can be updated to MAS 500, the accounting system. This includes writing at least one procedure for each of the following process:
- Creating new merchants in the accounting system
- Calculating cost of goods sold and inputting vouchers into the accounting system
- Inputting debit memo vouchers for returns
- Calculating and recording any sales/ cost of goods sold discounts
- Creating statements to send with the monthly payments to vendors.
For purposes of this project, I have chosen to focus on the new merchant process as well as the creation of the monthly payments statements.
The new merchant process extracts the merchant information for all items sold in the current month and compares it to existing vendors in the accounting system. If it is unable to match the information, it allows the user to match the merchant to an existing vendor or to mark it as a new merchant through the use of the same user form. The procedure then uses this information to create and populate an uploadable file, which the user then uploads into the accounting system to create the new vendors.
To create the monthly payment statements, a different procedure compiles sales, refunds, and discounts information from the current month as well as prior months. This information is grouped by deal, month and subtotaled by merchant. Payment information is grouped by payment week, and the weekly subtotal by merchant equals the amount of the check scheduled to be mailed on that week.
Once the information has been compiled and sorted, the totals can be automatically compared to the scheduled payments in the accounting system. After differences have been researched and corrected, the user can then use the procedure to format the statement in an aesthetically pleasing format.
Finally, the statements are printed by the procedure in the same order that the checks will be printed in order to allow easy matching and mailing of both in the same envelope. In addition, a PDF of each statement is saved to a network location and unique subfolder based on the merchant name. This allows for easy retrieval in the event that the customer has a question about their statement or payment amount.
Result:
The results of this automation process including procedures not outlined in detail are also twofold (1) Time savings were approximately 56 hours. A reduction from 60 hours to 4 hours with the majority of the existing time aimed at resolving quality assurance and accuracy issues which are flagged by the processes. (2) Increased consistency resulting in decreased merchant inquiries. Merchant inquiries decreased from 3-4 inquiries per day with 3 or 5 payment corrections each month to 2-5 inquiries per month and no payment corrections each month.
Download the following files for a detailed overview and to run parts of the Macro yourself:
- http://files.gove.net/shares/files/14w/skmoon1/Daily_Sales-04-30-14.xls
- http://files.gove.net/shares/files/14w/skmoon1/Product_Sales-04-30-14.xlsx
- http://files.gove.net/shares/files/14w/skmoon1/MAS_500_Macros.xlsm
- http://files.gove.net/shares/files/14w/skmoon1/Vendor_Name_Mapping.xlsm
- http://files.gove.net/shares/files/14w/skmoon1/vendor_addresses.xls
- http://files.gove.net/shares/files/14w/skmoon1/Deal_Site_Macros-_Write-up.pdf
- http://files.gove.net/shares/files/14w/skmoon1/New_Vendor_List.xlsx
Point-of-Sale System
For my
project I created a small point-of-sale system for a fictitious small Mexican
restaurant. The system would be used by restaurant employees and managers to
record and track customer orders as well as view revenue and sales information.
The system was built using VBA and Excel formulas and runs in Excel. I used the
menu of Mountain West Burrito for the system menu items.
Files:
http://files.gove.net/shares/files/14w/devinwe/FinalProject.xlsm
http://files.gove.net/shares/files/14w/devinwe/FinalProject.pdf
Files:
http://files.gove.net/shares/files/14w/devinwe/FinalProject.xlsm
http://files.gove.net/shares/files/14w/devinwe/FinalProject.pdf
Unemployment PowerPoint Slides Maker
EXECUTIVE SUMMARY
When seeking an appropriate project for this final
assignment, I solicited my past professors from last semester asking if there
was something that they wish was automated in their daily work. Every professor that I emailed replied to me
with a potential project that would help them.
Unfortunately, I was only able to select one for one of the MBA finance
professors, Grant McQueen1
Professor McQueen has been tracking the economic recovery of
the US after the 2009 financial meltdown.
He makes presentations on the data including, US unemployment rates,
regularly using a PowerPoint presentation.
Each time he presents his findings he has to pull data from the Federal
Reserve Economic Data (FRED) website into excel, create several charts and put
these charts into his PowerPoint slide deck.
This process takes about 2 hours.
Professor McQueen asked me to automate this process. I was successful in creating a VBA tool that
reduces the processing time down to 60 seconds.
My Tools automates the process using 4 sub procedures:
1)
getData() – Pulls data from the FRED website
using the Professor Allen’s agent module and inputs the data into an excel
worksheet
2)
user() – the checks who the user is and which
API Key to use.
3)
MakeCharts() – Creates 4 charts of unemployment
data
4)
ChartsAndTitlesToPresentation() – Exports the
charts into an existing PowerPoint presentation
This project has increased my comfort level with the topics
covered in class, but it has also exposed me to several other topics not
covered in class specifically:
1)
Application Programming interface (API) keys
2)
Manipulating other Microsoft Office applications
other than excel using VBA
1 I was able to
leverage the interest from all of the professors to land a paid VBA consulting
project with the director of the MBA program, John Bingham.
- http://files.gove.net/shares/files/14w/dkt7/David_Toronto_Final_Project_Report_Rev1.pdf (Updated Learning outcomes section)
Box Office Crawler
Executive Summary
For my project, I decided to create something extremely practical and immediately useful in my own work and school. In Business Intelligence, my team and I wanted to research the answers to many important questions about the movie industry, such as what makes a movie successful. So, I created a VBA script to meet our huge need of grabbing thousands of pieces of data as well as converting them to usable forms and cleaning them in preparation for analysis. However, it has since evolved into a powerful data scraping and analyzing tool for anyone who wishes to use it. My VBA project works in conjunction with Box Office Mojo, the largest and most complete movie database in the world (owned by IMDB). Anybody who wishes to grab useful information on a movie or any number of movies, and then analyze the success of those movies based on many different factors, are welcome to use my workbook, and even embellish upon what I have created. The script I wrote to scrape data, easily executed through a custom button in the ribbon (“Get Movie Info”), uses the hyperlinks connected to any movie title copied from BoxOfficeMojo.com in the Title column of the “Movies A-Z” worksheet to grab the producing studio, total box office gross, opening date, rating, genre, director (if listed/known), total known days in theaters, day of the week opened, budget (if listed/known), and runtime of the movie, all in two seconds or less (per movie). It also converts all gross and budget figures to 2014 dollars, so that movies from all years may be compared side-by-side, and it extracts the specific year and month of the movie release, for purposes of analyzing. Once all records and related data have been retrieved, the user may click “Create Result Charts” and the workbook will generate multiple charts to compare movies based on earnings.Excel File
PDF Report
Senate Vulnerability
Over the last eight years, the Democratic Party has controlled the United
States Senate. Though they currently hold 53 of the 100 positions, many political
analysts believe the Republicans could take the majority after the 2014
election. Among these analysts is Nate Silver, a political statistician who
correctly forecast every state during the 2012 Presidential Election. With
only 45 current Republican Senators, this would require them to hold all the
positions they currently have and gain 6 additional seats from their opponents.
Is this likely?
Due to my interest in national politics, I used this final project as an
opportunity to conduct my own analysis on the topic. This project consisted of
three primary steps:
1. Transfer 2012 presidential election results from the internet into MS
Excel
2. Transfer data about our current U.S. Senate from the internet into MS
Excel
3. Determine which Senate positions are most vulnerable in 2014
Vulnerability was based on which Senate positions are currently held by a different
political party then the state’s preference during the 2012 Presidential Election.
For example, I determined which states voted for Mitt Romney in 2012 but
currently have a Democratic Senator up for re-election later this year. In
contrast, I looked at which states recently voted for Barack Obama but have a
Republican Senator up for re-election. Since the presidential election is the
most recent indication of public sentiment within each state, I believe we can
safely conclude that the senate positions under these conditions are most
vulnerable.
The results revealed that there are seven vulnerable Democratic Senators
from West Virginia, South Dakota, Montana, North Carolina, Alaska, Louisiana, and
Arkansas. On the other hand, only one Republican Senator from Maine is in a
similar position. Based on these results and the low approval rating of
President Obama, I believe the Republican Party has a viable shot at taking
over the Senate next year. We will know shortly as we enter the general
election in November.
Attachments:
http://files.gove.net/shares/files/14w/dustinlr/Dustin_Richins_-_VBA_Final_Project.pdf
http://files.gove.net/shares/files/14w/dustinlr/Dustin_Richins_-_Final_Project.xlsm
Attachments:
http://files.gove.net/shares/files/14w/dustinlr/Dustin_Richins_-_VBA_Final_Project.pdf
http://files.gove.net/shares/files/14w/dustinlr/Dustin_Richins_-_Final_Project.xlsm
Subscribe to:
Posts (Atom)