Home Finance in  Excel

by Helen Bradley with additional material by Eddie Hardman

 When you’re not sure where all your money is going a budget can help you plan your spending. One of the most useful projects you can create using Excel is a budget to plan and track your spending.

This enables you to look ahead to plan what you’ll spend and when">

 

 

Home Finance in  Excel

by Helen Bradley with additional material by Eddie Hardman

 When you’re not sure where all your money is going a budget can help you plan your spending. One of the most useful projects you can create using Excel is a budget to plan and track your spending.

This enables you to look ahead to plan what you’ll spend and when, and to track your progress towards your financial goals, whatever they may be. There are many ways you can attack a project like this in Excel, during this class I’ll show you one of them. We are going to create a home finance solution using two separate worksheets: one for your budget and one to enter your actual income and expenses. Then, to allow you to make comparisons, there will be a third worksheet to compare the two. All this will be done with the absolute minimum of work to set it up. In fact, you may be surprised at how quick and easy it is to use.

 To follow this project you’ll need access to Excel. I’ve used Excel 2000, but use any version from 97 onwards to 2002 and you should be able to follow these instructions very easily. In addition to creating a handy workbook for managing your home finances, you’ll learn about outlining a worksheet and how to alter the colours available in the Fill Color and Text Color palettes as well as doing conditional formatting so that any deficits will stand out. You’ll also see how to easily create references in one sheet to pull data from another.

 

The Basic Worksheet:-

 The first step is to create the basic worksheet, which will double for the Budget, and the Actual results for your year. Begin with a blank worksheet and type these entries into the listed cells:

 

A4
A7
A9
A18
A19
B4
B5
B6
B9
B10
B11
B12
B13
B14
B15
B16
B17
B20
C3

Income
Total income
Expenses
Total Expenses
Net gain/loss
Partner 1
Partner 2
Other
Rent/House
-Payments
Food
Insurances
Phone
Electricity & Gas
Credit Cards
Clothes and personal
Motor Car/s
Sundry
Bank
Jan

 

To enter the calendar names click cell C3 to select it and hold your mouse over the bottom right corner of the cell until the mouse pointer changes shape to a cross. Click and drag to the right until you reach cell column N and let go of the mouse button. This enters the month names into the worksheet automatically.

 Add the formulas to create the totals for each month for both income and expenses. Type =SUM(C4:C6) into cell C7 and =SUM(C9:C17) into cell C18. Copy the formula from cell C7 across to cells D7toN7 and the formula from cell C18 across to cells D18toN18. To create quarterly sub-totals you’ll first need to create some empty columns. Click column F and choose Insert, Columns. Repeat this in columns J and N. In cell F3 type Quarter 1, in cell J3 type Quarter 2, in cell N3 type Quarter 3, and in cell R3 type Quarter 4.

 Next you need to create the formulas to manage these sub-totals. Begin in cell F4. In this cell type the formula =SUM(C4:E4) and copy this to cells F5toF7 and again to F9toF18. Highlight and copy the range of cells from F4toF18 and paste them into the ranges of cells J4toJ18, N4toN18, and R4toR18 (click the top cell of each range and click Paste).

 In cell S3 type Total and in cell S4 type the formula =F4+J4+N4,+R4. This totals the values from the four quarters into column S,providing a total for the full year’s data. Copy this formula from cell S4 to cells S5toS7 and again to cells S9toS18.

 In cell C19 type =C7-C18: this subtracts the total expenses from the total income for

the month and shows you what you can expect to have left over. Copy this formula from cell C19 across to cell S19 to calculate the gain or loss for each month, each quarter and for the entire year.

 You can create a record of the cumulative gain or loss in row 20. However, you’ll need to take care with this step so you only account for each value once. Enter the starting balance in your bank account in cell A20. If you have no savings or you’re unsure of the starting balance, enter 0 for now. In cell C20 type =A20+C19so that the bank total for January will be the opening bank balance plus the gain or loss from that month.

 

In cell D20 type the formula =C20+D19, then copy this formula to cell E20. Now copy cells C20toE20 and paste them into cells G20, K20 and O20. Now, into cell F20 type the formula =E20, into cell J20 type the formula =I20, into cell N20 type the formula =M20, into cell R20 type the formula =Q20, and finally, into cell S20 type =R20. It seems complicated, but it will only take you a moment once you know the shortcuts. The finished results should look like this…

 

That’s the hardest part over with. The mechanics are in place so you’re now ready to format the worksheet so it looks good and is easy to navigate and read. Adjust the column widths, in particular, column B, so you can read the headings, and then align the months to the right over the numbers. Format the column and row headings of the worksheet so they stand out from the rest. Use colour or varying type styles and sizes to differentiate them.

Format the range C4toS20 as currency format — If you wish you can do this with no decimal places as you’re unlikely to be interested in the pennies, and working in pounds is less cluttered. However, I like to know where every single penny is going. Format the columns F, J, N and R in a different way from the other columns as these are the quarter subtotals and they should be highlighted so they stand apart. Format column S with its totals so it, too, is different from the rest as this is the final figure for the entire year.

Now format the rows so you draw attention to the rows containing totals. It’s sensible to create similar formatting for similar levels of totals so, for example, you may use the same format for rows 7 and 18. Choose a different format for the overall gain or loss in row 19 and a different format again for row 20. Using formatting like this isn’t strictly necessary, but it makes it easier for you to identify what you’re looking at.

Create the Second Sheet:-

Now you’ve created the first worksheet, its time to create the second. To create the second worksheet you can simply copy the first one to make an exact duplicate of it. One of the copies can then become the budget worksheet and the second can be

used to record your actual income and expenditure. To copy the worksheet, with the worksheet visible on the screen, choose Edit, Move or Copy Sheet, enable the Create a copy box and click OK to copy it.

To identify the worksheets, place their names in the top left corner, in cell A1, and then format the name appropriately. You may even choose to change the colouring of the formatting for the second worksheet so that it’s quickly identifiable which sheet you’re looking at.

To further differentiate the sheets, double-click the tabs at the foot of the sheets and type a new name for each. If you’re using Excel 2002 you can colour-code your worksheet tabs; right-click the tab, choose Tab Colour and pick a matching colour. Sadly, this feature isn’t available in earlier versions.

You can now complete the figures for your budget for the year, and when you have them, you can enter the actual figures in the cells, too. You’ll see the formulas automatically calculate your expected and actual monthly, quarterly and yearly results, as well as track how your bank balance may be affected by your changing cash flow.

The Outline function can help you see a summary of items on the screen. To set an outline, select the area from A3 to S2o, choose Data, Group and then Outline, Auto Outline. Excel will automatically create an outline on the worksheet, grouping like items together so that they can be viewed or hidden more easily.

 

 

 

You can repeat the outline for the other worksheet, and then click the + and —symbols to alter what you see on the page. For example, clicking the minus symbols across level two at the top of the worksheet will hide the monthly data, leaving only the quarterly results and the overall yearly total visible.

 

 

Working with outlines

Outlining is a great utility to use with a pair of worksheets like these, because they have a lot of useful data that’s sometimes too complicated to work with. When you use an outline you can expand and collapse parts of a worksheet so you can see other parts more easily.

 

There’s one outlining button that doesn’t appear on any of the default toolbars, but which you can add easily and which will help you manage your outline. To use it, right-click any toolbar and choose Customize.

 

From the Categories list choose the Data category and click and drag the Show Outline Symbols button from the Commands list onto the toolbar. When you click this button you’ll create and display an outline if there isn’t one already created. If there is an outline, you’ll toggle the display of the Outline Symbols on the worksheet.

 

 

 You can use the number buttons as well as the plus and minus symbols to show different levels of your worksheet. Clicking the button displaying 3 will show you the third (or in this case, the bottom) level of the worksheet, which is all the data. Clicking the button displaying number 2 will show the second level, in this case the sub-totals and the totals. Clicking the number 1 button shows only the top level of the worksheets, which are the totals themselves. There are buttons for each level on each side of the worksheet that control the display of the various levels of rows and columns.

If you ever try to copy the data from an outlined worksheet while it’s in a collapsed state to another worksheet, you’ll find that the hidden cells are copied, too. If all you want is the information from the visible cells, you must first display only the data you want to copy, choose Edit, Go To, Special, then choose the Visible Cells only option and click OK. You can now copy and paste the data.

Create yourself a comparison worksheet:-

 When you’ve finished with your budget and actual worksheets you’re ready to create the comparison worksheet, which will take the information from each and consolidate it so the figures can be compared.

 Make sure you have a new sheet available to build this in, if not, choose Insert, Worksheet to create one. Go to one of the sheets you made earlier and copy the data from A3:B2o and paste it into cell A3 of your comparison worksheet.

 Across row 2 starting in cell C2 type Quarter 1, Quarter 2, Quarter 3, Quarter 4, and then Total. Click cell C4, and press [=]. Now click the tab for the budget sheet, click cell F4 and press [Return]. You’ll move back to the comparison sheet and you’ll find C4 contains a formula something like this =budget!F4 (where budget Making formulas with this method is much less complicated than typing them.

 Repeat this process so that you have a link to cell Jt, of your budget sheet in cell Di, of the comparison sheet, you have a link to cell Ni1 of the budget sheet in cell Et of the comparison sheet, you have a link to cell Ri, of the budget sheet in cell Ft, of the comparison sheet, and you have a link to cell Si, of the budget sheet in cell Gt, of the comparison sheet.

 You can now click in each alternate column and add empty columns by choosing Insert, Columns. Across row 3 from C3 and working to the right, type in alternate cells the word Budget and Actual. You can then centre the headings for the quarters and the total across each pair of columns by selecting the cell containing the heading and the one next to it and using the Merge and Center button on the Formatting toolbar. This will merge the cells and centre the heading across them.

 Repeat the processes from the previous two paragraphs, but this time create references to the cells on the actual worksheet (rather than the budget sheet). Cells D4, F4, H4, J4 and L4, on the comparison sheet should contain references to these cells on the actual worksheet: F4, J4, N4, R4, and S4.

 Now that you’ve done this you can copy the formulas from the range C4:L4 and paste them into the range C5:L7 and then again into the range C9:L20 Format the cells in the worksheet appropriately formatting the budget columns in one colour and the actual ones in another will help you to identify which is which.

 

You can add an outline to this worksheet if you like, but you’ll have to do it manually. To add an outline, select the range C2:J2 and choose Data, Group, then Outline, Group, Columns and OK. You can now choose to display all the data or just the Totals. You can repeat this by selecting cells Bt:B6 and choosing Data, Group, then Outline, Group, Rows and OK. Repeat it again, but this time with cells B9:Ba7 selected. This gives you a handy outline to work with.

 

When the comparison sheet is complete you can protect it from accidental damage by preventing changes from being made to it. To do this, with the worksheet selected, choose Tools, Protection, Protect Sheet. Unless you really need one, don’t provide a password as this is only a measure to prevent accidental changes. Protecting this worksheet protects only the comparison sheet the other sheets can still be edited and the changes to these will flow through to the comparison sheet.

 You may have found that the colours you have to choose from for formatting your worksheets aren’t very attractive, and that finding a useful range of shades of a single colour is difficult. To create a greater variety of colours, choose Tools, Options, Colors and modify the colours in the top palette. These colour changes apply only to the current workbook so you won’t affect your other workbooks.

  By the time you’ve finished this project, not only will you have a handy workbook to record and manage your year’s finances, but you’ll also have used Outlining on your worksheet and seen how to create a summary worksheet by simply referring to cells in other worksheets. You’re ready now to alter the sheets to reflect your own expenditures. Better still, you know a great deal more about Excel.

 Conditional Formatting:-

 The final thing to do is Conditional Formatting.  Move your mouse to the left hand side of the screen and select the rows 19 and 20 so that both rows are highlighted. Then go to Formatting on the toolbar and click Conditional Formatting.  This dialogue box will appear. 

Notice the values set in each of the conditional slots or windows.  Set yours the same by clicking the small down arrow at the side of each window, select Lass Than in the middle window and Zero in the 3rd one and then click the Browse button. 

 

 

 

The Format Cells dialogue box opens, now select the colour option and choose red.  Now when you spend more then what you earn the numbers in the total columns or rows will appear in red. Try and keep them in the Black.

 

 

 

 

Home