Introduction to information systems T. Cornford, M. Shaikh is1 060 2013

combination of the two.
• The second law requires that any item of input data or model 
parameter should appear only once. This helps ensure that you will 
not have problems with inconsistent data or when updating some 
value.
Figure 2.3: A spreadsheet abiding by the two laws of spreadsheet modelling.

---

IS1060 Introduction to information systems
28
Figure 2.3 gives a simple example of the application of these two laws. The 
spreadsheet problem uses the interest rate as an input variable or parameter 
and it is entered in one spreadsheet cell only (in cell C4). Thereafter the 
model makes reference to that cell to use the interest rate in any subsequent 
calculation. You should thus never write the cell formula for cell D8 as 
=C8*0.065 (assuming that the interest rate was 6.5 per cent) and you 
would certainly never replicate such a formula. The correct approach is to 
enter the formula in cell D8 as =C8*$C$4 – the use of the $ signs makes an 
absolute and unchanging reference to cell C4. This is a formula that can be 
copied or replicated down the column. In this way we can be sure that all 
the formulae in column D use the same reference to the interest rate. If and 
when we wish to change it we need only enter the new rate (say 8.5 per 
cent) once. The alternative approach of writing the formula as C8*0.065 
would mean that we had to hunt down every use of 0.065 and change it 
and the potential for error in doing that would be very great.
Interrogation of an analytical model usually means the generation of 
numerical results, but it could be as textual data. More sophisticated 
interrogation practices include:
• What-if? analyses – What if the interest rate was to go up by 2 per cent?
• Sensitivity analyses – If the cost of one component of a manufactured 
product was to double, how much would the overall cost go up?
• Goal-seeking analyses – How much must the marketing budget be if we 
are to achieve a 4 per cent growth in market share? This spreadsheet 
would be based on a model that relates sales to marketing spend.
• Optimisation – What is the optimal mix of advertising spend as 
between newspaper advertisements and television commercials?
In each case, answers to these questions will require a particular style of 
interrogation of a basic model.
You are expected to consider the following areas in your project work and 
to write about this in your report:
• analysis of a problem domain in terms of variables and relationships 
incorporated in a model
• overall design of a spreadsheet for clarity and to support an appropriate 
style of interrogation (‘what-if?’, optimisation, etc.)
• use of appropriate functions for data manipulation (for example, sort, 
sum, average, look-up tables and other simple mathematical and 
statistical functions)
• formatting of cells for text and numbers
• design of an onscreen and printed report from the spreadsheet
• design of graphical reports including the choice of an appropriate 
graph type.