ImagineIT overview
Exploring data
Modelling
Advanced modelling
List of toolbar buttons
Handy hints for Excel
Try the toolbar
Buy the toolbar
Maths in Action book

When you have downloaded the spreadsheet and opened it, try clicking the button to see the formulae and then the button to return to showing values.

Set up this spreadsheet for yourself

If you wish, you can try setting this spreadsheet up for yourself, starting with just the data typed in. Right-click this link to download it: SavingsData.xls

1 Set up the parameters

(a) Enter the parameters and parameter names

Enter the parameter values and their names in two rows. You can add some descriptive labels in other rows.

In the Savings example the parameters are named D, S and P and these names and their values have been already entered for you along with some descriptive labels.)

(b) Tell the toolbar where the parameters are

• Click .

  You can specify up to five separate sets of parameters - choose the first when asked.

• Next you will see a box on screen with spaces where you can enter cell references telling the toolbar where the parameter names and their values are. You do this by clicking in the relevant cells on the spreadsheet.
  

• You specify the row you want to use for parameter names by clicking on any cell in the row

• You specify the first parameter value and the last parameter value by clicking on the appropriate cell.

Colours appear to help you remember where the parameter names and values are:

2 Set up the table of data

(a) Enter the column headings for your table of data

A table can contain both numerical values and values to be calculated by the spreadsheet. (Data must be arranged in columns, not rows.)

Like parameters, columns can be given names that can be used in formulae. In the Savings example shown above, the columns are named N, A and I.

Once you have created your table of data you will need to tell the toolbar which row to find the names in.

You can also choose three other rows to use for graph titles, axis labels, and plot labels when one column is plotted against another on a graph. (Plot labels are labels that appear on a graph to show which plots are which when there is more than one plot.

(b) Tell the toolbar how large the table will be and where it will be

Do one of the following:

Either

• Select the cell which is to be in the top left hand corner of your table (containing the number 1).

• Click .

• Enter the numbers of rows and columns and then click OK (for the BodySizes example this would be 19 rows and 15 columns).

Or

• Select the whole table, excluding column headings. (Hint: you can drag across the table with the mouse to select it, or you can click in the top left hand cell, then hold down shift while you click in the bottom right hand cell.)

• (If you did this with the Savings example you would have to select a block of cells containing 11 rows and 3 columns with the cell containing 2006 in the top left hand corner.)

• Click

The table is automatically highlighted with a yellow background so you can easily see where it is:

(c) Tell the toolbar which rows to use for the graph titles, axis labels, plot labels and names

Now you have created your table of data you need to tell the toolbar which row to find the column names in that you can use in formulae.

You also need to tell it where to find the rows that you are going to use for graph titles, axis labels, and plot labels when one column is plotted against another on a graph. (Plot labels are labels that appear on a graph to show which plots are which. Excel calls these 'series labels'.)

With any cell in the table selected, click . (Note it is important to select a cell in the table.)

You will see a box on screen with spaces where you can specify the rows you want to use for graph titles, axis labels, plot labels and column names. You say where each row is by clicking on any cell in the row. (You can leave any spaces blank that you don't need.)

In the case of the Savings example, prompts have been added in the first column to indicate which rows to use. (However, you do not have to keep to this arrangement when writing your own model. You can use any rows you choose whether or not they are adjacent, provided they do not overlap the table. You can also use the same row for more than one purpose.)

Once again, the toolbar adds colours to help you remember which rows have been used. Notice that both for table columns and parameters, names are shown in red on a blue background:

To show formulae rather than values, click . (In actual fact the column widths will double, and formulae will appear on the left hand side of a cell while values appear on the right hand side. For clarity these two features have been changed in the above illustration.)

Clicking causes the display to revert to showing values.

(b) Copy formulae down

Looking at the illustration showing the formulae, you will see the formulae =S and =D setting the starting values (or initial values) in the year and amount columns (N and A).

Below these cells are the formulae =B12+1 (which adds one to the year) and =C12+D12 (which adds the previous year's interest to the previous year's amount). These two formulae need repeating down to the bottom of the table. To do this, select the two cells containing these formulae and click which means Copy down.

The cell at the top of the interest column (named I) contains the formula =A*(P/100) which calculates the interest for the first year. Select this cell and then click to copy this formula down to the bottom of the table, so calculating the interest for every year.

The spreadsheet now looks like this:

The formulae in the spreadsheet now fill the columns:

(c) Format numbers

The numbers in the A and I columns are shown to more decimal places than we want. To show only two decimal places (i.e. pence as well as pounds):

• select all the cells in the A and I columns

• go to the Format menu, choose Cells, and then the Number tab, followed by the Number category, before setting the Number of places to 2, and clicking OK.

(e) Hints for writing formulae

You can use words as well as algebraic symbols to name quantities (but Excel won't let you use anything that looks like a cell reference, such as b3 and the two special letters c and r - which mean column and row - you could use b.3, cc, rr instead)

You can use dots, underscores and capitals to make names more readable, (e.g. a.2 or a_2 for a2 and starting_amount or StartingAmount for the starting amount of a quantity).

(f) Cell references or names?

Notice that a mixture of cell references and names have been used in the formulae. Generally it is best to use names, because this makes formulae far easier to understand, both for yourself and for others. This is particularly the case for large and complex models where it also makes debugging and the spotting of errors much easier. In the present case cell references have been used to generate the sequence of years.

They have also been used to calculate the values in the amount column A where one needs to refer to the row above in order to add the interest for the previous year to the amount in the previous year.

A more readable way of referring to previous rows The next illustration shows how the names lastA and lastI can been used in the formula for A. Typing lastA and lastI as new column names causes the toolbar to automatically create columns containing values from the previous row - either A or I as appropriate.

(g) A refinement - labelling individual data plots

The finished model with graphs drawn can have labels identifying different plots, as this shows:

Notice the plot label on the graph giving the percentage interest rate appropriate to the given plot. To obtain this the following formulae was entered into the labels for data plots row: ="at "&P&" %". This joins (concatenates) the text at (followed by a space) to the value of P, and adds the text % (preceded by a space) on the end. When a Super Graph (see below) is drawn, this results in the plot label shown.

Note that a plot label should be entered above the column containing the y-values, which in this case is A.

A plot label like this is interactive, so if the value of P on the spreadsheet changes, so too does the plot label. Furthermore, if you produce several plots on one set of axes (either by freezing copies of plots and then changing the spreadsheet values, or by adding new plots of different quantities), each plot will be correctly labelled to show what it represents.

4 Drawing a graph

Decide on the two columns that you want to plot one against the other and the type of graph you want (in the Savings example you are most likely to want to plot A against N - the amount in the account against the year):

• Click on any cell in the column that you want to use for the y-values (usually the vertical axis) - this would be the A column in this case. (Note that you must select a cell within the yellow table area, not the name row or one of the label rows above it)

• Click the appropriate graph button , , , , , , , (the last of these is not an x-y graph in the usual sense, but what Excel confusingly calls a 'line chart' - actually just another way of displaying a bar chart).

  In the present case the first or second option would be most appropriate.

  [Note that Excel uses the term 'chart' to refer to all types of charts and graphs.]

• Choose either a Normal Excel graph or a Super Graph. A Super Graph allows you to 'freeze copies of plots'. This means that if the spreadsheet data changes, plots of the new and old data both appear on the graph so that you can make comparisons. For models a supergraph is usually the best choice as it enables you to understand how the model behaves in different scenarios, by comparing the graphical output for different values of the model parameters.
  

• ·Say which column you want for the x-values (usually the horizontal axis) by clicking in the x-axis space in the box that appears, and then clicking on any cell in the x-value column

• Click the Plot all rows button

  (TIP If you only want to plot certain rows, select the relevant rows in either the x-value or y-value columns by clicking on the first value to be plotted, then holding down Shift while you click on the last value to be plotted - then choose Plot rows shown )

The resulting graph will look like this:

Notice that the graph opens in a new window. You can resize this and the spreadsheet window to give the best view of both the data and the graph.

5 Additional features of Super Graphs

(a) Freezing plots or copies of plots on Super Graphs

• Click on a single plot that you want to freeze, or on a graph where you want to freeze all the plots, or on the spreadsheet if you want to freeze all plots on all graphs.

• Click the Freeze plot button .

• Choose either Freeze or Freeze a copy (which makes copies of the chosen plots, and then freezes them - if you then make changes to the spreadsheet data, new plots appear alongside the copies of the originals, so that you can make comparisons)

  (TIP You can tell the plots which have been frozen because the plot labels appear inside square brackets.)
  

(b) Deleting frozen plots or copies of plots on Super Graphs

• Click on a single frozen plot that you want to delete, or on a graph where you want to delete all frozen plots, or on the spreadsheet if you want to delete all frozen plots on all graphs.

• Click .

(c) Changing the type of graph

• Click on the graph you want to change

• Either click on one of the graph buttons , , , , , , , or select Chart type from the Excel Chart menu.

(d) Changing the x or y values plotted

• Click on the graph window and then click on the plot you want to change

• Click on the appropriate buttons - to change just the x-values , just the y-values , or both x and y-values, and possibly which rows are plotted as well

(e) Adding extra plots to a graph

• Click on any cell in the y-value column

• Click on the Add plot button

• Say which column you want for the x-values column by clicking in the x-axis space in the box that appears, and then clicking on any cell in the x-values column

(f) Deleting plots on a graph

• Click on the plot you want to delete

• On a Super Graph, now click on the Delete plot button , (or, for a normal Excel graph, you can simply press the Delete button on your keyboard)

6 Additional features

(a) Forgotten where things are?

• Click and you will be shown where graph titles, axis labels, plot labels, names and tables are located.

(b) Got in a mess with names?

• Click and all names will be deleted except those currently given by the special names rows on your spreadsheet (normally shown as red type on a pale blue background). This is really useful if you have defined a lot of names which you no longer need while developing your spreadsheet.

Be warned, however, that this will also delete all additional names that you might have defined specially and still need which do not appear in the names rows.

Advanced modelling features

The ImagineIT Toolbar also provides a number of more advanced modelling features:

• Equally spaced values in a column

• Step by step calculations

• IIteration and random (Monte Carlo) simulations

For details of these see the separate page entitled Advanced modelling features.

Richard Beare
13th February, 2006