Recursive simulations of spreadsheet models constitute one of the more advanced uses of Excel across various industries, although perhaps most commonly in the financial services industry. Often, third-party add-ins are utilized to run Monte-Carlo simulations in Excel.
One of the biggest challenges in this form of Excel usage is combatting the performance detriments associated with batch simulation runs, which can often transform these tasks into hours or days of sequential processing. In addition, running such processes on individual user machines could occupy the full system’s resources for the duration of the simulation.
Often times, server-oriented environments prove to be a more appropriate environment for executing these types of processes, especially since these companies are frequently interested in publishing and distributing these sorts of calculation models over the web. SpreadsheetWEB’s core calculation engine has been designed to perform spreadsheet-based calculations in a server environment where concurrent usage and performance constitute the top priorities.
In this blog post, we aim to compare the performance of SpreadsheetWEB’s calculation engine against Excel’s calculation engine when executing a large amount of simulations against a complex model.
Testing Methodology
We used a spreadsheet (approximately 9.5 MB) that calculates the shipping costs of a package based on factors such as weight, size, origin, and destination. These variables constitute the inputs for the model. Once set, the engine performs its calculations and the system returns the defined output variables – in this case, the shipping cost.
In order to generate a comparable scenario, we generated a simple application to access SpreadsheetWEB’s calculation engine directly. We ran 8,000 simulations, distributed across 8 threads.
As a point of comparison, we generated a simple VBA macro to perform those same 8,000 simulations through Excel’s interface.
Four different simulation tests were performed across Excel and SpreadsheetWEB. The average of these four tests were compared in the Test Results section below.
Testing Setup
All tests were performed on a server with 8 cores Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz and 24GB RAM. The operating system on the server was 64-bit Windows Server 2012 R2 Standard.
The Excel tests were performed on 32-bit Microsoft Excel 2016. Since the server had 8-cores, Excel was configured to use all available processors.
Similarly, the SpreadsheetWEB calculations were performed on a 32-bit version of the calculation engine, using a pre-release version of the 6.1 engine.
Test Results
The chart below compares the results of completing 8,000 simulations in Excel versus SpreadsheetWEB. On average, it took Excel 208 seconds to complete all 8,000 simulations, whereas SpreadsheetWEB’s calculation engine required only 38.1 seconds. SpreadsheetWEB completed the simulation runs in 18% of the time that Excel required.
Interestingly, during the Excel simulations, the total CPU usage remained at approximately 30-40%, despite configuring it to use all processors. On the other hand, the CPU utilization during the SpreadsheetWEB runs was at a consistent 100% , since it was designed to utilize the full processor capacity of the target machine. Based on the test results above, configuring SpreadsheetWEB to utilize only 30-40% of processor capacity would have still yielded a net performance gain of approximately 50% over Excel.
