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.

- 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?

referring to the report sheets, circle and label the relevant values and explain:

- 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 prices?

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 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.

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?