Introduction: Monthly Budgeting in Excel
Many college students have little or no experience in handling money. Students should write down the monthly cost of tuition and fees, books, room and board, utilities, food, public transportation, personal car, entertainment and extra-curricular activities. In order to keep a smart budget, learning the difference between needs and wants is a major factor. The student who understands the difference is on the right path for keeping up with their budget.
Step 1: Using Excel
Making a budget can help you keep track of where you money goes, and help you set goals to get more out of your money. Excel is a great way to do this.
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.”
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.”
Step 2:
To start, the budget needs some basic parts, such as a date and an outline. By the end of this step, it should look like the picture.
Getting Started
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).
Getting Started
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).
Step 3:
By entering the correct formulas into the "total" row, the budget can automatically sum your goals, spending and the difference.
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.
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.
Step 4:
Cells can be formatted to show different things. This table is for keeping track of money, so we can make it show dollar signs and cents without us having to manually enter it,
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.
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.
Step 5:
With the table made to show budgets, total spendings, and the difference, you'll need a section to keep detailed track of spendings.
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.
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.
Step 6:
Select cells B14 through B44, under the “Date” column. Format the cells as “Date” cells using the same method shown earlier, only this time select the “Date” category in the “Numbers” tab of the “Format Cells” window. In our example we’ll use “MM/DD/YY” for our date format.
Step 7:
In cell B14, enter the formula “=DATE(B3,B2,1)”; this cell will output the date based on the inputs you entered into cells B2 and B3 earlier.
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.
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.
Step 8:
In cell C6, enter the formula “=SUM(C14:C44)”; this cell (C6) will output the sum of all dollar amounts entered into the corresponding column in the Expenses Table (cells C14 through C44). Enter the same formula into cells C7 through C10, each time paying attention to the input cells of the formula. For example, C7 denotes the amount “Spent” in the “Food” category, so you will enter the formula “=SUM(D14:D44)” into cell C7.
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.)
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.)
Step 9:
In cell D6, enter the formula “=B6+C6”. Right click cell D6 and select "copy", then select cells D7 to D10, right click, and select "copy".
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.
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
Here is a quick video demonstration to help you see all the steps in action.
Also attached is the example budget.
Also attached is the example budget.
