Setting up Custom Reports in QuickBooks

It is almost an art to create great financial reports. Traditional church accounting programs (Church Windows, Church Management Solutions, Power Church, etc.) usually come with pretty good standard reports that need little adjusting. QuickBooks is different. QuickBooks Online comes with over 100 standard reports, but I don’t think I would send any of them to a church board without customizing them.

In the video, I walk you through how to customize reports, save them, and even put them in a batch. That’s all the technical stuff and not the art. The art of any reporting (including reporting in QuickBooks) includes:

  • Asking the Recipient: Keep the recipient in mind when you’re creating the report. That does mean that sometimes you’re going to have to ask them, “Is there anything else that you need to see to make decisions?” I remember one pastor telling me to just make it simple enough so he could tell how the church was doing and how much money he could spend. His reports were customized with this in mind.
  • Consistency: Reports are going to evolve and change over time so I’m not talking about that kind of consistency. I’m talking about when you give them a customized report one month and then forget to save the changes so you give them a standard report the next time. Take advantage of those features that allow you to save and batch these customizations.
  • Attention to Details: The biggest one is when you add a new account in the Chart of Accounts. If you are customizing reports, double check that this addition is showing up in the right reports.

Useful Links

Why I do this

I’m currently on a a Simon Sinek kick. I read “Leaders Eat Last” twice. I just finished “Starting with Why” and am currently reading “Finding your Why.” These are great books! I highly recommend them. I’ve often seen how a church will struggle if they forget their Why (purpose, mission, vision, identity). Or maybe they don’t forget, but their Why gets…fuzzy. I’ve also seen churches turn it around so quickly when they remember their Why or become clear about their why. Nothing can stop a church with a clear sense of Why.

I think this is the same for individuals as well so I’ve taken some time to remember my own Why. In the video, I go into more depth, but, in a nutshell, here’s my Why: For a church to succeed, they need a successful treasurer. Most treasurers have little support for their work to help them succeed. Can I be part of the answer of supporting church treasurers?

Useful Links

Budgets and Projections in QuickBooks

For some reason QuickBooks just doesn’t seem to value budgets. There’s like ten different Profit & Loss reports, four different Balance Sheets, and only one Budget to Actuals Report. The budget tool itself is barely usable. This kind of ticks me off. The reason I’m complaining so much is that the budget can be one of the most tangibly transformative tools in a church leader’s arsenal. You’ve likely heard it said, “where your treasure is, there your heart will be also.” This is not just descriptive. It’s a promise. So…it’s likely true that I can tell what’s important to your church by looking at your budget and spending. By changing your budget, you can change what your church sees as important.

Enough soapbox. In the video I show you where QuickBooks hides their budget tool. One of the things I’ve found important in my own life and in some of the committees I’ve served is making mid-year projections on how the year will likely end. Many churches set their budget in the fourth quarter of the previous year. The Finance Committee does this with the best information they have at the time. By mid-year, they have even better information. Take this year for example. Most of my churches in the Dakotas are way underbudget for snow removal and mowing but are overbudget on utilities. Giving may be up or down. Many had unexpected income from either the government or the denomination or bequests. That’s a lot of moving parts to keep track of. Making a projection is like being able to update the budget with all the current data.

What I prefer to do is update my projections quarterly. This gives leaders enough data than to lead more confidently. If we project finances are doing poorly, we can look into reducing expenses. If we project that finances are doing great, we can be more open to spending money on unexpected opportunities…or even just putting it into savings.

Useful Links

How much should Pastors put aside for Taxes?

Disclaimer: This is general tax advice so don’t sue me. Pretty much every year I hear about one of my pastors having problems with their taxes. Clergy taxes are confusing and complicated so it’s going to happen. What makes it 10x worse is when the pastor has way underpaid taxes and now has interest and penalties on top of a mess to clean up. Here’s what I recommend:

If you are a Brand New Clergy

  1. Find a clergy tax professional: I provide a list below. They are few and far between. They are well worth the cost.
  2. Use my Clergy Tax Estimator: I also link that below. This does not replace a clergy tax professional, but it could help you get a better idea if your tax professional is legit.
  3. Fill out a practice Form 1040: This isn’t really preferred, but, if you already have some tax experience, it’s better than nothing.

If you are an Experienced Clergy

  1. Start with Last Year: Look at your 2020 Total Tax (Line 24)…assuming your return was prepared by a clergy tax professional. Assuming your compensation and withholdings are similar to 2020, this is the number you can aim for.
  2. Make Adjustments for Big Things: Big things that can be adjusted for would include: Being moved, getting large unexpected taxable income (e.g. Pension Rebate), adding a child or having one age out of the tax credit. For Child Tax Credits, the amount is increasing from $2,000 to $3,000 for those 7-18 and $3,600 for 0-6.
  3. Visit with a clergy tax professional: They usually provide estimates for you and can help you for the more complicated changes.

Useful Links

My All-Time Favorite Spreadsheets

Over the years, I’ve created hundreds and hundreds of spreadsheets. Excel has been my go-to tool for almost any problem. It was not easy to choose my all-time favorite. What criteria do you use? I chose the ones that I felt were innovative, that I personally use often, and would hurt the worst if I lost it.

Here they are:

  1. Personal.xlsb: The personal.xlsb is actually an idea from Microsoft to help you store your favorite macros for easy use. The macros that I’ve written allows you to quickly and easily Paste Values or Negate a number with just a key board shortcut. Microsoft has made it easier to Paste Values in recent years, but I’d still prefer to ctrl+c and ctrl+shift+v to copy and paste values rather than clicking around.
  2. Timesheet with Log: With a lot of Excel timesheets, you just reuse the same spreadsheet each pay period. I like the idea of keeping a running log of how I’ve used my time. I’ve used versions of this spreadsheet for close to 15 years. This one can be tricky to customize. If you run the Turn_On_Calc macro (Password = Jimmies), you can see the hidden hidden sheets. In Project_Activity and TS_Filler, there are PivotTables. If something isn’t showing up in the timesheet, you may have to go into these Project filter and make sure the projects are checked. You don’t want the Projects starting with “SA” to be checked because that will cause a duplication.
  3. Mass Mail Forms: I’m unaware of any tool like this that so greatly enhances the email mail merge. For those of you that are mail merge pros, what if you could quickly and easily mass mail custom attachments? That’s what this does.

Useful Links

Endowment vs Investment Account – Draw me a picture

The other day Sheri Meister and I led a workshop on becoming Financially Fit. During the workshop, we discussed long-term investing strategies. That’s when we got the question again: What’s the difference between an Endowment versus an Investment Account with the Foundation? This wasn’t the first time I’ve answered that question, but I don’t think I’m always speaking the same language so I’ve been thinking of an analogy to help.

Long-term investing is like an apple tree. A few years back, Gus got my wife Betty an apple tree for Mother’s Day. That is a long-term investment. The first few years you just hope it survives (much like an investment). After a while, you get to enjoy the fruit of your patience (apples or investment income). Now here’s the difference:

  • Endowments: If you manage the apple tree like an endowment, you are not allowed to harvest any wood from the tree. You can eat the apples each year, but you can’t lop off a branch to make a walking stick. The tree (i.e. principal) is off limits. You just enjoy the fruit each year.
  • Investment Account: If you manage the apple tree like an investment account, you are to free to harvest the wood as you please. You can even cut the whole thing down. The tree is there for you to do as you wish. As long as you don’t cut it all the way down, you can still enjoy the apples whenever you want.

Useful Links

QuickBooks: Setting up Recurring Transactions

Recurring transactions are always an afterthought for me. I know how helpful recurring transactions are once they are setup, but the setup has been the intimidating part. I used some time playing around with the options, and now I feel much more confident.

Here are the types of recurring transactions and how they are used. The video shows you how to set them up:

  • Scheduled: Automatically creates a transaction on the schedule you set. This is only for transactions that are the same every time because the transaction is actually created and posted. This works well for rents, contracts, debt payments, certain utilities.
  • Reminder: Creates a notification for a transaction on the schedule you set. The transaction isn’t created until you click on the notification. This works well for utilities where the amount changes each month, regular contracted services, regular deposits (e.g. the Sunday offering), and journal entries for monthly investment activity.
  • Unscheduled: This is really just a template you can use to create transactions on demand. I would use these for complicated transactions. The example I give is stock gifts.

Useful Links