Chapter 12

Introduction to Simulation Using Risk Solver Platform

This material is made available to instructors and students using Spreadsheet Modeling and Decision Analysis, 5e by Cliff T. Ragsdale, published by South-Western, a division of Cengage Learning. No part of this work may be reproduced or used in any form or by any means – graphic, electronic, or mechanical, including photocopying, recording, taping, Web distribution or information systems storage and retrieval systems – without the written permission of the publisher (Tel (800) 730-2214, Fax (800) 730-2215, http://www.Cengage.com). COPYRIGHT © 2009 ALL RIGHTS RESERVED.

12.0 INTRODUCTION

Chapter 1 discussed how the calculations in a spreadsheet can be viewed as a mathematical model that defines a functional relationship between various input variables (or independent variables) and one or more bottom-line performance measures (or dependent variables). The following equation expresses this relationship:

Y = f(X1, X2, ..., Xk)

In many spreadsheets, the values of various input cells are determined by the person using the spreadsheet. These input cells correspond to the independent variables X1, X2, ..., Xk in the previous equation. Various formulas (represented by f( ) above) are entered in other cells of the spreadsheet to transform the values of the input cells into some bottom-line output (denoted by Y above). Simulation is a technique that is helpful in analyzing models in which the value to be assumed by one or more independent variables is uncertain.

This chapter discusses how to perform simulation using a popular commercial spreadsheet add-in called Risk Solver Platform, created and distributed by FrontLine Systems. A limited-life (15-day) trial version of Risk Solver Platform and related products may be downloaded from FrontLine's web site at www.solver.com. A non-expiring educational version of Risk Solver Platform is also available to students.

12.1 RANDOM VARIABLES AND RISK

In order to compute a value for the bottom-line performance measure of a spreadsheet model, each input cell must be assigned a specific value so that all the related calculations can be performed. However, some uncertainty often exists regarding the value that should be assumed by one or more independent variables (or input cells) in the spreadsheet. This is particularly true in spreadsheet models that represent future conditions. A random variable is any variable whose value cannot be predicted or set with certainty. Thus, many input variables in a spreadsheet model represent random variables whose actual values cannot be predicted with certainty.

For example, projections of the cost of raw materials, future interest rates, future numbers of employees, and expected product demand are random variables because their true values are unknown and will be determined in the future. If we cannot say with certainty what value one or more input variables in a model will assume, we also cannot say with certainty what value the dependent variable will assume. This uncertainty associated with the value of the dependent variable introduces an element of risk to the decision-making problem. Specifically, if the dependent variable represents some bottom-line performance measure that managers use to make decisions, and its value is uncertain, any decisions made on the basis of this value are based on uncertain (or incomplete) information. When such a decision is

Chapter 12 Introduction to Simulation Using Risk Solver Platform

2

made, some chance exists that the decision will not produce the intended results. This chance, or uncertainty, represents an element of risk in the decision-making problem. The term “risk” also implies the potential for loss. The fact that a decision’s outcome is uncertain does not mean that the decision is particularly risky. For example, whenever we put money into a soft drink...