Data Collection and Music Program Fundraising

Fundraising is a necessary “evil,” required for most of our programs to survive and flourish. Unfortunately, few music educators were also trained as accountants. Because sloppy accounting often looks very similar to mismanagement of funds, we all need to add careful data collection to the skills we use in the classroom each year.

One solution several of my colleagues have chosen is to buy accounting software. The convenience of these programs is wonderful. But this convenience comes at a cost. And if we’re fundraising that often means we don’t have money for accounting software.

So, I roll up my sleeves and use what I have access to for free. In many school systems this means Excel, but for this work, Google Sheets is a fine option as well. While it does cost me some time initially to set up, it lets me to track data and funds on many levels. It also allows me to generate reports, statements, invoices, and many more documents essential for clear communication.

Organizing the Spreadsheet

I find that using the rows to list the kids’ and parents’ names and the columns for each individual entry works the best.

Some basic sample columns might include:

  • Student first/last name
  • Grade
  • Instrument/section
  • Parent name
  • Contact email

These all help with disaggregating the data or creating lists when needed. I can sort by instrument and by grade levels. I have easy access to “groups” of parent contacts.

Moving further to the right, the next several columns cover financial recording keeping. Our groups travel every three years. So I will create columns for each month of each school year, where we record any deposits students have made towards the trip. At the end of this section is a column that calculates the total of all deposits. Next, I’ll create a column for each fundraising event. We typically try to have three fundraisers over the course of two years to help build a scholarship fund and to offset the cost of the trip for all participants. Again, I make a column that totals the fundraising amounts.

Finally, I create a few more columns that automatically add the deposits and fundraising totals together for a “total on account,” a column for additional funds needed to fund the trip, and a counting column for total participants.

I add the names of the kids – one per row – and do some adjusting to fill colors, borders, etc. to make the data easy to recognize and read. Then I create a row that totals each column so I can again disaggregate the info by month, fundraiser, etc. This is helpful for determining budget needs and projections.

After Each Event

At the end of any financial project (fundraising, trips) we do a wrap up sheet that shows where all the money came from and where all the money went. The “totals” columns and rows really make this a quick and easy process – usually less than 5 minutes to complete. The totals columns also make it easier to do such tasks as cross-reference deposits for errors, tracking NSF checks, identifying which students are current with funding needs, and determining which students need financial assistance (even those unwilling to ask).

When Sheets Get Too Big

Do you need more info about a particular fundraiser but your spreadsheet is getting too big? While expanding and contracting columns helps make large spreadsheets more manageable, it may be best to create another spreadsheet just for that fundraiser. I usually just create a second or third sheet within the same file or workbook for convenience. Then I link the totals cells for each kid to their cell in the original (what I call “master”) spreadsheet. This way, when you change totals in the fundraising spreadsheet, for example, they will automatically update into the master. In the sample below, I illustrate how the “profit for trip” is linked from the trip spreadsheet to the fundraising spreadsheet.

click on image for larger view

Creating Statements, Invoices, and Other Communications

Sending home a monthly statement to parents is an ideal way to keep financial communications current and transparent. Our statements are generated automatically using the mail merge features within MS Word and MS Excel (other branded software suites will do the same). Using the emails within the spreadsheet allows me to send statements electronically. Linking data cells to specific locations within the Word document allow me to print and send paper statements home via kids (the old school way). With a column for postal addresses, you can also create mailing envelopes automatically.

Creating the documents will take several minutes each depending how particular you are. However, I would like to offer a word of caution – keep statements simple! Include student’s name, parent’s name, group name, each deposit, each fundraising credit, total in the account, and total due to be current. You may also wish to have a place at the bottom for announcements. Remember, too many words and people won’t read. Use highlight, bold and italic text, underline, color, etc. to draw parents’ and kids’ eyes to the most important info. And try your best not to include anything else.

I format the letter or statement, then, using Word’s Mail Merge (Mailings Menu) features I link cells, columns, and rows to specific points within the document to create individualized monthly statements.

Managing data in this way makes it easy for me to also create a monthly report that auto-fills the data I share with our school bookkeeper. If an error shows up, a monthly accounting will make it much easier to track and correct. This simple task keeps our school’s administration in the “loop” so there are no surprises as to “where did the money go?” For the record I do a daily report for myself to make sure I have not “forgotten” to enter something – as you know, the kids come to class whether we’re done with the paperwork or not.

Here is an example of a merged statement, where all monetary values, group, grade, and names are merged from a spreadsheet.

Final Thoughts on Music Program Fundraising Data

Using data is crucial for our teaching profession in so many ways. Just as tracking kids’ progress is essential to good teaching, so is making sure your finances are in order. This is also crucial to peace of mind and the successful execution of the business side of our profession.

Lastly, save your work with a backup in a different location (Google Drive, Dropbox, flash drive, etc.). Nothing will ruin your perfect system more than a crashed hard drive. I know because I’ve been there!

Get the best from MakeMusic

Discover practical music tips, delivered directly to you!

Sign up