|
Step 1
|
|
A spreadsheet is a rectangular table or grid consisting of rows and columns of individual squares, (cells), used mainly for displaying financial information. The term came from the original paper versions, where information was 'spread' across two adjacent pages in the workbook or ledger. Information was calculated and entered manually by a bookkeeper or accountant. Although most bookkeeping and accounting is done on special computer software, the name 'spreadsheet' has been kept, because much of the format and appearance is the same. In addition, contemporary software versions have the ability to perform many extra functions, using the computers math co-processor.
This tutorial was written for Appleworks, but should be adaptable to MS Excel.
|
|
|
|
|
Fig. 1
|
|
Open Appleworks or Excel on your computer, and choose the Spreadsheet option. This is what you will see.
This is a blank unformatted spreadsheet, which looks similar to what you might see in a paper-based ledger (book). The page consists of several horizontal rows and several vertical columns of small rectangles called Cells
Cells are referenced according to their location, (like the game Battleship). The highlighted Cell in Fig. 1 is Cell D8. You highlight a Cell by selecting it with the mouse.
|
|
 |
| Fig. 2 |
|
By default, Appleworks creates all columns and rows a certain size, but you can customize them to whatever size you like.
By highlighting the row number of Row 1 I can alter the height of the entire row of cells by stretching it with the mouse. As you move the cursor over the gray squares you will notice it change to an arrow shape. For more precision I go to the Format menu and select Row Height. For this exercise choose a height of 24 pt. Do the same for Row 2.
Row 2 is going to contain our headings, but for now, Row 1 will be empty.
|
|
 |
| Fig. 3 |
|
Type in the headings as shown in Fig. 3. You will notice that when you try to enter information into a highlighted cell the text appears in the white space above the rows, until you hit the Enter key.
You will also notice that you can use the arrow keys to negotiate around the spreadsheet, instead of using the mouse.
|
|
|
|
|
 |
|
Fig. 4
|
|
As a finishing touch we will Bold the headings. The easiest way is to select the gray row number to highlight the entire row, then select the Bold Text icon (B) in the Tool Bar.
|
|
| Step 2 |
|
Fig. 5
|
|
The Appleworks package includes a basic spreadsheet program. In this example, the monthly earnings, expenses, and accumulated savings of an individual are shown over a one year period. Each individual square is called a 'cell'. Although every cell can be filled in by manually entering data, the program has various functions that can make life a lot easier.
You will notice that Billy-Bob earns $1408.00 per month before expenses (gross income). His monthly expenses, for the sake of this example, are consistently $1275.00 per month, leaving $133.00 per month left over. The Balance column shows Billy-Bobs accumulated savings. All this could be done with a calculator, but . . .
|
|
|
Fig. 6
|
|
You can let the computer do most of the work for you. Notice that I have filled in the year 2006 from July to December, but I have entered 2007 only for the month of January. In the next few steps I will use the "Fill Down" function under the "Calculate" menu, in order to save myself some work.
|
|
|
Fig. 7
|
| Click and drag downward from cell B9 (containing '2007') to Cell B14, as shown in Fig. 7. Next, go to the 'Calculate' menu and choose 'Fill Down' from the drop-down menu. The cell contents automatically fills down through each of the selected cells. |
|
|
|
|
|
Fig. 8
|
|
Wow! So far, so good, but what about some more serious stuff!
|
|
|
Fig. 9
|
|
You'll notice that I haven't calculated the 'Net' earnings yet. I could do this with a calculator for each line, but instead, I will set up the spreadsheet to do it for me.
The first step is to click and highlight Cell E3 where the Net Income will reside. While it is still highlighted select the Entry Bar and type in an equal (=) sign as shown in Fig. 9.
|
|
|
|
|
Fig. 10
|
|
Now do the following steps:
The result is automatically displayed in Cell E3. You may have noticed that as you type, a formula is being created in the Entry Bar.
|
|
You have just created a function in your spreadsheet that could save you a lot of work. Whatever values you enter in the Income and Expenses columns will be automatically calculated in the Net column. This is only the beginning . . .
|
|
|
Step 2
Now that we have seen what a spreadsheet is and what it can do, we will customize so it will act as a running ledger of our monthly earnings, expenses, and bank balance.
|
|
|
|
Fig. 11
|
|
This is our spreadsheet project from Part One. The only change I have done is transfer the Net from July into the Balance column. I didn't just copy it. If you look in the Entry Bar you will see the formula "=E3". I will expand on this later, but what this means is that whatever is in Cell E3 will be transferred to Cell F3.
|
|
|
|
|
 |
| Fig. 12 |
|
For now we will assume that the monthly income and expenses are going to be the same for the rest of the year, so we can use the Fill Down function as in Fig. 6 and Fig. 7, for both columns. Click and drag down from Cell C3 to Cell C14, then choose Fill Down from the Calculate menu. Do the same for the Expense column. Click and drag from Cell D3 to Cell D14, then choose Fill Down from the Calculate menu.
|
|
 |
| Fig. 13 |
|
|
We can do the same thing for the Net column. If we click on Cell E3 and drag down to Cell E14, then select Fill Down from the Calculate menu, each cell in column E is calculated according to the formula that we created. If you don't believe me try changing your income in the month of April, and see what happens in Cell E12.
|
 |
| Fig. 14 |
| You will notice that Cell E12 changes too. |
|
|
Now for the fun part.
The Balance column will show a running balance for each month. To do that, it will have to the current months Net to the previous months Balance. To do this we will build a formula similar to the way we did it in Fig. 9 and Fig. 10.
|
 |
| Fig. 15 |
|
Do the following steps:
Cell F4 now displays the current balance. The next step is to use the 'Fill Down' function to allow a running balance. Select Cell F4 and drag down to Cell F14, go to Calculate, Fill Down. A balance of each month will be created.
|
|
|
|
|
 |
| Fig. 16 |
|
We now have a computerized ledger system for calculating our monthly expenses and on-going bank balance.
Save this file with your original file name. Now go to 'save as' and give it a different name, such as tc_ss1b.cwk (my initials_spread_sheet_1b). It is a good practice to always use unique file names, and have them make some sense if possible. If you use default files names it is too easy to accidently overwrite a previous file.
This will create a back-up copy for our next project.
|
|
With your back-up copy, try changing your monthly income and expenses to see what happens to your balance. It should update automatically.
|
|
|
Save your file with a unique name such as tc_ss1.xls (client_name_spread_sheet_1.xls for example)
|
|
|
Home
|
| |
