Microsoft Excel 16.
0 Answer Report
Worksheet: [Walker Wines - Student.xlsx]Model
Report Created: 25/07/2019 2:41:24 PM
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0.61 Seconds.
Iterations: 5 Subproblems: 0
Solver Options
Max Time 100 sec, Iterations 1000, Precision 0.00000001
Max Subproblems 1000, Max Integer Sols 1000, Integer Tolerance 5%, Assume NonNegative
Objective Cell (Max)
Cell Name Original Value Final Value
$B$28 Profit Total 0 131814.102564
Variable Cells
Cell Name Original Value Final Value Integer
$B$17 Decision Variable Shiraz 0 12871.7948718 Contin
$C$17 Decision Variable Merlot 0 19307.6923077 Contin
$D$17 Decision Variable Advt. Shriaz 0 2374.35897436 Contin
$E$17 Decision Variable Advt. Merlot 0 0 Contin
Constraints
Cell Name Cell Value Formula Status Slack
$F$21 Budget_Utilized 50000 $F$21<=$H$21 Binding 0
$F$22 Min Req Contract -9.094947E-13 $F$22>=$H$22 Binding 0
$F$23 Max Req Contract -9653.84615385 $F$23<=0 Not Binding 9653.8461538
$F$24 Demand Shiraz 1000 $F$24<=$H$24 Binding 0
$F$25 Demand Merlot 0 $F$25<=$H$25 Not Binding 2000
Microsoft Excel 16.0 Sensitivity Report
Worksheet: [Walker Wines - Student.xlsx]Model
Report Created: 25/07/2019 2:41:24 PM
Variable Cells
Final Reduced Objective Allowable
Cell Name Value Cost Coefficient Increase
$B$17 Decision Variable Shiraz 12871.7948718 0 4.65 0.5
$C$17 Decision Variable Merlot 19307.6923077 0 3.85 1E+030
$D$17 Decision Variable Advt. Shriaz 2374.35897436 0 -1 2.5
$E$17 Decision Variable Advt. Merlot 0 -3.6217948718 -1 3.6217948718
Constraints
Final Shadow Constraint Allowable
Cell Name Value Price R.H. Side Increase
$F$21 Budget_Utilized 50000 2.6217948718 50000 1E+030
$F$22 Min Req Contract -9.094947E-13 -0.4487179487 0 8964.2857143
$F$23 Max Req Contract -9653.8461538 0 0 1E+030
$F$24 Demand Shiraz 1000 0.7243589744 1000 12513.513514
$F$25 Demand Merlot 0 0 2000 1E+030
Allowable
Decrease
10.225
0.3888888889
51.125
1E+030
Allowable
Decrease
46300
13228.571429
9653.8461538
251000
2000
Microsoft Excel 16.0 Limits Report
Worksheet: [Walker Wines - Student.xlsx]Model
Report Created: 25/07/2019 2:41:25 PM
Objective
Cell Name Value
$B$28 Profit Total 131814.10256
Variable Lower Objective Upper
Cell Name Value Limit Result Limit
$B$17 Decision Variable Shiraz 12871.794872 12871.794872 131814.10256 12871.7948718
$C$17 Decision Variable Merlot 19307.692308 5516.4835165 78717.948718 19307.6923077
$D$17 Decision Variable Advt. Shriaz 2374.3589744 2374.3589744 131814.10256 2374.35897436
$E$17 Decision Variable Advt. Merlot 0 0 131814.10256 -7.275958E-12
Objective
Result
131814.10256
131814.10256
131814.10256
131814.10256
Solver Results Spreadsheet
A B C D E F
1 Walker Wines
2
3 Data
4 Shiraz Merlot
5 Cost/bottle $ 1.60 $ 1.40
6 Price/bottle $ 6.25 $ 5.25
7
8 Base demand 1,000.00 2,000.00
9 Increase/$1 Adv. 5.00 8.00
10 Min. percent requirement 40%
11 Max. percent limitation 70%
12
13 Total Budget $ 50,000.00
14
15 Model
16 Shiraz Merlot Advt. Shriaz Advt. Merlot
17 Decision Variable 12871.7948718 19307.6923 2374.358974 0
18 Objective Parameters $ 4.65 $ 3.85 -1 -1
19
20 Contraints
21 Budgetary $ 1.60 $ 1.40 1 1 50000
22 Min Req Contract 60% -40% 0.00 0.00 0
23 Max Req Contract 30% -70% 0 0 -9653.846154
24 Demand Shiraz 1 0 -5.00 0 1000
25 Demand Merlot -8.00 0
26
27 Total
28 Profit 131814.102564
Solver Results Spreadsheet
G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 Sign RHS
21 <= 50,000
22 >= 0
23 <= 0
24 <= 1,000
25 <= 2,000
26
27
28
