I T T
Growth of savings
The spreadsheet below shows a model of the growth
of savings in an account. This is how it looked.
Setting up the spreadsheet
First of all the spreadsheet title and the parameters
and labels at the top were typed in.
Next the Specify parameters button
was clicked allowing the user to identify (1) the cells containing
the values of the parameters Deposit, Starting Date and Per Cent
Interest Rate (shown in green at the top) and (2) the cells containing
the names D, S and P for referring to these parameters in formulae
(shown as red type on a blue background).
Note that once names have been specified they are
automatically ready to use in formulae. (This saves a great deal
of time over Excel as it stands, where one has to go to the Insert
menu, then choose Name, then select Define or Create).
Next the data table was set up by selecting the
top left hand corner cell, clicking the new table button and choosing 11 rows and 3 columns. The default yellow colour
for the background helps remember where this data table is.
With any cell in the table selected, the Specify
table labels button was then clicked and the rows to be used for the
following were chosen: (1) graph titles, (2) axes labels, (3) labels
for identifying different data plots (all colour coded as green),
and finally (4)names for data columns that can be used in formulae
(red text on blue background).
Next the table headings were typed into the rows
labelled graph titles and axis labels, and the names N, A and I
as the column names. Again note that once names have been specified
they are automatically ready to use in formulae.
The formulae shown above were entered into the top
two rows of the table, and with the second row of formulae highlighted
the Copy down button
was clicked, filling the whole table with values as shown in the
Finally, with any cell in the Amount at start of
year (A) column selected, the x-y graph button
was clicked allowing a 'Supergraph' to be produced showing Amount
of savings on the y-axis and the year on the x-axis. Both axes are
automatically labelled, the title 'Growth of savings' is added.
To get the data plot labelled 'at 5%', the following
formula was entered into cell C10: ="at "&P&"
%" (the '&' sign is used to join text and values together).
Choosing a 'Supergraph' instead of a normal Excel
graph makes the graph title, axis labels and data plot labels interactive
so they change when the spreadsheet is changed. Even more importantly,
plots can be superimposed on a Supergraph for different values of
the parameters (something that is otherwise time-consuming to do
using Excel as it stands). This is illustrated by the following
screen shot, showing a comparison between different interest rates:
The additional plots are added by clicking the Freeze
plot button and
choosing to freeze a copy of the existing plot. Then when the interest
rate is altered the frozen copy of the plot remains unchanged, but
the original which is still linked to the spreadsheet changes.
Downloading the example spreadsheets
To download the spreadsheet already set up, right-click
(Mac: control-click) this link, choosing to save to your hard disc,
and then open the downloaded file from within Excel.
If instead you want to download just the data and
practise setting up the parameters, table, formulae and graph for
yourself, use this link:
This example is a very simple one just showing how
to set up a very simple model. For further ideas look at the complete
details of toolbar buttons. Alternatively, look at the example of
graphical data exploration.