Small Business CRM & Invoice Creator

This is a simple CRM system created for my mom for her professional organizing business, Consider It Done!. This system allows her to maintain and update customer information, and create invoices, which can then be saved as pdf and emailed to the customer, as well as saved for her records. This is a huge step up from her current CRM system: a paper, hand-written phone book and a filing basket!

by Meaghan Maher

Project Files:

http://files.gove.net/shares/files/14w/meaghanp/Consider_It_Done_CRM.xlsm

http://files.gove.net/shares/files/14w/meaghanp/CID_CRM_System.pdf

Etsy Shop Analytics Tool

Lorin Bird

Thomas Chiu

Executive Summary

              Brenda Bird is a talented designer who operates her design business via the Etsy.com platform. Etsy.com is a marketplace where people around the world connect to buy and sell unique goods. Brenda has worked as a designer for four years and has been using Etsy to reach her customers since 2011.

https://www.etsy.com/shop/frommewithlovedesign

              However, the analytics page provided by Etsy is really primitive and only provides limited information for Brenda regarding how her business is doing, as seen below. Most importantly, it nowhere contains a summary of performance for Brenda’s listings, which is the main piece of information she wanted.

Objective:

              We decided to use VBA for excel to build an analytics tool to help Brenda gain insights into her business. This tool would provide information on a more granular level and inform Brenda:

·       Number of completed purchases and revenue for each of Brenda’s listed products

·       Number of views each of her listed products has received

·       Number of times each of her listed products has been ‘favorited’

This analytics tool generates a dash board with visual representation of the relevant information. Armed with this information and insights, Brenda can manage and grow her business by reacting to how her customers (and prospective customers) are reacting to her product offerings. For example, at a glance, Brenda would be able to reach out to her most loyal customers via email and offer discounts and suggest new items. The insights gained can also potentially help her with strategizing targeted pricing or with launching a “Google adwords” campaign.

Looking to the future, we hope to continue building this too to automate other tasks such as adding a procedure where the email addresses of the customers will be populated and Brenda can simply press a button to send out emails reaching out to targeted groups of her customers.

• http://files.gove.net/shares/files/14w/tomdcn/VBA_Final_Project_Write_Up.docx
• http://files.gove.net/shares/files/14w/tomdcn/Controlling_Etsy_final_project.xlsm

Fantasy Football Split Stats Evaluator

Executive Summary

I’m a fantasy football enthusiast who takes a lot of pride in beating my friends every Sunday during the football season.  It’s safe to say that I do more research on this subject than the average fantasy football player and this of course often takes me more time than I want to invest.  Like most people, when determining which player to play in the upcoming week, I often rely on two things: average stats as calculated from all accumulated games played that season and an educated guess of whether they will perform well against the next defense they face.  However, there are many factors that can be used to predict future performance and I wanted to get more specific with my research.

Split stats are situational statistics that are recorded for every player.  For example, these stats tell you how well a player has performed in hot or cold weather, at home or away, on turf or grass, versus a specific defense, in the 1st, 2^nd, 3^rd and 4^th quarters, etc.  However, it’s a pain and it’s takes a lot of time to look up this information for each individual player.  Therefore, I wanted to create a program which would allow me to input the criteria I’m looking for, pull it from the internet and display it in one easy form. 

The VBA program that I created does just that.  Now when I’m deciding whether I should play Peyton Manning the following Sunday, I can input his name, the defense he will be playing against, the weather he will be playing in and the type of field he will be playing on.  After running the program it will display his previous average stats for each of these queries allowing me to make a more analytical and informed decision of who would be my optimal player. 

• http://files.gove.net/shares/files/14w/byh2/Brian_Hill_SplitStats_Summary.pdf
• http://files.gove.net/shares/files/14w/byh2/FantastyFootball_SplitStats_BrianHill.xlsm

Project Tracker

Executive Summary

The business problem that I decided to focus on for my final project was to create an effective way to track meeting minutes, agenda items, and task lists in one location for a friend of mine that owns a small construction company.  Although this is a small construction company, there are multiple projects going on at a time, with even more decisions and action items per project that need to be tracked on a weekly basis.  The goal of this project was to create a simple to use excel workbook that would allow for a complete project history to be documented through the use of an agenda and meeting minutes template.  The format would not only be simple to use and easy to distribute to project team members, but would also avoid time intensive formatting in preparation for and during each meeting. With this form, an accurate history of the project can be easily documented freeing up employee time to work on more important activities.

The system that I built uses a handful of buttons and user forms to gather and update information about the project.  All formatting is handled through VBA code.  Project information, current and future meeting information, meeting attendees, and agenda items are all added and updated by clicking on the appropriate button and filling out the user form that displays on the screen.  At the end of the meeting, any item that has been marked as completed can be archived to the bottom of the page by clicking a single button.  Next to each archived item is the date that the item was archived, or completed.  In preparation for the next meeting, the user simply has to click on the “Create New Meeting Tab” button, and a new worksheet labeled with today’s date is created, and all existing project information is transferred over to a new form.  This allows a complete and comprehensive record of all meetings, attendees, and discussion items to be documented for future reference.  The following section details out the functionality of the finalized project tracker workbook.

http://files.gove.net/shares/files/14w/cab75/Final_VBA_Project.pdf

http://files.gove.net/shares/files/14w/cab75/Final_Project.xlsm

Google to Excel Data Transportation

Throughout the past year, I have networked with many people.  I have networked mostly with people in the Real Estate Industry.  I am trying to land a job in the field of real estate and the best way to get a job in this field is through networking.  Finding a job through networking is not done the traditional way of having company recruiters come to campus, interview candidates, and then hire.  Networking is working with mentors and contacts that refer you to others, and those “others” refer you to others; this process continues until one of these referrals is in need of a future employee and that future employee is you. 

A large part of the networking process is keeping track of your contacts and when you need to reach out to them.  I first tried keeping track of all of them in my head; this was not working well.  I then decided I needed to find a better way to keep track of all of these contacts.  As I pondered a solution to this problem, I decided that the best way to solve this problem was to create a VBA solution in Excel

My solution for this problem is to create a user form in excel that includes all the inputs that I felt were important to remember the individuals I am networking with; these fields include: First Name, Last Name, Company, Position, Phone Number, Email Address, Preferred Method of Contact, Follow Up, and Notes.  This solution was nice, but then I realized, I might not be by my computer, so I created code that would allow me to upload the Excel spreadsheet into a Google doc.  For the purpose of this assignment, I also created a spreadsheet to keep track of the girls I am dating as well as the code to transfer this information to Google document (for privacy sake, the names of my networking contacts and dates have been changed).

http://files.gove.net/shares/files/14w/tgraham5/Write-Up.pdf
http://files.gove.net/shares/files/14w/tgraham5/Final_Project_1.2.xlsm
Google Doc - Recruiting
Google Doc - Dating

Monthly Report Preparation

     In my previous position before coming back to school I created 30+ monthly reports on mortgage backed securities. At the end of each month I would spend time “rolling” these files. This process simply entailed prepping the files for the beginning of the next month. This consisted of copying and pasting info into new cells, deleting information, changing colors of cells, changing dates, moving rows based on interior colors, etc.  Although this process took no more than 5 minutes for each file when you are doing over 30 it can take a whole morning to get through this mind numbing act. I decided that I could create a macro to take care of this process each month and take the time spent “rolling” files from around three hours to 15 minutes or so. When you consider that there were 20 other people doing this same thing every month this could free up a significant amount of time to work on other projects for the department.

     Because my previous employer considered these files to be highly confidential and they did not want others to view the information within them I had to make a replica version of the report with no real data inside. However, the code simply needs to be copied into the actual report files and will work with a small amount of tweaking. I gave my code to my previous manager and he is planning on implementing it into as many reports as possible. If only I could have figured out a way to extract some sort of fee from them for this.

http://files.gove.net/shares/files/14w/pjones85/MBS_Monthly_Report_WriteUp.pdf

http://files.gove.net/shares/files/14w/pjones85/MBS_Monthly_Report.xlsm

TA Ratings Compiler - Taylor Remington

Executive Summary

I decided to do a project that goes along with the work I was doing before entering the MBA program. I worked for the Mechanical Engineering department putting together training materials for the TAs that work there. As part of the TA improvement program I was implementing I decided to create Qualtrics surveys that would gather data from the students about how they rated their TAs. The surveys were created and the data was gathered but there was no way to get the data from Qualtrics except by downloading them one at a time. I was working on solving the problem but I ran out of time as the semester was starting for my first year in the MBA program.

I have now developed the final portion of the work I was doing before I came here. My code goes onto Qualtrics.com and opens every folder that has TA information on it for any chosen semester. I then take the data that the students have provided and come up with average grades for all the ratings that the students gave me. With the average grades I rank the TAs from best to worst color coding the TAs by how highly they were rated.

Project Write up:

• http://files.gove.net/shares/files/14w/rtaylord/Final_Project_Taylor_Remington.pdf

Excel File with Code:

• http://files.gove.net/shares/files/14w/rtaylord/Final_Project.xlsm

Final output:

• http://files.gove.net/shares/files/14w/rtaylord/TA_Rating_Fall_2013.xlsx