Example: This was once used as an exercise, but it makes a good classroom example:

Sensitivity Analysis on the Technician Hiring Case,

and expanding the model to add employment costs

If you want to print sheets to study the results you can print sheets all to one page by file /printpreview/setup/landscape
check  fit to 1 page wide by 1 tall
Use the EXCEL LP model already assembled for you as  techhire.xls
The data you input is in purple. the solution is in yellow. This model already has constraints built in.
Although this doesn't matter for what we are doing, I made it symmetrical by adding capability to use costs for dummy supply.
I've changed all costs to be in $K per trip

Run solver by tools/solver/solve and print out the sheet with the optimal solution. Compare everything to this optimal solution.

Incremental analysis

keep all else constant in the initial optimal condition as you do each of these:

Sensitivity Report

restore to original values. solve and select report for sensitivity (provide printouts with your name on them)
referring to the report sheets, circle and label the relevant values and explain:
  1. What are the shadow prices for capacities of technicians from NY, STL, and HOU? Compare these to what you got in the incremental analysis, above
  2. What does the shadow price mean?
  3. What are the upper limits on the ranges of validity for each of those shadow prices?

New shadow price when you go outside the range of validity

Add enough capacity in STL to exceed the Upper Limit on range of validity for the shadow price. Then add one more. What is the value of one more trip in this new range?

Lower limit on range of optimality NY->HOU

According to the sensitivity report, how low can the cost of trips from NY to HOU (cell D17) go before the solution (in cells b7-d7) changes?
Experiment with the cost of the trip from NY to HOU. Although the total cost changes, within a range, the solution in cells b7-d9 will stay the same. How far down can you adjust this cost before the solution changes to a different corner? You will be indifferent between the original solution and the new solution at the lower limit of the range of optimality, so you may have to go just a little bit further to get it to shift. At the indifference point, the solution is "degenerate"and has an infinite number of solutions on a line that intersects two corners. Think about the geometric analysis we did in class. Describe the range of optimality for this cost and what Range of Optimality means.

What is the new solution and why?

Take the cost down just far enough to get a different solution and compare it to the original.
What is the new solution in cells b7-d7?
Compare the old and new solutions in terms of trips from NY->HOU, STL->HOU, STL->STL and NY->STL. What changed?
Consider the new cost in cell D17 at the indifference (degeneracy) point.
Explain in terms of relative costs of trips in those 4 cells why you are indifferent between old and new solutions at the limit point of the range of optimality.

Adding employment costs into the model

Cost of capacity can be built into this model. Suppose the costs differ depending on city of origin and you have the option of hiring in one city and firing in another. Suppose the (per trip) costs of capacity are as shown in cells f17-f19.
Modify  the model as needed to include the costs of capacity in the different cities and solve for the optimum staffing plan. This can easily be achieved by removing the capacity constraints and adding in the employment costs. Employment costs might be most easily included by multiplying thew technician trips used by the employment cost per trip and putting the results in cells G17-G20. Add up the employment costs in cell G21, then add employment costs to travel costs that are in cell G13. The resulting total cost can then be set as the Solver target cell to be minimized.  Because employment costs go up as more technician trips are used, the capacity constraints are unnecessary except for the one that keeps dummy trips at zero (E10<=F10)

If you want to print out a screen shot of your new model, You can do so by capturing the screen to the clipboard. An easy way to print out the model is to open solver so that the constraints show in the solver window and do a screen capture by pushing the printscreen button. That puts the image of the screen on the windows clipboard.  You can then open a word processor, such as WORD and paste the image into a document.and write some description on the printout.

With these costs included, what is the new optimal plan? Why does it make sense?

Back to MS483 Online Syllabus