On the tab labeled "Original Problem", I have provided the modeling of Problem #18 on Page 119 in Chapter 3 with some minor modifications. Carefully read the statement of the problem in the textbook and then review the model to familiarize yourself with the integration of the LP into Excel.
Your first requirement is to load the solver based on the set up I have provided on the Original Problem tab assuming you want to minimize the cost of producing a mix that meets the constraints noted in the textbook. In loading the solver, there is no need to change any cell values or equations in the spreadsheet itself. As a hint, the cells highlighted in YELLOW are either decision variables, or the optimization cell. Once you have loaded the solver and solved the problem, you are required to obtain the sensitivity analysis; if you do this correctly, the analysis will show up in a new tab in the bottom left-hand corner of the sheet. If you are unsure how to obtain the report, please review my video in the Content area. HINT: if you have loaded the solver correctly, the minimized cost should be between $5.25 and $5.35.
From there, proceed to the "Solutions" tab where you will find five questions. To answer each question, you will be required to consult the sensitivity analysis and locate the correct information from the analysis to answer each question. All answers should come from solving the model (after loading the solver) and obtaining and consulting the sensitivity data, not by resolving the model multiple times!
In your response to each question, you should begin with a written sentence(s) that explains where specifically in the sensitivity analysis you found the information you need to answer each question followed by a sentence answering each respective question. Each question is worth 4 points: 2 points for explaining where in the sensitivity analysis you found the information necessary to answer the question (mechanical component) and 2 points for a clear, concise, grammatically answer to the question (written component). Questions should be answered on the Solutions tab and typed in unbolded print right after the statement of the question.
Good luck, and as always...HAVE FUN!
1 (4 Points): What is the optimal solution?
2. (4 Points): What would happen to the optimal solution if the cost per pound of grain decreaed by five cents?
3. (4 Points): What would happen to the optimal solution if the cost per pound of chocolate increased by eighteen cents?
4. (4 Points): Considering the four nutritional constraints (vitamins, minerals, protein, and calories), explain why the shadow prices of protein and calories are zero and the shadow prices of vitamins and minerals are non-zero.
5. (4 Points): In the Contstaints portion of the sensitivity analysis, use the shadow price for "Amount (lbs) Total" to determine the total cost of the mix if the amount of mix increased from 2 to 2.02 pounds.
Your first requirement is to load the solver based on the set up I have provided on the Original Problem tab assuming you want to minimize the cost of producing a mix that meets the constraints noted in the textbook. In loading the solver, there is no need to change any cell values or equations in the spreadsheet itself. As a hint, the cells highlighted in YELLOW are either decision variables, or the optimization cell. Once you have loaded the solver and solved the problem, you are required to obtain the sensitivity analysis; if you do this correctly, the analysis will show up in a new tab in the bottom left-hand corner of the sheet. If you are unsure how to obtain the report, please review my video in the Content area. HINT: if you have loaded the solver correctly, the minimized cost should be between $5.25 and $5.35.
From there, proceed to the "Solutions" tab where you will find five questions. To answer each question, you will be required to consult the sensitivity analysis and locate the correct information from the analysis to answer each question. All answers should come from solving the model (after loading the solver) and obtaining and consulting the sensitivity data, not by resolving the model multiple times!
In your response to each question, you should begin with a written sentence(s) that explains where specifically in the sensitivity analysis you found the information you need to answer each question followed by a sentence answering each respective question. Each question is worth 4 points: 2 points for explaining where in the sensitivity analysis you found the information necessary to answer the question (mechanical component) and 2 points for a clear, concise, grammatically answer to the question (written component). Questions should be answered on the Solutions tab and typed in unbolded print right after the statement of the question.
Good luck, and as always...HAVE FUN!
2. (4 Points): What would happen to the optimal solution if the cost per pound of grain decreaed by five cents?
3. (4 Points): What would happen to the optimal solution if the cost per pound of chocolate increased by eighteen cents?
4. (4 Points): Considering the four nutritional constraints (vitamins, minerals, protein, and calories), explain why the shadow prices of protein and calories are zero and the shadow prices of vitamins and minerals are non-zero.
5. (4 Points): In the Contstaints portion of the sensitivity analysis, use the shadow price for "Amount (lbs) Total" to determine the total cost of the mix if the amount of mix increased from 2 to 2.02 pounds.
