We use cookies to give you the best experience possible. By continuing we’ll assume you’re on board with our cookie policy

Solver: Constraint Satisfaction and Dialogue Box

essay
The whole doc is available only for registered users

A limited time offer! Get a custom sample essay written according to your requirements urgent 3h delivery guaranteed

Order Now

1. The Product Mix Example
The Outdoor Furniture Corporation manufactures two products: benches and picnic tables for use in yards and parks. The firm has two main resources: its carpenters (labor) and a supply of redwood for use in the furniture. During the next production period, 1200 hours of manpower are available under a union agreement. The firm also has a stock of 5000 pounds of quality redwood. Each bench that Outdoor Furniture produces requires 4 labor hours and 10 pounds of redwood; each picnic table takes 7 labor hours and 35 pounds of redwood. Completed benches yield a profit of $9 each, and tables a profit of $20 each. We formulated the following linear program to solve this problem: Decision Variables:

Objective Function:

Constraints:

We will now solve this LP using the Excel Solver.

Getting Started

To begin using Excel, double-click on the Excel icon. Once Excel has loaded, enter the input data and construct relationships among data elements in a readable, easy to understand way. When building this foundation for your model, think ahead about the optimization model you will be developing. Make sure there is a cell in your spreadsheet for each of the following: • the quantity you wish to maximize or minimize • every decision variable • every quantity that you might want to constrain If you don’t have any particular initial values you want to enter for your decision variables, you can start by just entering a value of 0 in each decision variable cell.

The cell below “Total” and the cells below “LHS” in the spreadsheet are defined by using the SUMPRODUCT function in the manner seen below.

If you are not familiar with “sumproduct”, you can use “= B3*B5+C3*C5” instead of “sumproduct(B3:C3,B5:C5)”, and so on. See example below. Both serve the same purpose.

Constructing an Optimization Model Using Solver

Once you are satisfied with the basic spreadsheet model you have built, you are ready to build an optimization model using Solver. To begin using Solver, • click on “Tools” and select “Solver”.

A gray dialogue box will appear. You will build the constrained optimization model using this dialogue box.

Selecting the Cell to Optimize

To select the cell you wish to optimize, look at the “Set Cell” window within the dialogue box. Erase (or simply drag the cursor over) everything that is in that window. Then, either • • • type the address of the cell you wish to optimize, or click on the cell you wish to optimize. Choose either “Max” or “Min” depending on whether the objective is to maximize or minimize the target cell.

If you wish to click on the optimization cell but the dialogue box is in the way, you can move the dialogue box by positioning the arrow on the title bar at the top of the box and dragging it out of the way.

Identifying Decision Variable Cells

Move the cursor to the “By Changing Cells” window within the dialogue box. Again, erase anything that may be there (unless you put it there before and want to keep it). You now must tell Excel which cells are decision variables—i.e., which cells Excel is allowed to change when trying to optimize. To do this: • type the addresses of every cell you wish to treat as a decision variable, separating them by commas, or • drag the cursor across all cells you wish to treat as decision variables.

If you wish to use the “dragging” method, but the decision variables to not all lie in a connected rectangle in the spreadsheet you can “drag” them in one group at a time: • drag the cursor across one group of decision variables, • put a comma after that group in the “By Changing Cells” window, • drag the cursor across the next group of decision variables, • etc….

Defining Constraints

To begin entering constraints, click on the “Add” button to the right of the constraints window. A new dialogue box will appear. The cursor will be in the “Cell Reference” window within this dialogue box. • • Type the cell address that contains the quantity you want to constrain, or click on the cell that contains the quantity you want to constrain.

Again, if the dialogue box is in the way, you can move it by dragging the bar at the top of the box. The default inequality that first appears for a constraint is “

Related Topics

We can write a custom essay

According to Your Specific Requirements

Order an essay
icon
300+
Materials Daily
icon
100,000+ Subjects
2000+ Topics
icon
Free Plagiarism
Checker
icon
All Materials
are Cataloged Well

Sorry, but copying text is forbidden on this website. If you need this or any other sample, we can send it to you via email.

By clicking "SEND", you agree to our terms of service and privacy policy. We'll occasionally send you account related and promo emails.
Sorry, but only registered users have full access

How about getting this access
immediately?

Your Answer Is Very Helpful For Us
Thank You A Lot!

logo

Emma Taylor

online

Hi there!
Would you like to get such a paper?
How about getting a customized one?

Can't find What you were Looking for?

Get access to our huge, continuously updated knowledge base

The next update will be in:
14 : 59 : 59