• Corpus ID: 15265443

Arup’s Doctoral College conferences. The

@inproceedings{Xuereb2012ArupsDC,
  title={Arup’s Doctoral College conferences. The},
  author={Konrad Xuereb},
  year={2012}
}

• K. Xuereb
• Published 2012
• Computer Science

Complex engineering analysis spreadsheets are widely used and known to be error prone. These models frequently change hands and evolve as they are applied to new projects requiring engineers to relearn their inner workings. We apply computational techniques to extract, visualise and analyse the calculation graph underlying the model. Extraction is done by starting with a model output, parsing its formula, identifying which cells it references, then recursively processing these cells in turn to build up a calculation graph, which is then analysed using many techniques as shown in Figures 1-4. This Extraction and Analysis Methodology (EAM) has been applied to three generations of Arup’s sustainability assessment Integrated Resource Management (IRM) model. This model integrates sustainability models from many disciplines into a coherent assessment tool. Exploring this model gave insight into model complexity, focus, construction and bugs. Figure 1 shows a visualisation of the per capita transport carbon metric showing its constituent calculations for different modes of transport and a model bug, which was later fixed. Figure 2 shows the evolution of Arup’s IRM model over several years from a fairly simple early concept model to a comprehensive and detailed model built using complex structure, which as shown in Figure 3, can be difficult to maintain. Finally we see from Figure 4 the focus of the 2009 model on energy demand and passenger transport disciplines. We identify the creep from strict demarcation of input and output worksheets by the number of inputs required in the output models. These analysis techniques provide much needed insight to practitioners and model maintainers. Value • Presents tools for comprehending large engineering spreadsheets • Identifies model quality challenges and aids maintenance of such models • Explores the evolution of a complex model over several years and several projects identifying changes in focus and complexity • Sensitivity analysis identifies the inputs with most scope to impact project goals from the thousands in the model. Conclusions & next steps Applying this Extraction and Analysis Methodology to Arup’s IRM models we have shown the need for model analysis tools to aid practitioners in their adaptation of the model to new projects. We also applied a sensitivity analysis to each model to identify which of the over 1000 model inputs will give most scope to impact the projects per capita carbon emissions, whilst measuring impacts upon other metrics. This gave valuable focus to efforts to improve the projects sustainability. We believe that there is a substantial need for spreadsheet analysis tools such as these to improve model quality and auditing, as well as aid practitioners in their tasks. We also foresee requirements for tools to help maintain different branches of the IRM model incorporating model fixes automatically. Optimisation efforts should be supported by more sophisticated sensitivity analysis and optimisation engines such as constraint based optimisation. Figure 1: Visualisation of Transport Carbon calculation graph Identifying a model bug which was later fixed Figure 2: Evolution of Arup’s IRM model Showing a rise in size and complexity of the model Figure 3: Formula length in the 2011 IRM model Identifying a minority of formulas which were difficult to maintain Figure 4: Discipline specific metrics from the per capita Carbon calculation Identify model focus by areas of highest complexity in the model