Transcript Decision Support Systems - California Polytechnic State

Decision Support Systems
Management Information Systems
BUS 391
Barry Floyd
Agenda
Excel Examples
Definition
Fundamentals
Conclusion
Tax Computation
Given certain assumptions, earnings,
and savings goals, how much should
John and Sue pay in estimated
quarterly taxes?
Tax Computation
Save 5% of total income in a tax deductible
retirement account, up to a maximum of
$3,000
Entitled to personal exemption of $3,100
each
Standard deduction for joint tax filers is
$8,000
Tax brackets 15% for up to $48,000 and 26%
for $48,001 to $115,000
How much estimated taxes should they pay
each quarter?
Parameters
Problem Parameters
Retirement Saviings
Maximum savings
Personal Examption per person
Standard Deduction
Tax brackets
0.05
3000
3100
8000
0.15 up to
0.26 up to
48000
115000
Input area
Input Data
David's Income
Sue's Income
45000
40000
Tax Computation
Tax Computation
Total Income
Retirement Savings
Personal Exemptions
Standard Deduction
Taxable Income
Tax @ 15% rate
Tax @ 26% rate
Total Tax
Estimated Tax per quarter
85000
3000
6200
8000
67800
7200
5148
12348
3087
Breakeven Analysis
Determine Total Revenue
Fixed Cost
Total Variable Cost
Total Cost
Profit
Known Parameters
Know Parameters
Selling price per unit
Fixed cost
Variable cost per unit
$10.00
$1,000.00
$5.00
Input
Input Data
Number of units, X
2200
Results
Results
Total revenue
Fixed cost
Total variable cost
total cost
Profit
$22,000.00
$1,000.00
$11,000.00
$12,000.00
$10,000.00
What is a DSS?
An interactive information system that
provides information, models, and data
manipulation tools to help make
decisions in semi-structured and
unstructured situations where no one
know exactly how the decision should
be made.
Steps involved
in Decision Modeling
Formulation



Defining the problem
Developing a model
Acquiring Input data
Solution


Developing a solution
Testing the solution
Interpretation


Analyzing the results and sensitivity analysis
Implementing the results
Steps involved
in Decision Modeling
• Formulation
– Defining the problem
– Developing a model
– Acquiring Input data
• Solution
– Developing a solution
– Testing the solution
• Interpretation
Develop a clear,
concise statement of
the problem. Go
beyond symptoms,
look for cause! Find
measurable
objectives.
– Analyzing the results and sensitivity analysis
– Implementing the results
Steps involved
in Decision Modeling
• Formulation
– Defining the problem
– Developing a model
– Acquiring Input data
Develop a model …
for decisions
modeling, this is a
• Solution
mathematical model.
– Developing a solution
Decision variable is
controllable, a
– Testing the solution
parameter is an
• Interpretation
inherent measurable
– Analyzing the results and sensitivity analysis
quantity.
– Implementing the results
Steps involved
in Decision Modeling
• Formulation
– Defining the problem
– Developing a model
– Acquiring Input data
• Solution
– Developing a solution
– Testing the solution
• Interpretation
Get data from
reports or interviews
or sampling, etc.
(e.g., time to
manufacture a
widget).
– Analyzing the results and sensitivity analysis
– Implementing the results
Steps involved
in Decision Modeling
• Formulation
– Defining the problem
– Developing a model
– Acquiring Input data
• Solution
Manipulate model to
arrive at the best (or
optimal) solution to
the problem.
– Developing a solution
– Testing the solution
• Interpretation
– Analyzing the results and sensitivity analysis
– Implementing the results
Steps involved
in Decision Modeling
• Formulation
– Defining the problem
– Developing a model
– Acquiring Input data
• Solution
Test completely. Use
known data,
comparison data,
etc.
– Developing a solution
– Testing the solution
• Interpretation
– Analyzing the results and sensitivity analysis
– Implementing the results
Steps involved
in Decision Modeling
• Formulation
– Defining the problem
– Developing a model
– Acquiring Input data
• Solution
– Developing a solution
– Testing the solution
Determine
implications of
solution. What
happens if results
are implemented?
How sensitive is the
solution to
fluctuations?
• Interpretation
– Analyzing the results and sensitivity analysis
– Implementing the results
Steps involved
in Decision Modeling
• Formulation
– Defining the problem
– Developing a model
– Acquiring Input data
Can be the most
difficult part …
• Solution
– Developing a solution
– Testing the solution
• Interpretation
– Analyzing the results and sensitivity analysis
– Implementing the results
Different flavors of DSS
Simulation and optimization
OLAP and Data Mining
Expert Systems
Neural Networks
Fuzzy Logic
Case-based Reasoning
Intelligent Agents
Simulation
and optimization
Simulation – calculates outcomes based
on some abstraction (typically
mathematical) of the situation
Optimization – calculates the ‘best’
answer given certain sets of constraints
(e.g., which set of fixed and variable
costs given a range of potential sales
would provide the most profit).
OLAP
and Data Mining
OLAP – Online analytical processing

Explores large volumes of transaction data
Data Mining

Explores large volumes of data looking for
patterns that help managers understand critical
relationships
 Eg if someone buys cake mix, do they also buy frosting?
 What drives paint sales? Not new home purchases, but
sales of existing homes.
Expert Systems
Builds on ‘knowledge’ typically extracted
from an expert (e.g., a medical
specialist on cancer)

Knowledge must be ‘captured’ and
represented within the system
 Typically done with If-Then rules


Data about particular case
Inference engine applies rules to data to
derive an outcome
Neural Networks
Statistical method for finding and
representing patterns in data
Neural represents the way researchers
believe the brain works

A neural network is an information system
that recognizes objects or patterns based
on examples that have been used to train
it.
Fuzzy Logic
Fuzzy logic is a form of reasoning that makes
it possible to combine imprecise conditions
stated in a form similar to the types of
descriptive categories people use.


Don’t use either/or logic, allow categories to be
somewhat vague and potentially overlapping: Very
profitable, somewhat profitable, slightly profitable
categories.
Use multiple rules and build a system that
combines the rules in a meaningful manner.
Case Based Reasoning
A DSS method based on the idea of
finding past cases most similar to the
current situation in which a decision
must be made.


Must maintain a Database of cases and a
means of searching the cases to match the
problem at hand.
Must have a means of ‘categorizing’ cases
and limiting structure to a manageable set.
Intelligent Agents
An autonomous, goal-directed computerized
process that can be launched into a computer
system/network to do background work

Shopbots, email agents, news agents.
 Shopbot – find best price for X
 Email – scan email messages as they arrive and
determine if user should be interrupted
 News Agent – scan news sources to put together a
customized newspaper.
Conclusion
DSS is decided different than TPS and
MIS
We’ll employ Excel as our modeling/DSS
environment
Data Table
and Scenario Management
Barry Floyd
Data Tables
and Scenario Management
Data Table

Displays results of multiple what-if analyses
 One variable Data Table

Specify one input cell and any number of result cells
 Two variable Data Table

Specify two input cells and one result cell
Scenario

Allows you to define a set of input cells and result
cells and to then view the results in a systematic
fashion
One Variable Data Table
One input
cell
One or more
result cells
Number of units Total Revenue Total Cost Profit
2200
$22,000.00 $14,200.00
$7,800.00
2300
$23,000
$14,800
$8,200
2400
$24,000
$15,400
$8,600
2500
$25,000
$16,000
$9,000
2600
$26,000
$16,600
$9,400
One Variable Data Table
Steps





Create row of output formulas
Define column of input values
Highlight formulas and input values
Select data, table
Indicate the 'input' cell - note we have a column of
values and so choose column
Two Variable Tables
Variable 1
values in first
row
-Number of Units
2300
2400
2500
2600
2700
10
$10,500
$11,000
$11,500
$12,000
$12,500
10.5
$11,650
$12,200
$12,750
$13,300
$13,850
Selling
11
$12,800
$13,400
$14,000
$14,600
$15,200
Variable 2
values in first
Column
Price per Unit
11.5
12
$13,950 $15,100
$14,600 $15,800
$15,250 $16,500
$15,900 $17,200
$16,550 $17,900
12.5
$16,250
$17,000
$17,750
$18,500
$19,250
13
$17,400
$18,200
$19,000
$19,800
$20,600
Result values
displayed in
table
Two variable tables
Steps in creating a two variable data table









Create column of variable costs
Create row of number of units
Place "RESULT CELL" reference in the upper left hand
corner
Format cell to show a label rather than the formula
Add a label
Highlight table area
Select data, select table
Assign B4 to row input cell, B9 to column input cell
Format table to currency
Scenario Manager
Used to perform ‘what-if’ analyses given more
than two variables
Identify key variables whose values are
important for characterizing the ‘scene’

High quality
 Fixed costs are higher, variable costs are higher, Selling
price is higher

Low quality
 Fixed costs lower, variable costs are medium, selling
price is low

Mid quality
 Fixed costs mid, variable costs are medium, selling price
is medium
Values
High
$50
Medium
$30
Low
$10
Fixed
Costs
$5000
$2500
$1000
Variable
Costs
$20
$10
$3
Selling
Price
Steps
Select tools, scenarios
Create a scenario
Add values to attributes
Repeat for each scenario
Click on a scenario, click show
Or click on summary
Output results
Scenario Summary
Current Values:
Changing Cells:
Variable_cost_per_unit
Selling_price_per_unit
$B$5
Result Cells:
Profit
High Quality Medium Quality
Low Quality
$5.00
$10.00
$1,000.00
$20.00
$50.00
$5,000.00
$10.00
$30.00
$2,500.00
$3.00
$10.00
$1,000.00
$10,000.00
$61,000.00
$41,500.00
$14,400.00
Summary
Use the power of excel to analyze data
in an interactive format.
Do ‘what-if’ analyses on a one variable,
two variable and multi-variable format.
Very powerful, relatively easy to use.