99.1% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
Using Excel For Simple Home Accounting And Budgets
Sun 20th June 2010
It is based on a monthly budget listing everything as a monthly income or outgoing expenditure. This can be changed to annual or after a year the monthly details can be added to find the yearly budgets. If you have a yearly expenditure such as a membership to a breakdown service with an annual one off fee that you still need to budget for then there is a decision to make. You can either divide the amount by twelve and out it onto each monthly worksheet or you can place it as a one off payment on the month it was made. Either way would work fine, but I prefer the monthly method since it keeps each month very similar and I do not need to change anything. It also breaks down the cost for me so I can tell how much I need to put away each month to cover the payment for the next year.
In this example we will use three columns, the first to list the type of ingoing or outgoing such as a monthly salary payment. When you first open the programme, go to cell B1 and type Incoming. In cell C2 type Outgoing. These will be your two column headers. Format both columns as currency, (remember that everything you put in here must be in one currency.) Format the cells by clicking the main column headers, (B and C) and right click to choose Format Cells. You can then choose Currency type with two decimal places and UK pound assuming this is the currency you need.
It is now time to start adding everything in column A. Any money whatsoever that comes into your account and any expenditure that leave will be put in here. The value should then be placed in either the Outgoing or Incoming column. Please note that this does not mean adding one value and row per receipt. Instead attempt to group things into categories and putting a total. For example, if you eat out a lot then you wouldn't put a separate row per meal as this would be too much and would be radically different each month. Instead put a title of Entertainment or Restaurant Fees and put the total amount you spent in the month in the Outgoing column. You can manually add up your receipts for the month to do this if you wish or if you are attempting to use a projected value to essentially guess what you spend then sensibly estimate the amount.
Some examples of things that would go into your Incoming column are monthly salary, and child tax credits or benefits you receive and if you are being particularly pedantic, any money received from gifts. Outgoings themselves usually fall into different categories such as your standard outgoings like utility bills, loan repayments, mortgage or rent and TV license. Then there are categories that are less standard such as travel costs you spent on commuting, car maintenance and MOT, petrol, pet vaccinations, breakdown cover and any charitable donations. The third category will be things like entertainment budget, clothing allowance, groceries and holidays. You may have all or some of these categories and may have many more that are personal to you. It is a good idea to keep receipts and then look through them at the end of the month to get a clear picture on where you spend your money.
Once everything has been entered in these three columns, you should have a fairly accurate idea on where your money is going and coming from. The scary yet vital part is totalling the columns. Obviously the Outgoing column must be less than the incoming column for your finances to be healthy. At the bottom of the column entries click the cell and type the following formula: =SUM(B1:B60) assuming that the last entry in column B was in cell B59. Adjust the last number according to your own sheet. Click out of the cell and the answer should appear. Click back into this cell and hover the mouse over the bottom right corner until it changes shape. Click and drag the formula into the next column and the next total will appear.
This worksheet will give you an idea on what your finances look like, please note that the analysis must be done yourself! It is now your job to look through and see what can change and where. If your outgoings are too high then perhaps your entertainment budget must be reduced. If you have lots left over then you could look into saving and investments. This is a very personal step and any advice should only be taken from qualified financial advisors.
The worksheet you have just completed is for one month alone. If for example it is for January then highlight the tab name by double clicking it and changing it to Jan. You can copy and paste the same worksheet multiple times and amend it each month which will eventually give you an annual set of accounts. It may be that you would like to have an annual summary sheet detailing the total annual payments. If this is the case then create a new annual worksheet. Enter the months in the first column. In the next column, click in the cell for a month and type '=' and then go to the January sheet and select the total cell. Hit the return button and in the summary sheet will be the value from the other sheet. You may need to format it.
Using this simple method you can build up a picture of your monthly accounts and budget accordingly. As you learn more about Excel there will be further more advanced additions you can make to the sheet. Excel has a number of functions and even has the ability to calculate the APR on a loan automatically. You can also add more sheets containing things you would like to budget for in the future such as a holiday or a new kitchen etc. with the amount it will cost giving you an idea of what you need to save for.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
REM (Real Estate Management)
Max was very enthusiastic and knowledgeable, passionate about his job.
His explanation of Excel was very clear and concise.
Morgans Hotel Group
Meera was great :)
Really had a fab day!!!
The course was excellent and Marius took a lot of time to answer questions that were raised and explain them in detail.