Creating a Personal or Company Budget with OpenOffice/StarOffice Calc
Part 1: Basics
Mike Angelo -- 21 December 2003 (C) -- Page 2
Next place the mouse pointer at the top-left of the spreadsheet and click to open the first cell of the document for input. This should be cell A1. Please see Figure 2, below.
Please take a look at the bottom toolbar, the Formula Bar. Notice the letter-number combination in the input-selection box on the Formula Bar. It's right under the Font input-selection box on the Object Bar in Figure 2, below.
That letter-number combination is the currently selected cell or cell range. You can select a cell or range of cells by typing it into that cell input-selection box.
If you are new to spreadsheets, cell names such as A1 or B5 are made by taking the letter at the top of the spreadsheet designating the column such as A, B, C, and so forth and adding that to the row number at the left edge of the spreadsheet designating the row such as 1, 2, 3, and so forth. Thus cell D6 is the cell in column D and row 6. In Figure 2, cell D6 is the Mortgage Payment cell.
Now, if you already have not done so, please type "OpenOffice Monthly Budget Demo" or whatever you would like to call your budget into Cell A1. Then click on the center, text-alignment icon on the Object Bar. Next highlight the budget title and click the down arrow on the Font Size box on the Object Bar. It's just to the right of the Font Selection box. Or, you can go to the Font Tab under the Format Menu as we did above. Then select 20-pt. type. Please see Figure 2, below.
To finish your budget title for now, simply click the B button on the Object Bar or on Bold in the Typeface selection box in the Format Menu > Cell > Font Tab. You now should have the title of your budget centered at the top leftmost cell set in 20-pt., bold, Nimbus Sans L type.
Please notice that the entire OpenOffice Monthly Budget Demo title is only in the top-left cell, A1. Moreover, it appears to flow into the next six cells, A1 through F1. Please see Figure 2, below.
However, it really does not. That's just a spreadsheet display illusion. Further on in this tutorial, we will merge the OpenOffice Monthly Budget Demo title into cells A1 through H1 so that it truly will be centered across the top of your budget. Please see Figure 3 on page 3.
You periodically should save your work in case your program or computer crashes or in case you make a mistake that you cannot back out of easily. That way you do not lose everything. This would be a good place to save what you already have accomplished.
You have a choice of file formats in which you can save your budget spreadsheet. Instead of using the default OpenOffice-Calc file format, sxc, let's use the MS-Excel file format, xls. The reason for using the MS-Excel file format is so that associates with whom you might share files and who use MS-Excel can read and work with your budget spreadsheet.
To do that, please click on File > Save As and then on the Save as type down arrow. Then please scroll to Microsoft Excel 97/2000/XP (xls;xlw) and click it. Next please insert OpenOffice-Calc-Demo-Contact-Manager.xls into the File name input box and click Save.
When you execute a save command in the .xls format, Calc might prompt you to save in the Calc format instead. Just say NO and save it in the .xls format.
Now, let's add the heading for each column in your budget. To do that, please start by typing Income in cell A4.
Then hit the TAB key thrice to advance the insertion point to cell D4. Next, type Fixed Expenses into cell D4.
In similar fashion, let's add Regular Expense Estimates into cell G4.
Rows 2 and 3 plus Columns B, C, E, and F intentionally have been left blank for now.
Next, please insert Day Job, Moonlight Job, and CD Interest into Cells A6, A7, and A8 respectively. These are your sources of income for this demonstration budget. You can use the keyboard down-arrow to move down to the next cell.
For your demonstration budget Fixed Expenses let's add Mortgage Payment, Car Payment, and Internet Connection into Cells D6, D7, and D8 respectively.
Now let's add the Regular Expense Estimates, Food, Gas, Electric, and Phone into Cells G6, G7, G8, and G9 respectively.
To finish all the labels for now, let's do the totals. Total Income goes into Cell A12, Total Fixed Expenses in D12, and Total Estimates in G12.
At this point, your budget spreadsheet should look similar to the one in Figure 2.
Please feel free to add any other columns or items you like for your budget spreadsheet. The items included in the demo budget are far from a complete or exhaustive list of budget items.
If you are not familiar with formatting Calc spreadsheet cells, please see the Formatting the cells section in our Calc Contact Manager article. Once you have done that or if you already know how to format cells, here are the cell formats for your budget spreadsheet. All these settings can be found in the Format > Cells tabs.
Income, Fixed Expenses, and Regular Expense Estimates get set to Text, Nimbus Sans L, 16-pt, Bold, Underlined, Horizontal Center, Vertical Middle, and NO Automatic line break -- except for Regular Expense Estimates, which gets set to Automatic line break.
As you implement the cell formatting, some text will start to word-wrap or seem to move across cell boundaries. Don't worry about that now. It all will come into alignment when we adjust the column widths further on.
The actual data items Day Job, Moonlight Job, CD Interest, Mortgage Payment, Car Payment, Internet Connection, Food, Gas, Electric, and Phone should be set to Text, Nimbus Sans L, 12-pt, Regular, Horizontal Left, Vertical Middle, and NO Automatic line break.
Next, please set Total Income, Total Fixed Expenses, Total Estimates, Total Expenses, and Disposable Income to Text, Nimbus Sans L, 12-pt, Bold, Horizontal Left, Vertical Middle, and NO Automatic line break.
Columns B, E, and H from Row 6 down will contain dollar amounts. So, please set Columns B, E, and H from Row 6 down to Currency, Nimbus Sans L, 12-pt, Regular, Horizontal Right, Vertical Middle, and NO Automatic line break. Use the -$1,234.00 Currency format with 2 Decimal places and 1 Leading zeros. Negative numbers red and Thousands separator should be checked.
Next, please set a 1.00 pt single-line bottom border for Cells B11, E11, and H11.
In order to set the column widths, please insert 1600 into cell B6, 400 into cell E6, and 400 into cell H6. The currency formatting that you did above for these cells should cause these numbers automatically to jump to $1,600, $400, and $400.
Use the automatic column-width feature to set the column widths for columns B, D, E, and H. Drag the Column A-B boundary to the position at which Disposable Income is on one line. Then drag the Column G-H boundary to the position at which Regular Expense is on one line with Estimates below it.
Perhaps you have been wondering why columns C and F have been left blank. They are used for spacers. Please now set them to widths of 0.13 inches to set the spacing. (We used 0.13-inches in order to get the sheet to fit in the column width in this article. However, to make your sheet look nicer, use a wider width for columns C and F.)
By now, your budget spreadsheet should look much more like the one in Figure 3 than the one in Figure 2. This would be a good place to save your work so far.
Is Netscape Losing the Browser Wars?