26922810 excel and excel qm examples
DESCRIPTION
excelTRANSCRIPT
Program Name Source Content
1.3 Pritchett Clock Repair Shop Excel QM Breakeven Analysis
1.4 Pritchett Clock Repair Shop Excel QM Goal Seek
2.1 Expected Value and Variance Excel Expected Value and Variance
2.2 Binomial Probabilities Excel Binomial Probabilities
3.1 Thompson Lumber Excel QM Decision Table
3.5 Bayes Theorem for Thompson Lumber Example Excel Bayes Theorem
4.1 Triple A Construction Company Sales Excel QM Regression
4.2 Jenny Wilson Realty Excel QM Multiple Regression
4.3 Jenny Wilson Realty Excel QM Dummy Variables - Regression
4.4 MPG Data Excel QM Linear Regression
4.5 MPG Data Excel QM Nonlinear Regression
4.6 Solved Problem 4-2 Excel Regression
5.1 Wallace Garden Supply Shed Sales Excel QM Weighted Moving Average
5.2 Port of Baltimore Excel QM Exponential Smoothing
5.3 Midwestern Manufacturing's Demand Excel Trend Analysis
5.4 Midwestern Manufacturing's Demand Excel QM Trend Analysis
5.6 Turner Industries Excel Regression
6.1 Sumco Pump Company Excel QM EOQ Model
6.2 Brown Manufacturing Excel QM Production Run Model
6.3 Brass Department Store Excel QM Quantity Discount Model
7.2 Flair Furniture Excel Linear Programming
7.4 Holiday Meal Turkey Ranch Excel Linear Programming
7.6 High note sound company Excel Linear Programming
8.1 Win Big Gambling Club Excel Linear Programming
8.3 Fifth Avenue Industries Excel Linear Programming
8.5 Top Speed Bicycle Company Excel Linear Programming
8.6 Goodman Shipping Excel Linear Programming
9.1 High note sound company Excel Linear Programming
9.2 Manufacturing Example Excel Linear Programming
10.1 Executive Furniture Company Excel QM Transportation
10.2 Birmingham Plant Excel QM Transportation
10.3 Fix-It Shop Assignment Excel QM Assignment
11.2 Harrison Electric IP Analysis Excel Integer programming
11.4 Bagwell Chemical Company Excel Integer programming
11.5 Simkin, Simkin and Steinberg Excel Integer programming
11.7 Great Western Appliance Excel Nonlinear programming
11.8 Hospicare Corp Excel Nonlinear programming
11.9 Thermlock Gaskets Excel Nonlinear programming
11.10 Solved Problem 11-1 Excel 0-1 programming
13.1 Crashing General Foundry Problem Excel Crashing
14.1 Arnold's Muffler Shop Excel QM Single Server (M/M/1) system
14.2 Arnold's Muffler Shop Excel QM Multi-Server (M/M/m) system
14.3 Golding Recycling, Inc. Excel QM Constant Service Rate (M/D/1)
14.4 Department of Commerce Excel QM Finite population queue
15.2 Harry's Tire Shop Excel Simulation (inventory)
15.3 Generating Normal Random Numbers Excel Random #s and Frequency
15.4 Port of New Orleans Barge Unloadings Excel Simulation (waiting line)
15.5 Three Hills Power Company Excel Maintenance Simulation
16.4 Three Grocery Example Excel Markov Analysis
16.5 Accounts Receivable Example Excel Fundamental Matrix & Absorbing States
17.1 ARCO Excel p-Chart Analysis
Module
M1.1 AHP Excel
M5.1 Matrix Multiplication Excel
Dummy Variables - Regression
Constant Service Rate (M/D/1)
Fundamental Matrix & Absorbing States
Pritchett Clock Repair Shop
Breakeven Analysis
Data
Rebuilt Springs
Fixed cost 1000
Variable cost 5
Revenue 10
Results
Breakeven points
Units 200
Dollars 2,000.00$
Graph
Units Costs Revenue
0 1000 0
400 3000 4000
0
1000
2000
3000
4000
5000
0 200 400 600$
Units
Cost-volume analysis
Costs Revenue
Pritchett Clock Repair Shop
Breakeven Analysis
Data
Rebuilt Springs
Fixed cost 1000
Variable cost 5
Revenue 10.71
Volume (optional) 250
Results
Breakeven points
Units 175
Dollars 1,875.00$
Volume Analysis@ 250
Costs 2,250.00$
Revenue 2,678.57$
Profit 428.57$
Graph
Units Costs Revenue
0 1000 0
350 2750 3750
x P(x) xP(x) (x-mean)squared*P(x)
10 0.2 2 54.45
20 0.25 5 10.5625
30 0.25 7.5 3.0625
40 0.3 12 54.675
26.5 122.75
Mean Variance
The Binomial Distribution
n= 5
p= 0.5
r= 4
Cumulative probability P(r<_) 0.9688
P(r) 0.1563
Thompson Lumber
Decision Tables
Data Results
Profit
Favorable
Market
Unfavorable
Market EMV Minimum Maximum Hurwicz
Probability 0.5 0.5 coefficient 0.8
Large Plant 200000 -180000 10000 -180000 200000 124000
Small plant 100000 -20000 40000 -20000 100000 76000
Do nothing 0 0 0 0 0
Maximum 40000 0 200000 124000
Expected Value of Perfect Information
Column best 200000 0 100000 <-Expected value under certainty
40000 <-Best expected value
60000 <-Expected value of perfect information
Regret
Favorable MarketUnfavorable Market Expected Maximum
Probability 0.5 0.5
Large Plant 0 180000 90000 180000
Small plant 100000 20000 60000 100000
Do nothing 200000 0 100000 200000
Minimum 60000 100000
Bayes Theorem for Thompson Lumber Example
Fill in cells B7, B8, and C7
Probability Revisions Given a Positive Survey
State of Nature P(Sur.Pos.|state of nature) Prior Prob. Joint Prob.
Posterior
Probability
FM 0.7 0.5 0.35 0.78
UM 0.2 0.5 0.1 0.22
P(Sur.pos.)= 0.45
Probability Revisions Given a Negative Survey
State of Nature P(Sur.Pos.|state of nature) Prior Prob. Joint Prob.
Posterior
Probability
FM 0.3 0.5 0.15 0.27
UM 0.8 0.5 0.4 0.73
P(Sur.neg.)= 0.55
Triple A Construction CompanySUMMARY OUTPUT
Sales (Y)Payroll (X) Regression Statistics
6 3 Multiple R 0.833333
8 4 R Square 0.694444
9 6 Adjusted R Square0.618056
5 4 Standard Error1.311011
4.5 2 Observations 6
9.5 5
ANOVA
df SS MS F Significance F
Regression 1 15.625 15.625 9.090909 0.039352
Residual 4 6.875 1.71875
Total 5 22.5
CoefficientsStandard Error t Stat P-value Lower 95%
Intercept 2 1.742544 1.147747 0.31505 -2.83808
Payroll (X) 1.25 0.414578 3.015113 0.039352 0.098947
Significance F
Upper 95%Lower 95.0%Upper 95.0%
6.838077 -2.83808 6.838077
2.401053 0.098947 2.401053
SELL PRICE SF AGE
35000 1926 30
47000 2069 40
49900 1720 30
55000 1396 15
58900 1706 32
60000 1847 38
67000 1950 27
70000 2323 30
78500 2285 26
79000 3752 35
87500 2300 18
93000 2525 17
95000 3800 40
97000 1740 12
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.81968
R Square 0.67188
Adjusted R Square0.61222
Standard Error 12156.3
Observations 14
ANOVA
df SS MS F Significance F
Regression 2 3328484242 1.66E+09 11.26195 0.002179
Residual 11 1625532901 1.48E+08
Total 13 4954017143
CoefficientsStandard Error t Stat P-value Lower 95%Upper 95%Lower 95.0%Upper 95.0%
Intercept 60815.4 12741.04143 4.773193 0.000578 32772.6 88858.29 32772.6 88858.29
SF 21.9097 5.140482535 4.262184 0.001338 10.59556 33.22381 10.59556 33.22381
AGE -1449.34 398.282471 -3.63898 0.003895 -2325.96 -572.729 -2325.96 -572.729
Upper 95.0%
SELL PRICESF AGE X3(Exc) X4(Mint) Condition
35000 1926 30 0 0 Good
47000 2069 40 1 0 Excellent
49900 1720 30 1 0 Excellent
55000 1396 15 0 0 Good
58900 1706 32 0 1 Mint
60000 1847 38 0 1 Mint
67000 1950 27 0 1 Mint
70000 2323 30 1 0 Excellent
78500 2285 26 0 1 Mint
79000 3752 35 0 0 Good
87500 2300 18 0 0 Good
93000 2525 17 0 0 Good
95000 3800 40 1 0 Excellent
97000 1740 12 0 1 Mint
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.947618
R Square 0.89798
Adjusted R Square0.852637
Standard Error7493.777
Observations 14
ANOVA
df SS MS F Significance F
Regression 4 4.45E+09 1.11E+09 19.80444 0.000174
Residual 9 5.05E+08 56156698
Total 13 4.95E+09
CoefficientsStandard Error t Stat P-value Lower 95%Upper 95%Lower 95.0%Upper 95.0%
Intercept 48329.23 8713.307 5.5466 0.000358 28618.36 68040.1 28618.36 68040.1
SF 28.2138 3.473758 8.121981 1.96E-05 20.35561 36.07199 20.35561 36.07199
AGE -1981.41 298.0139 -6.64872 9.39E-05 -2655.56 -1307.26 -2655.56 -1307.26
X3(Exc) 16581.32 6089.81 2.722798 0.0235 2805.216 30357.43 2805.216 30357.43
X4(Mint) 23684.62 5324.635 4.448122 0.001605 11639.46 35729.78 11639.46 35729.78
Upper 95.0%
Automobile Weight vs. MPG SUMMARY OUTPUT
MPG (Y) Weight (X1) Regression Statistics
12 4.58 Multiple R 0.86288
13 4.66 R Square 0.74456
15 4.02 Adjusted R Square0.71902
18 2.53 Standard Error5.00757
19 3.09 Observations 12
19 3.11
20 3.18 ANOVA
23 2.68 df SS MS F Significance F
24 2.65 Regression 1 730.909 730.909 29.14802 0.000302
33 1.70 Residual 10 250.7577 25.07577
36 1.95 Total 11 981.6667
42 1.92
CoefficientsStandard Error t Stat P-value Lower 95%Upper 95%
Intercept 47.6193 4.813151 9.89359 1.75E-06 36.89498 58.34371
Weight (X1) -8.246 1.527345 -5.39889 0.000302 -11.6491 -4.84283
Lower 95.0%Upper 95.0%
36.89498 58.34371
-11.6491 -4.84283
Automobile Weight vs. MPG SUMMARY OUTPUT
MPG (Y) Weight (X1) WeightSq.(X2) Regression Statistics
12 4.58 20.98 Multiple R 0.9208
13 4.66 21.72 R Square 0.8478
15 4.02 16.16 Adjusted R Square0.8140
18 2.53 6.40 Standard Error 4.0745
19 3.09 9.55 Observations 12
19 3.11 9.67
20 3.18 10.11 ANOVA
23 2.68 7.18 df SS MS F Significance F
24 2.65 7.02 Regression 2 832.2557 416.1278 25.0661 0.000209
33 1.70 2.89 Residual 9 149.411 16.60122
36 1.95 3.80 Total 11 981.6667
42 1.92 3.69
CoefficientsStandard Error t Stat P-value Lower 95%
Intercept 79.7888 13.5962 5.8685 0.0002 49.0321
Weight (X1) -30.2224 8.9809 -3.3652 0.0083 -50.5386
WeightSq.(X2) 3.4124 1.3811 2.4708 0.0355 0.2881
Significance F
Upper 95%Lower 95.0%Upper 95.0%
110.5454 49.0321 110.5454
-9.9062 -50.5386 -9.9062
6.5367 0.2881 6.5367
Solved Problem 4-2
Advertising ($100) Y Sales X
11 5
6 3
10 7
6 2
12 8
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.9014
R Square 0.8125
Adjusted R Square 0.7500
Standard Error 1.4142
Observations 5
ANOVA
df SS MS F Significance F
Regression 1 26 26 13 0.036618
Residual 3 6 2
Total 4 32
CoefficientsStandard Error t Stat P-value Lower 95%Upper 95%Lower 95.0%Upper 95.0%
Intercept 4 1.5242 2.6244 0.0787 -0.8506 8.8506 -0.8506 8.8506
Sales X 1 0.2774 3.6056 0.0366 0.1173 1.8827 0.1173 1.8827
Upper 95.0%
Wallace Garden Supply Shed Sales
Forecasting Weighted moving averages 3 period moving average
Data Error analysis
Period Demand Weights Forecast Error Absolute Squared
January 10 1
February 12 2
March 13 3
April 16 12.16667 3.833333 3.833333 14.69444
May 19 14.33333 4.666667 4.666667 21.77778
June 23 17 6 6 36
July 26 20.5 5.5 5.5 30.25
August 30 23.83333 6.166667 6.166667 38.02778
September 28 27.5 0.5 0.5 0.25
October 18 28.33333 -10.3333 10.33333 106.7778
November 16 23.33333 -7.33333 7.333333 53.77778
December 14 18.66667 -4.66667 4.666667 21.77778
Total 4.333333 49 323.3333
Average 0.481481 5.444444 35.92593
Bias MAD MSE
SE 6.796358
Next period 15.3333333
Port of Baltimore
Forecasting Exponential smoothing
Alpha 0.1
Data Error Analysis
Period Demand Forecast Error Absolute Squared
Quarter 1 180 175 5 5 25
Quarter 2 168 175.5 -7.5 7.5 56.25
Quarter 3 159 174.75 -15.75 15.75 248.0625
Quarter 4 175 173.175 1.825 1.825 3.330625
Quarter 5 190 173.3575 16.6425 16.6425 276.9728
Quarter 6 205 175.0218 29.97825 29.97825 898.6955
Quarter 7 180 178.0196 1.980425 1.980425 3.922083
Quarter 8 182 178.2176 3.782382 3.782382 14.30642
Total 35.95856 82.45856 1526.54
Average 4.49482 10.30732 190.8175
Bias MAD MSE
SE 15.95065
Next period 178.595856
Midwestern Manufacturing
Time (X) Demand (Y)
1 74
2 79
3 80
4 90
5 105
6 142
7 122
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.89491
R Square 0.800863
Adjusted R Square0.761036
Standard Error12.43239
Observations 7
ANOVA
df SS MS F Significance F
Regression 1 3108.036 3108.036 20.10837 0.006493
Residual 5 772.8214 154.5643
Total 6 3880.857
CoefficientsStandard Error t Stat P-value Lower 95% Upper 95%Lower 95.0%Upper 95.0%
Intercept 56.71429 10.50729 5.39762 0.00295 29.70445 83.72412 29.70445 83.72412
Time (X) 10.53571 2.34950 4.48424 0.00649 4.49613 16.57530 4.49613 16.57530
Upper 95.0%
Midwestern Manufacturing's Demand
Forecasting Regression/Trend analysis
Data Error analysis
Period Demand (y) Period(x) Forecast Error Absolute Squared
1993 74 1 67.25 6.75 6.75 45.5625
1994 79 2 77.78571 1.214286 1.2142857 1.47449
1995 80 3 88.32143 -8.32143 8.3214286 69.24617
1996 90 4 98.85714 -8.85714 8.8571429 78.44898
1997 105 5 109.3929 -4.39286 4.3928571 19.29719
1998 142 6 119.9286 22.07143 22.071429 487.148
1999 122 7 130.4643 -8.46429 8.4642857 71.64413
Total 0.00 60.071429 772.8214
Intercept 56.7142857 Average 0.00 8.5816327 110.4031
Slope 10.5357143 Bias MAD MSE
SE 12.43239
Next period 141 8
Correlation 0.89491
Year Quarter Sales X1 Time PeriodX2 Qtr 2 X3 Qtr 3 X4 Qtr 4
1 1 108 1 0 0 0
2 125 2 1 0 0
3 150 3 0 1 0
4 141 4 0 0 1
2 1 116 5 0 0 0
2 134 6 1 0 0
3 159 7 0 1 0
4 152 8 0 0 1
3 1 123 9 0 0 0
2 142 10 1 0 0
3 168 11 0 1 0
4 165 12 0 0 1
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.99718
R Square 0.99436
Adjusted R Square0.99114
Standard Error1.83225
Observations 12
ANOVA
df SS MS F Significance F
Regression 4 4144.75 1036.188 308.6516 6.03E-08
Residual 7 23.5 3.357143
Total 11 4168.25
CoefficientsStandard Error t Stat P-value Lower 95%Upper 95%Lower 95.0%Upper 95.0%
Intercept 104.104 1.332194 78.14493 1.48E-11 100.954 107.2543 100.954 107.2543
X1 Time Period2.3125 0.16195 14.27913 1.96E-06 1.92955 2.69545 1.92955 2.69545
X2 Qtr 2 15.6875 1.504767 10.4252 1.62E-05 12.12929 19.24571 12.12929 19.24571
X3 Qtr 3 38.7083 1.530688 25.28819 3.86E-08 35.08883 42.32784 35.08883 42.32784
X4 Qtr 4 30.0625 1.572941 19.11228 2.67E-07 26.34308 33.78192 26.34308 33.78192
Sumco Pump Company
Inventory Economic Order Quantity Model
Data
Demand rate, D 1000
Setup cost, S 10
Holding cost, H 0.5 (fixed amount)
Unit Price, P 0
Results
Optimal Order Quantity, Q* 200
Maximum Inventory 200
Average Inventory 100
Number of Setups 5
Holding cost $50.00
Setup cost $50.00
Unit costs $0.00
Total cost, Tc $100.00
COST TABLE Start at 25 Increment by 15
Q Setup cost Holding costTotal cost
25 400 6.25 406.25
40 250 10 260
55 181.8182 13.75 195.5682
70 142.8571 17.5 160.3571
85 117.6471 21.25 138.8971
100 100 25 125
115 86.95652 28.75 115.7065
130 76.92308 32.5 109.4231
145 68.96552 36.25 105.2155
160 62.5 40 102.5
175 57.14286 43.75 100.8929
190 52.63158 47.5 100.1316
205 48.78049 51.25 100.0305
220 45.45455 55 100.4545
235 42.55319 58.75 101.3032
250 40 62.5 102.5
265 37.73585 66.25 103.9858
280 35.71429 70 105.7143
295 33.89831 73.75 107.6483
310 32.25806 77.5 109.7581
325 30.76923 81.25 112.0192
050
100150200250300350400450
25 115 205 295
Co
st
($)
Order Quantity (Q)
Inventory: Cost vs Quantity
Setup cost
Holding cost
Total cost
340 29.41176 85 114.4118
355 28.16901 88.75 116.919
370 27.02703 92.5 119.527
Brown Manufacturing
Inventory Production Order Quantity Model
Data
Demand rate, D 10000
Setup cost, S 100
Holding cost, H 0.5 (fixed amount)
Daily production rate, p 80
Daily demand rate, d 60
Unit price, P 0
Results
Optimal production quantity, Q* 4000
Maximum Inventory 1000
Average Inventory 500
Number of Setups 2.5
Holding cost 250
Setup cost 250
Unit costs 0
Total cost, Tc 500
COST TABLE Start at 1000 Increment by333.3333
Q Setup cost Holding costTotal cost
1000 1000 62.5 1062.5
1333.333 750 83.33333 833.3333
1666.667 600 104.1667 704.1667
2000 500 125 625
2333.333 428.5714 145.8333 574.4048
2666.667 375 166.6667 541.6667
3000 333.3333 187.5 520.8333
3333.333 300 208.3333 508.3333
3666.667 272.7273 229.1667 501.8939
4000 250 250 500
4333.333 230.7692 270.8333 501.6026
4666.667 214.2857 291.6667 505.9524
5000 200 312.5 512.5
5333.333 187.5 333.3333 520.8333
5666.667 176.4706 354.1667 530.6373
6000 166.6667 375 541.6667
6333.333 157.8947 395.8333 553.7281
6666.667 150 416.6667 566.6667
7000 142.8571 437.5 580.3571
7333.333 136.3636 458.3333 594.697
7666.667 130.4348 479.1667 609.6014
8000 125 500 625
0
200
400
600
800
1000
1200
10002666.6666674333.33333360007666.666667
Co
st
($)
Order Quantity (Q)
Inventory: Cost vs Quantity
8333.333 120 520.8333 640.8333
8666.667 115.3846 541.6667 657.0513
Inventory: Cost vs Quantity
Setup cost
Holding cost
Total cost
Brass Department Store
Inventory Quantity Discount Model
Data
Demand rate, D 5000
Setup cost, S 49
Holding cost %, I 20%
Range 1 Range 2 Range 3
Minimum quantity 0 1000 2000
Unit Price, P 5 4.8 4.75
Results
Range 1 Range 2 Range 3
Q* (Square root formula) 700 714.4345083 718.1848465
Order Quantity 700 1000 2000
Holding cost $350.00 $480.00 $950.00
Setup cost $350.00 $245.00 $122.50
Unit costs $25,000.00 $24,000.00 $23,750.00
Total cost, Tc $25,700.00 $24,725.00 $24,822.50 minimum
Optimal Order Quantity 1000
=
$24,725.00
Flair Furniture
Tables Chairs
Left Hand
Side
Right Hand
Side Slack
Objective function 70 50 4100
Carpentry 4 3 240 <= 240 0
Painting 2 1 100 <= 100 0
Solution Values 30 40
Holiday Meal Turkey Ranch
Brand 1 Brand 2
Left Hand
Side
Right Hand
Side Surplus
Objective function 2 3 31.2
Ingredient A 5 10 90 >= 90
Ingredient B 4 3 48 >= 48 0
Ingredient C 0.5 0 4.2 >= 1.5 2.7
Solution Values 8.4 4.8
High note sound company
CD PlayersReceivers
Value 0 20
Total
Profit 50 120 2400
Used Sign Available
Electrician hours 2 4 80 <= 80
Audio technician hours 3 1 20 <= 60
Win Big Gambling Club
1 minute
TV spots
newspaper
ads
30 second
radio spots
1 minute
radio spots
Solution 1.96875 5 6.20689655 0
Variables X1 X2 X3 X4
Audience reached per ad 5000 8500 2400 2800
Maximum TV 1
Maximum Newspaper 1
Maximum 30-second radio 1
Maximum 1 min. radio 1
Cost per ad 800 925 290 380
Radio dollars 290 380
Radio spots 1 1
RHS
67240.302
1.96875 <= 12
5 <= 5
6.2068966 <= 25
0 <= 20
8000 <= 8000
1800 <= 1800
6.2068966 >= 5
Fifth Avenue Industries
Variety
Number
(X)
Selling
price
Monthly
minimum
Monthly
demand
Material
(yards) silk polyester cotton
All silk 6400 6.7 6000 7000 0.125 100%
All polyester 14000 3.55 10000 14000 0.08 100%
Poly-cotton
blend 1 16000 4.31 13000 16000 0.1 50% 50%
Poly-cotton
blend 2 8500 4.81 6000 8500 0.1 30% 70%
Total revenue 202425 800 2175 1395
Material Cost Available Used
Silk 21 800 800
Polyester 6 3000 2175
Cotton 9 1600 1395
Total Cost 42405
Total Profit 160020
Top Speed Bicycle Company
Transportation
Data
COSTS New York Chicago Los AngelesSupply
New Orleans 2 3 5 20000
Omaha 3 1 4 15000
Demand 10000 8000 15000 33000 \ 35000
Shipments
Shipments New York Chicago Los AngelesRow Total
New Orleans 10000 0 8000 18000
Omaha 0 8000 7000 15000
Column Total 10000 8000 15000 33000 \ 33000
Total Cost 96000
Goodman Shipping
Item
Percent
loaded
Max
percent
loaded Value ($) weight (lbs)
1 0.333333 1 22500 7500
2 1 1 24000 7500
3 0 1 8000 3000
4 0 1 9500 3500
5 0 1 11500 4000
6 0 1 9750 3500
Total 31,500$ 10000
Weight Capacity 10000
High note sound company
CD PlayersReceivers
Value 0 20
Total
Profit 50 120 2400
Used Sign Available
Electrician hours 2 4 80 <= 80
Audio technician hours 3 1 20 <= 60
Manufacturing Example
mower blower
variable-> 100 200
Total profit
profit 30 80 19000
used available
labor hours 2 4 1000 < 1000
steel (lbs) 6 2 1000 < 1200
snowblower engines 1 200 < 200
Executive Furniture Company
Transportation
Data
COSTS AlbuquerqueBoston Cleveland Supply
Des Moines 5 4 3 100
Evansville 8 4 3 300
Fort Lauderdale 9 7 5 300
Demand 300 200 200 700 \ 700
Shipments
Shipments AlbuquerqueBoston Cleveland Row Total
Des Moines 100 0 0 100
Evansville 0 200 100 300
Fort Lauderdale 200 0 100 300
Column Total 300 200 200 700 \ 700
Total Cost 3900
Birmingham Plant
Transportation
Data
COSTS Detroit Dallas New York Los AngelesSupply
Cincinnati 73 103 88 108 15000
Salt Lake 85 80 100 90 6000
Pittsburgh 88 97 78 118 14000
Birmingham 84 79 90 99 11000
Demand 10000 12000 15000 9000 46000 \ 46000
Shipments
Shipments Detroit Dallas New York Los AngelesColumn Total
Cincinnati 10000 0 1000 4000 15000
Salt Lake 0 1000 0 5000 6000
Pittsburgh 0 0 14000 0 14000
Birmingham 0 11000 0 0 11000
Column Total 10000 12000 15000 9000 46000 \ 46000
Total Cost 3741000
Fix-It Shop Assignment
Fix-It Shop Assignment
Assignment
Data
COSTS Project 1 Project 2 Project 3
Adams 11 14 6
Brown 8 10 11
Cooper 9 12 7
Assignments
Shipments Project 1 Project 2 Project 3 Row Total
Adams 0 0 1 1
Brown 0 1 0 1
Cooper 1 0 0 1
Column Total 1 1 1 3
Total Cost 25
Harrison Electric IP Analysis
Chandeliers Fans
Solution 5 0
Total
Profit 7 6 35
Used Sign Limit
wiring hours 2 3 10 < 12
assembly hours 6 5 30 < 30
Bagwell Chemical Company
xyline (bags) hexall (lbs)
value 44 20
profit 85 1.5 3770
used sign available
ingredient a 30 0.5 1330 <= 2000
ingredient b 18 0.4 800 <= 800
ingredient c 2 0.1 90 <= 200
Simkin, Simkin and Steinberg
Stock Company Name Invest Return Cost
1 Trans-Texas Oil 0 50 480
2 British Petroleum 0 80 540
3 Dutch Shell 1 90 680
4 Houston Drilling 1 120 1000
5 Texas Petroleum 1 110 700
6 San Diego Oil 1 40 510
7 California Petro 0 75 900
Total 360 2890
Limit 3000
Bound
Texas Constraint 2 >= 2
Foreign oil constraint 1 <= 1
California Constraint 1 = 1
Great Western Appliance
MicrotoasterSelf-clean Total
Number 0 1000 1000 < 1000
Profit 0 271000 271,000.00$
used Sign capacity
Hours 0.5 0.4 400 < 500
Hospicare Corpx1 x2
value 6.066259 4.100253
terms x1 x1^2 x1*x2 x2 x2^3 1/x2
values 6.066259 36.79949 24.87319 4.100253 68.93374 0.243887
total
revenue 13 6 5 1 248.846
constraint 1 2 4 90 < 90
constraint 2 1 1 75 < 75
constraint 3 8 -2 40.3296 < 61
Thermlock Gaskets
x1 x2
value 3.325326 14.67227
total
cost 5 7 119.3325
constraints
x1 x1^2 x1^3 x2 x2^2
value 3.325326 11.05779 36.77076 14.67227 215.2756 Total
Constraint 1 3 0.25 4 0.3 136.0122 > 125
Constraint 2 13 1 80 > 80
Constraint 3 0.7 1 17 > 17
0-1 integer Program
x1 x2 x3
values 1 1 0
total
maximize 50 45 48 95
Limit
constraint 1 19 27 34 46 < 80
22 13 12 35 < 40
1 1 1 2 < 2
Crashing General Foundry ProblemYA YB YC YD YE YF YG YH XST XA XB XC XD XE XF XG XH XFIN
Values 0 0 1 0 0 0 2 0 0 2 3 3 7 7 6 10 12 12
Minimize cost 1000 2000 1000 1000 1000 500 2000 3000
A crash max. 1
B crash max. 1
C crash max. 1
D crash max. 1
E crash max. 1
F crash max. 1
G crash max. 1
H crash max. 1
Due date 1
Start 1
A constraint 1 -1 1
B constraint 1 -1 1
C constraint 1 -1 1
D constraint 1 -1 1
E constraint 1 -1 1
F constraint 1 -1 1
G constraint 1 1 -1 1
G constraint 2 1 -1 1
H constraint 1 1 -1 1
H constraint 2 1 -1 1
Finish constraint -1 1
Totals
5000
0 < 1
0 < 2
1 < 1
0 < 1
0 < 2
0 < 1
2 < 3
0 < 1
12 < 12
0 = 0
2 > 2
3 > 3
2 > 2
4 > 4
4 > 4
3 > 3
5 > 5
5 > 5
6 > 2
2 > 2
0 > 0
Arnold's Muffler Shop
Waiting Lines M/M/1 (Single Server Model)
Data Results
Arrival rate (l) 2 Average server utilization(r) 0.666667Service rate (m) 3 Average number of customers in the queue(Lq) 1.333333
Average number of customers in the system(L) 2
Average waiting time in the queue(Wq) 0.666667
Average time in the system(W) 1
Probability (% of time) system is empty (P0) 0.333333
Probabilities
Number Probability Cumulative Probability
0 0.333333 0.333333
1 0.222222 0.555556
2 0.148148 0.703704
3 0.098765 0.802469
4 0.065844 0.868313
5 0.043896 0.912209
6 0.029264 0.941472
7 0.019509 0.960982
8 0.013006 0.973988
9 0.008671 0.982658
10 0.005781 0.988439
11 0.003854 0.992293
12 0.002569 0.994862
13 0.001713 0.996575
14 0.001142 0.997716
15 0.000761 0.998478
16 0.000507 0.998985
17 0.000338 0.999323
18 0.000226 0.999549
19 0.000150 0.999699
20 0.000100 0.999800
Arnold's Muffler Shop
Waiting Lines M/M/s
Data Results
Arrival rate (l) 2 Average server utilization(r) 0.33333
Service rate (m) 3 Average number of customers in the queue(Lq) 0.08333
Number of servers(s) 2 Average number of customers in the system(L) 0.75
Average waiting time in the queue(Wq) 0.04167
Average time in the system(W) 0.375
Probability (% of time) system is empty (P0) 0.5
Probabilities
Number Probability Cumulative Probability
0 0.500000 0.500000
1 0.333333 0.833333
2 0.111111 0.944444
3 0.037037 0.981481
4 0.012346 0.993827
5 0.004115 0.997942
6 0.001372 0.999314
7 0.000457 0.999771
8 0.000152 0.999924
9 0.000051 0.999975
10 0.000017 0.999992
11 0.000006 0.999997
12 0.000002 0.999999
13 0.000001 1.000000
14 0.000000 1.000000
15 0.000000 1.000000
16 0.000000 1.000000
17 0.000000 1.000000
18 0.000000 1.000000
19 0.000000 1.000000
20 0.000000 1.000000
Computations
n or s (lam/mu)^n/n!Cumsum(n-1)term2 P0(s)
0 1
1 0.666667 1 2 0.33333
2 0.222222 1.666667 0.333333333 0.5
3 0.049383 1.888889 0.063492063 0.5122
4 0.00823 1.938272 0.009876543 0.51331
5 0.001097 1.946502 0.001266223 0.51341
6 0.000122 1.947599 0.000137174 0.51342
7 1.16E-05 1.947721 1.2835E-05 0.51342
8 9.68E-07 1.947733 1.05569E-06 0.51342
9 7.17E-08 1.947734 7.74175E-08 0.51342
10 4.78E-09 1.947734 5.12021E-09 0.51342
11 2.9E-10 1.947734 3.08314E-10 0.51342
12 1.61E-11 1.947734 1.70369E-11 0.51342
13 8.25E-13 1.947734 8.69754E-13 0.51342
14 3.93E-14 1.947734 4.12575E-14 0.51342
15 1.75E-15 1.947734 1.82758E-15 0.51342
16 7.28E-17 1.947734 7.59283E-17 0.51342
17 2.85E-18 1.947734 2.96998E-18 0.51342
18 1.06E-19 1.947734 1.09751E-19 0.51342
19 3.71E-21 1.947734 3.84312E-21 0.51342
20 1.24E-22 1.947734 1.27871E-22 0.51342
21 3.92E-24 1.947734 4.05276E-24 0.51342
22 1.19E-25 1.947734 1.22628E-25 0.51342
23
24
25
26
27
28
29
30
Rho(s) Lq(s) L(s) Wq(s) W(S)
0.666667 1.333333 2 0.666667 1
0.333333 0.083333 0.75 0.041667 0.375
0.222222 0.009292 0.675958 0.004646 0.337979
0.166667 0.001014 0.667681 0.000507 0.33384
0.133333 0.0001 0.666767 5E-05 0.333383
0.111111 8.8E-06 0.666675 4.4E-06 0.333338
0.095238 6.94E-07 0.666667 3.47E-07 0.333334
0.083333 4.93E-08 0.666667 2.46E-08 0.333333
0.074074 3.18E-09 0.666667 1.59E-09 0.333333
0.066667 1.88E-10 0.666667 9.39E-11 0.333333
0.060606 1.02E-11 0.666667 5.11E-12 0.333333
0.055556 5.15E-13 0.666667 2.57E-13 0.333333
0.051282 2.41E-14 0.666667 1.21E-14 0.333333
0.047619 1.06E-15 0.666667 5.3E-16 0.333333
0.044444 4.36E-17 0.666667 2.18E-17 0.333333
0.041667 1.69E-18 0.666667 8.47E-19 0.333333
0.039216 6.22E-20 0.666667 3.11E-20 0.333333
0.037037 2.17E-21 0.666667 1.08E-21 0.333333
0.035088 7.17E-23 0.666667 3.59E-23 0.333333
0.033333 2.26E-24 0.666667 1.13E-24 0.333333
0.031746 6.82E-26 0.666667 3.41E-26 0.333333
0.030303 1.97E-27 0.666667 9.84E-28 0.333333
Garcia-Golding Recycling
Waiting Lines M/D/1 (Constant Service Times)
Data Results
Arrival rate (l) 8 Average server utilization(r) 0.666667Service rate (m) 12 Average number of customers in the queue(Lq) 0.666667
Average number of customers in the system(L) 1.333333
Average waiting time in the queue(Wq) 0.083333
Average time in the system(W) 0.166667
Probability (% of time) system is empty (P0) 0.333333
Waiting cost/hour 60.00$
Waiting cost/trip 5.00$
Department of Commerce
Waiting Lines M/M/s with a finite population
Data Results
Arrival rate (l) per
customer 0.05 Average server utilization(r) 0.436048
Service rate (m) 0.5 Average number of customers in the queue(Lq) 0.203474
Number of servers 1 Average number of customers in the system(L) 0.639522
Population size (N) 5 Average waiting time in the queue(Wq) 0.933264
Average time in the system(W) 2.933264
Probability (% of time) system is empty (P0) 0.563952
Effective arrival rate 0.218024
Probabilities
Number, n
Probability,
P(n)
Cumulative
Probability Number waiting
Arrival
rate(n)
0 0.5639522 0.5639522 0 0.25
1 0.2819761 0.8459283 0 0.2
2 0.1127904 0.9587187 1 0.15
3 0.0338371 0.9925558 2 0.1
4 0.0067674 0.9993233 3 0.05
5 0.0006767 1 4 0
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
1.7732
Term 1
Sum term
1 Term 2
Sum term
2
Decum
term 2 P0(s)
1 1 1 1 0.7732
0.5 1.5 0.5 1.5 0.2732 0.563952
0.2 1.7 0.0732
0.06 1.76 0.0132
0.012 1.772 0.0012
0.0012 1.7732 0
Harry's Tire Shop NOTE: The random numbers appearing here may not be the same as the ones in the book, but the formulas are the same.
Probability
Probability
Range
(Lower)
Cumulative
Probability
Tires
Demand Day
Random
Number
Simulated
Demand
0.05 0 0.05 0 1 0.738713 4
0.1 0.05 0.15 1 2 0.809414 4
0.2 0.15 0.35 2 3 0.858616 5
0.3 0.35 0.65 3 4 0.906845 5
0.2 0.65 0.85 4 5 0.632865 3
0.15 0.85 1 5 6 0.871298 5
7 0.17927 2
8 0.739672 4
9 0.527331 3
10 0.257875 2
Average 3.7
Results (Frequency table)
Tires
Demanded Frequency Percentage Cum %
0 0 0% 0%
1 0 0% 0%
2 2 20% 20%
3 2 20% 40%
4 3 30% 70%
5 3 30% 100%
10
NOTE: The random numbers appearing here may not be the same as the ones in the book, but the formulas are the same.
Generating Normal Random Numbers NOTE: The random numbers appearing here may not be the same as the ones in the book, but the formulas are the same.
Random number Value Frenquency Percentage
38.56168904 26 0 0.0%
44.12934062 28 2 1.0%
39.09006016 30 3 1.5%
41.6115212 32 4 2.0%
36.8373438 34 8 4.0%
40.58881682 36 18 9.0%
45.16354566 38 24 12.0%
47.41344557 40 38 19.0%
34.57334599 42 37 18.5%
36.0474607 44 23 11.5%
42.1638933 46 22 11.0%
28.29700386 48 11 5.5%
38.14649298 50 6 3.0%
42.23390822 52 3 1.5%
41.85412671 54 1 0.5%
35.95991143 56 0 0.0%
27.93157837 200
38.54188857
39.04520022
32.56023403
41.69639146
44.43350295
41.85227064
38.45075418
37.38882091
33.02101696
40.6400646
41.17258569
39.96474019
41.03583802
44.60003945
38.06981023
42.90673701
37.07801997
32.84127465
41.80699589
41.67911025
49.24258993
35.01932776
43.61010545
41.81771246
50.80814037
38.77385236
38.47929316
37.71896993
35.92948329
43.44322161
39.95048214
41.89463451
37.76545142
38.09549431
44.33478259
36.13992556
34.12232602
42.03601649
36.71482384
29.13328035
42.92556993
37.50066263
35.02111028
42.33221803
40.24424266
38.8368427
40.98538447
27.67315395
34.09959069
39.24256618
29.58638652
49.5076796
31.74448455
45.69617468
47.35126958
44.46185606
46.56239048
36.10574416
39.36494594
42.12464207
45.0290262
45.91150619
36.42252659
46.13615538
36.04178886
41.97013999
45.60078043
34.70077225
45.39929756
34.11849742
38.70581248
38.747506
50.64820379
45.88826842
36.40261979
41.52208587
46.59614633
49.75444815
48.48194393
38.97037886
40.33469476
35.48822395
41.0830677
41.00359209
42.48147104
43.57190573
41.16914865
51.45406355
45.79309542
37.73215968
37.13860654
40.97192721
39.76302815
44.99998136
48.97407901
35.47674677
38.92208945
37.73568588
37.15233765
39.76609951
46.98934684
33.36900325
41.5515104
45.15152291
31.75704356
39.34025643
41.60487736
36.07407901
38.6140063
36.74786838
33.06146144
42.75324176
42.5026408
32.99124216
33.13558609
42.64159038
42.74632693
35.05647801
39.97289129
39.89324781
40.2956706
38.14531751
41.2648517
39.41162201
43.12350197
40.15107936
34.59976578
48.8346183
47.74501279
52.36157989
41.00668786
40.02543857
40.39739927
38.25853047
38.88513525
38.84859408
34.50344166
41.36399548
39.75417349
42.35035309
39.68634974
41.37830095
33.51514677
47.01137633
36.86512154
46.11033393
43.66033294
44.06863988
41.0921877
38.53390409
40.47577984
36.82718645
42.81969651
37.035601
43.74497596
38.45984057
41.77411443
42.40898258
45.11910123
40.77840551
38.56061648
43.14300434
35.15652821
39.35622989
39.23034706
31.84024945
40.24890939
47.83578473
41.78150918
35.80741397
38.02931441
46.72580016
42.96416483
30.69024827
36.97738421
44.1269921
45.39807655
44.47722189
45.89792101
37.93462946
44.28650007
35.61303521
35.06684899
NOTE: The random numbers appearing here may not be the same as the ones in the book, but the formulas are the same.
Port of New Orleans Barge Unloadings NOTE: The random numbers appearing here may not be the same as the ones in the book, but the formulas are the same.
Day
Previously
delayed
Random
number Arrivals
Total to
be
unoaded
Random
Number
Possibly
unloaded Unloaded
1 0 0.108295 0 0 0.160394 2 0
2 0 0.100507 0 0 0.483036 3 0
3 0 0.320609 2 2 0.702392 4 2
4 0 0.182938 1 1 0.524397 3 1
5 0 0.576297 3 3 0.766404 4 3
6 0 0.682204 3 3 0.82367 4 3
7 0 0.244693 1 1 0.646211 3 1
8 0 0.864116 4 4 0.158178 2 2
9 2 0.353314 2 4 0.830843 4 4
10 0 0.008447 0 0 0.064438 2 0
Barge Arrivals Unloading rates
Demand Probability Lower CumulativeDemand Number Probability Lower
0 0.13 0 0.13 0 1 0.05 0
1 0.17 0.13 0.3 1 2 0.15 0.05
2 0.15 0.3 0.45 2 3 0.5 0.2
3 0.25 0.45 0.7 3 4 0.2 0.7
4 0.2 0.7 0.9 4 5 0.1 0.9
5 0.1 0.9 1 5
NOTE: The random numbers appearing here may not be the same as the ones in the book, but the formulas are the same.
CumulativeUnloading
0.05 1
0.2 2
0.7 3
0.9 4
1 5
Three Hills Power NOTE: The random numbers appearing here may not be the same as the ones in the book, but the formulas are the same.
Breakdown
number
Random
number
Time
between
breakdowns
Time of
breakdowns
Time
repairperson
is free
Random
Number Repair time
Repair
ends
1 0.0529581 1 1 1 0.3852438 2 3
2 0.9245766 3 4 4 0.8913291 3 7
3 0.5936416 2 6 7 0.3614929 2 9
4 0.9111224 3 9 9 0.2881283 2 11
5 0.6038654 2.5 11.5 11.5 0.0588177 1 12.5
6 0.0172306 0.5 12 12.5 0.3399594 2 14.5
7 0.0516984 1 13 14.5 0.0860723 1 15.5
8 0.533433 2 15 15.5 0.8584862 3 18.5
9 0.8751594 3 18 18.5 0.7751288 2 20.5
10 0.3091988 2 20 20.5 0.5317927 2 22.5
Demand Table Repair times
Time between breakdownsProbability Lower Cumulative Demand Time Probability
0.5 0.05 0 0.05 0.5 1 0.28
1 0.06 0.05 0.11 1 2 0.52
1.5 0.16 0.11 0.27 1.5 3 0.2
2 0.33 0.27 0.6 2
2.5 0.21 0.6 0.81 2.5
3 0.19 0.81 1 3
NOTE: The random numbers appearing here may not be the same as the ones in the book, but the formulas are the same.
Lower CumulativeLead time
0 0.28 1
0.28 0.8 2
0.8 1 3
Three Grocery Example
State Probabilities
American Food StoreFood Mart Atlas Foods
Time #1 #2 #3 Matrix of Transition Probabilities
0 0.4 0.3 0.3 0.8 0.1 0.1
1 0.41 0.31 0.28 0.1 0.7 0.2
2 0.415 0.314 0.271 0.2 0.2 0.6
3 0.4176 0.3155 0.2669
4 0.41901 0.31599 0.265
5 0.419807 0.316094 0.264099
6 0.4202748 0.3160663 0.2636589
Accounts Receivable Example
1 0 0 0
P= I : 0 = 0 1 0 0
A : B 0.6 0 0.2 0.2
0.4 0.1 0.3 0.2
I - B = 0.8 -0.2
-0.3 0.8
F = (I - B) inverse 1.37931 0.344828
0.517241 1.37931
FA = 0.965517 0.034483
0.862069 0.137931
ARCO Quality Control
Number of samples 20
Sample size 100
Data Results
# Defects % Defects Total Sample Size 2000
Sample 1 6 0.06 Total Defects 80
Sample 2 5 0.05 Percentage defects 0.04
Sample 3 0 0 Std dev of p-bar 0.019596
Sample 4 1 0.01
Sample 5 4 0.04 Upper Control Limit 0.098788
Sample 6 2 0.02 Center Line 0.04
Sample 7 5 0.05 Lower Control Limit 0
Sample 8 3 0.03
Sample 9 3 0.03
Sample 10 2 0.02
Sample 11 6 0.06
Sample 12 1 0.01
Sample 13 8 0.08
Sample 14 7 0.07
Sample 15 5 0.05
Sample 16 4 0.04
Sample 17 11 0.11 Above UCL
Sample 18 3 0.03
Sample 19 0 0
Sample 20 4 0.04
Graph information
Sample 1 0.06 0 0
Sample 2 0.05 0 0
Sample 3 0 0 0
Sample 4 0.01 0 0
Sample 5 0.04 0 0
Sample 6 0.02 0 0
Sample 7 0.05 0 0
Sample 8 0.03 0 0
Sample 9 0.03 0 0
Sample 10 0.02 0 0
Sample 11 0.06 0 0
Sample 12 0.01 0 0
Sample 13 0.08 0 0
Sample 14 0.07 0 0
Sample 15 0.05 0 0
Sample 16 0.04 0 0
Sample 17 0.11 0 0
Sample 18 0.03 0 0
Sample 19 0 0 0
Sample 20 0.04 0 0
AHP n= 3
Hardware Sys.1 Sys.2 Sys.3 Sys.1 Sys.2 Sys.3 Priority Wt. sum vector Consistency vector
Sys.1 1 3 9 Sys.1 0.6923 0.7200 0.5625 0.6583 2.0423 3.1025 Lambda
Sys.2 0.3333 1 6 Sys.2 0.2308 0.2400 0.3750 0.2819 0.8602 3.0512 CI
Sys.3 0.1111 0.1667 1 Sys.3 0.0769 0.0400 0.0625 0.0598 0.1799 3.0086 CR
Column Total 1.4444 4.1667 16
Software Sys.1 Sys.2 Sys.3 Sys.1 Sys.2 Sys.3 Priority Wt. sum vector
Sys.1 1 0.5 0.125 Sys.1 0.0909 0.0769 0.0943 0.0874 0.2623 3.0014 Lambda
Sys.2 2 1 0.2 Sys.2 0.1818 0.1538 0.1509 0.1622 0.4871 3.0028 CI
Sys.3 8 5 1 Sys.3 0.7273 0.7692 0.7547 0.7504 2.2605 3.0124 CR
Column Total 11 6.5 1.325
Vendor Sys.1 Sys.2 Sys.3 Sys.1 Sys.2 Sys.3 Priority Wt. sum vector
Sys.1 1 1 6 Sys.1 0.4615 0.4286 0.6000 0.4967 1.5330 3.0863 Lambda
Sys.2 1 1 3 Sys.2 0.4615 0.4286 0.3000 0.3967 1.2132 3.0582 CI
Sys.3 0.1667 0.3333 1 Sys.3 0.0769 0.1429 0.1000 0.1066 0.3216 3.0172 CR
Column Total 2.1667 2.3333 10
Factor Hard. Soft. Vendor Hardware Software Vendor Priority Wt. sum vector
Hardware 1 0.125 0.3333 Hardware 0.0833 0.0857 0.0769 0.0820 0.2460 3.0004 Lambda
Software 8 1 3 Software 0.6667 0.6857 0.6923 0.6816 2.0468 3.0031 CI
Vendor 3 0.3333 1 Vendor 0.2500 0.2286 0.2308 0.2364 0.7096 3.0011 CR
Column Total 12 1.4583 4.3333
n RI Hardware Software Vendor Priority
2 0.00 Sys.1 0.658 0.087 0.497 0.231
3 0.58 Sys.2 0.282 0.162 0.397 0.227
4 0.90 Sys.3 0.060 0.750 0.107 0.542
5 1.12
6 1.24
7 1.32
8 1.41
Consistency vector
3.0541
0.0270
0.0466
3.005543075
0.0028
0.0048
3.0539
0.0269
0.0464
3.0015
0.0008
0.0013
Matrix Multiplication
A= 1 2 3 B= 2 1
1 2 0 1 1
3 2
AxB = 13 9
4 3
Matrix Inverse
A= 2 1 A-inverse= 1.5 -0.5
4 3 -2 1
Matrix Determinant
A= 3 4 det(A)= -10
4 2