need a Yearly Total for each category so you need to put a "TOTAL-08" or similar in the last column after December. Some of you may want your months to run from April to March for the "fiscal year", it's up to you,

The next part is total each month. So you need to type "TOTALS" in the first column underneath everything else.

ENTERING FORMULAS INTO YOUR WORKSHEET

If you are Microsoft Excel (or whatever you are using) savvy then this bit is a cinch. If you are not so confident then this explanation will be essential.

You need to place a SUM formula in the bottom row underneath the months and in the end column to total all the categories. If you are using sub categories it makes things a little trickier so I'll assume for now you don't have any,

Place the cursor in the January total cell. You want to total this entire column. With Excel it's easy. Find the Auto-sum symbol in the tool bar. It looks like an "M" on its left side. Click it and then highlight the cells above it you wish to total; the formula will appear in the box. Now drag that cell across all the 12 columns and it will be copied to each cell.

You can do the same for the row totals in the last column.

If you have sub categories you will need to use these techniques to create sub totals. You cannot then use the auto-sum function for the grand totals as you are now totaling a mix of summed cells and plain figures. So what you do is put the cursor in the first Grand Total cell and type "=sum(" now click on the first cell or highlight a range of cells, the click "+", then the next range or cell with a sub-total, then "+" again until you are done then press "Enter" and the correct formula will appear in the cell and you can just drag that cell across (or down) as before. Test it to see if it is correct by adding some figures and adjust as necessary.

Now all you have to do is clear any test figures and populate the worksheet every month. But before you do I would strongly recommend you make a copy of the worksheet so that next year you have a blank version you can start over with.

You'll soon see the benefits of modeling your household budget and be able to use it as an effective budgeting and predictive tool. Keep refining it as you go along until you are happy with it and you'll never regret the day you sat down and spent a couple of hours on your financial model.

Learn more about this author, Ray Cook.
Click here to send this author comments or questions.

• << previous page
• 1
• 2

---

Add your voice

Know something about How to set up a personal finance worksheet?
We want to hear your view. Write now!