Lab 6: Sensitivity Analysis using MS Excel
October 23, 2012
In this part of the assignment you need to build a model for solve a fertilizer blending problem. You should recognize the similarity between this problem and the cereal mixing problem from previous lab and lectures.
The Hickey Fertilizer Co. produces special order fertilizers for customers in the citrus business. Their most recent order is for exactly 1000 tons of fertilizer meeting the following exact specifications:
A) The mixed fertilizer must be at least 20% nitrogen by weight (200 tons).
B) The mixed fertilizer must be at least 30% potash by weight (300 tons).
C) The mixed fertilizer must be no more than 8% phosphate by weight (80 tons).
D) The total weight of the final mix must equal 1000 tons.
Hickey Co. makes their special order blends of fertilizer from four fertilizer stocks they have in inventory. Call these fertilizers F1, F2, F3, and F4. Table 1 below gives the ingredient information for each of the fertilizers in stock.
Table 1. Fertilizer Nutrient Contents
Nutrient / F1 / F2 / F3 / F4Nitrogen Pct. / 40 % / 30 % / 20% / 5 %
Potash Pct. / 20 % / 10 % / 40 % / 5 %
Phosphate Pct. / 10 % / 5 % / 5 % / 30 %
Charge per Ton / $16 / $12 / $15 / $8
Hickey Co. is contractually obligated to produce the 1000 tons of specialized blend fertilizer for a previously agreed upon fee. Since the revenue side of the problem is given (the fee and quantity are already set), the objective for the company is to produce the blend to the specifications at minimum costs. The last row of Table 1 gives the per ton charge each of the stock fertilizers from which the custom blend is made.
The first step is to write the model’s algebraic form. From there, we can build the spreadsheet model and begin answering specific questions about the optimal mix and how it changes when parameters are adjusted. Please remember to type your answers.
Homework Assignment 5
1) Write the algebraic form of Hickey’s problem. Use F1, F2, F3, and F4 as your decision variables and tons as the units (note that the prices are given in $/ton of a given fertilizer stock). Use units of tons for the RHS values on constraints. The Nitrogen constraint is given below as a hint:
Nitrogen: 0.4*F1 + 0.3*F2 + 0.2*F3 + 0.05*F4 >= 200.
2) Enter the model into Excel and solve. Report all relevant results from the solution. Refer to past labs for any help you need in setting up and solving the problem.
3) Add 1 to the Nitrogen constraint and resolve the model. Report the difference between the total cost of the fertilizer mix when the RHS of the Nitrogen constraint is 201 versus when it is 200. Change the RHS of the Nitrogen constraint back to 200 and increase the RHS of potash by 1 unit. Again report the change in profits relative to the initial solution from question 2. Continue this pattern for the final two constraints.
Change in profits with Nitrogen increased by 1 and all other constraints at base values =
Change in profits with Potash increased by 1 and all other constraints at base values =
Change in profits with Phosphate increased by 1 and all other constraints at base values =
Change in profits with Total Weight increased by 1 and all other constraints at base values =
4) Reset all constraints to their base values. Solve the model using solver. When the box appears showing that Solver is completed, click on the ‘sensitivity report’ in the reports window. Once it is highlighted click ok and a new sheet should open in your workbook with sensitivity information. (Note if Solver fails to open the sensitivity report when you highlight it you will need to use the directions on the next page of this handout).
Compare the information in the sensitivity report under ‘Shadow Price’ to the results you generated in question 3. Given this comparison, provide an explanation of how shadow prices measure sensitivity of a linear programming model solution.
5) Due to a mistake in a machine setting the fertilizer that is mixed for the customer ends up being 3 tons short of the 300 ton minimum required potash content. Use the sensitivity information from the optimal solution to determine the maximum discount that the mixer can offer to this customer to make up for the error.
Solver’s Sensitivity Bug Workaround
Due to some issues with the 2007 MS Excel software, you may be required to reinstall the Solver utility to view sensitivity results. The following steps describe the reinstall process. First be sure you have your excel model spreadsheet open and then follow the steps below. Once you have completed the uninstall/reinstall of Solver you should be able to Solve the model and view a sensitivity report.
Preliminary Steps:
I. Click on the MS Office 2007 Button at the top left of the spreadsheet.
II. At the bottom of the menu that you see, click on Excel Options.
III. In the Excel Options menu, select Add-Ins.
IV. In the bottom of the Add-Ins menu, choose Excel Add-Ins in the Manage: drop down. Click on Go…
V. In the Add-Ins list that pops up, look for the Solver Add-In box. If there is NO check mark in the box proceed to V.1. If there is a check mark next to Solver Add-In, proceed to V.2.
V.1 Click on the box to place a check mark there. Click OK and you are finished.
V.2 Click on the box to remove the check mark next to Solver Add-In. Click OK. Return to Step I and redo the steps stopping at V.1.