Tuesday, April 14, 2015

Subway "Eat Fresh" Code

My father-in-law, Doug, owns a Subway franchise located in Spokane, Washington. Doug runs a lean operation. He has one full-time manager in charge of purchasing, employee training/scheduling, and all day-to-day logistics. The rest of the staff are part-time, minimum wage employees. To analyze his business, Doug manually updates two reports. One is a daily sales report. The other is a record of his cost of goods sold. My objective was to automate these reports. I tried to keep the formatting as close as possible to the originals, in order to make it easy for Doug to navigate.

Loan Editor, Insurance and Maturity Report Generator -- Dan Gunderson

Executive Summary:

Prior to coming to the BYU MBA program, I worked for Zions Bank in Salt Lake City, in the National Real Estate department. In my position, I was responsible for providing certain customer service functions on several hundred commercial property loans (the total portfolio, nationwide, was something in the neighborhood of five thousand loans).

There were several elements to providing service on these loans – functions that included the processing of monthly payments, answering questions for borrowers, executing annual property inspections, calculating payoff figures for borrowers who wished to close their accounts, ensuring that the bank held adequate insurance on the property, and preparing documentation for the lien release of loans that were nearing maturity.

In my position, two of my responsibilities included tracking the property insurance for a large number of loans, and also preparing documents for the loans that were nearing maturity. This could be an arduous process, however, requiring many hours of tedious work.

This project makes a loan database easily searchable, editable, and expandable, and enables very easy generation of insurance and maturity reports.

Excel File: http://files.gove.net/shares/files/15w/dgunder2/Final_VBA_Project_2_--_Dan_Gunderson.xlsm

Write-Up: http://files.gove.net/shares/files/15w/dgunder2/VBA_Final_Project_PDF_Write-Up_--_Dan_Gunderson.pdf

MBA Alumni Networking Tool

The Sherpa Program is an organization designed to help first-year MBA students to find a summer internship by pairing them with a second-year student who can guide them through the process. My project is a tool to be used by the Sherpa Program to help the first-year students find contact information for alumni in companies with which they would like to intern, as well as provide them a centralized location for tracking their interactions with alumni. It performs a web scrape of the BYU alumni database based on company names input by the user and creates worksheets with a list of BYU MBA alumni with contact information for each company.

Monday, April 13, 2015

Cost of Living Comparison Tool

When it is time to decide where to accept a full time employment offer, one of the important factor among many is the cost of living of a city. When first started working full time after my bachelor degree from BYU Idaho, the cost of living was something I did not consider. The cost of living in New York was significantly higher than Idaho and the salary I got from fresh out of undergrad was not sufficient enough to sustain my financial goals. In order to achieve financial freedom, there need to be a balance of % of living cost that can be allocated and others for saving and investments.

Therefore, in order to make thoroughly considered decision of where to work, cost of living on top of total financial compensation is a key factor. The tool I developed will allow me to compare the cost of living index by cities as well as salary. This tool will pull the information from a website based on cities and a salary of choice and create comparable table for me to review.

http://files.gove.net/shares/files/15w/jk001/Final_Project_-_Jun_Kosaka.pdf

http://files.gove.net/shares/files/15w/jk001/Cost_of_Living_Comparison_Tool_-_Jun_Kosaka.xlsm

Operating Expense Report Management Tool

Executive Summary

The project builds an Operating Expenses (OE) report management and analysis tool for the finance department in the bank I worked for. Each month, staffs in the finance department receive over 100 replies regarding each individual cost center’s OE in standard Excel format. They then manually copy and paste all replies to one master worksheet, perform data analysis, search and update some cost entries as needed. This VBA tool reduces manual workload by automating report consolidation, exception report generation, variance analysis and follow-up processes. It also provides access control to ensure data security.

Below is a high-level workflow of the OE report management process:

This VBA tool allows the following functions:

User login verification (user name: finadmin; password: 123456)
Consolidate selected OE reports to a master worksheet
Generate exception reports to monitor not-yet-replied or not-confirmed items
Generate pivot table to analyze expense variances
Search cost information by inputting “Cost Center ID” and “OE ID”
Update cost information and confirmation status through a user form

Sunday, April 12, 2015

Email & Text Marketer (Brett Archibald)

Executive Summary

Automated email marketing campaigns are an important part of many businesses. These systems allow marketing and sales departments to efficiently and effectively reach out to customers and prospects. Once prospective customers are contacted, businesses need to be able to track and follow-up with interested or qualified prospects. Often, however, small businesses cannot afford professional solutions and are left to manually email hundreds or thousands of leads and manually attach marketing collateral and track qualified leads.

Part I

The system I built allows marketing and sales departments to send customized emails with attached marketing collateral specific to company size (large, mid, and small). Along with the attachment, the emails will address company contacts by first name, contain an email body that can be customized and formatted via HTML code, and include a custom email signature. The user has the option to send the marketing message via Microsoft Outlook, Gmail, or text message.

Part II

The email body also has the option to contain a link to a google form that captures additional information from leads who are interested in the company’s products or services. The system will pull the data collected from the google form, sort the leads by level of interest and date submitted, and conditional format so sales reps will know which leads they should follow-up with. The user will then have the option to view the “Qualified Leads” report, save it as a new workbook, or print to PDF.

Scheduling Support (Alliance Health)

Executive Summary

The following VBA project was completed for the purpose of simplifying and improving the schedule management used at Alliance health. Previous to returning to BYU for an MBA I was employed at this company and dealt with frustrations shared by other managers. As a result of these frustrations I offered to complete the following project to help improve a portion of their operations.

Description of Business

Alliance Health is a company that operates in the Healthcare/Pharmaceutical Services industry. They currently target diabetics across the United States as their core consumer with efforts being placed into expanding into additional maintenance medications at a low cost with the convenience of home delivery.

Alliance Health has found a great deal of success in growing their business largely because of the talent and efforts of their growing inside sales staff.

Overview of System Built

In order to aid in managing the rapid growth and ever-changing format of their sales structure I have built an interactive scheduling tool that they can use to manage the schedules of their inside sales departments. At the current moment all scheduling was done manually in excel. With my proposed solution managers at Alliance health will be able to manage their schedule with only a fraction of the effort.

Adding new reps, transferring employees, updating schedules, and hiring new managers can now be done in seconds.

In addition to those great features managers will now be able to check the attendance of scheduled employees with ease. Rather than individually looking through the schedule of nearly 200 employees to find which employees are scheduled for a specific shift for the day the program will automatically create this list and has the option to e-mail it as an attachment to specific individuals according to the day of the week.

http://files.gove.net/shares/files/15w/llosee/VBA_Final_Project_Write-up.pdf

http://files.gove.net/shares/files/15w/llosee/Schedule_Final_Project.xlsm

MBA VBA Projects