The Decision Sciences Journal of Innovative Education

Using Correlation Matrices and Optimization to Add Practical Functionality

to Spreadsheet Simulation for MBA-Level

Quantitative Analysis Courses

Patrick R. McMullen

Babcock Graduate School of Management

Wake Forest University

Winston-Salem, NC 27109

patrick.mcmullen@mba.wfu.edu

INTRODUCTION AND BACKGROUND

            A publication by Albritton et al. (2003) indicates that while MBA-level Management Science/Operations Research courses have been de-emphasized in recent years, they continue to survive — typically as part of a quantitative modeling class, which includes both statistical analysis and optimization components.  The Albritton research effort presents survey results indicating that MBA-level quantitative modeling courses do provide intensive coverage of the traditional optimization approaches of linear programming formulation, integer programming formulation and network optimization (survey respondents claiming coverage rates of 91%, 78% and 77%, respectively).  Unfortunately, spreadsheet simulation was covered by only 54% of the survey respondents, and the intensity of this coverage was less than average (on a seven point Likert scale, the mean intensity was 3.25).  This finding suggests that the coverage of spreadsheet simulation is essentially introductory — treating spreadsheet cells as assumptions, which behave according to some probability distribution.

            While treatment of spreadsheet cells as assumptions is the cornerstone of spreadsheet simulation, a few simple extensions can be employed to add practical functionality to the spreadsheet model.  This note focuses on two of these simple extensions:  (1) treating assumptions as having interdependence with each other; and (2) choosing values of decision variables to optimize objective functions, while certain inputs to the spreadsheet model are treated as correlated, stochastic assumptions.

            Many good quantitative business modeling texts cover spreadsheet simulation.  To the author’s knowledge, however, only one of them (Powell & Baker) mentions the treatment of assumptions as interdependent, and this is via a brief discussion only (no example).  Additionally, the author is aware of only three texts which detail optimization with stochastic assumptions (Powell & Baker; Ragsdale; and Winston), and these optimization applications do not address the interrelationships between assumptions.  Because of this void in textbook coverage of the interrelationships between assumptions, the author feels justification in presenting this teaching note showing the benefit of using interrelated stochastic assumptions.  An additional motivation for this teaching note is that the previously mentioned Albritton study shows that classroom treatment of spreadsheet simulation is essentially at the introductory level only.

            There are two popular tools for spreadsheet simulation:  Palisade’s (http://www.palisade.com/) “@Risk,” and Decisioneering’s (http://www.decisioneering.com) “Crystal Ball.”  The interested reader can learn more about these tools from the references cited below.  While both of these tools are capable of performing the extensions of interest here, all subsequent examples are given via Crystal Ball.

SPREADSHEET SIMULATION MODELING EXTENSIONS

            Two types of examples are presented:  the first showing the use of assigning interrelationships among the assumptions; the second showing how objective functions can be optimized when both stochastic assumptions and decision variables co-exist, with interdependence among the assumptions.

Interdependence of Assumptions Example

            Consider the simple example where an investor has three stocks in their portfolio:  Stocks A, B and C.  The investor is interested in estimating the portfolio value in one year given the following assumptions:

Insert Table 1 About Here

It is also assumed that shares will neither be purchased nor sold in the one-year period.  The current value of this portfolio is $117,380.  When the following assumptions are incorporated into a simulation model and run for 25,000 trials, the expected value of the portfolio one year from the present is $122,813.92, with a standard deviation of $3,999.93.  Given these assumptions and results, the probability of the portfolio having more value one year from today is 91.13%.  Figure 1 details this result via the Crystal Ball frequency chart.

Insert Figure 1 About Here

This spreadsheet simulation is a typical application of using assumptions to estimate a forecast, and then asking “what-if” types of questions.  Unfortunately, this model has a profound flaw.  This flaw is in the form of the implicit assumption that the three stock prices are unrelated to each other — in other words, the assumptions are assumed to be independent of each other.  The three stock prices do in fact have some degree of interrelationship.  The present naïve assumption can be remedied via the modeler inputting the degree of correlation between each assumption.  In Crystal Ball, this is done by creating a correlation matrix, which governs the degree of interrelationship between the variables.  The degree of relationship that Stock A has with both Stocks B and C is assumed to be 60%, while the degree of relationship between Stocks B and C is assumed to be 90%.  It is important to remind the reader that the correlation between two variables is the ratio of the covariance between the two variables to the product of the two variable’s standard deviations — in essence, a simple measure of the pairwise relationship between the variables.  This Crystal Ball correlation matrix is detailed in Figure 2.  When the simulation is re-run with the same assumptions as before, along with the correlation of the assumptions, the expected value of the portfolio one year from the present is $122,908.09, with a standard deviation of $5747.60.  The expected value is not unique from the previous result, but the standard deviation is much higher when addressing the interdependence of the three assumptions.  Subsequently, the probability of the portfolio having a higher value one year from today has now been reduced to 80.62% — this is due to the higher variation associated with the forecast.  This is illustrated in the Crystal Ball frequency chart in Figure 3.

Insert Figures 2 and 3 About Here

            The second simulation shows that treating the assumptions as interdependent reduces the likelihood that the portfolio will have more value in one year as compared to the first simulation.  The increased variation of the portfolio value is the reason for this.  This is obviously an unfortunate development, but it does reflect a more realistic scenario, which will essentially provide decision-makers with a better ability to model more complex scenarios.  It is appropriate to note that the difference in these two probabilities is statistically significant when each of the above scenarios are replicated ten times each (F = 1631.07, p < 0.0001), which subsequently suggests the importance of treating the assumptions as interdependent.

Stochastic Linear Programming Example

              Consider the following optimization problem, where it is desired to maximize profit via deciding the quantities to produce of two different items.  Relevant data is provided in Table 2.

Insert Table 2 About Here

This can be easily formulated into a linear programming model when x₁ is used to represent the quantity to produce of item 1, and x₂ is used to represent the quantity to produce of item 2.  The formulation is as follows:

              The optimal solution to this problem is obtained when x₁ = 350 units, and x₂ = 900 units, for a maximized profit of $6,950.  The major assumptions with this solution, however, are that the unit profit and unit labor requirements are known with complete certainty, along with the assumption that all stochastic variables are independent of each other.  This in reality, however, is rarely the case.  These unit quantities, or coefficients, can and will exhibit variation, and the assumptions have some degree of relationship to each other.  Consider Table 3, showing the modification of these coefficients reflecting the more realistic stochastic behavior, and Table 4 showing correlations between the assumptions.

Insert Tables 3 and 4 About Here

              Determining a feasible combination of x₁ and x₂ so as to optimize the profit now becomes more difficult given that the coefficients exhibit variability, along with their interrelationships.  This complicating issue is treated via a simulation of the coefficient values and examination of the expected value of the objective function for each feasible solution.  The spreadsheet in Figure 4 shows this Crystal Ball formulation:

Insert Figure 4 About Here

Since the assumptions (along with their interrelationships), forecasts and decision variables have been defined, the Crystal Ball optimizer, OptQuest is ready to explore the combinations of the decision variables that would maximize profit.  A caveat to this problem is that what are typically considered constraints (the Assembly and Finishing labor) must be treated as forecasts, given the fact that their “left hand sides” are dependent upon the assumptions defined above.  This is handled in OptQuest by placing an upper bound on the available assembly and finishing hours.  Figure 5 shows this.

Insert Figure 5 About Here

The simulation shows that values of x₁ = 312 and x₂ = 925 result in the maximized expected profit of $6819.49, while preserving feasibility in terms of the assembly and finishing availability.  Note that this solution is different from the one above when all coefficients were treated as uncorrelated, deterministic entities.  Of particular interest is the fact that when coefficients are treated stochastically, the maximum profit is reduced from $6950 to $6819.49.  This is due to the fact that the stochastic treatment of coefficients essentially serves as implicit constraints — placing additional restrictions on the problem.  While this is unfortunate, it does reflect a more realistic management scenario, which subsequently has value.  Figure 6 shows the Crystal Ball/OptQuest output for this stochastic linear programming problem.

Insert Figure 6 About Here

SPREADSHEET SIMULATION IN PERSPECTIVE FOR MBA-LEVEL STUDENTS

              In the author’s experience, teaching spreadsheet simulation to MBA students, especially working professional students (executive and evening students, etc.) typically receives a surprise reaction from the students.  First, the students are surprised to learn that such a tool exists.  Second, the students are enthusiastic about a spreadsheet’s ability to treat cells as something other than deterministic.  Their justification for this enthusiasm is that they can actually act on their a priori understanding that some spreadsheet entries should be treated as entities exhibiting variation.  Once they have a grasp of this ability, the practical ramifications present themselves in the form of these types of questions:

·        Do we know these objective function coefficients with certainty?

·        Are we certain that monthly demand is 200 units?  Could it be more? Could it be less?

·        What if lead time is more than the two weeks that we typically budget?

·        How many units should be produced when we’re uncertain of demand?

·        Are these assumptions that we’ve made correlated to each other?  If so, can we address this?

All of the above types of questions have been asked in classes that the author has taught, and in all cases, these situations can be addressed via the features of the spreadsheet simulation software — specifically via the correlation matrix and risk optimization features of the chosen software (either @Risk or Crystal Ball).

            MBA students, especially working professional students, are usually competent with spreadsheets and their features (such as linear regression and cash-flow functionality).  Nevertheless, there is almost always a reaction of pleasant surprise from them when they learn of their ability to model stochastic data.  This surprise reaction is further justification for this note — it is important to “trumpet” the availability of these tools to practicing professionals.  With the two simple extensions of spreadsheet simulation presented here, even more “real-world” functionality can be added to their quantitative analysis experience.

References

Albright, S.C., Winston, W.L. & Zappe, C. (2003).  “Data Analysis and Decision Making with Microsoft Excel, 2^nd Edition.  Duxbury:  Boston, Massachusetts.

Albritton, M.D., McMullen, P.R. & Gardiner, L.R. (2003).  “OR/MS Content and Visability in AACSB-Accredited US Business Programs.”  Interfaces, 33(5), 83-89.

Powell, S.G. & Baker, K.R. (2004).  The Art of Modeling with Spreadsheets. Wiley:  New Jersey.

Ragsdale, C.T. (2004).  Spreadsheet Modeling & Decision Analysis, 4^th Edition.  South-Western:  Mason, Ohio.

Winston, W.L. (2004) Operations Research, Volume Two:  Probability Models.  Thompson:  Belmont, California.

Table 1.  Data for Stock Price Example

Table 2.  Deterministic Problem Data

Table 3.  Stochastic Problem Data

Table 4.  Correlations between Assumptions for Stochastic Optimization Problem

Figure 1.  Result of Simulation without Correlation

Figure 4.  Spreadsheet Formulation of Stochastic Linear Programming Problem

Figure 5.  Inputting of Objective Function and Constraints as Forecasts

Figure 6.  OptQuest Output of Stochastic Optimization Problem