Executive Summary
Description of Business
I work for Leader.org,
a nonprofit foundation. The Foundation distributes about 150 grants each year to
help K-8 schools implement The Leader In Me program. Each grant contains a
combination of classroom materials, student activity guides, and training days.
As such, each grant is unique and results in a different dollar amount.
In the past
three years, Leader.org has created and generated these grants entirely through
its Salesforce account. Schools apply for a grant through an online application
and Leader.org captures and stores this information in its Salesforce account.
This has worked relatively well in recent years. However, this year, the
Foundation has added some complex logic to determine a school’s grant package.
We submitted our customization requests to a Salesforce developer and the bid
came in at $26,000! I told my boss that I thought we might be able to do the
entire process with VBA in Excel.
Overview of System Built
This project has
four main objectives:
1. Create an Excel spreadsheet to calculate a grant package for a
given school.
2. Import the raw data from Salesforce, clean the data, and organize
it into the spreadsheet.
3. Generate grant exhibits from the spreadsheet that include the products,
prices, and total amounts for the grant.
4. Merge Excel grant exhibits with the Word grant agreement and save it
as a PDF file.
A major
challenge with this project was that I had to make the grant agreement PDFs
look clean and professional. This required me to install Adobe Acrobat Pro in
order to manipulate PDFs through VBA. A
prerequisite to running this macro successfully is that a user must have Adobe
Acrobat Pro installed on their machine. The final output of this project (the grant
PDF) is attached in Appendix A.
*NOTE: The
uploaded workbook does not contain sensitive data. Actual Foundation school
names have been replaced with a random list of Utah school names found
online.
LINKS
LINKS
No comments:
Post a Comment