Paper 1: Quantitative Methods- I (Mathematical Methods) Spreadsheets. ECONOMICS Module 38: Solving Linear Programming Problems using Spreadsheets

ECONOMICS

Module 38: Solving Linear Programming Problems using Spreadsheets

Subject

Economics

Paper No and Title Paper 1: Quantitative Methods- I (Mathematical Methods) Module No and Title Module 38: Solving Linear Programming Problems using

ECONOMICS

Module 38: Solving Linear Programming Problems using Spreadsheets

TABLE OF CONTENTS

1. Learning Outcomes

2. Introduction

3. Creating a Spreadsheet

4. Solving the Problem

5. Loose Ends / Problems associated with Spreadsheet

5.1. Infeasible Problems

ECONOMICS

Module 38: Solving Linear Programming Problems using Spreadsheets

1. Learning Outcomes

After studying this module, you shall be able to:

 Analyze linear problem with Excel

 Create Spreadsheets in Excel

 Solve LPPs using Excel

 Understand the problems associated with solving linear Problems with Spreadsheet.

2. Introduction

Excel is a widely used tool which enables us to solve linear programming problems. In this module we will learn to solve and analyze linear problem with the help of Excel. Though the question can easily be solved by using Simplex Method or graphically, we will show how to use Excel so that you are able to tackle problems where the graphical method or Simplex Method is impossible or not practical.

In order to solve LPP, we will make use of the solver tool found on Excel to solve problems. The capacity to solve linear programs is only available if you install the solver tool. Typically this feature is not pre-installed in Excel when it is first setup on your hard disk. To add this facility to your Tools menu you need to carry out the following steps (once-only):

1. Select the menu option Tools | Add_Ins (this may take some time to load the necessary file).

2. From the dialogue box presented check the box for Solver Add-In.

3. On clicking OK, it will enable you to access the Solver option from the new menu option Tools | Solver (which appears below Tools | Scenarios ...)

3. Creating a Spreadsheet

ECONOMICS

Module 38: Solving Linear Programming Problems using Spreadsheets

Let’s start by probing an Excel worksheet for the linear programming

In this worksheet we are trying to calculate what we wish to produce for the linear programming as stated above.

ECONOMICS

Module 38: Solving Linear Programming Problems using Spreadsheets

4. Solving the Linear Problem in Excel

The worksheet makes use of Excel’s Solver Add-In in order to find the key to the linear programming problem. Installation of Solver Add-In in Excel is a pre-requisite to create a worksheet. Once that is ensured, worksheet as shown above can be created.

Listed below are the steps to show how to load the Solver Add-In for Excel 2007. A different sequence may be followed in other versions of Excel. For assistance in loading the Solver Add-In in other versions, one may use Excel’s Help Utility with the term “solver”.

1. To load the Solver Add-In, click on the Office button in the upper left corner of Excel. 2. At the bottom of the menu that appears Select Excel Options. This will open the Excel options box.

ECONOMICS

Module 38: Solving Linear Programming Problems using Spreadsheets

4. If Solver Add-In listed appears, then click OK at the bottom of the box and move to step 6. Here the Solver Add-In is pre-loaded.

ECONOMICS

Module 38: Solving Linear Programming Problems using Spreadsheets

6. We are now ready to create the worksheet that we’ll use to solve the linear programming problem. Enter the text shown below in the individual cells.

ECONOMICS

Module 38: Solving Linear Programming Problems using Spreadsheets

The cursor will transform to a double headed arrow. Left click, hold and drag the cursor till the column width is as much as you desire it to be. You may repeat this process till all column are adjusted to their desired widths

7. Enter the coefficients of the objective function in the row labeled “Profit”.

8. Enter the coefficients of the constraints in the rows labeled as “Capacity Constraint”, “Cutting Constraint”, and “Assembly and Finishing Constraint”.

9. Enter the values of right hand sides for each constraint in the appropriate row in the column labeled “RHS”.

10. In the row labeled as “Solution Values”, enter any non-negative number as the initial guess for the solution. In this worksheet we are using the values (x, y) = (0,0) for simplicity. The cells, in which we are entering the guesses in, i.e. B9 and C9 as per our worksheet, are termed as the changing cells. The values so put in are used to evaluate the objective function and left hand side of the constraints.

ECONOMICS

Module 38: Solving Linear Programming Problems using Spreadsheets

12. Cell D4 is called the Target call because in this cell we want to calculate the value of the objective at the solution values in cells B9 and C9. Place your cursor in the target cell.

In the formula area, type =SUMPRODUCT($B$9:$C$9,B4:C4) and press Enter on the

keyboard. The SUMPRODUCT function multiplies corresponding cells and sums up the results. In this case, the solution values in B9 and C9 are multiplied times the objective function

coefficients in B4 and C4. The summation of these products is placed in the target cell. 13. The dollars signs which are used in step 12 are known as absolute references. Absolute references are useful when we make use of fills to create entries in cells. Place your cursor active on cell D4 again. At the lower right hand corner of the cell, you will observe the darker box. This box is termed as the fill handle. Move your cursor over this box and you’ll observe that it

ECONOMICS

Module 38: Solving Linear Programming Problems using Spreadsheets

This fills the cells D5 through D7 with entries based on the formula that was entered in cell D4. If we click on cells D4 through D7 and examine the formula bar, we see a pattern as image below.

In each of the cells, you will observe that one of the factors of the product always appears from the solution values in cells B9 and C9. And the other factor in the product appears from the cells in the column B and C on the same row. Cells that are fixed with respect to a certain location are called absolute references and the dollar sign is used before them. Whereas cells which are located with respect to another cell are called relative references.

When we make use of fill feature of Excel to enter the cell values, the values so filled are attached with relative references as per their location. In this worksheet, second part of each of the product appears from the cells immediately to the left and on the same row. The other part of the product is fixed by absolute references in B9 and C9.

ECONOMICS

Module 38: Solving Linear Programming Problems using Spreadsheets

15. Hit Enter on the keyboard in order to compute the slack for the capacity constraint. The value so obtained is the difference between the right hand side and the left hand side of the capacity constraint.

16. Again Left click the mouse in cell G5. Using the fill handle enter the values in cells G6 and G7.

ECONOMICS

Module 38: Solving Linear Programming Problems using Spreadsheets

If there is any discrepancy of correct values in any of the cells, examine the contents of that cell and make changes to correct the entry or formula in the cell. Once the worksheet is computing the proper values when the changing cells are varied, we can make use of the Solver Add-In to work out the solution to the linear programming problem.

18. At the top of the window, Click on the Data tab. 19. In the Analysis panel, click in the Solver button.

ECONOMICS

Module 38: Solving Linear Programming Problems using Spreadsheets

20. Before entering any values into the Solver Parameters box, we have to set some options. Click on the Options button.

ECONOMICS

Module 38: Solving Linear Programming Problems using Spreadsheets

ECONOMICS

Module 38: Solving Linear Programming Problems using Spreadsheets

23. To fill up the constraints in the Solver, under the “Subject to the Constraints” heading- click on the Add button. This will open the Add Constraint box and will allow you to enter the inequality, left hand side, and right hand of the first constraint.

24. Place the cursor to activate the box below “Cell Reference”. Then click on the cell containing the left hand side of the capacity constraint, D5. Next move the cursor to the box below

ECONOMICS

Module 38: Solving Linear Programming Problems using Spreadsheets

Click the Add button.

25. Repeat step 24 for the other two constraints and the corresponding cells for the right and left hand sides. It is not essential to enter the non-negativity constraints. Once the constrictions have been filled in, click OK in the Add Constraints box. You’ll go back to the Solve Parameters box as seen below.

Now the linear programming problem has been completely entered into the Solver, and Excel is now enabled to solve it for us.

ECONOMICS

Module 38: Solving Linear Programming Problems using Spreadsheets

27. Highlight the Answer and Sensitivity Reports, and click OK button. There will be two new sheets that will be created in your Excel workspace, Answer Report, and Sensitivity Report. To see them you can click on the tab for either of these reports at the bottom of the Excel window.

5. Loose Ends / Problem associated with Spreadsheet

These are some of the problems associated with Solving Linear Problems with Spreadsheet. 5.1 Infeasible Problems

ECONOMICS

Module 38: Solving Linear Programming Problems using Spreadsheets

If your problem is unbounded, then Excel will tell you. The Solver Results Box appears with the statement that “The Set Target Cell Values do not converge." The spreadsheet then provides you with a finite guess for all of the variables, but the guess will have the property that you can increase (at least) one of the variables without bound; maintain feasibility by varying only the variables that have positive values; and increase the objective function without bound.

5.3 Mysterious Failures

Almost certainly something will go wrong at some point. Maybe it is the hard-ware. Maybe it is the software. Probably you made an incorrect assumption about how the program interprets the data you give it or you just typed a formula or cell address incorrectly. Identifying the problem is frustrating and often time consuming. If you obtain a numerical answer, then I urge you to examine it to see whether it looks sensible. It could be the answer to the wrong problem. If Solver does not work, then go over the data and formulas slowly and carefully.

Usually you will find a mistake. 5.4 Sensitivity Analysis

ECONOMICS

Module 38: Solving Linear Programming Problems using Spreadsheets

6. Summary

In this module, we have learnt that

 The best method to enter the question in Excel is to first list in a column the names of the objective function, decision variables and constraints

 How to Convert LPPs into Spreadsheets for use in Excel