Using Excel to solve linear programming problems
Technology can be used to solve a system of equations once the constraints and objective function have been defined. Excel has an add-in called the Solver which can be used to solve systems of equations or inequalities.
Consider this problem:
Example: A corporation plans on building a maximum of 11 new stores in a large city. They will build these stores in one of three sizes for each location a convenience store (open 24 hours), standard store, and an expanded-services store. The convenience store requires $4.125 million to build and 30 employees to operate. The standard store requires $8.25 million to build and 15 employees to operate. The expanded-services store requires $12.375 million to build and 45 employees to operate. The corporation can dedicate $82.5 million in construction capital, and 300 employees to staff the stores. On the average, the convenience store nets $1.2 million annually, the standard store nets $2 million annually, and the expanded-services store nets $2.6 million annually. How many of each should they build to maximize revenue?
Assign the variables:
x1 = number of convenience stores
x2 = number of standard stores
x3 = number of expanded services stores
Write the constraints:
a. x1 + x2 + x3 ≤ 11
b. 4.125 x1 + 8.25x2 + 12.375x3 ≤ 82.5
c. 30x1 + 15x2 + 45x3 ≤ 300
x1 ≥ 0, x2 ≥ 0, and x3 ≥ 0
Write the objective function: N(x1, x2, x3 ) = 1.2x1 + 2x2 + 2.6x3
(in millions)
We first need to open Excel and enter the data.
There are two methods one uses tables within the worksheet while the other uses only the constraints. The second method is preferred when we know the constraints since it is much faster!!!
Method One: Using Tables
Type in variable assignments at the top of the spreadsheet.
Assign decision variable cells.
Decision variable cells: D6, F6, and H6... [continues]
Technology can be used to solve a system of equations once the constraints and objective function have been defined. Excel has an add-in called the Solver which can be used to solve systems of equations or inequalities.
Consider this problem:
Example: A corporation plans on building a maximum of 11 new stores in a large city. They will build these stores in one of three sizes for each location a convenience store (open 24 hours), standard store, and an expanded-services store. The convenience store requires $4.125 million to build and 30 employees to operate. The standard store requires $8.25 million to build and 15 employees to operate. The expanded-services store requires $12.375 million to build and 45 employees to operate. The corporation can dedicate $82.5 million in construction capital, and 300 employees to staff the stores. On the average, the convenience store nets $1.2 million annually, the standard store nets $2 million annually, and the expanded-services store nets $2.6 million annually. How many of each should they build to maximize revenue?
Assign the variables:
x1 = number of convenience stores
x2 = number of standard stores
x3 = number of expanded services stores
Write the constraints:
a. x1 + x2 + x3 ≤ 11
b. 4.125 x1 + 8.25x2 + 12.375x3 ≤ 82.5
c. 30x1 + 15x2 + 45x3 ≤ 300
x1 ≥ 0, x2 ≥ 0, and x3 ≥ 0
Write the objective function: N(x1, x2, x3 ) = 1.2x1 + 2x2 + 2.6x3
(in millions)
We first need to open Excel and enter the data.
There are two methods one uses tables within the worksheet while the other uses only the constraints. The second method is preferred when we know the constraints since it is much faster!!!
Method One: Using Tables
Type in variable assignments at the top of the spreadsheet.
Assign decision variable cells.
Decision variable cells: D6, F6, and H6... [continues]
Cite This Essay
- APA
-
(2009, 03). Linear Programming., Corporation. StudyMode.com. Retrieved 03, 2009, from http://www.studymode.com/essays/Linear-Programming-Corporation-195234.html
- MLA
-
"Linear Programming., Corporation" StudyMode.com. 03 2009. 03 2009 <http://www.studymode.com/essays/Linear-Programming-Corporation-195234.html>.
- CHICAGO
-
"Linear Programming., Corporation." StudyMode.com. 03, 2009. Accessed 03, 2009. http://www.studymode.com/essays/Linear-Programming-Corporation-195234.html.
