Solver: Constraint Satisfaction and Dialogue Box

Good Essays
766 Words
Grammar
Plagiarism
Writing
Score
Solver: Constraint Satisfaction and Dialogue Box
Linear Programming: Using the Excel Solver
Outline:
We will use Microsoft Excel Solver to solve the four LP examples discussed in last class.

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.

1.1

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.

1.2

Constructing an

You May Also Find These Documents Helpful

• Good Essays

7. A plant manager is attempting to determine the production schedule of various products to maximize profit. Assume that a machine hour constraint is binding. If the original amount of machine hours available is 200 minutes., and the range of feasibility is from 130 minutes to 340 minutes, providing two additional machine hours will result in the:…

• 1177 Words
• 4 Pages
Good Essays
• Good Essays

You can use numbers directly in a formula, but it’s much better to use the references of the cells (A1, B1 & C1 etc.) containing the numbers you want to use.…

• 2823 Words
• 12 Pages
Good Essays
• Satisfactory Essays

Busn312 Hw3A

• 727 Words
• 3 Pages

Linear programming problems have a.|linear objective functions, non-linear constraints.| b.|non-linear objective functions, non-linear constraints.| c.|non-linear objective functions, linear constraints.| d.|linear objective functions, linear constraints.| ____C 7. The first step in formulating a linear programming problem is a.|Identify any upper or lower bounds on the decision variables.| b.|State the constraints as linear combinations of the decision variables.| c.|Understand the problem.| d.|Identify the decision variables.| e.|State the objective…

• 727 Words
• 3 Pages
Satisfactory Essays
• Powerful Essays

| In cell J5, insert a formula that will add the numbers in cells F5:I5. Use the fill handle to copy the formula into cells J6 through J8.Hint: To insert the formula, in cell J5, type =F5+G5+H5+I5 and then press ENTER. To copy the formula, use the fill handle in the bottom right corner of the cell.…

• 1215 Words
• 5 Pages
Powerful Essays
• Satisfactory Essays

2) Design a Do-While loop that asks the user to enter two numbers. The numbers should be added and the sum displayed. The loop should ask the user if he or she wishes to perform the operation again. If so, the loop should repeat, otherwise it should terminate…

• 1058 Words
• 5 Pages
Satisfactory Essays
• Good Essays

Ict Cousework

• 1730 Words
• 7 Pages

| To automatically add up the total money made and total money spent to give me the profit or loss.…

• 1730 Words
• 7 Pages
Good Essays
• Good Essays

In cell F2, enter a reference to the total income formula from column C. In cell G2, enter a reference to the total expenses formula from column C. And in cell H2, enter a reference to the balance formula from column C.…

• 660 Words
• 3 Pages
Good Essays
• Satisfactory Essays

The linear programming results show that Julie will have a profit of \$2250 minus the \$1000 for booth rent for the month which will leave \$1150. She only wants to make a profit of \$1000 therefore she has at least \$150 to keep \$50 to purchase more sandwiches and \$100 to pay a friend. After the purchase of more sandwiches Julie made need some additional help. Therefore it will be feasible to hire a friend for extra help which could help to generate more profit.…

• 364 Words
• 2 Pages
Satisfactory Essays
• Good Essays

Learning Excel

• 617 Words
• 3 Pages

References: Mayes, T., & Shank, T. (2012). Financial analysis with Microsoft Excel. (6th Ed.). Mason, OH: South-Western Cengage Learning.…

• 617 Words
• 3 Pages
Good Essays
• Powerful Essays

Excel contains a tool called the Asolver@ that lets you maximize or minimize functions subject to general constraints. We will use this tool to compute the global minimum variance portfolio and the tangency portfolio for the three-firm example (see the spreadsheet 3firm.xls). The spreadsheet for this tutorial is called solverex.xls. The data for this example are given in the following table…

• 2737 Words
• 11 Pages
Powerful Essays
• Satisfactory Essays

Shelby Shelving LP Decision Variables: Let S = # of Model S shelves to produce, and LX = # of Model LX shelves to produce. Shelby Shelving Linear Program 260 S 245 LX 385;000 (Net Proﬁt) (S assembly) (LX assembly) (Stamping) (Nonnegativity) 0:3 S (Forming) 0:25 S S LX 0:3 LX 0:5 LX S; LX 1900 1400 800 800 0…

• 473 Words
• 2 Pages
Satisfactory Essays
• Satisfactory Essays

g. Create a formula in cell B14 that determines whether you will make a profit or loss, then copy the formula into cell C14.…

• 334 Words
• 1 Page
Satisfactory Essays
• Powerful Essays

Let's add two numbers to create a third, 128+345=473. In Excel, this would be expressed by the formula, =128+345, as shown below.…

• 3776 Words
• 16 Pages
Powerful Essays
• Good Essays

For example, if I wanted to calculate the total cost of Tomatoes for the month of January, I would simply need to move to Cell C30, which is the cell below C29, and multiply C10 (which is the quantity of tomatoes and is the cell below C9, the quantity of oil) by C20, (which is the price of tomatoes and the cell below C19, the unit price of oil)…

• 1009 Words
• 5 Pages
Good Essays
• Good Essays

Roll-up: A roll-up involves summarizing the data along a dimension. The summarization rule might be computing totals along a hierarchy or applying a set of formulas such as "profit = sales - expenses".[5]…

• 337 Words
• 2 Pages
Good Essays