Budgeting with Excel

Article Author : Becky
Date : 13th Jun 2005

Introduction

The aim of this guide is to give you the basic skills needed to start creating your own financial plans. You will need to be familiar with the basic workings of Excel, but you need not be an expert by any means. Examples are worked through in this article, and you will be able to download the completed worksheets at the end.

Microsoft Excel is versatile and valuable program, yet one that precious few people make use of effectively. It is a popular misconception that advanced maths skills are required to operate Excel, but this is not the case. Excel can be an extremely useful tool when balancing financial records, working out a budget or simply as a means to keep track of personal spending. I shall demonstrate three ways to use Excel; Firstly, as a means to keep a detailed record of spending. This will be useful for those people who, for whatever reason, do not use online banking but wish to keep digital records of their accounts. Secondly, a more simple breakdown of spending against income. For those who think “where does all my money go?”, this is the one for you. Finally, I shall demonstrate a savings plan.

A Detailed Monthly Report

The most simple structure I will look at consists of just a list of transactions, much like a bank statement. This is very simple, yet very useful as a financial planner. It is made up of four columns; Date (the date the transaction took place), Description (of the transaction), Out and In. The last two relate to the amount of money involved in the transaction; if it was a payment made by you, the amount goes in the ‘Out’ column, if it was money received by you, the amount goes in the ‘In’ column. An example of this is given below:

In order to keep track of total outgoings against total income, all that is needed is a summary made up of Total Out, Total In, and Overall. (Note; This is just my own terminology, feel free to use your own). Enter this information on a spare part of the spreadsheet - in this case, F2 to F4. Click on the cell where you want the Total Out to display (G2), and write =sum( You will then be given the chance to select the data you wish to be summed. Select C3 to C25 by clicking and dragging the mouse over this data set.

Once you press enter, the total outgoings will now show in cell G2. Do the same for Total In in cell G3, but select the data set from D3 to D25. To get the overall spending (income minus outgoings), select cell G4 and type the following: =(G3-G2). The summary should look like this:

If any data is changed in the ranges selected, the summary will change accordingly. If you wish to enter any more information in the chart, you must change the data sets to include it; To do this, double-click on the Total Out (G2) and extend the coloured box to include the new data. Do the same for the Total In (G3). Any changes made will be shown in the Overall cell.

 Page Navigation : [1] [2] [3] Select a page1 - Introduction2 - Where does my money go3 - Planning Savings and Conclusion

Features