Creating a financial report for your nonprofit doesn’t require exceptional knowledge of Excel or Google Sheets. I have 3 Excel tips for your nonprofit financial reports that are quite simple. I use them myself all the time. Literally every week.
1.SUMIF
My absolute favourite formula in Excel is the SUMIF. It is the easiest way to add up amounts and present a total. For instance: the total expenses against a certain budget line. I use this all the time for creating reports and overviews.
How?
I export a data dump from my accounting system into my report format. I then add a column to this dump. And in it, I type or paste whatever I need for my sum. That can be the name of a person, a budget line, topic, etc. It’s a term that I use in my report format for which I want to calculate totals.
Next, I link the report format to the data dump through a SUMIF targeting my new column with my terms in it.
What does that look like?
SUMIF is a simple formula that basically says:
- Look in this specific column or row,
- And if you see this specific term,
- Then add up all the amounts in this specific other column.
In Excel (or Google Sheets) it looks like this: =SUMIF(Export!A:A,’report’!A12,Export!O:O).
So this formula adds up all the amounts in the Export sheet column O. But only if they are classified by the search term in my report sheet in cell A12. So if cell A12 says: international flights team, then it adds up all instances where international flights team is mentioned in column A of the export.
Why I like this
I love using SUMIF so much because it is flexible and transparent. Everyone can see which terms I use to classify expense items in my export. So a project manager might see that some items could be classified differently and we could easily change that. Without making any change to the accounts.
2.Conditional Formatting
Conditional formatting is great for flagging certain values. For an example.You created a report and calculated the % of budget used to date. If you want to be able to quickly spot important deviations, you can use Excel or Google Sheets. You can set conditional formatting to colour cells red if the % is higher than 110% for instance.
Why I like this
I like using this because it quickly draws my eye to potentially problematic items. If I know a donor has a 10% limitation on overspending per budget line, I set my conditional formatting alarm at a 5 or 8% difference. So that I know I’ll be in time to address it before it becomes a problem with my donor.
3.=
One thing I always do is check everything. Are my calculations correct? Does my overview or report include all the items in my export? An easy formula to check that one cell has the same number as another is the = formula.
What?
The = formula looks like this:
=’Export’!O130 =’report’!D20
If you’re wanting to check that the total of all expenses in your export equals the total amount in your overview or report.
And if these two cells contain the same number the cell where you put the = formula says TRUE.
If it says FALSE, you know you need to check for an issue.
3 simple Excel tips
These three simple Excel tips work in Google Sheets as well. They help me create overviews, summaries and reports very flexibly and easily.
Even if the accounting system isn’t set up for easy reporting, you can still create all the reports you want with SUMIF. With Conditional Formatting you can check for issues in the implementation and with the = formula you can check that your overview, summary or report includes all the accounting data.
I always say Finance and Accounting aren’t rocket science. And I think these three simple formulas prove my point. There are simple ways to create good reports that help you monitor and manage whatever data you need to monitor or report on.
How I can help
Want to take about dilemmas you face as a manager? Join the Nonprofit Management Confidence Club.
If you would like simple steps to set up and organise your finance & admin foundations guidance by me, you can get my bundle of six simple and short workshops.
- This bundle includes a bookkeeping workshop to understand important basics for nonprofits that could be purchased separately.
Watch my video 3 key things every nonprofit organisation must use to build reports in Excel or Google Sheets. You can also check out my YouTube channel here, and find a collection of videos on HR issues, fundraising, annual reporting, donations and other nonprofit operations topics.
Want to know more and ask questions?
If you want to discuss this more – jump into my nonprofit support community and get input from a wide range of peers and from myself!
Here is how you can join my free nonprofit support community
You can join my free nonprofit support community on the Heartbeat platform here. This group is a safe space for open exchange and discussion on potentially sensitive topics like boards, nonprofit management, fundraising, etc. You can visit the community via a browser or via an app. Here is the link to download the Heartbeat chat app in the Google Play store.
Want to support me with a cup of coffee?
The seaside always inspires me and helps me think of articles, videos, workshops and courses I can create for you. If you want to support me without getting a paid workshop, course or review – you can donate me a coffee and speed up my thinking process! You can support me here: https://ko-fi.com/suzannebakker