26922810 Excel and Excel QM Examples

(1)

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

(2)

Module

M1.1 AHP Excel

(3)

Dummy Variables - Regression

Constant Service Rate (M/D/1)

(4)

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

(5)

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

(6)

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

(7)

The Binomial Distribution n= 5 p= 0.5 r= 4 Cumulative probability P(r<_) 0.9688 P(r) 0.1563

(8)

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

(9)

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

(10)

Triple A Construction Company

SUMMARY 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

(11)

Significance F

Upper 95%Lower 95.0%Upper 95.0%

6.838077 -2.83808 6.838077 2.401053 0.098947 2.401053

(12)

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

(13)

(14)

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

(15)

(16)

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

(17)

Lower 95.0%Upper 95.0%

36.89498 58.34371 -11.6491 -4.84283

(18)

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

(19)

Significance F

Upper 95%Lower 95.0%Upper 95.0%

110.5454 49.0321 110.5454 -9.9062 -50.5386 -9.9062

(20)

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

Intercept 4 1.5242 2.6244 0.0787 -0.8506 8.8506 -0.8506 8.8506

(21)

(22)

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

(23)

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

(24)

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

(25)

(26)

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

(27)

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

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

(28)

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 0 50 100 150 200 250 300 350 400 450 25 115 205 295 C o st ($) Order Quantity (Q)

Inventory: Cost vs Quantity

Setup cost

Holding cost

(29)

340 29.41176 85 114.4118

355 28.16901 88.75 116.919

(30)

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)

(31)

8333.333 120 520.8333 640.8333 8666.667 115.3846 541.6667 657.0513

(32)

Inventory: Cost vs Quantity

Setup cost Holding cost Total cost

(33)

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

(34)

=

(35)

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

(36)

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

(37)

High note sound company

CD PlayersReceivers

Value 0 20

Total

Profit 50 120 2400

Used Sign Available

Electrician hours 2 4 80 <= 80

(38)

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

(39)

RHS

67240.302

1.96875 <=

12 5 <=

5 6.2068966 <=

25 0 <=

20 8000 <=

8000

1800 <=

1800

6.2068966 >=

5

(40)

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

(41)

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

(42)

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

(43)

High note sound company

CD PlayersReceivers

Value 0 20

Total

Profit 50 120 2400

Used Sign Available

Electrician hours 2 4 80 <= 80

(44)

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

(45)

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

(46)

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

(47)

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

(48)

Harrison Electric IP Analysis

Chandeliers Fans

Solution 5 0

Total

Profit 7 6 35

Used Sign Limit

wiring hours 2 3 10 < 12

(49)

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

(50)

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

(51)

Great Western Appliance

MicrotoasterSelf-clean Total

Number 0 1000 1000 < 1000

Profit 0 271000 $ 271,000.00

used Sign capacity

(52)

Hospicare Corp

x1 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

(53)

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

(54)

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

(55)

Crashing General Foundry Problem

YA 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

(56)

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

(57)

Arnold's Muffler Shop

Waiting Lines M/M/1 (Single Server Model)

Data Results

Arrival rate (l) 2 Average server utilization(r) 0.666667

Service rate (m) ₃ 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

(58)

Arnold's Muffler Shop

Waiting Lines M/M/s

Service rate (m) ₃ 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

(59)

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

(60)

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

(61)

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

(62)

Garcia-Golding Recycling

Waiting Lines M/D/1 (Constant Service Times)

Service rate (m) ₁₂ 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

Waiting cost/hour $ 60.00 Waiting cost/trip $ 5.00

(63)

Department of Commerce

Waiting Lines M/M/s with a finite population

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

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

(64)

(65)

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

(66)

(67)

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

(68)

(69)

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

(70)

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

(71)

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

(72)

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

(73)

(74)

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

(75)

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

(76)

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

(77)

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

(78)

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

(79)

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

(80)

ARCO

Quality Control

Number of samples 20

Sample size 100

# 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.11Above 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

(81)

(82)

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

(83)

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

(84)

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