This needs to be in excel. Thank you for your help! 

 

  A car dealership sells small, medium and large cars. Each small car yields a profit of $450, gets 38 miles per gallon, requires 70 square feet of floor space and takes 3 hours to sell. Each medium car yields a profit of $550 gets 24 miles per gallon, requires 90 square feet of floor space and takes 4 hours to sell. Each large car yields a profit of $950, gets 16 miles per gallon, requires 140 square feet of floor space and takes 10 hours to sell. The cars sold by the dealership must average at least 26 miles per gallon. The dealership has available 17,500 square feet of floor space and the salespeople can work at most 800 hours per week and are required to work at least 40 hours per week. How many cars of each type should the car dealership order each week to maximize its profit? (Assume that it can sell everything that it orders).

What is the marginal value (or shadow price) of the labor now?  (If you use Tools, Solver to solve this problem with Excel, and, under "Options", check "Assume linear model", the marginal value may be found in the "Sensitivity Analysis" as the value for the "shadow price".)   What is the range of validity for this marginal value?  Plot the maximum profit versus the number of sales hours available. (You may use the Chart Wizard in Excel to do this). 

 

 

2. Now, assume that the salespeople can still work at most 800 hours per week, and we can change the amount of floor space from its previous value of 17,500 square feet (and everything else remains the same as in Problem 1). What would the marginal value (or shadow price) of the floor space be, and what would be the range of validity of this value? Hint: One way to answer this is to think of adding a b column identical to the s2 column to the original simplex tableau. The final b column will be the same as the final s2 column. Graph the maximum profit versus the amount of floor space available. I recommend using the Chart Wizard in Excel to draw these graphs.