ExcelSolverTutorial
Tutorial: Using Excel for Linear
Optimization Problems
Part 1: Organize Your Information
There are three categories of information needed for solving an optimization problem in Excel: an Objective Function, Decision Variables, and
Constraints. It is simplest to organize these on paper before you start working with the spreadsheet. For tutorial purposes we will follow an example, “The
Cargo Problem”, from start to finish:
A shipping company has the capacity to move 100 tons of cargo per day by air. The company charges $250/ton for air freight.
Besides the weight constraint, the company can only move 50,000 ft3 of cargo per day because of limited volume of aircraft storage compartments. The following amounts of cargo are available for shipping each day:
Cargo
Weight (tons)
Volume (ft3/ton)
1
30
550
2
40
800
3
50
400
Maximize the profit for the shipping company.
¾ Set up this problem:
Objective Function: Profit = 250*(Cargo1 + Cargo2+ Cargo3) ($/week)
Decision Variables:
Cargo 1 (weight in tons)
Cargo 2
Cargo 3
Constraints:
Weight:
Cargo1 + Cargo2 + Cargo3 ≤ 100
Volume:
550*Cargo1 + 800*Cargo2 +400*Cargo3 ≤ 50000
Amount 1: Cargo1 ≤ 30
Amount 2: Cargo2 ≤ 40
Amount 3: Cargo3 ≤ 50
*Do not forget to include the trivial constraints!
Trivial 1:
Cargo1 ≥ 0
Trivial 2:
Cargo2 ≥ 0
Trivial 3:
Cargo3 ≥ 0
Part 2: Set Up the Problem in the Excel Spreadsheet
Solver is an Add-in for Microsoft Excel which is typically not enabled during the initial installation of Excel. If ‘Solver’ does not appear on the ‘Tools’ menu in
Excel, then you need to enable it as follows:
¾ Select the ‘Tools’ menu in Excel, and then choose ‘Add-ins’. Check the box titled ‘Solver Add-ins’ and then click ‘OK’. ‘Solver’ should then appear as an item on the Tools menu.
In order to enter your data and work with it in the most straightforward way it is best to stay organized by using consistent labels and columns. Refer to the following figure as one example for setting up this problem in a spreadsheet.
¾ Enter
Optimization Problems
Part 1: Organize Your Information
There are three categories of information needed for solving an optimization problem in Excel: an Objective Function, Decision Variables, and
Constraints. It is simplest to organize these on paper before you start working with the spreadsheet. For tutorial purposes we will follow an example, “The
Cargo Problem”, from start to finish:
A shipping company has the capacity to move 100 tons of cargo per day by air. The company charges $250/ton for air freight.
Besides the weight constraint, the company can only move 50,000 ft3 of cargo per day because of limited volume of aircraft storage compartments. The following amounts of cargo are available for shipping each day:
Cargo
Weight (tons)
Volume (ft3/ton)
1
30
550
2
40
800
3
50
400
Maximize the profit for the shipping company.
¾ Set up this problem:
Objective Function: Profit = 250*(Cargo1 + Cargo2+ Cargo3) ($/week)
Decision Variables:
Cargo 1 (weight in tons)
Cargo 2
Cargo 3
Constraints:
Weight:
Cargo1 + Cargo2 + Cargo3 ≤ 100
Volume:
550*Cargo1 + 800*Cargo2 +400*Cargo3 ≤ 50000
Amount 1: Cargo1 ≤ 30
Amount 2: Cargo2 ≤ 40
Amount 3: Cargo3 ≤ 50
*Do not forget to include the trivial constraints!
Trivial 1:
Cargo1 ≥ 0
Trivial 2:
Cargo2 ≥ 0
Trivial 3:
Cargo3 ≥ 0
Part 2: Set Up the Problem in the Excel Spreadsheet
Solver is an Add-in for Microsoft Excel which is typically not enabled during the initial installation of Excel. If ‘Solver’ does not appear on the ‘Tools’ menu in
Excel, then you need to enable it as follows:
¾ Select the ‘Tools’ menu in Excel, and then choose ‘Add-ins’. Check the box titled ‘Solver Add-ins’ and then click ‘OK’. ‘Solver’ should then appear as an item on the Tools menu.
In order to enter your data and work with it in the most straightforward way it is best to stay organized by using consistent labels and columns. Refer to the following figure as one example for setting up this problem in a spreadsheet.
¾ Enter