Making a Budget Planner with Excel

Sometimes it's easier to stick to something if you have a visual to go by. This is definitely true with budgeting. Different people find different methods work best for them, personally I found the best way for me was to use Microsoft Excel (I have Excel 2007). I would check out my statements and update my budget accordingly, Excel worked out all the totals by calculating formulas.

Some people find Excel difficult to work with, I did at first! That's why I'm putting up my method to using Excel for a budget into cyberspace. You can download budgets directly from Microsoft Office also, but I found most of these, whilst easy to work with and convenient because it was all done for me already, were made for other people who had different needs to what I did - this meant a lot of deleting things and adding them in. So I thought - screw it. Let's make my own :)

Step 1 - Setting up the "Income" section.

-Clicking a cell will enable you to type in that cell. Use the first column (A) to type in your different "categories" of income - "Wages" for example. Do the same for every source of income (I leave several spaces marked as other, because you never know). Add the word "Total" for the final cell in that column.

-Click the next available cell over (bearing in mind the longest text for previous column) add the "projected" income from each source. This means an informed guess (estimate) of how much will come in from each source. It doesn't have to be exact, but it's better to round down than up. You will use this to create your budget for expenditure that month. In the last cell in the column (next to "Total") type =SUM(x:y). Replace letter x with the top cell number (D4, for example) in that column. Replace letter y with the cell at the bottom (the one above the "Total" row).

-Click the next available cell in the "Total" row. Add the same formula: =SUM(x:y). Replace x with the top cell in the column, and y with the one above the "Total" row. This is where you will add your actual income.

-You don't need to do this part in the income section, but I do.
Click the next available cell in each row individually, and add the following formula: =IMSUB(w,v). There is a reason they are the wrong way round. Replace w with the cell number of the Actual value for that income. Replace v with the cell number of the Projected value for that income. Repeat in every row.

Now you have a neat looking table showing your projected income, your actual income and the difference between both :)

Step 2: Setting up the "Expenditure" section.

-You'll want this part a few cells over from your Income section, for neatness.
Type in your first Expenditure column your different categories. Think "Rent/Mortgage", "Water Rates", "Energy" etc. Be honest and remember to include a "Misc" section and a "Total" section.

-In the next available column, add your Projected Expenditure for the month for every category. Try and be as honest as you can stand. In the "Total" section add the formula: =SUM(x:y), filling in the top cell number and the bottom cell number respectively.

-In the next "Total" cell over, add the same formula, adding the top and bottom cell numbers for that column.

-In the next column over, add to each individual row the formula: =IMSUB(w,v) filling in the cell numbers the same way as step 1.

You should now have your completed Expenditure section.

Step 3: The Summary Section.

-You should be able to know how well you're doing at a quick glance, which is the purpose of this section.

-In the first row, put Profit/Loss. In the cells underneath, add categories for each thing you'd like to save for right away, keeping things realistic. I have four rows: Emergency Fund and Holiday Fund, leaving the other two rows for the only two debts that I have (which decide how much extra they get on top of my payment plan for that month). Usually, it is best to avoid saving until your debts are cleared. My debts are interest free (for now) which is why I'm saving.

-In the cell next to Profit/Loss, type the formula: =IMSUB(v,w). Replace v with the Total Actual Income cell number. Replace w with the Total Actual Expenditure cell number. This will show the difference between what you brought in for the month and what you spent that month.

-For the categories, insert the formula: =x/10. Replace x with the Profit/Loss cell number. this will calculate 10% of the Profit/Loss section for each category. You can adjust the percentage accordingly by changing the number - 4 for 25% or one quarter, for example. I recommend you leave at least half of your profit each month in your bank account rather than transferring it into other accounts. This means you may have to keep the percentages down.



You now have a working budget planner! Remember, budgets only help if you are honest about what you spend. If you're the only person viewing it, then you're the only person judging it - so there's no need to lie. Staying honest will help you see where you are overspending, so you will know where you need to make adjustments.

If anybody has a faster way of doing it with Excel, please share it. The good thing is, you only have to do it completely once - just delete the values of what you put in, excluding cells with formulas in.

If anybody would like to share their budgeting method, feel free to do so :)