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.
keep all else constant in the initial optimal condition as you do each
increase capacity in NY by 1 trip and reoptimize. What is the value of
this (decrease in cost)?
restore NY to 200. increase STL capacity to 51. reoptimize. What is the
decrease in cost?
restore STL to 50. increase HOU capacity to 126. reoptimize. What
is the decrease in cost?
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
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
What does the shadow price mean?
What are the upper limits on the ranges of validity for each of those shadow
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)
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
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
Back to MS483 Online