Building an Excel budget worksheet - page 2

We continue to build an Excel worksheet containing a monthly budget. Step by step.

9. This would be a good time to save the workbook. If you are saving the workbook for the first time, Excel uses a default file name of Book1.xls. Change it to MyBudget2009.xls or something more descriptive than Book1.xls.
10. Fill in the Total Expense formula from cell B11 to M11.
11. Enter an estimate for your income in cell B14. In the example here we are just using an estimate of $3000, and filling across from cell B14 to M14. But you could use more realistic and flexible formulas such as:
    
    • Income based on an hourly wage: =21*.8*8*5*4.3 e.g. $21 per hour discounted by a tax rate x 8 hours a day x 5 days a week x 4.3 weeks per month.
    • Income based on an annual take home salary: =50000*.75/12 e.g. $50,000 per year discounted by a tax rate divided by 12 months in a year.
    • Income from one person on an hourly wage and another person with an annual salary:
      =(21*.8*8*5*4.3)+(50000*.75/12)
12. Now that you have total monthly expenses and income, you can calculate monthly savings. Savings equals income minus expenses, so enter "Savings" in cell A16, then enter the formula =B14-B12 in cell B16, and fill across to cell M16.
13. Savings is something that can accumulate, so let's keep track of "Cumulative Savings" in cell A17, then enter the simple formula =B16 in cell B17. For February, cumulative savings equals savings in February plus cumulative savings from January. In cell C17 enter the formula =B17+C16, and fill over to cell M17.
14. Put a border under the "Entertainment" expense to show that the "Total Expense" row is a bottom line. Select the range A11 to M11 and point the mouse at the Borders tool (below). Click the icon for the Thick Bottom Border.
    
    
15. This is what the budget worksheet should look like at this stage:
    
    
16. Sometimes it's useful to have row totals for expenses and income. Row totals represent the yearly amount for a particular expense. In cell N1, enter "Expense Totals". Select cell N2 then click the AutoSum tool (highlighted below). Excel correctly guesses that you want to sum up the 12 monthly Utility expense cells in row 2.
    
    
17. Fill down from cells N2 to N11. Select cell N12 and click the AutoSum tool to create the forumla =SUM(N2:N11).
18. Select cell N14 and click the AutoSum tool to create the formula =SUM(B14:M14).
19. Save the workbook.

Use of formulas in the Budget worksheet makes it very flexible. Any change in an expense estimate will automatically change the Total Expense, Monthly Savings, and Cumulative Savings. Any change in Income will affect Monthly Savings, and Cumulative Savings.

This is where use of a formula for Income would help. A formula such as =21*.8*8*5*4.3 is better documentation than just a figure of $3000. Perhaps the formula for June is =23*.8*8*5*4.3 which indicates a $2/hour raise.

Here is the finished workbook BuildABudget.xls