Transcript Introduction to MIS Chapter 8

Introduction to MIS
Chapter 8
Models and Decision Support
Copyright © 1998-2002 by Jerry Post
Introduction to MIS
1
Models
Strategy
Decision
100
80
60
40
20
0
1st Qtr
2nd Qtr
Actual
3rd Qtr
4th Qtr
Forecast
Output
1
f ( x) 
 2
 1  x    2 
 exp 2    
Model
Tactics
Data
Operations
Company
Introduction to MIS
2
Outline











Biases in Decisions
Introduction to Models
Why Build Models?
Decision Support Systems: Database, Model, Output
Data Warehouse
Data Mining and Analytical Processing
Digital Dashboard and EIS
DSS Examples
Geographical Information Systems
Cases: Computer Hardware Industry
Appendix: Forecasting
Introduction to MIS
3
Choose a Stock
Stock Price
130
125
120
115
CompanyA
110
CompanyB
105
100
95
90
1
2
3
4
5
6
7
8
9
10 11 12
Month
Company A’s share price increased by 2% per month.
Company B’s share price was flat for 5 months and then increased by
3% per month.
Which company would you invest in?
Introduction to MIS
5

Acquisition/Input






Data availability
Selective perception
Frequency
Concrete information
Illusory correlation
Human Biases




Processing














Inconsistency
Conservatism
Non-linear extrapolation
Heuristics: Rules of thumb
Anchoring and adjustment
Representativeness
Sample size
Justifiability
Regression bias
Best guess strategies
Complexity
Emotional stress
Social pressure
Redundancy
Introduction to MIS
Output


Question format
Scale effects
Wishful thinking
Illusion of control
Feedback





Learning on irrelevancies
Misperception of chance
Success/failure attribution
Logical fallacies in recall
Hindsight bias
6
File: C08Fig08.xls





Understanding the Process
Optimization
Prediction
Simulation or "What If"
Scenarios
Dangers Goal or output
Why Build Models?
Optimization
Maximum
variables
25
Output
20
Model: defined
by the data points
or equation
15
10
5
5
3
0
1 2 3
4
5
Input Levels
6
7
8
1
9 10
Control variables
Introduction to MIS
7
File: C08Fig09.xls
Prediction
25
20
Economic/
regression
Forecast
Output
15
10
5
Moving Average
Trend/Forecast
0
Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1 Q2
Time/quarters
Introduction to MIS
8
File: C08Fig10.xls
Simulation
Goal or output
variables
25
Output
20
15
Results from altering
internal rules
10
5
0
1
2
3
4
5
6
7
8
9 10
Input Levels
Introduction to MIS
9
Object-Oriented Simulation Models
Custom Manufacturing
purchase
order
routing
& scheduling
purchase
order
Customer
Order Entry
Invoice
Parts
List
Production
Shipping
Schedule
Shipping
Inventory
Introduction to MIS
10
File: C08Fig11.xls
DSS: Decision Support Systems
Sales and Revenue 1994
300
Model
250
Legend
200
150
sales
154
163
161
173
143
181
revenue profit
204.5 45.32
217.8 53.24
220.4 57.17
268.3 61.93
195.2 32.38
294.7 83.19
prior
35.72
37.23
32.78
47.68
41.25
67.52
Sales
Revenue
Profit
Prior
100
50
0
Jan
Feb
Mar
Apr
May
Jun
Output
Database
Introduction to MIS
11
http://www.spotfire.com
Introduction to MIS
Data Mining: Spotfire
12
Data Warehouse
Predefined
reports
Interactive
data analysis
Operations
data
Daily data
transfer
OLTP Database
3NF tables
Data warehouse
Star configuration
Flat files
Introduction to MIS
13
Multidimensional OLAP Cube
Pet Store
Item Sales
Amount = Quantity*Sale Price
Customer
Location
Introduction to MIS
14
Microsoft SQL Server Cube Browser
Introduction to MIS
15
Microsoft Pivot Table
Introduction to MIS
16
Digital Dashboard
Stock market
Equipment details
Exceptions
Quality control
Plant or
management variables
Products
Plant schedule
http://www.microsoft.com/business/casestudies/dd/honeywell.asp
Introduction to MIS
17
Executive IS
Sales
Production Costs
Distribution Costs
Fixed Costs
Executives
Production Costs
South
North
Overseas
5000
4500
4000
3500
3000
2500
2000
1500
1000
500
0
Central Management
South
North
Overseas
1993
1994
1995
1996
Production: North
Data
Data
Sales
Data
Distribution
Introduction to MIS
Data
Item#
1995
1994
1234
2938
7319
542.1
631.3
753.1
442.3
153.5
623.8
Production
19
Marketing Research Data
Internal






Sales
Warranty cards
Customer service lines
Coupons
Surveys
Focus groups
Introduction to MIS
Purchase






Scanner data
Competitive market analysis
Mailing & phone lists
Subscriber lists
Rating services (e.g., Arbitron)
Shipping, especially foreign
Government


Census
Income
Demographics
Regional data
Legal registration
Drivers license
Marriage
Housing/construction
20
File: C08-10 Marketing Forecast.xls
Marketing Sales Forecast
GDP and Sales
2800
2600
100
GDP
90
Sales
2400
Forecast
80
2000
70
1800
60
Sales
GDP
2200
1600
50
1400
40
1200
30
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
1000
Quarter
forecast
Note the fourth quarter sales jump.
The forecast should pick up this cycle.
Introduction to MIS
21
Regression Forecasting
Data:
Quarterly sales and GDP for 10 years.
Model:
Sales = b0 + b1 Time + b2 GDP
Analysis:
Estimate model coefficients with regression.
Intercept
Time
GDP
Coefficients Standard Error
-98.175
15.895
-1.653
0.304
0.102
0.012
t Stat
-6.176
-5.444
8.507
Forecast GDP for each quarter.
Output:
Compute Sales prediction.
Graph forecast.
Introduction to MIS
22
File: C08-19 HRM.xls
Human Resources
Name
Caulkins
Jihong
Louganis
Naber
Spitz
Weissmuller
Department
Corporate
Performance
r1
r2
r3
9
7
6
3
6
7
8
7
7
9
8
8
3
4
3
5
4
6
6
6
6
5
6
5
Introduction to MIS
Merit Pay
Raise pool
Pct
Perf
73%
53%
73%
83%
33%
50%
Current
Salary
35.8
17.9
29.5
19.8
17.5
53.2
21.7
18.9
Salary range ($000)
High
Low
Avg
37.5
28.4
36.4
18.9
15.4
16.3
30.2
26.7
28.9
23.2
19.5
21.4
22.4
17.3
18.4
60.4
32.5
45.2
32.1
22.2
21.9
124
9.2
18.9
$10000
Merit
$100
733
533
733
833
333
500
3665
Market
Adjust.
Total
Raise
733
533
733
833
333
500
3665
Raise%
2.0%
3.0%
2.5%
4.2%
1.9%
0.9%
2.4%
23
Human Resources
dollars
Raises
4000
3500
3000
2500
2000
1500
1000
500
0
90.0%
80.0%
70.0%
60.0%
50.0%
40.0%
30.0%
20.0%
10.0%
0.0%
Caulkins
Jihong
Raise
Introduction to MIS
Louganis
Naber
Raise pct
Spitz
Weissmuller
Performance
24
File: C08-14 Finance NPV.xls
Finance Example: Project NPV
Rate = 7%
P r oj e c t C N P V = - $ 3 , 8 14
P r oj e c t A N P V=$ 18 , 4 7 5
100, 000
60, 000
50, 000
40, 000
20, 000
0
0
-50, 000
1
2
3
4
5
6
0
Cost s-A
-20, 000
0
1
2
3
Revenue-A
-100, 000
4
5
6
Cost s-C
Revenue-C
-40, 000
-150, 000
-60, 000
-200, 000
-80, 000
-250, 000
-100, 000
Y ear
Y ear
P r oj e c t B N P V=$ 6 , 0 6 4
80, 000
60, 000
40, 000
20, 000
0
-20, 000
Cost s-B
0
1
2
3
4
-40, 000
5
6
Can you look at these cost and
revenue flows and tell if the
project should be accepted?
Revenue-B
-60, 000
-80, 000
-100, 000
-120, 000
Y ear
Introduction to MIS
25
File: C08-15 Accounting.xls
Accounting
Balance Sheet for 2003
Cash
Receivables
Inventories
Total Current Assets
Net Fixed Assets
Total Assets
Introduction to MIS
33,562
87,341
15,983
136,886
Accounts Payable
Notes Payable
Accruals
Total Current Liabilities
45,673
182,559
Bonds
Common Stock
Ret. Earnings
Liabs. + Equity
32,872
54,327
11,764
98,963
14,982
57,864
10,750
182,559
26
Accounting
Income Statement for 2003
Sales
$97,655
Operating Costs
76,530
Earnings before interest & tax 21,125
Interest
Earnings before tax
taxes
Net Income
40%
60%
9763
4,053
17,072
6,829
10,243
Dividends
Add. to Retained Earnings
6,146
4,097
Earnings per share
$0.42
Introduction to MIS
tax rate
dividends
shares out.
27
Accounting Analysis
Balance Sheet projected 2004
Income Statement projected 2004
Cash
$36,918
Acts Receivable
96,075
Inventories
17,581
2
Accts Payable
Notes Payabale
Accruals
$36,159
54,327
12,940
Total Cur. Assets 150,576
Total Cur. Liabs. 103,427
Net Fixed Assets
Bonds
Common Stock
Ret. Earnings
14,982
57,864
14,915
Liabs + Equity
191,188
Total Assets
45,673
$196,248
Sales
Operating Costs
2
5
3
$ 107,421
84,183
Earn. before int. & tax
Interest
23,238
4,306
Earn. before tax
taxes
18,931
8,519
Net Income
10,412
Dividends
Add. Funds Need
6,274
5,060
Add. to Ret. Earnings
Bond int. rate
5%
Added interest
4
Earnings per share
$ 4,165
$0.43
253
1
Forecast sales and costs.
2
Forecast cash, accts receivable, accts payable, accruals.
3
Add gain in retained earnings.
4
Compute funds needed and interest cost.
5
Add new interest to income statement.
Introduction to MIS
1
Tax rate
Dividend rate
Shares outstanding
45%
60%
9763
Sales increase
Operations cost increase
10%
10%
Results in a CIRCular calculation.
28
File: C08-25 GIS.xls
Geographic Models
City
Clewiston
Fort Myers
Gainesville
Jacksonville
Miami
Ocala
Orlando
Perry
Tallahassee
Tampa
1990 pop
2000 pop 1990 per capita
2000 income
per capita
1990 income
soft sales
1990 hard sales
2000 hard sales
2000 soft sa
6085
8549
13598
15466
562.5
452.0
367.6
525.4
45206
59491
16890
20256
652.9
535.2
928.2
1010.3
84770
101724
13672
19428
281.7
365.2
550.5
459.4
635230
734961
15316
19275
849.1
990.2
1321.7
1109.3
258548
300691
16874
18812
833.4
721.7
967.1
1280.6
42045
55878
12027
15130
321.7
359.0
486.2
407.3
164693
217889
16958
20729
509.2
425.7
691.5
803.5
7151
8045
11055
14144
267.2
300.1
452.9
291.0
124773
155218
14578
20185
489.7
595.4
843.8
611.7
280015
335458
15081
19062
851.0
767.4
953.4
1009.1
Introduction to MIS
29
R ed
Ye llow
R ed
3.2
2.3
5.0
4.2
2.3
1.9
3.7
3.2
Green
Tallahassee
Blu e
R ed
Ye llow
Green
2.1
1.7
1.4
1.1
Green
R ed
Blu e
Ye llow
R ed
2000
Soft
Goods
1990
Hard
Goods
1990
Soft
Goods
1.7
1.1
1.1
1.0
Green
Blu e
Jacksonville
Perry
2000
Hard
Goods
Ye llow
Gainesville
Ye llow
1.8
1.5
1.4
1.2
Blu e
Ocala
Green
Blu e
R ed
Orlando
R ed
2.6
3.0
1.6
1.9
Ye llow
3.6
3.8
2.9
3.2
Green
Green
Tampa
20,700
19,400
14,600
18,100
R ed
R ed
16,800
12,200-
15,500-
1990
2000
Ye llow
3.5
3.8
2.0
2.5
Green
13,400
Blu e
Blu e
per capita income
17,000
15,800
Ye llow
Fort Myers
Clewiston
Ye llow
1.4
2.0
1.7
2.1
Green
Blu e
Miami
Blu e
R ed
3.7
4.8
2.7
3.2
Green
Introduction to MIS
Ye llow
Blu e
30
Cases: Computer Hardware Industry
Introduction to MIS
31
Cases: Dell Computer
Gateway 2000, Inc.
www.dell.com
www.gateway.com
What is the company’s current status?
What is the Internet strategy?
How does the company use information technology?
What are the prospects for the industry?
Introduction to MIS
32
Appendix: Forecasting Uses

Marketing




Future sales
Consumer
preferences/trends
Sales strategies
Finance



Interest rates
Cash flows
Financial market conditions
Introduction to MIS

HRM




Labor costs
Absenteeism
Turnover
Strategy



Rivals’ actions
Technological change
Market conditions
33
Forecasting Methods

Structural Models





Derive underlying models
Estimate parameters
Evaluate model
Focus on explanation and
cause
Time Series




Collect data over time
Identify trends
Identify seasonal effects
Forecast based on patterns
sales
P
trend
S
D’
D
Q
time
Increase in income
Introduction to MIS
34
Structural Equations

Demand is a function of



Price
Income
Prices of related products
Model
QD = b0 + b1 Price + b2 Income + b3 Substitute
Time
Quantity Price
Income
Substitute
1
24926
134
20000
155
Data
2
26112
150
21000
155
3
27313
142
22000
135
4
26143
141
21000
150
5
26741
144
21500
150
Estimate
QD = 11146- 0.1 26149
Price + 1.2 Income
- 21000
1.0 Substitute
137
155
7
27893
140
22500
143
Forecast
33318 = 1114
0.1
(155)
+
1.2
(20000)
1.0
(160)
8
26397
142
21200
153
9
24895
147
20000
155
Need to know (estimate) future price, income, and substitute price.
10
28501
148
23000
160
11
29747
150
24000
165
12
29175
134
23500
15335
Introduction to MIS
Time Series Components
sales
Seasonal
Trend
time
Dec
1. Trend
2. Seasonal
3. Cycle
4. Random
Introduction to MIS
Dec
Dec
Dec
A cycle is similar to the seasonal pattern,
but covers a time period longer than a year.
36
Exponential Smoothing
Exponential Smoothing
1600
1500
1400
1300
1200
1100
1000
900
800
Raw Data
Smooth:0.20
1
3
5
7
9
11 13 15 17 19 21
St = Yt + (1 - ) St-1
S is the new data point
 is the smoothing factor
Introduction to MIS
Use Excel:
Tools, Data Analysis
Exponential Smoothing
37
4
5
6
7
8
Time
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Introduction to MIS
26157
26710
20800
32000
26327
Sales
22500
28524
34600
29136
26000
30306
32133
36200
38300
35700
33355
29800
26145.27
26656.9
27168.52
27680.15
28191.78
Forecast
28703.41
=$F$20+$F$21*B6
29215.04
29726.66
30238.29
30749.92
31261.55
Coefficients
31773.18 Intercept
24098.7579
32284.8 Time
511.62782
32796.43
33308.06
33819.69
Tools + Data Analysis + Regression
34331.31
Dependent = Sales
34842.94
35354.57
Independent = Time
35866.2
36377.83
36889.45
Regression Analysis
42