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