Here I will introduce you to my proactive money management spreadsheet system to managing your checking account(s) and handling the monthly bills. There are lots of philosophies about how to handle your money out there. But few also provide you the logistical tools to get the job done simply for the long haul- which is crucial to achieving your longer term goals.
I have used this system for nearly 15 years (since my early 20’s), it’s that simple once you get it going. It’s more that just some generic Microsoft template or pretty monthly budget template- it’s a practice. It’s a tried and true, simple approach, that comes with a spreadsheet, to managing your personal finances in a way that helps you proactively build your best life.
What's In This Post
What’s in my money management spreadsheets
The backbone of my money management spreadsheet system for mastering your money and getting control of your family finances is a delightfully simple, but powerful set of spreadsheets with two key elements. First, the running bank account logs for each bank account (including checking and savings) you own, a Y2K version of a checkbook register if you will (yes, perfectly old school). Second, the debts worksheet. This article will cover the checking account tracker and planner and we’ll save the savings focus and debt worksheet for another time.
Make things simple anddownload my ready-to-go family finances spreadsheet template to get yours started quick and easy. It’s free using the form below!
Three key purposes of the checking account tracker & planner
The spreadsheet bank account logs serve two key purposes: 1) track money in and out (i.e. cash flow planning) to ensure you don’t ever overdraw and you know how much truly *available* money is in each of your accounts (essentially the job of a checkbook register) while facilitating really simple monthly bill paying, and 2) super simple forecasting of the months ahead which is the key to proactively strategizing your money game.
Imagine thinking about your bills only once per month, fully confident the money to pay them is at the ready. No waiting for the paycheck to hit so you can hustle all those becoming-late bill payments out, or hoping your landlord didn’t notice the rent check didn’t show up yet. No stress bill paying is where I want you to be, and we can get you there with this system.
Benefits of my money management spreadsheet system
Certainly there are money management software and apps available nowadays. But I would bet the minority of households utilize those for the long haul and you can’t controlif costs, features, or access will change over time, not to mention where your data goes.
My trusty excel spreadsheet has record of all my money ins and outs since I was 23. You can easily customize it, as well as access it from multiple places when using a secure file sharing service (we use Dropbox, and I also password protect the Excel file itself). And unlike a paper system, it’s easily backed up with your other household digital files (we use Tresorit).Also, my template is as cheap as it gets- FREE. Plus, you own the file for life as you continue to build on it since using a simple spreadsheet doesn’t require an ongoing subscription to anything!
The new approach to knowing your balance
With this approach, your bank account balance is dictated by your spreadsheet, which already accounts for all the ins/outs happening over the next month. Not the ATM receipt or online account balance. The ATM receipt could say $6,000, but it may actually be $250 according to your spreadsheet accounting for all of January’s scheduld obligations (see pic below). So, your spreadsheet balance (of $251.14 in the below example) number is your new master number, irrespective of your online balance.
Tracking: touch every virtual dollar
Many may argue they have so few transactions and/or that their expenses are so consistent each month that they don’t really need to keep such a spreadsheet. Many people just keep plenty of money in the checking account and set everything up automatically. Things run like a well-oiled machine. Well, hang with me… I think even well-oiled machines need tune-ups and deserve some monitoring. And no household is that predictable! The act of observation, coupled with forecasting ahead, forces you with very little effort to hone in on every dollar. This routine attention is the foundation of creating change in your money, and consequently your life.And if you don’t have plenty of money- which is probably the case for most- using this money management spreadsheet system will ultimately free you from constantly thinking about money, when the paycheck is coming and how close those due dates are looming.
With very little effort, with this system I regularly ‘touch’ each sum. So instead of mindlessly letting the cable bill pay, and absorbing little increases over time, I actually notice these changes. I notice because they were pre-entered as a particular amount in the spreadsheet and any changes must be manually edited in the cells that were copy/pasted down from the previous month.
This noticing a need to make an edit is what eventually led to us cutting the cord with cable. I got pretty furious when the bill jumped by $40 per month and they would not budge back down. Cord cut.
While I don’t worry about being 0.23 off between my spreadsheet and online account, it will also highlight when things go awry. For example, when my automatic HOA fee suddenly stopped paying because something went wrong with their system. Using this method, I also recently caught a mistake in our childcare expenses when new prices went into effect. The auto-debit was higher than the number pre-entered into my spreadsheet during forecasting (it’s coming… ), so it triggered me to actually open the weekly emailed invoice from the center and realized there was an error. The error was small enough to go unnoticed on a weekly basis, but it would have cost me a few hundred dollars over the year. Those things are worth noticing and getting fixed!
Forecasting: see your future bank account
The fun and very powerful part of the tracking spreadsheet is the looking ahead to see where you’re going financially and ultimately finding places to tweak, whether to absorb a large expense coming up or to better use dollars to increase savings or investments, or payoff debt. To do so, you will simply copy/paste a few month’s worth of the main recurring transactions repeated down the spreadsheet. From there, I make sure all the sums are in the planned column, then tweak a few dates for payroll, add/substract any incidental row items (e.g., quarterly water bill, semi-annual personal property taxes, sporadic paypal transactions, etc).
Next, I give an honest look at what the credit card billings (i.e. monthly discretionary spending) have been the past few months and make sure I have a realistic expected number in there for the months ahead. Do I anticipate any expensive purchases, like plane tickets or Christmas gifts? This thinking ahead separates the money masters from the money just-keeping-uppers. Know the known unknowns. You will have all transactions anticipated and simulated right there in your account spreadsheet for the quarter ahead (or longer when you’re really in strategizing mode).
Then you have a pretty solid estimate of what’s going to happen across the upcoming months and can temporarily drag and drop the column of planned future dollar amounts into the completed column to see how the balance is affected at various times.
A quick drop of transaction amounts into the completed column shows you if it’s looking like your future bank account balance is heading in a positive or negative direction. And that result helps guide how you will handle spending/saving going forward. If things are looking a bit rough, you can tighten things up, implement a blitz spending freeze, decide you need a chunk transferred from savings to cover an emergency expense that wasn’t successfully absorbed, and/or go to figure out where you’re getting out of balance with regular ‘small stuff’ spending.
For now, just get comfortable setting up an accurate portrayal of your months ahead and playing with moving the amounts back and forth to watch your future bank account evolve. (After you’re done tinkering, always make sure future transaction amounts that aren’t yet scheduled or completed go back to the planned column so your sheet stays accurate.)
And if things are looking good as time progresses, you can work in some automatic savings transfers to capitalize quickly on the cushion before getting too willy nilly with extra spending instead (avoid unintentional lifestyle creep!) Recently in this situation I reduced my take home pay by sending more of my paycheck into my HSA account- this way I could reimburse myself for medical stuff with tax-free dollars straight into savings since I didn’t crucially need that extra $200 in taxed take-home pay landing in the checking account each month anymore.
Only when money is getting stale in the savings account is it time to start thinking about new ways to spend it. A lot more cool options that way that you can explore in conjunction with your goals and vision for your future, rather than just letting another $50 or $100 a month slip through your fingers without a thought on trivial things, $600 or $1200 at the end of a year could actually be something memorable or significant. Add a zero to both those sums, and now you’re really able to start thinking bigger for your life!
So, forecasting is just a simple practice of simulating your income and billings for the months ahead, but it’s so powerful to have that glimpse of your future bank account. Instead of being reactive to the monthly onslaught of bills just trudging along without a plan, this practice puts you in a proactive position with your money… step one to MASTER your money. And it isn’t envelopes of cash assigned to categories which, while effective for controlling spending, isn’t practical for the long haul for most busy families, especially in the current climate of online living.
Have I convinced you? Once you’re set up, it’s easy peasy and sets you up for powerful money management. So let’s get to that!
Setting up YOUR money management spreadsheet
Have your spreadsheet pulled up? Need to download my monthly bills template included in my money management spreadsheet system? I’ll pop the form in one more time where you can get it instantly for free…
All set? Good.
Let’s get you set up. If your eyes glaze over looking at spreadsheets and you’d like a bit more hands on help getting your system set up and working for you quickly, reach out (familymoneymentor@gmail.com) for a virtual coaching session.
There are four categories of transactions: 1) income, 2) recurring autopay bills, 3) recurring manually paid bills, and 4) non-monthly incidental deposits/expenditures. In my spreadsheet example and template, the income is in the green shaded cells, the recurring bills are in the pink shaded cells, and there are extra cells shaded in blue to catch non-recurring incidental deposits and expenditures.
1. Autopay bills
I recommend putting all highly consistent “fixed” bills coming out of this account on autopay: the mortgages, utilities, phone/TV/internet, automated giving, etc. to avoid mishaps. All autopay items are represented in the pink shaded block of bill items in the tracking spreadsheet, but in blue text. So simply write in your particular item descriptions, the day of the month it is scheduled to autopay, and the expected amount. Using a quick “Verizon wireless, 4th” makes for a quicker copy/paste for future months instead of using the date column since these are completely repetitive. Amounts stay in the planned column to the right for now.
2. Manually paid bills
I recommend manually paying all credit card bills which forces you to pay a bit of extra attention to discretionary spending. Also, credit card balances are usually the most variable expense, so they don’t fit well into the autopay category, which should be reserved for the most consistent recurring expenses. A line item for each of your manually paid bills such as this should also be part of your red shaded block of bill items in the spreadsheet, but in black text, items which need you to actually log in and schedule your payment each month, or write a check out, etc. The line items are already pre-written in your block each month, which reminds you to check that account (if you have multiple credit cards in use, for example). The amounts for some of these items may be close estimates for now, particularly for credit cards carrying your discretionary spending.
3. Income
Enter your lines for usual income in the green shaded block. This will be two paychecks a month if you’re paid biweekly, but should also have lines estimating whatever recurring income transactions you receive.
4. Non-monthly, incidental deposits or expenditures
Last, leave space or write in expected incidental, non-recurring deposits/expenditures. These may just be unknowns you put in as they happen (kind of the nature of incidentals) but plan to leave some empty rows at the bottom of the month as placeholders for you to fill these in as needed.
That’s it!
Look over your month and make sure you’re not missing anything. Once you’re satisfied, select the entire block of monthly transactions and copy/paste it below two times (leaving a few rows in between) so you have an entire quarter slated out. Make sure all the amounts are in the planned column so they’re not affecting your balance yet, but are just ready to go for upcoming bill pay sessions and forecasting/simulating later. Take all one-month-worth-of-transactions and copy/paste it a few times down below to give you multiple months blocked out.
Great! You’re set up.
Repeat this process for any other checking accounts you manage as a household so you have a separate spreadsheet tab for each checking account. Hopefully you just have one for simplicity getting started, but I admittedly have three currently, so everyone’s situation is different.
How to pay your bills: your new easy-peasy monthly money session
Now that your initial setup is done, it’s time for your monthly money management session…
Each month, generally the last week of the month, you will spend 30 or so minutes reconciling the past month’s transactions and handling your next month’s transactions. You can spend more time when you get into the forecasting and strategizing, but the monthly chore of handling bills will be quick and easy now you are set up.
1. Balance your checkbook (so to speak)
Open your spreadsheet and, first, reconcile with your online bank account. Simply scan over last month’s items in the completed column and confirm that they each paid as expected in from your online bank account. And if anything else happened noted in your online account that you didn’t enter yet (whoops, forgot that debit charge or an ATM transaction, etc.), enter a line in your spreadsheet for that now (“insert row”). The block of blue shaded items at the bottom of the month are for these non-recurring incidental transactions. They are separated simply because it leaves your recurring block easy to copy/paste over and over for future months and leaves these one-off items at the end.
One set of items you will likely need to update at this point is your income lines from the previous month, which live with the block of next month’s bills (always pay bills with last month’s money! I know… this could seem impossible but hang with me!). So, January’s paychecks hang out down there with February’s bills and so on.
This is an important mindset shift, ensuring all monthly transactions are ready to go right on the 1st of the month. This leaves plenty of space between the 1st and the various bill due dates for correcting issues before they become big headaches. And this leaves you NOT thinking about bills all month long.
Once you’ve made these adjustments and updates, you should have matching balances between your spreadsheet and online account. If not, find the problem and fix it. Good. Checkbook balanced.
2. Set up next month’s bill payments
Next, you’ll set up your transactions for the coming month. Highlight the block of autopay recurring transactions, drag and drop them into your completed column, thereby applying them against your effective available (i.e. spreadsheet) account balance. Paid. Done.
Then go through each of your manually paid items, scheduling them in your various online accounts, or making those payments by whatever other mode you prefer. Enter the exact final amount being paid and the pay date into your spreadsheet as you do each one. Confirm each payment is successfully set up and then move that number to the completed column of your spreadsheet as you do so. A number in the completed columns = done. Checkmark. Paid.
Also, even though the money is there to get all the bills paid now, and it’s considered paid from the viewpoint of your spreadsheet, no need to actually send your payees money early. When paying your online bills, schedule them for their due date and let that money sit in YOUR account instead of theirs until the actual payment date! An interest-bearing checking account will pick you up some more cents this way.
Lastly, set up any incidental, non-recurring payments or anticipated deposits you may know about in advance and enter a placeholder and estimated amount those as well. The dollar number will stay in the planned column until this one off transaction actually goes through. unless it is an automatic item.
Let me explain. Most of the blue shaded items are odd balls that you don’t know are coming (wrote a check to a coworker, deposited a check from grandma). But there may be a utility bill that comes quarterly, so it’s not exactly an oddball, but it’s also not a monthly bill. However, it’s predictable and may still be on autopay. Since it’s not monthly you will handle that bill down in this blue area at the bottom of the month, but since you know it’s coming and likely know the amount in advance or can make a very close estimate, you should put that amount in the completed column so it is accounted for. I usually highlight those in yellow so I know to come back and correct it to an exact amount at the next bill pay session when I reconcile everything. Think “Water bill…. $150” and later I’ll updated it to “Water bill…. $152.36”
That’s it, that’s your monthly bills management. Super simple. No budget categories to calculate or stress over. Bill paying, quick and easy, but also under close control.
Additional notes
1. Debit vs credit
I recognize that this will be a lot more work if you are using a debit card for all your discretionary spending, since then each separate purchase is a line item in your online bank account… and consequently your spreadsheet. Temporarily, you could switch to a cash system and just enter one chunk of cash withdrawals a couple times during the month (i.e. a couple ATM line items in your spreadsheet instead of every purchase separately), or you could total your debit purchases separately somewhere and enter them as a weekly line (but this will also be a pain). But longer term, for many reasons besides tracking convenience, I promote using credit cards… something I will cover in other articles on the blog (don’t panic- baby steps).
2. Paying bills with last month’s money
Also, I recognize you very likely don’t have an entire month’s income ready to go to pay a whole month’s worth of bills in one fell swoop… yet. You may have to split the month in two for awhile, getting two weeks ahead before a whole month ahead. At this point, I want you to understand the nuts and bolts of the system, the end game. Do what you need to in the interim if you’re not paying bills with last month’s money.
But make it your first goal to get ahead. Just follow my eight strategies to get yourself to the point of paying bills with last month’s income. Whatever your situation, there is a solution.
You Might Also Like:
- Cash flow planning: the secret sauce for your family’s finances
- Organizing personal finances: Where to start?
- Why you can’t stick to a budget: Budgeting philosophies and how to spend wisely without a budget
Email me any questions to the comments so I can make sure to address your particular scenario!
Congratulations, I’m so excited you’re on board with this money management system. And remember even if you struggle or fall off the wagon for a bit, nothing can keep you from continuing to try, learn, and make changes. Thank you for being a part of this community of up and coming money masters!
