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

Tuesday, April 15, 2014

Be A Philanthropist Decision Support Tool

Be a Philanthropist Response Tool

Executive Summary

Be A Philanthropist ( is a grant distribution organization in the Grantwell program.  Grantwell is part of the Romney Institute at BYU and is primarily run by MPA students.  Be A Philanthropist (BAP) distributes $20,000 per year ($10,000 per semester) in $300 increments.  The Sorensen Legacy Foundation entrusts the student-run organization to award the micro grants to applicants that have small cash requirements and innovative ideas. 

Each week, the BAP team designates three team members as grant evaluators with one acting as “Lead Evaluator” that has final say.  In order to make the process more objective, the BAP team came up with several criteria used to rate each application.  Late this semester, we developed a more comprehensive weighted system with a few qualifier questions to make sure that the applications fit into the scope given by the fund sponsors.  Since quick turnaround time is critical in the BAP process, I offered to make an automated decision support tool using VBA. 

The tool has three worksheets (Main, Criteria, and Data) that enable users to rate a given application in ~2 minutes per person instead of approximately 10-15 minutes per person using the old process.  VBA sub procedures populate the “Data” sheet from a Google spreadsheet where the application forms filled out at are collected.  The data is cleaned up on this sheet before new records are brought over to the “Main” sheet.

The “Main” sheet holds application information as well as scores, comments, and decisions made for each application.  At the top of this page, there are four buttons that control the data update and the three rater user forms.  The “Criteria” sheet holds all of the various levers that can be pulled by the BAP team to tweak the user forms should future BAP teams want to adjust the rating criteria in the future.  There is space for four qualifier questions (three currently used) and eight rating criteria (six currently used) that allow weight to be assigned as determined by the BAP team.  The “Criteria” sheet also holds the potential recommendations available to reviewers as well as stock email responses for each recommendation.

The user forms bring in the applicant name, project description, and specific use of funds fields from the applications.  This allows the reviewers to rate them with all relevant information at hand.  After the first two reviewers have finished their recommendations and comments, the lead evaluator’s form also displays the opinions of the other two reviewers.  After making a final decision, the lead evaluator has the option of sending an email response immediately to the applicant.  Another form is activated that allows the evaluator to adjust the canned response if they so desire (applicant name is already pulled in) and then send it from the account.  

No comments:

Post a Comment

Blog Archive