Make Better Decisions Through Financial Report Automation & Consensus

Problem

Can we make better decisions about how to spend our energy with timely and transparent financial reports?

I manage the finances for two cultural organizations. One is a nonprofit and the other acts like one. Nonprofits make decisions based on the mission of the organization. “Does this thing we want to do fulfill the mission or not?” This is how nonprofits should operate however, nonprofits need to be profitable and not all mission fulfilling ideas generate revenue.

The main distinction for companies that operate as a nonprofit is that any profit a nonprofit generates must to go back into the organization and be used to deliver the mission. This is in contrast to a public company whose profits can be paid out in the form of bonuses or dividends to owners or investors. So if a nonprofit makes a profit, they can use any extra funds to grow the business by hiring more staff, offering free events or launching new programs. Money = mission. More money = more mission.

Nonprofits are created to deliver a mission and they deliver their mission through their programs. A nonprofit’s mission is why they exist. For example, the mission for our nonprofit Babycastles, is to “increase the diversity of voices that contribute to videogame culture.” One of our programs is curating exhibitions that showcase games made by independent artists and another program is renting out our space for private events during exhibitions.

While exhibitions are core to our mission, they cost more money to produce than they generate in profit. Because we lose money with each exhibition we host, we augment this loss by running programs that generate profit like renting out our space for events. Rentals subsidize the exhibits.

‘Platform Games’ exhibition by Patrick LeMieux at Babycastles

Renting our space has a lower impact to our mission but a higher profit margin. As a team, we need to weigh our need for revenue and the amount of resources available to run programs that may not be as core to our mission against the types of programs we love to host like exhibitions. If we run too many programs that do not support our mission, we burn out volunteers and ruin trust within our community.

Some programs or revenue streams are more profitable than others and nonprofits have to make decisions about where to focus their energy and resources. The common challenge is finding a balance between bringing in enough money to cover your programs and still having enough resources like time and manpower to deliver the programs that best serve your mission. Nonprofit Quarterly has a good article that goes deeper into the process of identifying your programming mix called the ‘Matrix Map’.

Image from Nonprofit Quarterly

“Do we need to focus on profit or programs?” is the question all nonprofits should be asking on a regular basis but when the answers are buried inside data — it’s hard to build consensus. The goal with this effort is to identify the program’s profitability, spread this knowledge around the organization and make decisions together from the same place of understanding.

Solution

Inspired by this article, I realized it’s pretty easy to build your own solution. If you have excess budget, you could pay for a reporting tool on top of your accounting software but I’m a geek and I’m cheap so here’s how you can do it yourself and create the exact reports you need. Maybe you can adapt some of the things below for your own organization.

Tools used

• Xero for accounting (I might update later for QuickBooks)
• Airtable (I have a pro account for reporting apps but you don’t need pro)
• This handy script (under “Sync Airtable to Google Sheets (the flexible way)”)
• Google Sheets
• Schedule & Send Emails in Spreadsheets(google apps plugin)

In Xero

You only need to do this process at the beginning of a new month for the previous month’s data.

1. Reconcile all transactions in Xero before you generate the report
2. Run a ‘Detailed Account Transaction Report’ and export it to Excel
3. Change the columns with financials to US dollar. (Maybe it’s just me but my financial data is always in pounds?)

In Airtable

1. Copy the base here
2. Copy all of your data from the Xero report and input it in the ‘Enter new data’ view starting in column B
3. Find and remove any duplicates by referencing the date created column (I keep new entries and delete old ones)
4. Fix errors in the ‘Add and Categorize’ view by adding programs and verifying the “Program Type” formula.

In Google Sheets

1. Re-synch the data and double check your numbers are populating
2. Create your reports
3. Enable ‘Schedule & Send Emails in Spreadsheets (google apps plugin)’ to generate PDF’s

Sharing Knowledge

Now you can create reports to most commonly asked questions. Here’s an example of last year’s financial report for the School for Poetic Computation. I only have time to generate this at the end of the year but with this monthly automation, I hope we can have these important conversations about how we allocate our resources more frequently.

Here are some of our frequent questions and their report examples.

How much money do we have now and how much will we have at the end of the year?

Where did the money come from?

How profitable is each program?

I hope this post helps. Reach out with your thoughts, I’d love to hear them.