The Excel team’s blog had an entry recently introducing the concepts that are central to using a backend Windows HPC cluster to solve complex Monte Carlo simulations in an Excel spreadsheet.
If you are an old school CFD guy who mostly uses Excel to do budgets for proposals, this will seem like putting a 500-pound saddle on a 100-pound pony. But there are significant swaths of industries like risk management and finance that use very complex models built into Excel spreadsheets that can take days or weeks to evaluate. Making them faster is a good thing for those businesses.
This is actually the second of two Excel posts, and it focuses more on the specific case of Monte Carlo models. You can find the more general first post here.
The example in the post is interesting and helpful to motivating the discussion
Let’s say that we work for the local government and we want to calculate when we will likely have to perform expensive maintenance work to a road bridge in a nearby town. We know that the rate of fatigue of the bridge is a function of several variables: average daily traffic density, average daily air temperature and average material durability are just a few that come to mind. We also know that each of these variables behaves randomly. That is, we cannot predict their exact values – but we do know what range of values they will have and we know that some values are more likely than others. Because of the non-deterministic nature of the system we’re modeling, one way of solving our problem is to build a stochastic model and to use a Monte Carlo simulation to help us figure out when we’ll most likely need to repair the bridge.
How would we use Excel 2010 and Windows HPC to do that?