How to use the Mac -- Applications -- Spreadsheets

One of the many applications available on the Mac is generically known as the spreadsheet.  Spreadsheets are used widely for many purposes: creating financial tables and graphs, generating lists of items such as address books, creating call sheets for film and television productions, making templates for quotes and invoices, form outlines for anything from purchase requests to student quizzes and much more.  And the Mac has several powerful packages available as spreadsheets, with AppleWorks and Excel as two widely known versions (see our Links page for more information).

Originally, spreadsheets were created to address one need: to help with number crunching.  This example will run briefly through a simple repeative calculation to demonstrate how a spreadsheet can work.

Making a Spreadsheet

First, launch your spreadsheet software.  Each package will have some variation on menus and what is displayed on the screen, so refer to your owners guide for clarification [software-specific instructions are included in brackets in this example]. Here, we'll use Appleworks as an example in which we will first create a new document.  [In AppleWorks, we select the File menu to create a New document.  A dialog will come up asking us what kind of document to create, and we will select SpreadSheet.]  When the spreadsheet appears, it will generally look like a gridwork of rectangles called CELLS.

Each spreadsheet has columns of cells, normally labeled with letters such as A, B, C, etc. and rows of cells labeled with numbers 1, 2, 3, etc.  Each cell can be clicked on to select it, or collections of cells can be dragged over as a group.  Most spreadsheets also have a bar displayed above the cells which will tell you which cell is presently selected (here, it is cell A1), along with other tools.  There is also a blank space where one enters or edits formulas or data for that particular cell.

As with most other software, horizontal and vertical scrollbars, zoom features and specialty buttons will also be present.

Typically, one can select entire rows or columns by clicking on the label for that group.  Individual cells and groups can also be set to a particular format (display as text, numbers, decimals, currancy, etc.)

To enter information into a cell, one first selects the cell -- then types in the desired information and presses the enter or return key.

Let's develop a spreadsheet which will calculate a simple compounded interest rate and the overall balance for a series of numbers.

Here, we have entered labels for the columns in the cells A1 and B1.  We are in the process of entering the lable for C1, to be called Interest. We will continue to add a few more cells to complete the labeling.

Once the cell labels have been entered, we can begin to add information to additional cells.  For instance, we'll want to add dates for our experimental math example. This example will show the effect of simple compound interest on an investment account.  Here, we will only compound annually, so we will select a starting date and add one year to each cell below it.

The example is shown as a split screen where we can display both our starting points and our end points at the same time. [Many spreadsheets will let you divide your window this way by selecting on the thick black lines adjacent to each scroll bar.  Here, we pulled down a horizontal split from the vertical scroll bar.  Refer to your manual for more on these features.]

Dates from 1/1/1923 to 1/1/2002 now populate the cells in our date column. [You may not have to enter each date by hand if your software supports "fill special" or "paste special" commands for autofilling cells.]

Be smart and save your work frequently.  We'll call this file "Investment Yield" from now on.

Formulas and Calculations

Now, we need to learn about formulas.  Here is the true power of a spreadsheet -- to do repetative calculations for us.  We know from simple math that if we start with an amount called Principal (P) and apply an Interest (I) rate to it, over a period of time, our Yield (Y) is the math formula of Y=P x I.  We also know that at the end of the year, our new principal Balance (B) will be B=P+Y.  Using these formulas, we can plug in equations into the appropriate cells to have our spreadsheet perform these calculations for us.
We plug in our equations like this: first, select the cell which will show the answer or result of the equation.  For simple starters, we will pretend that our starting balance at the end of 1923 is $1000.00.  What we want to do is have that balance automatically carried over to January 1, 1924.  So, we select the cell for January 1, 1924, B3, and in the cell edit bar, we write a formula which uses the CELL DESIGNATION for the Balance of 1923, here it is E2.

Next, we format our Interest column.

The Interest column is first formatted to display as percentage (if desired).  This way, the Interest rate can be entered in decimal format, but no mistake can be made as to the true rate.  After the rate is entered in the topmost cell, we can duplicate the rate in each descending cell either by copying and pasting 0.08 or with a formula [cell C3=C2, cell C4=C3 and so forth]. Using formulas is better -- because if you wish to change your interest rate later, only the first cell needs to be modified.
O.K., with only two equations left to go, we will be finished with our first full row (#3).

Moving along, we can create a formula for cell D3 to calculate the yield based on one year of simple interest (C3) times the principal amount for that year (B3).  The formula is D3=C3*B3.  In computers, the * character is used for multiplication.

And, we can now create our New Balance formula for E3, which is the yield plus the original principal (E3=B3+D3).

We've managed to do the hard part -- all of our necessary formulas are now in place, along with our starting numbers. At this point, we can select a group of cells containing our formulas, copy and paste them into the cells immediately below to calculate continuing investments, reproducing our calculations easily.

Repeat this copy and paste for the remainder of your spreadsheet, and the entire sheet will give you your investment yields and total compounded interest at a glance.

Changing the values in C2 (Interest rate) and/or E2 (initial Principal) will help illustrate the power of compound interest and the effect of time, too.

 

 

This is just one of many powerful uses of spreadsheets -- and illustrates what can be managed if you invest a little money and leave it alone for long enough.

Try changing some of the parameters of this spreadsheet for your own dreams.

 

 

 

 

Suggestioned projects: (1) The stocks which make up the Dow Jones list have, on average, grown by about 12% per year since 1923.  Determine what $1000.00 initially invested would be worth today if compounded yearly.  (2) Many long-term investors like to add to their investment every year in something called dollar-cost averaging.  Create additional columns and alter your cell calculations to determine yields if additional money was added to the New Balance every year and what effect that would have on the final amounts compared to just leaving the starting sum alone.