A Spreadsheet Scenario Analysis Technique that Integrates with Optimization and Simulation

Thomas A. Grossman - tagrossman@usfca.edu
School of Business and Professional Studies, University of San Francisco, San Francisco, California 94117

Özgur Özlük - ozgur@sfsu.edu
College of Business, San Francisco State University, San Francisco, California 94132

Abstract

Scenario analysis is a widely used technique, and business students should be proficient in running scenarios through a spreadsheet model. We propose 10 desirable properties for a scenario analysis technique, and show that the Microsoft Excel Scenario Manager tool satisfies only one of them. We provide a tutorial for a better technique that can be programmed into an existing spreadsheet in a matter of minutes. We show how to integrate the technique with the Excel Solver to allow for rapid optimization of multiple scenarios and with the Crystal Ball add-in for Monte Carlo simulation to enable simulation of multiple scenarios. Our undergraduate and MBA students are able to learn the technique with little difficulty.

Key words
spreadsheets; scenarios; scenario analysis; optimization;
simulation

History
Received: July 2008; accepted: January 2009.

Download the PDF
10.1287/ited.10.1.18

Citation Information
Grossman, T. A., Ö. Özlük. 2009. A Spreadsheet Scenario Analysis Technique That Integrates with Optimization and Simulation. INFORMS Trans. Ed. 10(1) 18-33. Available online at http://ite.pubs.informs.org/.

DOI: 10.1287/ited.1090.0027