In another two weeks, I’m jumping Intuit’s ship, diving off the side of the SS Quicken into the cool waters of Excel.
Over the past five years as a program administrator, I’ve learned just enough about how Excel works to set up a spreadsheet to operate as a checkbook. Using very basic skills (indeed), I’ve figured out not only how to create a running balance but also how to make Excel reconcile a checkbook against a bank statement. None of this is rocket science. What’s new is realizing I can make a generic program like Excel or its open-source equivalents do all I need Quicken to do.
Because I don’t use Quicken for online banking, my needs for the program are pretty plain-vanilla: keep track of expenses and income, categorize them, create an annual tax report, and reconcile bank accounts. Quicken’s various planning calculators, especially the ones related to loans, come in handy, but similar calculators are all over the Internet.
Nor do I use Turbotax, Intuit’s companion to Quicken, billed as the answer to all your income-tax reporting problems.
Why, you ask, do I eschew Quicken’s whiz-bang online features? Simple: I don’t trust Intuit, a corporation that issues ever-more-bloated versions of its program each year and then forces consumers to buy them by jimmying the code to render data entered in “older” programs unreadable. I can’t say how much I resent that. Also, customer service is nonexistent. It’s a consumer-unfriendly product manufactured by a consumer-unfriendly corporation. There is no way I’m going to put my personal data online through that outfit’s tentacles.
I do my online banking directly with the credit union, thank you. Every financial institution that does business with me makes online transactions and statements readily available. All I need to do that is a reasonably secure browser, say…Firefox running on a Mac, for example.
My taxes are so complicated, there is no chance on God’s green earth I could do them myself, nor would I trust Turbotax to stuff all my square pegs into its round holes. Income has, over the years, derived from salary, contract work, limited partnerships, securities investments, nontaxable bonds, mutual funds, a 403(b), an annuity, a whole life policy, alimony, Social Security, unemployment insurance, sales of real estate, a C-corporation, a sole proprietorship, an S-corporation…it goes on and on. A vast array of laws and loopholes applies to these things, none of them even faintly comprehensible to the amateur.
It’s also very frustrating that Quicken data will convert from PC format to Mac format, but not back. This means that when the day comes that I no longer can afford Macintosh computers—and that day arrived on Tuesday, after I bought the MacBook—to continue using Quicken I will have to start completely anew, losing all historical data. Excel operates smoothly on both platforms, and so I can move between the PC and the Mac with no problem.
After Canning Day, my banking strategy will be much simplified. Instead of disbursing paychecks into various piggybanks (one to cover the monthly credit-card bills; one to “escrow” the annual property tax, homeowner’s insurance, and car insurance; one for monthly savings; one for monthly nondiscretionary bills; one to pool all income; one for the S-corporation; a joint checking account with M’hijito to handle the mortgage payments…and so on ad infinitum), all incoming money will go into one checking account, which will have a $10,000 cushion to back up the poverty-level wages I’ll be making. Most expenses will be paid from this account. The self-escrow account will stay in business, since the only way I can be sure to have enough to cover those breathtaking bills is to set aside $325 a month, every month. I will try to set something aside each month in a savings account to cover budget overruns and buy occasional indulgences like clothing. And of course the joint checking account will have to stay.
That will cut the number of credit-union accounts from eleven to four, not counting the redundant savings accounts, which sit dormant and so really don’t need to be reconciled anyway.
Further simplifying matters is the S-corporation itself. When The Copyeditor’s Desk was a sole proprietorship, I ran business income and expenses through my personal checking and credit-card accounts. This meant I had to run a category report for the tax lawyer. Now all tax-related items except the mortgage interest and medical costs will go through CE Desk’s dedicated checking account. This will make it very easy to run out a report: everything related to FaM and CE Desk is gathered in one account. Mortgage interest is reported on a yearly form from the lender. That leaves only one category, “medical,” in my personal accounts to report on…and that’s easy.
Reconciling an account against a bank statement is also pretty easy in Excel. Just translate the instructions on the back of your statement into Excel actions:
First check off all the cleared transactions in your checkbook spreadsheet; changing their font color makes it easy to spot uncleared entries. Then enter your current bank balance, subtract uncleared debits (checks and EFTs) and add uncleared credits (deposits). Click AutoSum (Σ) to total these figures. Next, enter your bank statement’s ending balance, add any deposits made after the ending date, and subtract uncleared debits. Click AutoSum again to tote up those figures. Now compare the two totals: if you haven’t missed entering anything, then the two should be the same. If they’re different, you’ve made a mistake and need to recheck the figures in your checkbook spreadsheet.
So, how will this work with day-to-day bank and credit-card transactions? It’s easy to track credits (deposits) and debits (checks and electronic payments) in an ordinary spreadsheet. However, because I charge all my grocery and other discretionary spending on credit cards, paying them off in full at the end of each month, the single payment to American Express or Visa doesn’t show what, specifically, that payment covered. In Quicken, this failing can be addressed with split transactions. Since I don’t know how to enter a Quicken-style split transaction in Excel (or even if that can be done), I believe one would need a separate spreadsheet to track credit-card charges. In my case, this represents no extra work: I already do that in Quicken, because I make too many charges each month to stuff into a single split transaction.
So, we first set up a spreadsheet for the checking account:
To explain how Excel builds the figures in the “Balance” column, in the “Cleared” column I’ve noted the commands I entered in “Balance.” For those who are even less conversant with Excel than I am, here’s what I did: Place the cursor in the target cell; type the = sign and then click on the cells you want Excel to total; after each cell, enter a + sign. In my first row, I’ve asked Excel to take the next cell under “Credit, “$10,000, and add it to the figure under “Debit,” which is blank, giving an opening balance of $10,000. That little green triangle on the opening balance is Excel complaining that no value appears in the second (“Debit”) cell. This is just whining—you can either ignore it or quiet the program down by entering a 0 in those blank cells.
This establishes the opening balance. To figure the running balance, remember that you have to include deposits as well as checks and electronic payments. So, I’ve gone to the second row, placed my cursor in the second cell under “Balance,” and asked Excel to take the previous figure under “credit” and add it to “debit” and then add these to the previous figure under “balance.” Because the debits are entered with minus signs (i.e., as negative numbers), using the + sign will cause Excel to subtract that figure from the series’ total. That is, you’re getting x + (-y) + (z).
(And yes, Virginia: that’s why we take algebra in high school or college!)
Now, to keep track of what that $800 paid to American Express was actually spent on, we have a second spreadsheet:
In this spreadsheet, I would like to know not only what specifically I’m diddling away money on, but also how much is left out of each month’s $800 credit-card spending budget. So under “credit” I enter the amount budgeted. This spreadsheet doesn’t represent a bank account and so never has to be reconciled. Consequently I can be a bit more casual here. I create the opening balance simply by copying the first make-believe “credit” into the “Balance” column. Now to make Excel do a running balance, all I have to do is ask it to subtract each debit from the preceding balance. I do this by entering an = sign in the first cell below the opening balance, then clicking on the first debit (notice that it also has a minus sign in front of it, making it a negative number), then entering a + sign, then clicking on the number right above, in the “Balance” column. This subtracted $86.50 from $800, showing $713.50 was left in the monthly budget. Now click on that “$713.50” cell, notice the little “knob” in the cell’s lower right-hand corner; grab that with the cursor and pull the cursor down the column. Every cell the cursor has swooped over will perform the same function as new values are entered in “credit” and “debit,” neatly keeping a running balance for you.
Nifty, eh?
Excel will default to show negative numbers in red, in parentheses—accountant-style. It also probably will default to give you plain-vanilla figures with only one decimal place, as it has done here. To adjust these formats to fit your taste, go to Format > cells and explore around…that’s about the only self-explanatory part of this program.
Come April, I’ll want to send a report on tax-related expenditures to my tax lawyer. It is possible to make Excel do reports, but I’ve never figured out how. The online “Help” manual is utterly incomprehensible to the English major’s mind, and the reports chapter in Excel for Dummies is also over my head. However, it’s easy to generate an English-major report by using the “sort” function. Here, I’m pasting entries from the check-register spreadsheet and entries from the credit-card spreadsheet into a single new spreadsheet and running a “sort” on them:
To sort a spreadsheet, highlight the data you want to sort and then go to Data > Sort. Here I’ve asked Excel to sort first by Category, then by Income, then by Expenses. The result appears above.
As I mentioned above, most tax-related transactions will now occur in the S-corporation. However, it develops that COBRA, Medicare, and long-term care premiums are tax-deductible, and of course these will be paid from personal checking. So I’ve created a category, TR, to flag tax-related items. Sorting the data first by categories gathers transactions by category. From there, it’s easy to tote up the expenses or income in each category. To arrive at $388.09 worth of tax-related transactions, I clicked in a cell next to where those transactions are grouped, and entered the = sign + each figure in the TR category.
In this sheet, as you can see, it’s also very easy, to arrive at a whole year’s income and expenses: just put your cursor in the cell at the bottom of “Income” or of “Expenses” and click the AutoSum icon (looks like a Greek letter Σ).
Excel is easier than it looks. Describing these maneuvers, even in plain English, makes them look harder than they are.