|
Example model 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 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
With any cell in the table selected, the Specify
table labels button 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 Finally, with any cell in the Amount at start of
year (A) column selected, the x-y graph button 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 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.
|
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).
and choosing 11 rows and 3 columns. The default yellow colour
for the background helps remember where this data table is.
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).
was clicked, filling the whole table with values as shown in the
first screenshot.
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.
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.