Microsoft Excel 16.

92 Answer Report
Worksheet: [Integer Programming Examples Exhibits (2).xlsx]Problem8 (2)
Report Created: 28/12/24 7:54:13 PM
Result: Solver found a solution. All constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 497.262 Seconds.
Iterations: 1 Subproblems: 12
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative

Objective Cell (Max)

Cell Name Original Value Final Value
$C$16 NetNPV 0 17500

Variable Cells
Cell Name Original Value Final Value Integer
$K$5 Limited warehouse expansion Select 0 0 Binary
$K$6 Extensive warehouse expansion Selec 0 0 Binary
$K$7 Test market new product Select 0 1 Binary
$K$8 Advertising campaign Select 0 1 Binary
$K$9 Basic research Select 0 0 Binary
$K$10 Purchase new equipment Select 0 1 Binary

Constraints
Cell Name Cell Value Formula Status Slack
$K$11 SelectCondition 0 $K$11<=1 Not Binding 1
$L$5 Year1Cost 9000 $L$5<=$C$11 Not Binding 1500
$M$5 Year2Cost 6000 $M$5<=$D$11 Not Binding 1000
$N$5 Year3Cost 7700 $N$5<=$E$11 Not Binding 1050
$K$5:$K$10=Binary
Capital Requirements ($)
Projects Year 1 Year 2 Year 3 NPV ($)
Limited warehouse expansion 3,000 1,000 4,000 4,000
Extensive warehouse expansion 2,500 3,500 3,500 6,000
Test market new product 6,000 4,000 5,000 10,500
Advertising campaign 2,000 1,500 1,800 4,000
Basic research 5,000 1,000 4,000 8,000
Purchase new equipment 1,000 500 900 3,000
Capital Funds Available ($) 10,500 7,000 8,750

NetNPV 17500
Projects Select Year1Cost Year4Cost Year3Cost
1 Limited warehouse expansion 0 9000 6000 7700
2 Extensive warehouse expansio 0
3 Test market new product 1
4 Advertising campaign 1
5 Basic research 0
6 Purchase new equipment 1
SelectCondition 0

ConditionD 2
ConditionF 1
Capital Requirements ($)
Projects Year 1 Year 2 Year 3 NPV ($)
Limited warehouse expansion 3,000 1,000 4,000 4,000
Extensive warehouse expansion 2,500 3,500 3,500 6,000
Test market new product 6,000 4,000 5,000 10,500
Advertising campaign 2,000 1,500 1,800 4,000
Basic research 5,000 1,000 4,000 8,000
Purchase new equipment 1,000 500 900 3,000
Capital Funds Available ($) 10,500 7,000 8,750

NetNPV 13000
Projects Select Year1Cost Year4Cost Year3Cost
1 Limited warehouse expansion 1 6500 5000 8400
2 Extensive warehouse expansio 1
3 Test market new product 0
4 Advertising campaign 0
5 Basic research 0
6 Purchase new equipment 1
SelectCondition 2

ConditionD 2
ConditionE
Capital Requirements ($)
Projects Year 1 Year 2 Year 3 NPV ($)
Limited warehouse expansion 3,000 1,000 4,000 4,000
Extensive warehouse expansion 2,500 3,500 3,500 6,000
Test market new product 6,000 4,000 5,000 10,500
Advertising campaign 2,000 1,500 1,800 4,000
Basic research 5,000 1,000 4,000 8,000
Purchase new equipment 1,000 500 900 3,000
Capital Funds Available ($) 10,500 7,000 8,750

NetNPV 17500
Projects Select Year1Cost Year4Cost Year3Cost
Limited warehouse expansion 0 9000 6000 7700
Extensive warehouse expansio 0
Test market new product 1
Advertising campaign 1
Basic research 0
Purchase new equipment 1
Labor Hour Requirement
Department Product 1 Product 2 Product 3 Labor Available
Department A 1.5 3 2 450
Department B 2 1 2.5 350
Department C 0.25 0.25 0.25 50

Product 1 Product 2 Product 3

Profit Contribution 25 28 30
Max Demand 175 150 140
Setup Cost 400 550 600

TotalProfit 0
Department Product 1 Product 2 Product 3 TotalLabour
Department A 0 0 0 0
Department B 0 0 0 0
Department C 0 0 0 0
Totaldemand 0 0 0
ProducedOrNot 0 0 0
MaxDemand 0 0 0
Microsoft Excel 16.86 Answer Report
Worksheet: [Integer Programming Examples Exhibits (2).xlsx]Sheet1
Report Created: 01/01/25 11:05:57 PM
Result: Solver found a solution. All constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 554.086 Seconds.
Iterations: 2 Subproblems: 0
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 1E-06, Use Automatic Scaling
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative

Objective Cell (Max)

Cell Name Original Value Final Value
$B$14 NetNPV Year 1 17500 29166.666667

Variable Cells
Cell Name Original Value Final Value Integer
$J$3 Limited warehouse expansion Select 0 0 Contin
$J$4 Extensive warehouse expansion Select 0 0 Contin
$J$5 Test market new product Select 1 0 Contin
$J$6 Advertising campaign Select 1 0 Contin
$J$7 Basic research Select 0 0 Contin
$J$8 Purchase new equipment Select 1 9.7222222222 Contin

Constraints
Cell Name Cell Value Formula Status Slack
$K$3 Limited warehouse expansion Year1Cos 9000 $K$3<=$B$9 Not Binding 1500
$L$3 Limited warehouse expansion Year4Cos 4861.1111111 $L$3<=$C$9 Not Binding 2138.8888889
$M$3 Limited warehouse expansion Year3Cos 8750 $M$3<=$D$9 Binding 0
Microsoft Excel 16.86 Sensitivity Report
Worksheet: [Integer Programming Examples Exhibits (2).xlsx]Sheet1
Report Created: 01/01/25 11:05:57 PM

Variable Cells
Final Reduced Objective Allowable
Cell Name Value Cost Coefficient Increase
$J$3 Limited warehouse expansion Select 0 -9333.3333333 4000 9333.3333333
$J$4 Extensive warehouse expansion Select 0 -5666.6666667 6000 5666.6666667
$J$5 Test market new product Select 0 -6166.6666667 10500 6166.6666667
$J$6 Advertising campaign Select 0 -2000 4000 2000
$J$7 Basic research Select 0 -5333.3333333 8000 5333.3333333
$J$8 Purchase new equipment Select 9.7222222222 0 3000 1E+030

Constraints
Final Shadow Constraint Allowable
Cell Name Value Price R.H. Side Increase
$K$3 Limited warehouse expansion Year1Cos 9000 0 10500 1E+030
$L$3 Limited warehouse expansion Year4Cos 4861.1111111 0 7000 1E+030
$M$3 Limited warehouse expansion Year3Cos 8750 3.3333333333 8750 3850
Allowable
Decrease
1E+030
1E+030
1E+030
1E+030
1E+030
1000

Allowable
Decrease
1500
2138.8888889
8750
Microsoft Excel 16.86 Limits Report
Worksheet: [Integer Programming Examples Exhibits (2).xlsx]Sheet1
Report Created: 01/01/25 11:05:58 PM

Objective
Cell Name Value
$B$14 NetNPV Year 1 29166.666667

Variable Lower Objective Upper Objective

Cell Name Value Limit Result Limit Result
$J$3 Limited warehouse expansion Select 0 0 85 250 18835
$J$4 Extensive warehouse expansion Selec 0 0 110 398.5 20035
$J$5 Test market new product Select 0 0 125 597 21020
$J$6 Advertising campaign Select 0
$J$7 Basic research Select 0
$J$8 Purchase new equipment Select 9.7222222222
Capital Requirements ($)
Projects Year 1 Year 2 Year 3 NPV ($)
Limited ware 3,000 1,000 4,000 4,000
Extensive wa 2,500 3,500 3,500 6,000
Test market 6,000 4,000 5,000 10,500
Advertising 2,000 1,500 1,800 4,000
Basic researc 5,000 1,000 4,000 8,000
Purchase new 1,000 500 900 3,000
Capital Funds 10,500 7,000 8,750

NetNPV 17500
Projects Select Year1Cost Year4Cost Year3Cost
Limited ware 0 9000 6000 7700
Extensive wa 0
Test market 1
Advertising 1
Basic researc 0
Purchase new 1
Time Number of Tellers
9:00 A.M.-10:00 A.M. 6
10:00 A.M.-11:00 A.M. 4
11:00 A.M.-Noon 8
Noon-1:00 P.M. 10
1:00 P.M.-2:00 P.M. 9
2:00 P.M.-3:00 P.M. 6
3:00 P.M.-4:00 P.M. 4
4:00 P.M.-5:00 P.M. 7
5:00 P.M.-6:00 P.M. 6
6:00 P.M.-7:00 P.M. 6

Salary Full Time 105

Salary Part Time 32