How to stay solvent using Excel

Income
Below the Total Expenses row, list in column A your sources of income. This might be Salary, Interest paid gross, and net, or parental grants or pension.

Below that, in column A, enter summary labels for Income, Expenses, Income tax, and Savings. In cell B3, here the Cash outlays for Books/Papers expenses, enter the formula:

=SUMIF(Cash!$F$3:$F$128,$A3,Cash!$B$3:$B$128)

The 128 is arbitrary and assumes that within a year there will be no more than 128 entries on the entry worksheets. Make it more if you think you will need them.

The formula looks for Books/Papers entries in column F of the Cash worksheet and totals the outlays entered in that sheet’s column B. Drag this formula down to row 13. Cell C3 has the similar formula:

=SUMIF(Current!$F$3:$F$128,$A3,Current!$B$3:$B$128)

the only difference being it looks for outlays on the Current worksheet.

Create similar formulas for columns D and E. Cell F3 has the totalling formula for the row =SUM(B3:E3)

Cell G3 has =F3/MONTH(TODAY())

to give a current monthly average as the year progresses.

The totalling rows, 14, 25, and 34 are self-explanatory. Cell F37 has =-F27

F38 has =-SUMIF(Current!$F$3:$F$128,A38,Current!$B$3:$B$128)

and F39 has =ROUND(SUM(F36:F38),2)

The ROUND function drops any stray decimal points and helps this cell agree with cell F9 on the Assets sheet.

Open the Assets worksheet. Down column A list the names on the worksheet tabs Cash, Current, ESavings, and Premium_Bonds.

The purpose of this worksheet is to show how each money-source account has grown or been depleted in a year. It keeps track of money as it moves from one account to another.

In B2, enter =Cash!M2. In D2 enter =Cash!H2, and in F2 enter =B2-D2.

In other words, calculate the difference between the starting amount in the Cash account and the current balance. Columns C and E are dividers.

For each of these four accounts enter comparable formulas. Below a subtotal of these balances in row 6, enter in column A Less Credit_card and Less Store_card. Logically, cell B7 has =Credit_card!M2.

You can complete this block as for the accounts above and total them.

Cell B9 has =SUM(B6:B8), deducting the negative balances in your credit card accounts from the total of your cash and bank accounts. Cell F9 has =ROUND(B9-D9,2) and uses the Round function again to ignore any extraneous decimal points.

The formula in cell F10, =IF(F9=Expenses!F39,”Agrees”,”Disagrees”), checks the total of these Current Assets accounts agrees with the totals of the Expenses.

To simplify the formulas that pick up the balances from each money-source account, the formulas refer to cell M2 on each of those worksheets. A macro regularly copies the current balance to the cell M2.

Press Alt & F11 to open the VBA for Excel editor. Click on ThisWorkbook in the left-hand panel. On the VBA Insert menu, choose Module and in the right-hand panel enter:

Sub LastCell()
ActiveSheet.Range(“H1”).End(xlDown).Copy
ActiveSheet.Range(“M2”).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
ActiveSheet.Range(“H1”).End(xlDown).Select
End Sub

Press Alt & F11 again to return to a worksheet and press Alt & F8. Click the Options button and create a keyboard shortcut, such as Ctrl & Shift & L, to run the macro.

After making any entry for income, expense, or a transfer between accounts you can run this macro to keep your Assets worksheet up to date.

If you prefer to run the updates for all the sheets at once, you can insert the following macro in the same Module before the Sub LastCell() macro listing

Sub Update()
Application.ScreenUpdating = False
Sheets(“Cash”).Select
Application.Run “LastCell”
Sheets(“Current”).Select
Application.Run “LastCell”
Sheets(“ESavings”).Select
Application.Run “LastCell”
Sheets(“Premium_Bonds”).Select
Application.Run “LastCell”
Sheets(“Credit_card”).Select
Application.Run “LastCell”
Sheets(“Store_card”).Select
Application.Run “LastCell”
Sheets(“Assets”).Select
ActiveSheet.Range(“A12”).Select
Application.ScreenUpdating = True
End Sub

When you make an entry to pay off a credit card from your current account, or get cash from a bank account, don’t forget to enter balance entries in both affected accounts.
If you are using Excel 2007, save the file with an .xlsm extension as the default format ­ .xlsx doesn’t allow macros. Under Excel Options, Trust Centre Settings check, Trust access to the VBA project object model.

This article was first published in June 2009.