Transforming Spreadsheets into System Dynamics Models: Some Empirical Findings

Michael Kennedy

School of Computing IS and Mathematics,

South Bank University London, SE1 OAA, UK

Tel: 0171 815 7416 Fax: 0171 815 7499



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 criticised 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


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.


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 concerned 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.


Ackoff, R., (1981) Creating the Corporate Future, John Wiley & Sons, New York

Ansoff, H.I 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 ModellingÓ, 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

O'Brien, 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) Ò 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