HRIS Decision Support System

Executive Summary

How to determine the pay structure? What about employee benefits? How can we manage performance or training processes? When do we perform a job analysis? Can we change the workflow structure from that analysis? These are just a few examples of human capital challenges within organizations.

Decisions for this kind of issues need to be aligned to the organization's strategy, and in order to better inform these decisions, the HR function is being transformed to be more data driven.
Managers need decision support systems that not only provide meaningful insights, but that are also easy to use and portable.

The “HRIS Decision Support System” project is part of the MBA 614: Spreadsheet Automation course learning experience, and is designed to provide a tool for HR managers to make decisions concerning job analysis, pay structure, and individual employee’s compensation.

The solution is codded in VBA, and applies most of the tools acquired throughout the course, plus some other elements from online research, Even when the company it is based on is fictitious, the decision support system could be used in real business situations as a complementary tool for HR Managers in small/medium-size organizations, allowing them to import data, analyze it, and create useful reports.

• http://files.gove.net/shares/files/15f/luistv/VBA_Final_Project_Write-up_LUIS_TENORIO.pdf
• http://files.gove.net/shares/files/15f/luistv/HR_Information_Decision_System_-_Luis_Tenorio_v1.xlsm
• http://files.gove.net/shares/files/15f/luistv/employeePics.zip

Email Events

This project is for a very small university in Bethlehem, Palestine. Some of my family members are associated with this university, and I found out that the secretaries have to go through a lot of hard work to contact students and professors about certain events - even though it is a small university. So I created a code that could be used by these secretaries to update students and professors about upcoming events by sending them emails using a simple user form.

http://files.gove.net/shares/files/15f/lkuttab/Write-up_Final_Report_IS_520.pdf
http://files.gove.net/shares/files/15f/lkuttab/Final_Project_IS_520.xlsm

• http://files.gove.net/shares/files/15f/lkuttab/Write-up_Final_Report_IS_520.pdf
• http://files.gove.net/shares/files/15f/lkuttab/Final_Project_IS_520.xlsm

Calculate LTV of customer

Executive Summary

The business is for an independent insurance agency selling property and casualty insurance on behalf of several different carriers. For an agency, residual commissions are key for the overall revenue; since the value of a new customer cannot be measured by the first month’s commission, customer lifetime value (LTV) is used. This agency is in a startup phase, where retention curves are still unclear and commission contracts are being renegotiated frequently based on historical attrition. The project takes data pulled from the company’s servers and calculates the LTV for each new customer, allowing decision makers to see how the company is performing against budget. Using VBA allows the commissions and retention curves to be modified quickly in the spreadsheet without having to modify any formulas.

http://files.gove.net/shares/files/15f/robnance/VBA_Final_Project_Nance_Rob.pdf

http://files.gove.net/shares/files/15f/robnance/VBA_final_project_Nance_Rob.xlsm

Science Fair (CUSF) Judging Tool

Executive Summary:

My project was for the Central Utah Stem Fair (CUSF). The fair season beings with students competing at their individual schools and district in various categories, depending on the fair level (Elementary, Junior, or Senior). A select number of winners from each of the local fairs are invited to the annual-regional fair is hosted at BYU. CUSF is the BYU organization that is in charge of organizing, judging, and processing all data for the fairs. 

One of their major pain points is keeping track of all their judges and the judge requirements for each fair. My system allows for easy tracking of judges through add/delete tools and calculates judge requirements for each fair based on the number of projects. The calculations for judge requirements are essential as, they not only affect staffing logistics, but also, a certain number of judges are needed in order to statistically normalize all project scores.

http://files.gove.net/shares/files/15f/allredj8/FINALPROJ2.xlsm


http://files.gove.net/shares/files/15f/allredj8/VBA_FINAL_WriteUp.pdf

Current Events!

Executive Summary

In my Corporate Finance class our professor stressed the need to stay up-to-date on current events. I have heard counsel from other professors many times that we should read the Wall Street Journal or consistently read business and world news. As a poor college student, I cannot afford to pay for a subscription to the Wall Street Journal, nor do I have the time to sift through pages of articles about world topics. Sometimes I just forget to read the news because I am so busy with other course work.

The “Current Events!” Excel workbook is the perfect solution to this problem. The program will send you an email every day containing the main headlines from the Wall Street Journal and the Economist. The program routes through the BYU library website to access the free version of the Wall Street Journal and the Economist. It copies the article headlines and links then it sends them in an email to the email address of your choosing. If you see an article that interests you then you can click the attached link to read the entire article for yourself. The program is automated to run at 5:00pm every day, so I will never miss a day of current events.

The “Current Events!” workbook solves the problem of cost, because it gets news headlines from BYU channels completely free of charge. It saves me time, because I do not have to go out and look for the news, the news comes right to me. Finally, on the days where I forget about current events, I will receive a nice little email at 5:00pm to remind me of what is going on in the world.

I’ll never miss out on a day of current events again!

Note: the passwords for the NetID and email address have been removed from this workbook. In order to run the sub procedures you will need to type in your own BYU netID, email address, and passwords.

• http://files.gove.net/shares/files/15f/jbrown91/Current_Events.xlsm
• http://files.gove.net/shares/files/15f/jbrown91/Current_Events_write_up.pdf

Purchasing & Travel Compliance Report Creater

Executive Summary

In the Purchasing & Travel Department at BYU, each month a report is run to find issues that are reported on a monthly “Compliance Report” to controllers of the department on campus. The purpose of the report is to make sure employees are reporting and documenting the use of university resources in a correct and timely manner.

Five reports are run through Y-Expense, formatted to keep only the information needed, and then compiled to create the Compliance Report. These reports are Unattached Card Transactions, Unattached Cash Advances, and Open Purchasing, Travel and Visitor Expense reports.

My boss who compiles these reports usually takes a whole day to complete the task. He runs each report, filters and formats the information to leave the correct data, deletes duplicates, pulls information in to match operating units to department controllers, then sends each controller whose department is included on the report a copy. My project streamlines this process to make the task one that can be completed with a few clicks instead of hours of tedious work. 

• http://files.gove.net/shares/files/15f/hpj924/Final_Project-Hannah_Rios.pdf
• http://files.gove.net/shares/files/15f/hpj924/Format_BYU_Compliance_Reportfakeinfo.xlsm

BYU Club Signin

I wanted to be able to solve a problem that BYU Clubs face due to lack of availability of tools as much as disorganization. During club events, club leaders have to find a way to track who is and isn’t a member of their club, and aggregate those numbers in order to know total attendance. Since many clubs have a limited budget, having many “freeloading” participants may prevent them from being able to organize additional events due to lack of funds. Knowing attendance values is as useful to the club itself as it is to recruiters and each department's respective career centers. Unfortunately, BYU doesn’t enable this data gathering in any way. 
I think this can be solved by automating member check-in during these club activities. My application will allow a club to import their list of club members and check them in; and be able to reach out to gather information from prospective members who are visiting a club event without being a current member in order to send them information about the club such as benefits, upcoming activities, and how to join. I wanted to make the interface as easy to use as possible for club leaders so that they can focus on their roles as leaders, as well as quick in order to field a lot of people effectively. Once all of this data is gathered, it is trivial to aggregate the values and provide them as a report for accountability.

• VBA_Project_-_Club_Signin.xlsm
• MOCK_DATA.csv
• cfiloteo_FinalProjectWriteup.pdf

Apparel Quote

Executive Summary

Pedal Industries is a family-owned small business specializing in custom apparel. Their business process starts with a quote to a customer, which is built in Excel. The sales rep adds line items to the quote, and creates an estimate for the customer. The estimate must be emailed out for approval. At the same time, the quote is sent to a production manager, who will then create purchase orders and send those to the correct vendors. Often, a quote will involve two vendors: a blank items vendor and a decorator.

For Pedal Industries, automation opportunities include order tracking, calculating screen printing run charges, Estimate and Purchase Order generation, and email generation. This solution confronts all of Pedal Industries’ challenges through VBA code.

The user will begin with basic quote-level information: customer, vendors, in-hands date. He will then request a job number from the Master Job List workbook. Next, he will use data entry userforms to add line items to the quote. The process will first ask for the blank item type for the line, and then allow the user to enter up to 10 printing locations and color combinations for a single item.

Once the quote data is entered into the spreadsheet, the user can use ribbon buttons to accomplish the following: build estimates and purchase orders, print these to PDF in the job folder, and email PDFs to customers and vendors. Please refer to the Implementation Documentation and example workbook for more information.

Implementation Document: http://files.gove.net/shares/files/15f/trevortb/Brown_Trevor_520_FinalProject.pdf

Example File: http://files.gove.net/shares/files/15f/trevortb/Example_Apparel_Quote.xlsm

Blank Apparel Quote File: http://files.gove.net/shares/files/15f/trevortb/Apparel_Quote.xlsm

Master Job List - must be in same folder as Apparel Quote to get new job number: http://files.gove.net/shares/files/15f/trevortb/Master_Job_List.xlsx

FRED Real Estate Indices, Chart Creation, and Chart Extraction into Microsoft® PowerPoint®

_________________________________________________

REAL ESTATE INDICES & CHARTS FROM FRED

_________________________________________________

Extracting Real Estate Specific Indices from the Federal Reserve Economic Database (FRED)

I manage a real estate private equity fund in Henderson, Nevada. The name is Green Mesa Capital, LLC.  Green Mesa Capital, LLC (the “Company”) was formed in 2014 as a firm specializing in alternative investments with a particular focus on institutional-grade real estate throughout North America and Europe. In addition, the Company advises clients in selecting highest and best use tangency for debt and equity real estate transactions. Activities of the Company include applied value investing strategies, investment research, portfolio design, joint ventures, asset management, and optimizing asset sales.

One of the most repetitive tasks is that of creating investor presentation(s). With no previous programming experience, getting up-to-date macroeconomic data, and market data, is not only taxing on time, but also the mind. If I could automate the data gathering process, I could work more on the qualitative commentary. On a quarterly basis, a presentation of the current market is needed. The current process is a three step process: (i) download the data from FRED,  or other economic data website(s); (ii) manually clean and import data into a new Microsoft® Excel®  tab, and create an appropriate chart; and (iii) transfer each chart into Microsoft® PowerPoint®. My entire objective for enrolling in this course was simply to perfect this task. If it could be done, the ROI on my time, and the professionalism on slides, would be infinite.

To work properly, please download the following three files, and have Microsoft® PowerPoint® open.

Project File: http://files.gove.net/shares/files/15f/rnorton/Green_Mesa_Capital_VBA_Write-up_by_Randy_Norton.xlsm

Write-up: http://files.gove.net/shares/files/15f/rnorton/Green_Mesa_Capital_VBA_Write-up_by_Randy_Norton.pdf

Excel Add-in: Please see https://research.stlouisfed.org/fred-addin/

KSL Car Scraper

Originally, I thought of creating a buying tool for golf clubs after taking a beginning golf class here at BYU. The tool would scrape KSL and send me an email of the latest deals. It was a great idea, until my entrepreneurial roommate came up with something much more lucrative. 

Instead of golf clubs, we decided to search for cheap car prices listed on KSL and then compare them to the Kelly Blue Book listings. If the gap is big enough ($2,000 or more) because some local seller forgot to do their Kelly Blue Book research, then we plan to buy it cheap then turn around and sell it for a big profit. This project was an excellent opportunity to create a tool that would email me directly of the latest deals on KSL.

My entrepreneurial roommate and I hope to pay off the rest of college with our car trading business and graduate BYU debt free! Please click the links below to view my project and write-up files:

• KSL Car Scraper Project
• Project Write-up (PDF)