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:
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.
Where does my money go
The next example is a little more complex, but still easy to do. Rather than have a complete list of transactions, spending is grouped instead. I have shown a six month example below:
(The categories down the left-hand side are up to you, this is only an example)
The next step is to do a subtotal for each month using the same method as in the detailed monthly report example. To save time, enter the formula to the Subtotal for January =sum(B2:B10), and then drag the bottom right corner of the cell over the other five months:
Following this enter income, as shown below:
The penultimate step is the Total, which is the Income minus the Subtotal. Again, enter the information into the cell for January =(B15-B13), and drag it across for the other months:
The final step is the Overall Total. This is the sum of all the totals which we have just obtained, and can be done by using the formula =sum(B17:G17):
As a final (but optional) measure, highlight all the numbers and right click on the selection. Choose ‘Format Cells…’ (the Number tab at the top should be selected already). From the list click ‘Custom’, and then choose the option £#,##0;[Red]-£#,##0, and click OK. This will now display negative numbers in red, put the pound sign before any number and make sure there are no decimal places. If you want 2 decimal places, choose £#,##0.00;[Red]-£#,##0.00 instead (If this is to complex, choose ‘Currency‘ from the list and select the options you want):
Entering different details in the outgoings columns for the various months will now show different final results, and any negative numbers will now be brought to attention.
Planning Savings
To form the main body of the chart, start with three rows; Amount Saved - this is the Total from the previous chart, Target - which is the amount required per month, and Amount Short - being the Amount saved minus the Target. To transfer the ‘Total’ information from the first sheet to this one, first select the cell where you want the information to end up (B7). Type =, click on the January Total from the other sheet (B17) and press enter. Next, select B7 again and drag the bottom corner to give the corresponding information for the other months.
The Target is taken from the amount required per month at the top of the new sheet. To get this to appear the same for all months, type =$H$2 in B7 (the dollar symbols prevent the formula from auto-correcting), and drag the corner for the rest of the months. This means that whatever the information is in H2, it will now appear in cells B7 to G7. The Amount Short is the Amount Saved minus the Target, so type =(B8-B7) into B9, and drag across to fill the rest of the cells. You should have something looking like this:
Note: In this example, when the amount saved is negative, it shows in red text. To do this, follow the instructions from the end of the last example. To get the Amount Short showing in green (when it is negative you have achieved your target), follow the previous instructions but replace the word [red] with [green]:
Underneath the Amount Short create a Subtotal, which is the accumulation of money saved. In cell B10, type =(B7), in cell C10 type =(B7+C7), in cell D10 type =(B7+C7+D7) and so on.
The final stage is to create a row which tells you how much money is still needed; the total target amount minus the subtotal. In first cell of the next row (B11) type =($C$2-B10), and drag across for the other months. The finished chart should look like this:
We can see that in this example, the individual manages to save enough for the car with £13 to spare. These two charts can be used together to track income and spending, and also to plan savings.
Conclusion
The finished charts are available to download below. Try playing around with the numbers and see the effects it has on the final outcomes. When you feel more confident, try designing your own!
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.
Where does my money go
The next example is a little more complex, but still easy to do. Rather than have a complete list of transactions, spending is grouped instead. I have shown a six month example below:
(The categories down the left-hand side are up to you, this is only an example)
The next step is to do a subtotal for each month using the same method as in the detailed monthly report example. To save time, enter the formula to the Subtotal for January =sum(B2:B10), and then drag the bottom right corner of the cell over the other five months:
Following this enter income, as shown below:
The penultimate step is the Total, which is the Income minus the Subtotal. Again, enter the information into the cell for January =(B15-B13), and drag it across for the other months:
The final step is the Overall Total. This is the sum of all the totals which we have just obtained, and can be done by using the formula =sum(B17:G17):
As a final (but optional) measure, highlight all the numbers and right click on the selection. Choose ‘Format Cells…’ (the Number tab at the top should be selected already). From the list click ‘Custom’, and then choose the option £#,##0;[Red]-£#,##0, and click OK. This will now display negative numbers in red, put the pound sign before any number and make sure there are no decimal places. If you want 2 decimal places, choose £#,##0.00;[Red]-£#,##0.00 instead (If this is to complex, choose ‘Currency‘ from the list and select the options you want):
Entering different details in the outgoings columns for the various months will now show different final results, and any negative numbers will now be brought to attention.
Planning Savings
Using this chart, it is possible to track savings. This is handy for when you have a limited period of time to save a certain amount of money, and so the example I shall look at is saving £2000 to buy a car within 6 months. The chart required for this is much more simple, but it works from the final data of the chart we have just completed. For this reason, select a different sheet (at the bottom left corner of the page) on which to do the chart. That way, you can easily transfer information. At the top, write a brief summary of the item being saved for, the cost, and the amount of time required. It is important to have the numbers in different cells to the text, as they will be used in the following chart. Hence, if you change the amount being saved, all corresponding data will change as a consequence. The required amount to be saved each month is the cost divided by the time, in this case, =(C2/C3).
To form the main body of the chart, start with three rows; Amount Saved - this is the Total from the previous chart, Target - which is the amount required per month, and Amount Short - being the Amount saved minus the Target. To transfer the ‘Total’ information from the first sheet to this one, first select the cell where you want the information to end up (B7). Type =, click on the January Total from the other sheet (B17) and press enter. Next, select B7 again and drag the bottom corner to give the corresponding information for the other months.
The Target is taken from the amount required per month at the top of the new sheet. To get this to appear the same for all months, type =$H$2 in B7 (the dollar symbols prevent the formula from auto-correcting), and drag the corner for the rest of the months. This means that whatever the information is in H2, it will now appear in cells B7 to G7. The Amount Short is the Amount Saved minus the Target, so type =(B8-B7) into B9, and drag across to fill the rest of the cells. You should have something looking like this:
Note: In this example, when the amount saved is negative, it shows in red text. To do this, follow the instructions from the end of the last example. To get the Amount Short showing in green (when it is negative you have achieved your target), follow the previous instructions but replace the word [red] with [green]:
Underneath the Amount Short create a Subtotal, which is the accumulation of money saved. In cell B10, type =(B7), in cell C10 type =(B7+C7), in cell D10 type =(B7+C7+D7) and so on.
The final stage is to create a row which tells you how much money is still needed; the total target amount minus the subtotal. In first cell of the next row (B11) type =($C$2-B10), and drag across for the other months. The finished chart should look like this:
We can see that in this example, the individual manages to save enough for the car with £13 to spare. These two charts can be used together to track income and spending, and also to plan savings.
Conclusion
The finished charts are available to download below. Try playing around with the numbers and see the effects it has on the final outcomes. When you feel more confident, try designing your own!
