Employee Performance Tracker

Executive Summary

My sister is a supervisor in a call center for a security company. Employees in her department are evaluated and coached weekly as an initiative to improve customer service. The evaluations are based on showing up to work each day, clocking in and out at proper times, number of calls put on hold, number of calls answered, graded reports for randomly selected calls, etc. Each Supervisor has a team of 20 employees. My sister’s biggest concern is that she can easily keep track of each employee’s improvement week to week so that she can be well aware of areas of concern and achievement for each person. Right now, my sister manually copies and pastes information for data downloaded from AVAYA, KRONOS, and CMS Applications into one summary worksheet.

I offered to build her a program that will take care of updating the master sheet each week once the necessary data is downloaded. The program analyzes different worksheets individually then summarizes specific data in the master sheet. Not only will updating the master sheet take only seconds compared to hours, the user can easily see where the areas of improvement and achievement are based on color coded cells corresponding to previously set standards.

• http://files.gove.net/shares/files/11w/kauimw/Final_Project.xlsm
• http://files.gove.net/shares/files/11w/kauimw/MBA_614_Final_Project.pdf

Centerville Youth Survey Process

VBA Final Project

Oliver Crane

Executive Summary

The city of Centerville has a summer recreation program in which they run approximately twenty classes.  My evaluation team in the MPA program has contracted to evaluate the summer recreation program.  As part of the evaluation my team developed an evaluation rubric based on assessing participants 1) fun, 2) interest, and 3) safety levels. 

As part of this evaluation process I have created this VBA project to automate the data processing.  The spreadsheet has two buttons, 1) refreshes the page, and 2) processes the data.  The spreadsheet accesses the data which has been published by the Google Docs survey.  The spreadsheet then automatically divides all the data into different tabs and consequently displays the data on a summary tab.  The summary tab is easy to use and assess the success of the summer recreation program.  The data processing button allows the user to search the data by year which allows cross year analysis.

The spreadsheet allows a user to understand large amount of survey day through two buttons in excel.  The most difficult thing in creating this was in debugging the loop function for checking the survey questions with counting the responses.  It just took a long time with help from others to figure it out.  Also I made simple syntax mistakes in making sure the reference cells for the counters matched the questions cells that were searched.

This document will hopefully be used by Centerville to better assess their efforts in providing fun, interesting, and safe summer recreation. 

• http://files.gove.net/shares/files/11w/obc2/Youth_Participant_Survey.xlsm
• http://files.gove.net/shares/files/11w/obc2/ExecutiveSummary.pdf

Amazon.com Price / Attribute Harvester

Executive Summary

For my final project, I decided to develop an attribute & price harvester for a company called Oxspring Paul. Oxspring Paul is a product distribution company that specializes in e-commerce and media based sales channels. They primarily deal in watches. As part of their business, they have to manually collect attribute & pricing data on new watch products that they start to sell in order to create product profiles. In addition, they perform regular pricing checks on items currently in their inventory to ensure optimal marketplace competitiveness. Currently, to collect these two data types, an employee will manually look up each item on Amazon.com to harvest the available attribute data and also establish a marketplace pricing benchmark on initial product build. To maintain accurate pricing data, it is required that employees cycle through the entire existing inventory and retrieves pricing data on a regular basis. Collecting attribute & pricing data represents a very large amount of employee labor hours. With nearly 2000 new unique products being processed per year and over 4000 current SKUs that must be price checked regularly, it is estimated that new attribute & price data harvesting requires about two full-time employees. In addition, the pricing portion is just not being done as well as it should be done. Ideally, a price check would be performed on every item, every day. Oxspring Paul would be lucky if pricing data was updated on every item once a month.

In developing the attribute /price harvester, I had the following goals:

1. I wanted the harvester to be capable of operating on just one input, the Amazon unique identification number or ASIN. I chose this identifier because Oxspring Paul can easily get a list of the product ASINs of every item they sell on Amazon. In further developments, I would like to build out a version that can find an ASIN based off just the manufacturers model number.

2. Collect the following pricing attributes: List Price, Price, Buy Box Price, Buy Box Shipping, Buy Box Total Price, New Market Competitors, New Market Price, Amazon Sales Rank, Rank Category and Selling Status. In addition, I wanted it to collect the following product attributes: Brand Name, Model Number, Part Number, Model Year, Item Shape, Display Type, Clasp, Metal Stamp, Case Material, Case Diameter, Case Thickness, Band Material, Band Length, Band Width, Band Color, Dial Color, Bezel Material, Bezel Function, Calendar, Special Features, Movement, Water Resistant Depth and Dial Window Material Type.

3. I wanted the harvester to be able to diagnose what type of page (status) it was receiving from Amazon and process it accordingly. Amazon products pages are formatted in five possible statuses: In Stock, Available from These Sellers, Currently Unavailable, Only Available at These External Websites and Oops. The formatting of the page is designated based on the current selling status of the product or in the case of the Oops format, the lack of a product with the ASIN used.

4. Lastly, I wanted the program to save the sheet of data as its own .csv file in a folder (Data) based on the current location of the workbook. This .csv file would later be automatically processed by a SQL based system that would import and analyze the data.

• http://files.gove.net/shares/files/11w/tf35/Amazon_Price_Attribute_Harvester.zip
• http://files.gove.net/shares/files/11w/tf35/AmazonHarvester_Price_Attribute_V2.2.xlsm
• http://files.gove.net/shares/files/11w/tf35/Trevor_Feller_Final_Project_Writeup.pdf

Wells Fargo Personal Finance Program

Based on personal experience, I have observed that many people will avoid doing even the most crucial tasks unless there is an extremely quick and easy way to accomplish it. This can even include going online to check bank account balances, see if the gas bill has been paid yet this month, or if we are behind on any loan payments. In order to consolidate these processes, I decided to create a program that would allow me to perform many of these same functions, all from within the same excel workbook.

The program allows a user to input his/her Wells Fargo account information, and the program will return the names and account balances of any accounts they have. If the user inputs a merchant's name into the program, it will also update and show the dates that transactions have been made with this merchant. This will let people see how often the go to certain vendors, or check to see if they remember to pay their gas bill yet this month.

The program also checks the balances and current payment information for student loans through UHEAA or FEDLOANS. It will tell you when you made your last payment and how much it was, as well as if you currently owe a payment. If you do, you will receive a reminder text message telling you to remember to pay before the due date.

- note: the actual program has been modified to remove any personal information.

http://files.gove.net/shares/files/11w/jbailey4/Jared_Bailey_-_Project_Writeup.pdf

http://files.gove.net/shares/files/11w/jbailey4/Wells_Fargo_Budget_Submission.xlsm

Products Input & Labeling Project

Executive Summary:

The present project has as an objective to build a database for a food company. Such database must contain details as type of product, where it is storage, quantity per unity of product, and generate a label that describes the localization of the product in the storage place.

The process consists in a input form that allows to choose the necessary elements that will fill a datasheet. Such datasheet will be base to create the labels and print them.

Implementation Documentation

The following steps were followed to develop the project:

1. Identify the needs of the clients were followed to develop the project:
2. To get a up to date list of products used for the client (Fig 1)
3. Develop a system of labeling that allows product localization in it storage place
4. Develop an input form
5. Develop macros for labeling
6. Develop label masks (Fig 2 & 3)

Products List Example

Description:

The process was developed using a series of concepts learned during this semester at VBA classes. An user will fill an input form created as a Userform object. All variables are linked to a spreadsheet in such way that, after clicking the OK button, all data will populate the spreadsheet. Before the data are inputted in the spreadsheet, a

check routine is performed to verify if any fields are left in blank. This routine is executed using successive IF commands to verify blank spaces in each field.

An code was created having as main tool a FOR EACH loop to allows the form initialization and keep it fresh for new inputs “in a row”.

Labels are built from the datasheet. Macros were created to compo

und arrays and allows that fields in the label Masks could be filled.

The printing process must be performed in an usual manner trough the Excel program.

Label mask Examples 2 & 3

http://files.gove.net/shares/files/11w/Marcelio/Final_Project_VBA_Class.xlsm

http://files.gove.net/shares/files/11w/Marcelio/Products_Input_Project.pdf

Equity Analysis Tool

The Problem:  

I want to form opinions about publicly traded equities quickly. I participated in a student run investment fund, Global Financial Advisors. I listen to student presentations on firms operating across the market.  We discuss these firms at length.  I like many students, balance listening to the presentation with pulling up financial data, charts, and qualitative information. The financial data is spread across several webpages. The basic data is available, but it isn’t formatted to be intuitively meaningful.
I also like to paper-trade options.  Often the trade is quick and exploits a technical chart pattern.  In this case, as well as the first, it is helpful to have a snapshot of the company that draws a picture of the company fundamentals.  For example, if I see positive signal in the market for the name it would be helpful to know if the company was over-levered first. Conversely, if the name is significantly undervalued I would want to know before selling it.

The Solution:

This workbook consolidates the information that I like to have available to form an opinion about a publicly traded equity.  The primary purpose of this tool is to articulate meaning with the data.  It does this in four ways:

·         It only shows essential data on the main tab.

·         It organizes tables more efficiently.

·         It converts raw data into ratios that convey meaning.

·         It consolidates balance sheet data into charts.  

The fundamental questions it answers is,
            “Should I look at this company further?” & “What should I look at?”
It is not intended to answer the question, “Should I buy this stock?”  So, if you use it in that way it isn’t my fault if you have a bad experience.

http://files.gove.net/shares/files/11w/crl27/equityVBAProject.docx

http://files.gove.net/shares/files/11w/crl27/equityVBAProjct.xlsm