How to stay solvent using Excel

One thing everyone needs in the current economic climate is a way to manage spending. The Microsoft Office website offers four budgeting templates, but the chances are that none fit your needs. It’s better to use Excel to tailor your own bookkeeping system.

Since bills come in monthly, quarterly, or annually, you will need to devote one workbook to a year’s records.

To recreate the example here you will need eight worksheets. To add a worksheet press Shift & Alt & F1 or right-click on a worksheet tab and select Insert, Worksheet and click OK.

Group six of the sheets by holding down Shift and clicking on the first and sixth sheet. Label cells A1:H1 Date, Out, In, To, From, For, Detail, and Balance. The only formula on these six sheets is entered in cell H3 and is =H2+C3-B3.

Drag this down the column as needed. To ungroup the sheets, right-click on any tab within the group and choose Ungroup Sheets.

Double-click the first tab and name it Cash. Name the other seven sheets Current, ESavings, Premium_Bonds, Credit_card, Store_card, Expenses and Assets. Tabs with two-word names need an underscore. You can change these names to suit your needs.

In cell H2 of any of the first six sheets, enter the current balance in hand. In the example the starting Current (account) balance is £123.45. The ESavings account has £1,400. The Credit_card and Store_card accounts have been paid up to date so currently have zero balances.

The date of each transaction is entered in column A. The amount payment made is entered in column B, under Out. Income, which might be salary or interest, and so on, is entered in column C. Who money was given to is listed in column D. Who money is received from is entered in column E.

Columns D and E are not particularly significant in this bookkeeping system but you may wish later to run a filter and see all the entries pertaining to a particular supplier.

The entries in column F are critical. It’s imperative that the names of expenses and income used here are identical to those used on the Expenses and Assets worksheets. Column G, headed Detail, is simply a convenience for making a note about a particular purchase or income source.

Click on the Expenses sheet tab. Across the columns, use headings that list the sources of payment. These must be the same as the names on the first six worksheet tabs. Although you may use plastic just for convenience, and pay it off every month, you need to be able to keep track of the balances owed in credit or store card accounts.

The penultimate column records a running total for each account throughout the year. The final column can optionally calculate a monthly average for each expense.

List in column A all the categories on which you are likely to spend. They can be as detailed or as generalised as you like. Rent, mortgage interest, home insurance and utilitiy bills may all be considered large enough to keep separate but they can be subtotalled under Home. Ideally, though, you won’t have vague account names.

You may eventually have groupings for Car, Hobbies, or other expenses. Further down, add together the subtotals in each column to show the totals for each source account.

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,4
Cash!$B$3:$B$128)
(Key: 4 code string continues)
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,4
$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:4
$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 (see screen 4).
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,4
”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”).4
End(xlDown).Copy
ActiveSheet.Range(“M2”).4
PasteSpecial Paste:=xlValues
Application.CutCopyMode = 4
False
ActiveSheet.Range(“H1”).4
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 4
= 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 = 4
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 af fected 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.