These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Monday, April 12, 2010

http://files.gove.net/shares/files/10w/kdl22/VBA_Final_Project_Staffing_Macro_Kyle_Lemmon_Kyle_Freebairn.xlsm

http://files.gove.net/shares/files/10w/kdl22/Project_Write_up_-_Kyle_Lemmon_Kyle_Freebairn.pdf

Executive Summary

Both of us have done work for consulting firms. The particular firm that we have built this project around is a global business consulting firm composed of experts specializing in risk, advisory and transaction services. Consultants bill clients by the hour. As such, it is critical that schedules are clearly planned out each week. Monthly, the regional HR representative creates a summary of the consultant’s schedule by level. Currently this process is manual. Once created, the summary is circulated to the regional Associate Directors and Managers to determine if there are consultants that have availability that can be leveraged on their upcoming projects. The goal is to have no one left “sitting on the bench” by everyone being billable. Billable hours are how the company makes money.

The macro takes a Microsoft CSV file which is downloaded from the scheduling database and automatically summarizes the information needed by Managers and HR to complete staffing. The summary shows the following:

1) Scheduling by level, office, service line, week, include scheduled PTO, holidays, employee name, client, and project code.

2) Compiles a chargeable hour summary by level with different ratios and statistics generated from the data.

3) Hours in the week unassigned and who is available for project deployment.

No comments:

Post a Comment

Blog Archive