Are you a budget novice? Constantly overdrafting? Never have enough money to buy what you really want? Wish you could get your shit together? We’ve got a sexy free Excel document to share with you.
We used to be like you. By the time we were done bouncing checks we thought we were “keeping track of in our head,” all our credit cards were in collections, we had to do all of our banking in person, and no bank in the state would let us open an account. Eventually we stopped being a crackhead and put together a basic budget based on what we remembered from that accounting class we flunked.
After trial and error, we developed a pretty good Excel system. We never overdraft. We can plan ahead for big-ticket items. Our savings are in the five digits.
Inside, we’re sharing with you what we learned the hard way. And in 9 easy steps, you’ll be managing your money instead of your money managing you—without spending a dime on a fancy-pants accounting program…
• A strong desire to take control of your personal finance destiny
Date: The transaction date.
Check No: The check number, if applicable.
Description: What the money was for, i.e. “milk,” “movie tickets,” or “congressional bribes.”
Debits: How much money is going out
Credits: How much money is coming in
Total: How much money is in the account, in total, for that time period.
The TOTAL field is a simple formula: =SUM(X:Y)+Z
This sums a range of transactions together (from x to y), then adds the Z value, which in this case is the previous month’s total.
This TOTAL is real time. When you make or plan a credit or deduction, you’ll automatically know how much money is left in the account, and how it will affect future balances. This is one of the system’s most powerful features.
Note: Transactions are either debits or credits, never both at the same time.
Step 4: Enter totals. If you already have a checking and/or savings account, put the current available balance for each in ROW 3, under TOTAL. Find this out by checking your account online or calling your bank. If you don’t have online banking set up yet, now is an excellent time. Contact your bank for details.
Step 5: Enter your monthly paycheck in place of the number next to Payday, under the CREDITS column. Copy and paste this number throughout all the months. Do the same if you have any other regular sources of income, like a stipend, dividends, or that pot of gold you’re always finding under the full moon.
Step 6: Enter your monthly debits. We set up a few examples, like electricity, heat, food, Netflix, and cable. We use the yellow paint bucket to show payments that we expect to make. Ones that don’t have a specific date they’ll be processed on, we place at the end of the month until they get processed.
Note: We recommend setting aside at least 10% of what you earn towards savings. Change the “Nest Egg” number to reflect your income or delete, as appropriate.
Step 7: Enter any expected future one-time credits and debits, such as pending checks, gifts, trips, DVDs you plan on getting, whatnot.
We highlight checks we’ve written but haven’t been cashed yet in purple. When the check actually processes, we change the date to the processed date and remove the highlight.
Click to enlarge.
Note: You may probably have more credits and debits to keep track of. If you need more rows, just highlight the amount of rows you want to insert, right click, select insert, radio button “entire row,” hit enter.
Step 8: Review. First thing to do is make sure all your TOTALs are correct. Right click on each one and make sure the blue highlight box is for the dates it’s supposed to be. Make sure the green box is on the previous month’s total.
Are there any negatives in future checking dates? This means you’re going into the red, bucko. Limit your spending to compensate. See if certain expenditures can be moved around. Deposit money in from savings.
Step 9: Update your budget at least once a week. What did you buy this week? What money came in this week? Put it in the system. Again, eyeball future dates and make sure nothing is going into the negative. Adjust spending as necessary.
If your bank offers online banking, you can reduce errors by copying your transactions directly from their interface.
a) Go to your account.
b) Download transaction history.
c) Specify the date range.
d) From the download software selector, choose “.CSV” which stands for “comma separated value.”
e) Open with excel
f) Highlight the transaction history
g) Data -> Sort -> Date column ascending
h) Cut and paste the result into your budget
Tip: Cutting and pasting, and even day to day use can get the number formats changed around. To keep everything the same, you may need to periodically highlight the debits and credits columns, right click, hit format cells, and select accounting. This puts nice parentheses around all your debits.
If you don’t do online banking, keep all your receipts and put them in a box. Empty the info from the box into your system once a week. Throw the receipts away (unless you’re saving some for taxes).
! Warning ! Make sure you’re either entering info based all on receipts, or all from your online banking. If you mix and match and try to play catch up later, you’re likely to miss something and get off between your system and what the bank says.
Should I keep track of cash expenses? It’s up to you. We find it easier just to keep track of ATM withdrawals but you may find it helpful, especially if you’re really trying to monitor ALL your transactions, to create a third set column for “petty cash.” Treat it just like the other accounts. Just make sure that you aren’t tabulating your petty cash spending against your checking or savings account.
What if my TOTALS get off from what the bank says? First, check to make sure your SUM equations aren’t selecting the wrong dates. 9 times out of 10, that’s the culprit. Secondly, go over your transactions between your system and the online itinerary (or by phone) to make sure you’re not missing anything. Third, check to make sure you entered the proper account TOTALS in step 4. As a last resort, you can enter a credit or debit as necessary to get your number to match the bank number. Call it something like “oops” or “magic money.”
FINAL WORDS: If you’re already using Qucken and are amortizing you 401k or whatever gives you a boner, this setup is not for you. It’s for rank noobs, novices, people who have never put together a budgeting system before. We could tell them to go buy Microsoft Money, but if you’re having trouble managing your money, you probably can’t afford that, eh?
For the rest of you, use this system properly, and you will never have another overdraft. You’ll know how much and when money is coming in and going out. All your financial details will be arranged in an understandable system. You’ll find yourself with greater control over your life, less frustrated, and happier, clearing space in your brain for the important things in life, like figuring out how to make more money. — BEN POPKEN