|
Question 1198171: Consider a chef who must plan a banquet. Possible menu items and the cost data presented below:
Item Cost ($) Per Serving
Appetizers
A 0.25
B 0.30
C 0.35
Salads
X 0.25
Y 0.45
Vegetables
P 0.20
Q 0.15
R 0.40
Main Meals
1 1.25
2 2.50
3 3.00
The banquet is to consist of:
A. at least one appetizer;
B. exactly one salad;
C. at least one but no more than two vegetables; and
D. exactly one main meal.
The chef has been advised to select items from the menu which will minimize
total cost of the banquet. All decision variables take the form of the binary
numbers only.
Instructions:
i. Write mathematical form of the inequalities of all the constraints. (5)
ii. Write mathematical form objective function (5)
iii. Tabulate your problem into the solver (5)
iv. Using Excel Solver, solve the problem and interpret your solution (5)
Answer by onyulee(41)   (Show Source):
You can put this solution on YOUR website! Certainly, let's formulate the banquet planning problem and solve it using Excel Solver.
**i. Mathematical Inequalities (Constraints)**
* **Appetizer Constraint:**
* A + B + C ≥ 1
* **Salad Constraint:**
* X + Y = 1
* **Vegetable Constraints:**
* P + Q + R ≥ 1
* P + Q + R ≤ 2
* **Main Meal Constraint:**
* 1 + 2 + 3 = 1
* **Binary Variables:**
* A, B, C, X, Y, P, Q, R, 1, 2, 3 ∈ {0, 1}
**ii. Objective Function (Minimize Cost)**
* Cost = 0.25A + 0.30B + 0.35C + 0.25X + 0.45Y + 0.20P + 0.15Q + 0.40R + 1.25*1 + 2.50*2 + 3.00*3
**iii. Tabulation in Excel Solver**
1. **Set up the Excel Sheet:**
* Create a table with the following columns:
* Item
* Cost
* Decision Variable (Binary)
* List all items (A, B, C, X, Y, P, Q, R, 1, 2, 3) and their corresponding costs.
* In the "Decision Variable" column, enter a cell for each item (e.g., B2 for item A, B3 for item B, etc.).
2. **Define Decision Variables:**
* In the "Decision Variable" cells, enter 0 or 1.
3. **Calculate Total Cost:**
* In a separate cell, use the SUMPRODUCT function to calculate the total cost based on the decision variables and costs:
* `=SUMPRODUCT(B2:B11,C2:C11)`
4. **Set up Constraints:**
* In separate cells, create formulas to represent each constraint:
* Appetizer: `=SUM(B2:B4)`
* Salad: `=SUM(B5:B6)`
* Vegetables: `=SUM(B7:B9)`
* Main Meal: `=SUM(B10:B12)`
5. **Open Solver:**
* Go to "Data" -> "Solver".
6. **Set Objective:**
* Set the "Set Objective" cell to the cell containing the total cost formula.
7. **Select "Min"** to minimize the cost.
8. **By Changing Variable Cells:**
* Select the range of cells containing the decision variables (e.g., B2:B12).
9. **Add Constraints:**
* Click "Add" to add each constraint:
* Appetizer: `=SUM(B2:B4)>=1`
* Salad: `=SUM(B5:B6)=1`
* Vegetables: `=SUM(B7:B9)>=1`
* Vegetables: `=SUM(B7:B9)<=2`
* Main Meal: `=SUM(B10:B12)=1`
* Binary Constraints:
* For each decision variable cell, add a constraint: `B2=0` or `B2=1`
10. **Select Solving Method:**
* Choose "Simplex LP" or "GRG Nonlinear" as the solving method.
11. **Solve:**
* Click "Solve".
**iv. Interpret Solution**
* Solver will find the optimal combination of menu items that minimizes the total cost while satisfying all the constraints.
* The "Decision Variable" cells will indicate which items to include in the banquet (1 = include, 0 = exclude).
* The "Total Cost" cell will display the minimum cost of the banquet.
**Note:**
* The specific steps and interface may vary slightly depending on your Excel version.
* This solution provides a framework. You'll need to adapt it to your specific Excel setup.
By following these steps, you can effectively use Excel Solver to determine the most cost-effective menu for the banquet.
|
|
|
| |
