While personal finance programs geared toward checkbook management are readily available, a spreadsheet application may be more appropriate for your own situation. Spreadsheets are completely customizable and offer flexibility in data entry and analysis that a dedicated personal finance application may not. A spreadsheet may be a desirable option if you already own the necessary software and can avoid purchasing and installing a separate application. If you do not own a spreadsheet program, you can use one of the free options such as Open office or Google Docs.
Calculating a Running Balance
Open a blank workbook in your spreadsheet application. In the first row, label the columns with the titles "date," "description," "deposit," "payment," "balance" and "reconciled" from left to right. Adjust the width of the columns to the desired size by placing the mouse pointer between the lines in the top row of the column. Click the left mouse button, and drag the separator line to the desired width for each column.
Enter the opening balance of the account in the "balance" column in the first row. Enter the first transaction in the second row, filling in the information that corresponds with each column heading. Leave the "balance" column and "reconciled" column empty for now.
Create a formula to calculate the balance in the column labeled "balance" in the second row. Use the correct formula syntax for your spreadsheet application. For example, in Excel, use an equals sign as the beginning of the formula, and enter the appropriate letter-number reference for the "balance" cell in the previous row. Enter a plus sign, then the letter-number reference for the "deposit" column, followed by a minus sign and the letter number reference for the "payment" column.
Continue entering transactions in each row. When you get to the "balance" column, move the mouse pointer to the balance entry one row above. Right click the balance entry, and on the context menu click "copy." Right click in the row immediately below, and click on "paste" from the context manual. This will place the formula in the cell and and automatically calculate the running balance in each row.
Reconciling the Account
Review your latest bank statement. You will need to compare your bank statement to the entries that you made in your workbook tracking account entries. Correct any entry that does not equal the corresponding entry on the statement. Add any missed transactions by inserting rows using your spreadsheet application's "insert" function. Place an "X" in the "reconciled" column for each transaction that you have matched with the bank statement.
List the transactions that are not reconciled, or marked with the "X", in two separate columns to the right of your transaction ledger, one column each for deposits and withdrawals. Record the deposits in the left column and withdrawals on the right. Enter the beginning bank balance from your statement at the top of the deposits column.
Calculate the total of each column. Use your spreadsheet's "sum" function. With Excel, enter "=@sum." Left-click with the mouse in the top column of your unreconciled column, and drag down through the entire column, releasing the mouse button when complete. Excel will complete the formula. Press "enter" to calculate the total of the column. Repeat this for the other column.
Subtract the total of the "withdrawals" column from the "deposit" column. The difference should equal your bank statement ending balance. If it does not, re-check your transaction entries and bank statement to find the error.
- If reconciling your account shows an error in your calculations and the error is for a small amount, make an offsetting entry in your checkbook registry to compensate for this. It may not be worth spending hours searching for a relatively small mistake. Use your spreadsheet's copy and paste functions when adding unreconciled transactions to avoid retyping the entries.
- Balance or reconcile your statement as soon as possible after you receive it. You will have to account for fewer unreconciled transactions, and reduce the possibilities for errors.
Items you will need
- Computer, tablet or smartphone capable of running spreadsheet software
- Spreadsheet application
- Comstock/Comstock/Getty Images