linear programming
Spreadsheet Modeling and Excel Solver A mathematical model implemented in a spreadsheet is called a spreadsheet model. Major spreadsheet packages come with a built-in optimization tool called Solver. Now we demonstrate how to use Excel spreadsheet modeling and Solver to find the optimal solution of optimization problems. If the model has two variables, the graphical method can be used to solve the model. Very few real world problems involve only two variables. For problems with more than two variables, we need to use complex techniques and tedious calculations to find the optimal solution. The spreadsheet and solver approach makes solving optimization problems a fairly simple task and it is more useful for students who do not have strong mathematics background. The first step is to organize the spreadsheet to represent the model. We use separate cells to represent decision variables, create a formula in a cell to represent the objective function and create a formula in a cell for each constraint left hand side. Once the model is implemented in a spreadsheet, next step is to use the Solver to find the solution. In the Solver, we need to identify the locations (cells) of objective function, decision variables, nature of the objective function (maximize/minimize) and constraints. Example One (Linear model): Investment Problem Our first example illustrates how to allocate money to different bonds to maximize the total return (Ragsdale 2011, p. 121). A trust office at the Blacksburg National Bank needs to determine how to invest $100,000 in following collection of bonds to maximize the annual return. Bond Annual Return Maturity Risk Tax-Free A B C D E 9.5% 8.0% 9.0% 9.0% 9.0% Long Short Long Long Short High Low Low High High Yes Yes No Yes No The officer wants to invest at least 50% of the money in short term issues and no more than 50% in high-risk issues. At least 30% of the funds should go in tax-free investments, and at least 40% of the total return should be