I like to do a monthly cash-flow projection for every client. (I don’t always, but I like to.) It’s really important to keep an eye on cash in small organizations, because a cash emergency can creep up and jump out at you like in a horror movie, except without any warning music. I’m sure that very soon you’ll be able to read all about that on my Cash Vs. Accrual page. Meanwhile, here’s how to pull cash actuals out of QuickBooks.
- Run a balance sheet for the closing month.
- Double-click on the bank total to drill down to a transaction report.
- Export that report to Excel.
- Copy the existing tab into a new one and format the data in the way that’s most comfortable for you (I’m Calibri 12 point, no bolds, 125% zoom). Delete the balance colum. Then sort it by transaction type.
- Delete all transfers, as long as they total zero.
- Open up the Cash Categories document you have previously prepared. As the name suggests, this lists the categories you want to show on your cash-flow report/projection. For example, under Cash In you might have Contributions, Program Service Revenue, and Grants; under Cash Out, Payroll first followed by all the expenses you want to differentiate.
- Proceeding from top to bottom down your list of transactions, copy and paste the appropriate cash category over the split field. You’re probably going to have to go back to QuickBooks to investigate individual deposits or anything that actually says “Split” in that column. If your organization does bill payments (instead, that is, of just recording expenses or checks), you’ll need to go back to QuickBooks to find the original expense account for those, too. Unless you know it off the top of your head!
- Sort and subtotal the finished list by Split.
- Hand-populate these totals in your cash flow.
Bonus tip. If you’re anything like me, you’re going to end up cutting-and-pasting over one of your Cash Category lines in a frenzy of categorizing actuals one day. Protect the sheet to avoid this!