How to Make a Budget Workbook

Counting pennies is a chore that is simplified and minimized with a budget.
i Jupiterimages/BananaStock/Getty Images

Budgeting with spreadsheet software such as Excel or OpenOffice Calc allows you to gain a complete overview of not only a budget process, but your personal cash flow situation. Customizing a budget workbook for your needs will give you the skills to perform what-if analysis as you create your workbook. At its most basic, a budget is a list of income and expenses, and spreadsheet workbooks make it easy to expand this basic list into a tool for forecasting, record-keeping and analysis.

Step 1

Open a new spreadsheet workbook and lay out your worksheet. In cell A5, type the word "Income," in B5 type "Target," then from C5 to the right, enter the months of the year. These serve as your column headings. The cells above are reserved for titles and summaries for entry at a later time.

Step 2

Enter a description of every income source that applies to you in column A, below "Income." Include full- and part-time jobs, as well as hobbies that earn money and income from other sources such as pensions and investments. Below your last entry, type "TOTAL." Values are entered in the rows to the right of the descriptions, and a formula will calculate amounts for the "TOTAL" row.

Step 3

Enter "Expenses" in column A after leaving one cell empty below your income total row. Beneath it list descriptions for all expenses you incur. These can be organized into groups like household, automotive and entertainment expenses. As with income, enter "TOTAL" below your last expense entry. Below "TOTAL," enter "Difference."

Step 4

Enter formulas to columns to add your budget figures. In column B, click the cell beside your income total description. Click the AutoSum button and the program will enter a formula and wait for you to define a range. Click and drag on the cells in column B that will add to give your income total. Press "Enter" when this range is defined. The number "0" will appear, indicating you have entered a valid formula. Enter test values in column B and you can verify automatic calculations. Repeat the procedure in column B for expense totals.

Step 5

Copy formulas by right-clicking on the income total formula in column B and selecting "Copy." Click and drag the cells to the right under the month columns until all 12 are selected. Right-click and select "Paste" and the formula is pasted under each month, with cell references adjusted automatically by the spreadsheet program. Repeat the process for your expense totals.

Step 6

Enter a formula to calculate the surplus or shortfall between your income and expenses in the column B cell beside "Difference." For this example, income total is in cell B9 and expense total is in cell B20. Enter the following formula: "=B9-B20," excluding the quotes. The "=" sign prepares the software to recognize a calculation, in this case subtracting the value in B20 from that in B9. A surplus will give a positive number, a shortfall will be negative. Copy this formula as you did with the income and expense totals.

Step 7

Enter target budget data in column B for income and expenses, adjusting your targets to balance your budget. Enter actual expenditures on the appropriate expense line, under the column for the appropriate month.

the nest