Transforming Spreadsheets into System Dynamics Models: Some Em
Michael Kennedy
School of Computing IS and Mathematics,
South Bank University London, SE1 OAA, UK
Tel: 0171 815 7416 Fax: 0171 815 7499
E-mail: kennedms@sbu.ac.uk
Abstract:
The widespread adoption of spreadsheets has led to most large companies constructing corporate models. Despite the desirable qualities
that have facilitated their success, some disadvantages of spreadsheets have become apparent. They have often become unwieldy and
inaccurate, but more fundamentally, they only incorporate the ‘hard’ aspects of the environment. System Dynamics [SD] models with their
ability to handle complexity and to incorporate both ‘hard! and ‘soft’ factors, would seem to offer advantages in comparison but have been
ctiticised as giving only indicative results. Is it therefore necessary to sacrifice precision in order to achieve a wider view? This paper
examines the validity of the ‘indicative’ criticism by comparing the results from models with identical logical relationships in the two
environments and demonstrates that identical numeric results can be produced given the same input data.
Key words: System Dynamics, Spreadsheets, Stella™ MS Excel, Investment Appraisal Model, Higher Education Funding Model
Introduction
The author is engaged in the investment appraisal of information systems and other capital projects and more general areas of business
modelling and information systems management. The problem domain involves both ‘hard’ (frequently financial) and ‘soft’ (especially human
perception) issues in a dynamic and complex environment. The author has described the inadequacies of the existing methods used for
investment appraisal (Kennedy, 1996) and has become interested in the potential of SD to deal with this difficult problem environment. He is
aware, however, of the criticism that SD models are indicative in nature (Ansoff and Slevin, 1968; Sharp and Prince, 1984) and would be
unsuitable therefore in dealing with the ‘hard’ aspects of the analysis required. While it would be possible to import results from another
modelling environment, (for example a spreadsheet), the author felt that this would dilute the value of the SD model in terms of
documentation, capturing dynamic behaviour and as a learning and evaluation tool for managers. He therefore wished to ascertain the extent
of the difficulties with using SD in a ‘hard’ scenario by practical experimentation. The first experiment involved the construction of SD model
(HPS, 1994) of an investment appraisal model for a power generation plant. A model already existed in spreadsheet form so the opportunity
was taken to construct a SD model replicating the logic of the spreadsheets, so that results could be compared. This experiment is described
in a companion paper (Savicic and Kennedy, 1997). Following the successful replication of the results, a further experiment was conducted
on a Higher Education Funding model with the same results. More general observations on the nature, advantages and disadvantages of
spreadsheets and SD modelling environments were made.
Corporate Modelling using Spreadsheets
Spreadsheets are selected for the vast majority of business modelling purposes. Clarke and Tobias (1995) found over 90% utilisation. They
also found an upsurge in the adoption of corporate modelling compared to an earlier study (Grinyer & Wooller, 1975) facilitated by widespread
spreadsheet use. When originally launched, the spreadsheet was a combination of an electronic calculator and an accountant's analysis
paper (Hurrel, 1990).
Spreadsheets can be flexible, cost-effective, portable, versatile and easy to interface to other software and enjoy universality and have quick
development time (Clarke and Tobias, 1995). Spreadsheet business models can also be used as exploratory platforms on which corporate
models are easily constructed and simulation experiments conveniently performed. However, spreadsheets can become complex, difficult to
use and inflexible as the model grows. The ever increasing size and high maintenance costs mean that they lack robustness. A recent
survey (Panko and Halverson, 1996), reveal that out of 40 models within 22 companies, 21% of the models developed under experimental
conditions and 80% of the expanded existing spreadsheets contained errors of some type. The type of errors ranged from simple omissions
and logic to development and structural errors. Freeman (1996) reports similar problems.
Corporate Modelling Using System Dynamics Tools
The spreadsheets in this study were both complex models that were of strategic importance to an independent power producer and to a UK
higher education establishment respectively. The author was prompted to propose these experiments by two recent reports on corporate
modelling in the UK, (O'Brien, 1995; Clarke and Tobias, 1995), supporting the use of alternative modelling environments to traditional
productivity tools like spreadsheets in business modelling and also by Rubin, Johnson and Yourdon (1994) advocating the use of software
process "Flight Simulation" utilising SD tools. The Information Modelling and Management Group at South bank University, of which the
author is the co-ordinator, introduced STELLA (a GUI driven system dynamics software tool) to both organisations to experiment on the
transferability of the spreadsheet modelling logic into the STELLA environment.
As reported in a companion paper (Savicic and Kennedy, 1997), there were two motives for the client organisations to participate in the
projects. Firstly, the spreadsheet models had suffered from problems connected with difficulties of maintaining overall control, documentation
and logical errors. The structure of input data & assumptions, calculation, and reporting had also become difficult to sustain. Secondly, the
managements wished to explore the possibilities of further enhancements to their business models by taking the claimed advantages of SD
tools (Wolstenholme, 1990). The shared vision that may be engendered by the construction and use of SD models (Senge, 1993) can assist
managers to ‘navigate’ their organisations to success. Ackoff (1981) supports the systematic analysis of corporate problems in the areas of
management and planning and it would appear that SD tools can assist in the attainment of this goal. Under the supervision of M. Kennedy,
the spreadsheet decoding and the Stella™ modelling, simulation and analysis were done by V. Savicic and D. Thomas.
Assessment
The author was aware of the criticism from some authorities that SD gives indicative results (Ansoff and Slevin, 1968; Sharp and Prince,
1984). The numerical accuracy of the results from the SD models constructed was of critical importance in these applications. Forrester
(1968, 1987) and Coyle (1986) have responded to specific attacks of Ansoff and Slevin (1968), and Sharp and Prince (1984) respectively in
defence of SD that SD is concemed with the structural relationships between levels and rates and their dynamic behaviour. Meadows (1982)
on the lessons of global modelling, state that models of social systems should not be expected to produce precise predictions.
Our experience in replicating MS Excel models into the STELLA environment, appears to cast doubt on this criticism where ‘hard! data is
used. In this small, self selecting, sample the numerical accuracy of results in the SD models depended on the correctness of data used.
However, the two projects not only replicated numerical accuracy, but also gave some other advantages. The client managers were
favourably impressed by the self- documenting capacity of the tool, the ability to ‘audit’ the transparent logic and the ability to communicate
the model logic both to other colleagues and to new system users (Peterson, 1992). The major problem lay in the time taken to ‘decode’ the
[undocumented or poorly documented] spreadsheets, the size of the resultant Stella™ files and the requirement to replicate model sectors
[which should be avoided by the use of arrays in Stella IV] and the general lack of managerial familiarity with the approach.
References
Ackoff, R., (1981) Creating the Corporate Future, John Wiley & Sons, New York
Ansoff, H.l and Slevin, D. P (1968), “ An Appreciation of Industrial Dynamics” Management science Vol: 14 383-397.
Clarke, S. and Tobias, A. (1995), Corporate Modelling in the UK: A survey, Spreadsheets may be the automatic choice, but do they inhibit
richness?, OR Insight, July- Sept. Pp: 15-20
Coyle, R. G, (1986), “ Comment on System Dynamics and Operational Research: An Appraisal” European Journal of Operational Research,
Vol: 23 - pp: 403 - 406
Coyle, R.G, (1995) Management System Dynamics, Chapman & Hall
Forrester, J. W , (1968) “Industrial Dynamics - a Response to Ansoff and Slevin” Management Science Vol: 14 601-618.
Forrester, J. W, (1987), “Lessons From System Dynamics ModellingO, System Dynamics Review, Vol 3, pp: 136-149
Freeman, D., (1996) How to Make Spreadsheets Error-Proof, Journal of Accountancy, Vol: 181, May , p: 75-77.
Grinyer, P.H.. and Wooller, J. (1975), Corporate Models Today - a new tool for Financial Management, Chartered Institute of Accountants,
London.
HPS [High Performance Systems Inc.] (1994) STELLA: An Introduction to System Thinking,
Hurrel, S (1990) Accounting for the future, Accountancy , September
Kennedy, M. S., (1996) Investment Appraisal of Information Systems Projects: Alternative Methodologies Explored., Proc. of Financial
Information Systems Conference, Sheffield, UK.
Meadows, D. M., (1982) “ Lessons from Global Modelling and Modellers”, Features, 14. 111-121
OBrien, F. (1995), Understanding Future Uncertainty, OR Insight, July- Sept. Pp: 9-14
Panko, R.R and Halverson, P.R, Jr (1996) “A Survey of Research on Spreadsheet Risks” Proc. of 20th International Conference on System
Science, Maiu, Hawaii.
Peterson, S. (1992) Software for Model-Building and Simulation: An Illustration of Design Philosophy: European Journal of Operational
Research, Vol.: 59 Issue: 1 p: 197-202
Savicic V. and Kennedy M.S. (1997) "The Transformation of a Power Plant Investment Appraisal Application from a Spreadsheet into a
System Dynamics Model” Proc. of 15th International System Dynamics Conference, Istanbul, Turkey.
Senge, Peter. M (1993) The Fifth Discipline: The Art and Practice of the Learning Organisation, Century Business
Sharp, J.A and Prince, S. H. R (1984) O System Dynamics and Operational Research: An appraisal" European Journal of Operational
Research, Vol: 16, pp: 1-12.
Rubin H.A., Johnson M., Yourdon E. (1994) Using software Process "Flight Simulation” to predict the impact of improvements in process
maturity, unpublished draft paper supplied by Rubin
Wolstenholme E. F, (1990) "System Enquiry-a System Dynamics Approach”, Wiley, Chichester
CC BY-NC-SA 4.0