Linear Programmi
In this unit, we will focus on formulating and solving linear pr
First, we will set up the solver and define our mathematical m
Then, we will optimize our problem and interpret the results.
0. setup
1. model formulation
2. optimization
3. tornado diagram
ramming Solver in Exce
and solving linear problems using Excel.
our mathematical model.
nterpret the results.
n Excel
0. Setting up the E
To be able to use a solver in Excel in general, the correspond
This is done as follows:
1. Go to "File" > "Options" > "Add-Ins".
2. Choose "Excel Add-Ins" from the dropdown list next to "Manage" and
3. Check the box next to "Solver Add-In" and click "OK".
4. After activating the Solver Add-In, you'll find the "Solver" button in the
Once the solver add-in is activated, we can perform the optim
To do this, we first need to converted our problem into a mat
This is done in the next worksheet.
p the Excel LP-Solver
eral, the corresponding add-in must first be activated.
wn list next to "Manage" and then click "Go".
d click "OK".
find the "Solver" button in the "Analyze" section under the "Data" tab.
an perform the optimization.
r problem into a mathematical formulation.
lver
Microsoft Excel 16.0 Sensitivity Report
Worksheet: [2_Solver_LP_solution.xlsx]1_model_formulation
Report Created: 10.04.2025 14:02:14
Variable Cells
Final Reduced Objective
Cell Name Value Cost Coefficient
$E$40 X_hydrogen 2500 0 300
$E$41 X_Biogas 1250 0 400
Constraints
Final Shadow Constraint
Cell Name Value Price R.H. Side
$C$51 Pipeline capacity Used 40000 18.75 40000
$C$52 CO2 budget Used 40000 12.5 40000
$C$53 Capacity fuel cell Used 2500 0 3500
$C$54 Capacity gas turbine Used 1250 0 1500
$C$55 Energy generation from hydrogen Used 2500 0 0
$C$56 Energy generation from Biogas Used 1250 0 0
Allowable Allowable
Increase Decrease
300 180
600 200
Allowable Allowable
Increase Decrease
9600 8000
4000 20000
1E+030 1000
1E+030 250
2500 1E+030
1250 1E+030
1. Model formulation
In this worksheet, the real problem is transferred into a mathematical
it according to the objective.
Problem description:
FlexEnergy as a local energy supplier operates a hybrid powerplant utilizing a gas turbine (Biogas) a
pipeline with a given maximum capacity. At the point of use, the hydrogen is separated from bioga
operator's goal is to maximize their contribution margin by deciding how much hydrogen and biogas
considering the properties (CO2 intensity and capacity requirement), costs, and revenues of the diff
regarding the pipeline capacity and the limited CO2 budget.
To solve this problem, a corresponding objective function as well as variables an
Please, follow these steps:
1. Add formula to compute contribution margin to worksheet (E46)
2. Complete right hand side according to data of the constraints (use reference to data)
3. Finish by adding the left hand side of the constraints (use formulas)
4. Check your formulas by changing the variable values
5. Start and configure solver (worksheet 2_optimization
Symbols and data
Pipeline capacity
CO2 budget
Pipeline capacity requirements
CO2 intensity
Unit revenue
Variable unit costs
Capacity
Decision variables
Energy generation from fuel cell (hydrogen)
Energy generation from gas turbine (biogas)
Objective function
Contribution margin
Constraints
Pipeline capacity
CO2 budget
Capacity fuel cell
Capacity gas turbine
Energy generation from hydrogen
Energy generation from Biogas
ulation
into a mathematical formulation in order to subsequently optimize
tilizing a gas turbine (Biogas) and a fuel cell (Hydrogen). Both gases are supplied using a shared
ogen is separated from biogas again through an innovative membrane technology. The
ow much hydrogen and biogas they actually want to use for energy generation. This requires
costs, and revenues of the different substances, while also taking into account the restrictions
n as well as variables and constraints must be formulated.
ference to data)
Symbol Unit Value
cap [m³] 40000
b [t CO2] 40000
Symbol Unit Hydrogen Biogas
req [m³/MWh] 12 8
e [t CO2 / MWh] 6 20
r [€/MWh] 550 550
c [€/MWh] 250 150
y [MWh] 3500 1500
Symbol Unit Value
X_h MWh 2500
X_b MWh 1250
Symbol Unit Value
P €/period 1250000.00
Used Limit
40000 ≤ 40000
40000 ≤ 40000
2500 ≤ 3500
1250 ≤ 1500
2500 ≥ 0
1250 ≥ 0
2. Optimization
To carry out an optimization using a solver in Excel, please fo
1. Configure the Solver: Click on the "Solver" button, and a dialog box
Set "Objective Cell" to the cell containing your objective function.
Choose "Maximize" or "Minimize" depending on whether you want to m
Set "Variable Cells" to the cells containing your model's variables.
Add the constraints by clicking "Add" and entering the respective cells
2. Select the Solver Method: Choose "Simplex-LP" as the solver meth
3. Run the Solver: Click "Solve" to start the solver. If your model has a
4. Accept the Solution: Click "OK" to accept the solution and apply it t
Hint: Before you accept the result, click on "Sensitivity" below "Reports". A sensitivity re
Questions:
1. Would you prefer a greater turbine or a gre
Does not make a difference, as utilization is below 100%.
2. What happens if additional CO2 certifikates
The objective value increases according to sensitivity, but is limited by
3. Should we favor increasing our CO2 budge
Pipeline capacity, as the shadow price is higher.
tion
er in Excel, please follow these steps:
ver" button, and a dialog box will open. Fill in the fields as follows:
your objective function.
ng on whether you want to maximize or minimize.
your model's variables.
entering the respective cells and conditions.
mplex-LP" as the solver method from the dropdown list.
e solver. If your model has a solution, Excel will display it; otherwise, it will show an appropriate message.
ept the solution and apply it to your Excel worksheet.
ow "Reports". A sensitivity report is then also given when you subsequently accept.
er turbine or a greater fuel cell?
is below 100%.
al CO2 certifikates are purchased?
o sensitivity, but is limited by the other constraints.
ng our CO2 budget or pipeline capacity by one un
n appropriate message.
3. Tornado diagram
Create a tornado diagram for sensitivity analysis based on th
A tornado diagram is a graphical representation used in sensitivity analysis to display
input variables on an output. It features horizontal bars extending from a central vert
of each bar indicating the relative influence of each variable. The bars are arranged v
sensitive variable at the top, resembling the shape of a tornado.
Copy the sensitivity report here:
Variable Cells
Final
Cell Name Value
$E$34 X_hydrogen 2500
$E$35 X_Biogas 1250
Constraints
Final
Cell Name Value
$C$45 Pipeline capacity Used 40000
$C$46 CO2 budget Used 40000
$C$47 Capacity fuel cell Used 2500
$C$48 Capacity gas turbine Used 1250
$C$49 Energy generation from hydrogen Used 2500
$C$50 Energy generation from Biogas Used 1250
Questions:
1. Would fluctuating prices be a problem for FlexEn
Small to moderate fluctuations won't affect the decision, but highly volatile prices
2. Is the present decision robust against external ch
Besides significant price fluctuations, changes in CO2 regulations could shift focu
technological advancements would have little immediate impact.
diagram
ity analysis based on the sensitivity report.
d in sensitivity analysis to display the impact of varying
ars extending from a central vertical axis, with the length
variable. The bars are arranged vertically with the most
of a tornado.
Reduced Objective Allowable Allowable
Cost Coefficient Increase Decrease
0 300 300 180
0 400 600 200
Shadow Constraint Allowable Allowable
Price R.H. Side Increase Decrease
18.75 40000 9600 8000
12.5 40000 4000 20000
0 3500 1E+030 1000
0 1500 1E+030 250
0 0 2500 1E+030
0 0 1250 1E+030
a problem for FlexEnergy's decision?
decision, but highly volatile prices could cause planning challenges.
t against external changes?
n CO2 regulations could shift focus towards emission intensity, while
mmediate impact.
Sensitivity: contribution margin
-300 -200 -100 0 100 200 300 400 500 600 700
X_Biogas
X_hydrogen
X_hydrogen -180 0 300
X_Biogas -200 0 600
Pipeline capacity Used -0.2 0 0.24
CO2 budget Used -0.5 0 0.1
Capacity fuel cell Used -0.28571429 0 1
Capacity gas turbine Used -0.16666667 0 1
Sensitivity: contribution margin
-1 -0.8 -0.6 -0.4 -0.2 0 0.2 0.4 0.6 0.8 1
Capacity gas turbine Used
Capacity fuel cell Used
CO2 budget Used
Pipeline capacity Used
