Analytics, Data Science and A I: Systems for Decision Support Eleventh Edition

(Chapter 8): Excel is probably the most popular spreadsheet software for PCs. Why? What can we do with this package that makes it so attractive for modeling efforts?

(Chapter 9): What are the common business problems addressed by Big Data analytics? In the era of Big Data, are we about to witness the end of data warehousing? Why?

Chapter 8

Prescriptive Analytics: Optimization

and Simulation

Slide in this Presentation Contain Hyperlinks.

JAWS users should be able to get a list of links

by using INSERT+F77

Analytics Overview

Model Categories

• Static versus dynamic models

– Time dependent / time independent

• Model management

• Knowledge-based modeling

• Current trends in modeling

– Model libraries

– Cloud-based modeling tools/platforms

– Model transparency / multi-dimensional modeling

– Influence diagrams for better modeling

Categories of Models

Table 8.1 Categories of Models.

Category Process and Objective Representative Techniques

Optimization of problems

with few alternatives

Find the best solution from a small

number of alternatives

Decision tables, decision trees,

analytic hierarchy process

Optimization via algorithm Find the best solution from a large

number of alternatives, using a

step-by-step improvement process

Linear and other mathematical

programming models, network

models

Optimization via an

analytic formula

Find the best solution in one step,

using a formula

Some inventory models

Simulation Find a good enough solution or the

best among the alternatives

checked, using experimentation

Several types of simulation

Heuristics Find a good enough solution, using

rules

Heuristic programming, expert

systems

Predictive models Predict the future for a given

scenario

Forecasting models, Markov

analysis

Other models Solve a what-if case, using a

formula

Financial modeling, waiting

lines

Structure of Mathematical Models for

Decision Support

• Quantitative Models: Mathematically links decision

variables, uncontrollable variables, and result variables

– Non-quantitative models: qualitative models

• Result (outcome variable)

• Decision variables

• Uncontrollable variable (or parameters)

• Intermediate results

Structure of Mathematical Models for

Decision Support

Examples of Components of Models

Table 8.2 Examples of Components of Models.

Area Decision Variables Result Variables Uncontrollable Variables

and Parameters

Financial

investment

Investment alternatives

and amounts

Total profit, risk Rate of

return on investment

(R O I) Earnings per

share Liquidity level

Inflation rate Prime rate

Competition

Marketing Advertising budget

Where to advertise

Market share

Customer satisfaction

Customer’s income

Competitor’s actions

Manufacturing What and how much to

produce Inventory levels

Compensation programs

Total cost Quality level

Employee satisfaction

Machine capacity

Technology Materials

prices

Accounting Use of computers

Audit schedule

Data processing cost

Error rate

Computer technology

Tax rates Legal

requirements

Transportation Shipments schedule use

of smart cards

Total transport cost

Payment float time

Delivery distance

Regulations

Services Staffing levels Customer satisfaction Demand for services

Modeling and Decision Making –

Under Certainty, Uncertainty, & Risk

• Certainty

– Assume complete knowledge

– All potential outcomes are known

• Uncertainty

– Several outcomes for each decision

– Probability of each outcome is unknown

• Risk analysis (probabilistic decision making)

– Probability of each outcomes is known

– Level of uncertainty → Risk (expected value)

Modeling and Decision Making –

Under Certainty, Uncertainty, & Risk

The Zones of Decision Making

Decision Modeling with

Spreadsheets

• Spreadsheet

– Most popular end-user modeling tool

– Flexible and easy to use

– Powerful functions (add-in functions)

– Programmability (via macros)

– What-if analysis and goal seeking

– Simple database management

– Seamless integration of model and data

– Incorporates both static and dynamic models

– Examples: Microsoft Excel (with Solver add-in)

Excel Spreadsheet – Static Model

Example

(Simple loan calculation of monthly payments)

(1 )

(1 ) 1

F P i

i i A P

= +

 + =  

+ − 

Excel Spreadsheet – Dynamic Model

Example

(Simple loan calculation – effect of prepayment)

Mathematical Programming

Optimization

• Mathematical Programming

A family of tools designed to help solve managerial

problems in which the decision maker must allocate

scarce resources among competing activities to

optimize a measurable goal

• Optimal solution: The best possible solution to a modeled

problem

– Linear programming (L P): A mathematical model for

the optimal solution of resource allocation problems.

All the relationships are linear.

Linear Programming Problem

Characteristics

1. Limited quantity of economic resources

2. Resources are used in the production of products or

services

3. Two or more ways (solutions, programs) to use the

resources

4. Each activity (product or service) yields a return in terms

of the goal

5. Allocation is usually restricted by constraints

Linear Programming (L P) Steps

1. Identify the …

– Decision variables

– Objective function

– Objective function coefficients

– Constraints

▪ Capacities / Demands / …

2. Represent the model

– L I N D O: Write mathematical formulation

– E X C E L: Input data into specific cells in Excel

3. Run the model and observe the results

L P Modeling – Example

The Product-Mix Linear Programming Model

• M B I Corporation

• Decision variable: How many computers to build next month?

• Two types of mainframe computers: C C-7 and C C-8

• Constraints: Labor limits, Materials limit, Marketing lower limits

CC-7 CC-8 Rel Limit

Labor (days) 300 500 = 100

Units 1 >= 200

Profit ($) 8,000 12,000 Max

Objective: Maximize Total Profit / Month

Common Optimization Models

• Assignment (best matching of objects)

• Dynamic programming

• Goal programming

• Investment (maximizing rate of return)

• Linear and integer programming

• Network models for planning and scheduling

• Nonlinear programming

• Replacement (capital budgeting)

• Simple inventory models (e.g., economic order quantity)

• Transportation (minimize cost of shipments)

Multiple Goals, Sensitivity Analysis,

What-If Analysis, and Goal Seeking

• Multiple Goals

– Simple-goal vs. multiple goals

– Vast majority of managerial problems has multiple

goals (objectives) to achieve

▪ Attaining simultaneous goals

– Methods of handling multiple goals

▪ Utility theory

▪ Goal programming

▪ Expression of goals as constraints, using L P

▪ A points system

Multiple Goals, Sensitivity Analysis,

What-If Analysis, and Goal Seeking

• Certain difficulties may arise when analyzing multiple

goals

– Difficult to obtain a single organizational goal

– The importance of goals change over time

– Goals and sub-goals are viewed differently

– Goals change in response to other changes

– Dynamics of groups of decision makers

– Assessing the importance (priorities)

– …

Multiple Goals, Sensitivity Analysis,

What-If Analysis, and Goal Seeking

• Sensitivity analysis

– It is the process of assessing the impact of change in

inputs on outputs

– Helps to …

▪ eliminate (or reduce) variables

▪ revise models to eliminate too-large sensitivities

▪ adding details about sensitive variables or scenarios

▪ obtain better estimates of sensitive variables

▪ alter a real-world system to reduce sensitivities

▪ …

– Can be automatic or “trial and error”

Multiple Goals, Sensitivity Analysis,

What-If Analysis, and Goal Seeking

• What-if analysis

– Assesses solutions based on changes in variables or

assumptions (scenario analysis)

– What if we change our capacity at the milling station by

40% [what would be the impact]

• Goal seeking

– Backwards approach, starts with the goal and determines

values of inputs needed

– Example is break-even point determination

▪ In-order to break even (profit = 0), how many products

do we have to sell each month

Decision Analysis with Decision

Tables and Decision Trees

• Decision Tables – a tabular representation of the decision

situation (alternatives)

• Investment Example

– Goal: maximize the yield after one year

– Yield depends on the status of the economy (the state

of nature)

▪ Solid growth

▪ Stagnation

▪ Inflation

Decision Table: Treating Uncertainty

• Optimistic approach

• Pessimistic approach

• Treating Risk/Uncertainty:

– Use known probabilities

– Expected values

Major Characteristics of Simulation

• Simulation: appearance of reality

• Descriptive versus Prescriptive

• Major Characteristics

– Imitates reality and captures its richness both in shape

and behavior

– “Represent” versus “Imitate”

– Technique for conducting experiments

– Descriptive, not normative tool

– Often to “solve” [i.e., analyze] very complex

systems/problems

– Simulation should be used only when a numerical

optimization is not possible

Advantages of Simulation

• The theory is fairly straightforward

• Great deal of time compression

• Experiment with different alternatives

• The model reflects manager’s perspective

• Can handle wide variety of problem types

• Can include the real complexities of problems

• Produces important performance measures

• …

Disadvantages of Simulation

• Cannot guarantee an optimal solution

• Slow and costly construction process

• Cannot transfer solutions and inferences to solve other

problems (problem specific)

• So easy to explain/sell to managers, may lead to

overlooking analytical solutions

• Software may require special skills

Simulation Methodology

Steps:

1. Define problem

2. Construct the model

3. Test and validate model

4. Design experiments

5. Conduct experiments

6. Evaluate results

7. Implement solution

Simulation Types

• Probabilistic/Stochastic vs. Deterministic Simulation

– Uses probability distributions

• Time-dependent vs. Time-independent Simulation

– Monte Carlo technique (X = A + B)

[A, B, and X are all distributions]

• Discrete Event vs. Continuous Simulation

• Simulation Implementation

– Visual Simulation and/or Object-Oriented Simulation

Visual Interactive Simulation (V I S)

• Visual interactive modeling (V I M), also called Visual

Interactive Simulation or Visual interactive problem solving

• Uses computer graphics to present the impact of different

management decisions

• Often integrated with 3G and G I S

• Users can perform sensitivity analysis

• Static or dynamic (animation) systems

• Virtual reality, immersive, …

Simulation Software

• Stand-alone desktop simulation tools

• Web-based simulation tools

• See O R/M S Today for software reviews

• Examples:

– Simio

– Arena

– ExtendSim

– S A S Simulation Studio

– …

The post Analytics, Data Science and A I: Systems for Decision Support Eleventh Edition appeared first on Versed Writers.