Chapter 9: Building and Using Decision Models

2007 Pearson Education

Models

A model is an abstraction or representation of a real system, idea, or object. Picture Spreadsheet Mathematical relationships

Types of Decision Models

Descriptive - describe relationships and provide information for evaluation Prescriptive (optimization models) determine an optimal policy, that is, the best course of action that a decision maker should take to maximize or minimize some objective

New Store Financial Analysis Model Spreadsheet

Net Present Value

Measures the worth of a stream of cash flows, taking into account the time value of money. A cash flow of F dollars t time periods in the future is worth F/(1 + i)t dollars today, where i is the discount rate.

Building Decision Models on Spreadsheets

Organize information into a logical framework that will make the spreadsheet easier to develop and reduce errors Sketch a logical design of the spreadsheet Separate the model inputs from the model itself and to reference the input cells in the model formulas

Example

Profit = Revenue - Cost

Revenue depends on the unit price and quantity sold. Total cost depends on the unit cost, quantity produced, and relevant fixed costs.

Profit = (Unit price)(Quantity sold) - Cost Profit = (Unit price)(Quantity sold) - [Fixed cost + (Unit cost)(Quantity produced)]

Profit Model Spreadsheet

Elements in Models

Constant data Uncontrollable inputs, quantities that can change, but cannot be directly controlled by the decision-maker. Controllable inputs, often called decision variables.

Spreadsheet Quality

Improve the design and format of the spreadsheet itself. Improve the process used to develop a spreadsheet. Inspect your results carefully and use appropriate tools available in Excel. Use cell and range names Use the Data Validation tool Inspect and audit formulas

Model Assumptions

All models reflect assumptions used by the modeler. Assumptions simplify models and make them easier to manipulate and solve Assumptions should be as realistic as necessary to make models useful but not overly complex Assumptions should be clearly stated and documented

Optimization Models

Sales = -2.6556 Price + 3016.2 Revenue = Price (-2.6556 Price + 3016.2) = 2.6556(Price)2 + 3016.2 Price Find the best value of price to maximize revenue

Models Involving Uncertainty

Uncontrollable inputs often exhibit random behavior, which must be incorporated into models Specify probability distributions for the appropriate uncontrollable inputs.

Example: assume that demand is normally distributed with a mean of 50,000 and a standard deviation of 10,000 units.

Models that include randomness are called probabilistic, or stochastic, models.

Distribution Fitting

For clues: Examine descriptive statistics Examine histogram

Normal Probability Plot

PHStat Normal Probability Plot

Distribution Fitting With Crystal Ball

Define Assumption button

Crystal Ball Distribution Gallery

Fit Distribution Dialog

Distribution Fitting Comparison Chart

Goodness of Fit Statistics

Model Analysis

Scenario evaluation evaluate specific combinations of model inputs that reflect key model assumptions. Sensitivity analysis (what if analysis) - the process of changing key model inputs to determine their effect on the outputs

Data Tables

Used for sensitivity analyses on variables of a decision model Excel tools One-way data tables Two-way data tables

One Way Data Table

Two Way Data Table

Tornado Chart

Graphically shows the impact that variation in one model input has on some output while holding all other inputs constant

Spider Chart

Shows the sensitivity that percentage changes in inputs have on a model output.

Solving Optimization Models

Analytical solutions closed-form mathematical formulas Algorithm - a systematic procedure that finds a solution to a problem Heuristics - solution procedures that generally find good solutions without guarantees of finding an optimal solution

Practical Issues

Managers may not always need exact optimal solutions

Inexact or limited data used to estimate uncontrollable quantities in models may contain more error than that of a non-optimal solution. The assumptions used in a model make it an inaccurate representation of the real problem, making having the "best" solution pointless. Managers may not need the very best solution; anything better than the present one will often suffice, so long as it can be obtained at a reasonable cost and in a reasonable amount of time.

Excel Solver

Maximize Revenue = -2.6556(Price)2 + 3016.2 Price Solver Model

Spreadsheet Model

Solution:
Price = $567.89 Revenue = $856,441.34