How to use your spreadsheet as a checkbook program. (Compute's Getting Started with Spreadsheets)
by Clifton Karnes
One of the handiest things you can do with a spreadsheet is use it to reconcile your checkbook. In this article, I'll show you how to do just that. The examples are based on Excel, but the principles are simple and can be applied to any spreadsheet.
The spreadsheet is arranged with each month taking one column. All transactions, credits, and debits to the account are listed in the column for that month.
The figures at the top of the spreadsheet are a summary of the month's account activity. You see Beginning Balance (this figure will be the same as the previous month's Ending Balance), Deposits, Withdrawals (this figure includes automatic withdrawals, ATM withdrawals, and checks), Service Charges, Interest, and Ending Balance.
You compute Ending Balance by taking your Beginning Balance, adding your credits to it, and subtracting your debits. In this example, you use the formula Ending Balance = Beginning Balance + Deposits - Withdrawals - Service Charges + Interest. In spreadsheet terms, this is the formula =(B3+B4-B5-B6+B7), found in cell B8.
The rest of the calculations in the month's column provide the totals used in the summary.
Show and Tell
The first section lists automatic debits and ATM charges. This section is straightforward. Simply list each debit when you get a statement. The Total Withdrawals section, discussed later, handles this group of figures.
Next are credits--deposits and interest-with each listed on a separate line. The deposit total is summed in the Deposits section of the summary (cell B4) with the formula =SUM(B19:B20). The interest is simply carried to the Interest section in the summary (cell B7) with the formula =B22.
The next line is Total Withdrawals. This figure is the sum of automatic debits and checks--which are listed next. The formula is =SUM(B11:B16) + SUM(B26:B256). The first part of the formula--SUM(B11:B16)--adds the automatic debits and ATM debits. The second part--SUM(B26:B256)--adds the month's checks. The second cell in the range--B256--will have to be adjusted as your check numbers increase.
Handling checks is the key to any checkbook system. The simplest way to do it is to use the first column of the spreadsheet--A, in our case--to list check numbers. For example, if your checkbook starts with check number 100, the first check number in A26 will be 100. Cell A27 is check 101, A28 is 102, and so on for as long as your spreadsheet goes.
In the column for each month, list the amount of each check opposite its number. In our example, the first month uses column B, so the first check, number 100, goes in cell B26.
We continue putting in amounts as checks as written. The formula for Total Withdrawals automatically sums the checks, adds this to the automatic debts, and updates our summary so Ending Balance always shows an up-to-date running total.
When your monthly statement arrives, update your spreadsheet so only checks that have cleared appear on the listing for the month. If a check hasn't cleared, move its amount to the next column, for next month's transactions.
That's it. If you enter checks and deposits as they're made and enter automatic debits when you receive notification of them, reconciling your checkbook with this spreadsheet won't take more than five or ten minutes a month.