Step 1: Using Excel
We'll be using Excel 2010 for this tutorial.
Excel is based on “cells,” boxes that can contain numbers or the results of mathematical formulas. These are the parts of Excel you will need to know for this tutorial.
Name Box: This is the name of the active cell. In this case, the cell being edited is A1.
Formula Bar: Rather than type in the cells, cells are edited in the formula bar.
Active Cell: The cell being edited at the moment is called the active cell, and is highlighted with a thicker black outline.
Column Letters: Cells are given a letter depending on which column they're in. The active cell is in row “A.”
Row Numbers: Cells are given a number for they're column. The active cell is in row “1.”
1. First, name the budget template by entering “Monthly Budget” into cell A1. Save the file as “Monthly Budget” as well. This will allow you to always begin your monthly budgets from the template you’re about to create.
2. In cell A2, enter “Month”. In cell A3 enter “Year”. Cells B2 and B3 will be the input cells for the month and year of the budget sheet. In our example, we’ll assume the budget is for October of 2011. Type “10” into cell B2 and “2011” into cell B3. You can change these inputs as the months and years change.
3. Tip: if the text you enter into a cell doesn’t fit, you can always resize the width of the cell by clicking on the border between the two column headers (for example, the line segment between the column headers “A” and “B”) and dragging the column to a more appropriate width.
The Budget Table
1. Now you will assemble your Budget Table, a data table allowing you to input budgeted amounts for particular categories of expenses such as “Food” or “Entertainment.” In cell A5, enter “Expenses”. In cell B5, enter “Budget”. In cell C5, enter “Spent”. Finally, in cell D5, enter “Difference”.
2. In the cells of column A, underneath “Expenses,” enter the categories of expenses that you’re anticipating for your monthly budget. In our example, in A6, we enter “Rent/Utilities” , A7 is “Food”, A8 is “Transportation”, A9 is “School”, and A10 is “Entertainment”. After all expenses categories have been created, enter “Total” at the bottom of the column (in our example, this is cell A11).
1. In cell B11, enter the formula “=SUM(B6:B10)”; this will sum the dollar amounts in the cells B6 through B10 and show the total in cell B11.
2. Right click cell B11 and select “copy” in the box that appears. Right click and select “paste” on cell C11 and again on D11. Excel will modify the cells from the formula in cell B11 so that C11 and D11 will their own columns.
Select the cell group B6 through D11 by dragging the mouse across this rectangular area of cells. Format the cells as “Currency” cells by clicking [Home]> [Format]> [Format Cells] and then selecting the “Currency” category in the “Numbers” tab of the “Format Cells” window. We’ll use “2” decimal places and select the option of having negative numbers appear in red font.
Now you will put together your Expenses Table, another data table allowing you to input the particular expenses of each calendar day into columns of your expense categories. In cell A13 enter “Notes” , cell B13 is “Date”, and in C13, enter your first expense category (in our example, we’ll enter “Rent/Utilities into cell C13). Enter each expense category into the adjacent cells in row 13.
In cell B15 , enter the formula “=B14+1”; this cell will add one day to the date in cell B15. Highlight cell B15, right click, and select "copy". Now drag and highlight cells B16 through B44, right click, and select "paste". The dates should fill themselves in.
After that's done, select the cell group C14 through G44. Format the cells as “Currency” cells. (For a reminder on how to do this, go to step 4.)
Now that you have completed the steps, your ready to begin budgeting on your own. You can add in your own numbers in what you have spent and what you want your budget to be. You can even customize the “expenses” to fit your needs.
Save this as a template, you can use it month to month by changing the numbers in cell B2 and B3.
Once the budget is made, it's time to set goals. In cells B6 through B10, estimate how much you plan to spend. Set reasonable goals that will help you save.
As you spend money, keep track of when and how much you spend, open up your budget, and put it in the spending table that starts in row 13. When you put expenses in there, it will automatically compare what you've already spent (in cell C6 to C11) with your budget (B6 to B10) and show you the difference in D6 to D10.
The key to using a budget is keeping it current. As a new month comes, make a new budget and set new goals.
Step 10: Video Demonstration
Also attached is the example budget.