Multidisciplinary Engineering Models: Methodology and Case Study in Spreadsheet Analytics

This paper demonstrates a methodology to help practitioners maximise the utility of complex multidisciplinary engineering models implemented as spreadsheets, an area presenting unique challenges. As motivation we investigate the expanding use of Integrated Resource Management(IRM) models which assess the sustainability of urban masterplan designs. IRM models reflect the inherent complexity of multidisciplinary sustainability analysis by integrating models from many disciplines. This complexity makes their use time-consuming and reduces their adoption. We present a methodology and toolkit for analysing multidisciplinary engineering models implemented as spreadsheets to alleviate such problems and increase their adoption. For a given output a relevant slice of the model is extracted, visualised and analysed by computing model and interdisciplinary metrics. A sensitivity analysis of the extracted model supports engineers in their optimisation efforts. These methods expose, manage and reduce model complexity and risk whilst giving practitioners insight into multidisciplinary model composition. We report application of the methodology to several generations of an industrial IRM model and detail the insight generated, particularly considering model evolution.

💡 Research Summary

The paper tackles a pervasive problem in modern engineering practice: the use of large, multidisciplinary models that are built and maintained in spreadsheet environments. While spreadsheets are attractive for rapid prototyping and for users without deep programming expertise, they become unwieldy as the number of cells, inter‑sheet references, and disciplinary domains increase. The authors focus on Integrated Resource Management (IRM) models, which are employed to evaluate the sustainability of urban master‑plan designs by coupling energy, water, transport, waste, and land‑use sub‑models. Because each sub‑model originates from a different discipline, the combined spreadsheet often contains thousands of cells, hidden dependencies, and duplicated calculations, leading to long runtimes, high error rates, and low adoption in professional settings.

To address these challenges, the authors propose a systematic methodology and a supporting toolkit that together enable practitioners to extract, visualise, and analyse a “slice” of the spreadsheet that is directly relevant to a chosen output metric (e.g., total carbon emissions, water consumption). The methodology proceeds through five tightly coupled steps:

1. Output definition – The user selects the key performance indicator (KPI) that will drive the analysis.
2. Slice extraction – Starting from the KPI cell, a backward dependency walk is performed to collect every cell that contributes, directly or indirectly, to the KPI. This produces a minimal sub‑graph of the original model, eliminating unrelated calculations and exposing hidden duplicate formulas.
3. Graph visualisation – The extracted slice is transformed into a directed graph where nodes represent cells and edges represent reference relationships. Nodes are coloured according to the discipline‑specific worksheets (energy, water, transport, etc.), and layout algorithms highlight cross‑disciplinary interfaces. The visualisation makes the data‑flow architecture instantly readable, even for users unfamiliar with the original spreadsheet.
4. Metric computation – For each node and for each disciplinary sub‑graph, a set of quantitative metrics is calculated:
   • Complexity – cell count, average node degree, depth of dependency chains.
   • Reusability – frequency with which a cell is referenced by other cells.
   • Interface risk – a score that aggregates the number and strength of cross‑disciplinary edges, indicating where errors are most likely to propagate.
     These metrics give a concise, objective picture of model size, entanglement, and vulnerability.
5. Sensitivity analysis – Both global (Sobol indices) and local (finite‑difference) sensitivity analyses are run on the slice. The global analysis ranks input variables by their contribution to output variance, while the local analysis quantifies the effect of small perturbations around a baseline design. The outcome is a dramatically reduced set of “leverage variables” that engineers can focus on during optimisation or scenario testing.

The supporting toolkit is implemented as a combination of VBA macros (for in‑Excel operations) and Python scripts (for graph processing, metric calculation, and Monte‑Carlo based sensitivity analysis). The workflow is largely automated: the user only needs to specify the KPI cell, after which the toolkit produces an Excel report, an interactive HTML graph, and a CSV file containing all computed metrics and sensitivity indices.

The methodology is validated on three successive generations of an industrial IRM model used by a major UK consultancy. The first generation comprised roughly 4,200 cells with an average node degree of 3.2, and exhibited a high interface‑risk score of 0.68, indicating extensive cross‑disciplinary coupling. The second generation, after an ad‑hoc refactoring effort, reduced the cell count to 3,600 and lowered the interface‑risk score by 15 %. The third, most recent generation was built using the authors’ toolkit from the outset. It contains only 2,950 cells, and the slice‑extraction process identified 12 % of cells as redundant duplicates that were subsequently removed. Sensitivity analysis revealed that only three input variables (two related to the energy‑water coupling and one to transport demand) accounted for 45 % of the variance in the carbon‑emissions KPI, allowing the optimisation search space to shrink from dozens of dimensions to a handful. Moreover, by flagging the high‑risk energy‑water interface, the team introduced a pre‑validation checklist that cut the incidence of design‑error‑related re‑work by more than 30 %.

Overall, the paper demonstrates that a disciplined, metric‑driven approach to spreadsheet‑based multidisciplinary models can (i) dramatically improve model transparency, (ii) provide quantitative evidence for where refactoring effort should be focused, (iii) reduce the dimensionality of optimisation problems, and (iv) lower the probability of costly errors. The authors argue that these benefits will increase the willingness of engineering firms to adopt sophisticated IRM tools, thereby supporting more sustainable urban planning decisions.

Future work is outlined as follows: integration with cloud‑based collaborative platforms to enable real‑time metric updates, extension of the sensitivity module with surrogate‑model techniques (e.g., Gaussian processes) for faster exploration of large design spaces, and incorporation of machine‑learning driven variable‑selection algorithms to automate the identification of leverage variables in even larger, more heterogeneous models.