This preview shows pages 1–3. Sign up to view the full content.
This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: ENGR62/MS&E111 Autumn 20032004 Prof. Ben Van Roy October 1, 2003 EXCEL SOLVER TUTORIAL This tutorial will introduce you to some essential features of Excel and its plug-in, Solver, that we will be using throughout ENGR62 to solve linear programs (LPs). You will learn how to represent LPs in an Excel worksheet, use the matrix functions SUMPRODUCT and MMULT , set up an LP in Solver, and generate various outputs from Solver. This quarter you will see many applications of Solver in class demonstrations and homework assignments. Keep this tutorial document as a reference as you review the class demos and as you formulate LPs in your homeworks. For additional Excel/Solver assistance, try consulting the Excel Help files or numerous online resources. Parts of this tutorial borrow from Prof. Veinotts detailed Formulating and Solving Linear Programs in Excel . 1 Setting Up Linear Programs In Excel Linear programs (LPs) can take many forms. In ENGR62 we will often be dealing with LPs that can be represented mathematically as maximize c T x subject to Ax b, where the matrices A , b , and c comprise the problem data . Our goal is to maximize the value of the objective function, c T x , by choosing an appropriate vector of decision variables, x . The matrices A and b specify a set of linear inequality constraints that reflect the requirements of the problem. EXCEL SOLVER TUTORIAL Page 2 of 6 a 11 a 12 a 13 a 21 a 22 a 23 a 31 a 32 a 33 a 41 a 42 a 43 a 51 a 52 a 53 Each matrix element occupies a cell in the worksheet. Entering Matrices into Excel It is easy to display the components of an LP in their natural way in Excel, i.e., as a range of cells. For example, if A is a 5 3 matrix, then each element of A can be entered into its corresponding cell in a 5 3 range of cells. Identify ranges of cells to contain the data matrices A , b , and c and enter them into the Excel worksheet. Do the same for the decision variables, x . To keep your worksheet organized, you might consider arranging the matrices as shown below. You are also encouraged to use labels and text to document your work. c T c T x A x Ax b A sample layout of LP matrices in an Excel worksheet. Naming Matrices in Excel Excel allows you to name ranges of cells in the worksheet. This feature is especially con- venient for doing matrix calculations and for setting up LPs in Solver. To name a range of cells, select the entire range with the mouse and use the Insert Name Define... menu....
View Full Document
This note was uploaded on 08/30/2011 for the course ECON 101 taught by Professor Rm during the Spring '11 term at Rochester.
- Spring '11