Showing posts with label Spreadsheet Modeling. Show all posts
Showing posts with label Spreadsheet Modeling. Show all posts

Tuesday, July 13, 2010

What qualifies as a Simulation Model?

A theme that has been running through my career since my Master's project has been the question of measuring complexity in modelling and simulation. When can one proclaim to have built a simulation model and when is one glorifying simple analysis?

In the Operations Research ecosystem the tendency is certainly to inflate. Salesmen, curriculum vitae authors, recruiters and consultancies across the spectrum are all motivated to embellish the work that they do and work that is done. Like any scientific individual I seek to slice through the static, inform myself as to who is doing extraordinary work, and to build myself a framework from which I can safely criticize the inflations of others.

I have been working on a set of rules for separating "models" into models, calculations and simulations. I feel like there is a gaping opportunity here for contribution from complexity, chaos, and other disciplines in Computer Science and Mathematics, but here's what I've put together thus far:

Simulations are models, but not all models are simulations. Calculations are not models.

Models
  1. A model is a simplified representation of a system.
  2. All models are wrong, but some models are useful
Calculations
  1. The result of a calculation can be expressed in a single equation using relatively basic mathematical notation.
  2. Where calculations contain an time element, values at different times can be determined in any order without referring to previous values.
Simulations
  1. A simulation is a calculation in which one parameter is the simulation clock that increments regularly or irregularly.
  2. The outcome of a simulation could not have been determined without the use of the clock.
  3. While an initial state is typically defined, an intermediate state at a given time should be difficult or impossible to determine without having run the simulation to that point.
  4. Almost any model that involves repeated samples of random numbers should be classified as a simulation.
Consider the following progression of "models" that output an expected total savings:
  1. Inputs: Expected total savings.
  2. Inputs: Annual savings by year, time-frame of analysis.
  3. Inputs: Annual savings per truck per year, number of trucks by year, time-frame of analysis.
  4. Inputs: Annual savings per truck per year, current number of customers, number of trucks per customer, annual increase in customers, time-frame of analysis
  5. Inputs: Annual savings per truck per year, current number of customers by geographical location, annual increase in customers by geographical location, routing algorithm to determine necessary trucks, time-frame of analysis.
  6. Inputs: Annual savings per truck per year, current number of customers by geographical location, distribution of possible growth in customers by geographical location, routing algorithm to determine necessary trucks, time-frame of analysis.
As you can see, complexity builds and eventually passes a threshold where we would accept it as a model. "Model" 4 is still little more than a back of the envelope calculation, but Model 5 takes a quantum leap in complexity with the introduction of the algorithm. Model 5 however I would still not classify s a simulation, because any year could be calculated without having calculated the others. Finally Model 6 introduces a stochastic variable (randomness) that compounds from one year to another and brings us to a proper simulation.

I've seen calculations masquerading as simulations models at a Fortune 500 company both internally and externally. While the result is the same: outcomes determined from data where validity is asserted by the author, I know that Operational Research practitioners reading this will appreciate my desire to classify. At the very least it will help us separate what the MBAs do with spreadsheets from our own work.

I welcome input from others on this topic, as I am only just developing my own theories.

Tuesday, November 4, 2008

ORdinary Spreadsheets and ORdnances

The July-August 2008 Interfaces Journal ran a theme of "The Use of Spreadsheet Software in the Application of Management Science and Operations Research". In their article from that issue, "A Spreadsheet Implementation of an Ammunition Requirements Planning Model for the Canadian Army", Hurley and Balez describe a successful spreadsheet model for planning training ammunition expenditures.

Ammunition is expended in training courses in a highly uncertain environment. Course registration rates, failure rates (before completing the entire course), and other uncertainties make it difficult to accurately forecast ammunition consumption. Planners must choose a course portfolio that will not result in an ammunition shortage. Of course, to minimize the chances of running out of ammo, planners to date had been planning to the maximum expenditure per course. The consequence of this was that the program came repeatedly under budget, 38.7% in 2002-03. This is far from ideal when attempting to allocate scare resources. Naturally this was an opportunity to apply risk management principles in order to either request a smaller budget to accomplish the same goals or to do more with the same budget.

The solution took the form of a spreadsheet tool. The Excel spreadsheet combined with Visual Basic for Applications (VBA) provided an easy and inuitive interface for planners to interact with the risk model. As a result, in 2004-05 the program was only 3.1% under budget. I will not go into too many more details as they are available in the article, but I had two interesting thoughts:

[1] A big advantage of using spreadsheets is the familiarity most managers have with it. Leveraging this, the team built a simple spreadsheet simulation to demonstrate the portfolio effect of running several courses. With repeated "F9-Simulations" (my term) they were able to demonstrate that while 10 course sections will never use 10 times the maximum (as presently budgeted), it is actually reliably much less than this. Moving up a level and using @Risk to run 10,000 simulations they were able to convincingly demonstrate the concept.
We cannot overemphasize the value of this type of spreadsheet demonstration in selling the potential of an OR model.
Interestingly enough their experience differs from my own. I tried to convince an utlrasound department supervisor that if average-45-minute appointments of uncertain lengths are booked every 45 minutes, her technologists would reliably work overtime. To do this I built a simple spreadsheet simulation, but it was totally lost on her. This is not meant as a knock against this approach, but rather to emphasize the importance of manager familiarity with spreadsheets. My ultrasound supervisor as a senior medical radiation technologist thinks differently from a rising Canadian Colonel.

[2] When selecting a portfolio of courses to fit the approved budget (less than requested), the Army chose to manually optimize using the tool rather than accept a priority-optimized result from a linear program. This perplexed the authours and I think they wrongly blamed themselves for a failure to achieve buy-in. It is my experience that when dealing with problems of a magnitude that an individual can wrap their heads around, clients prefer to leverage their intuition and optimize by hand. As OR practitioners we may not trust the client to acheive a truly optimal result, but as a client they do not trust a model to capture all of the nuances they know inuitively and the answer, of course, is somewhere in between.

The idea of doing OR with Excel probably wasn't what got you started in the field, but if you like seeing results it might just keep you in it.

Hurley, W.J., Balez, Mathieu. 2008. A Spreadsheet Implementation of an Ammunition Requirements Planning Model for the Canadian Army. Interfaces 38(4) 271-280