1 / 14

DSCI 3870 Case 3: SOME HELP Dr. Nick Evangelopoulos SPRING 2010

DSCI 3870 Case 3: SOME HELP Dr. Nick Evangelopoulos SPRING 2010. The big picture: structure of the spreadsheet. UPPER PART: Client requirements (parameters). MIDDLE PART: Assignment variables. LOWER PART: Cost contributions (components of the Objective function).

daktari
Télécharger la présentation

DSCI 3870 Case 3: SOME HELP Dr. Nick Evangelopoulos SPRING 2010

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DSCI 3870 Case 3: SOME HELP Dr. Nick Evangelopoulos SPRING 2010

  2. The big picture:structure of the spreadsheet UPPER PART: Client requirements (parameters) MIDDLE PART: Assignment variables LOWER PART: Cost contributions (components of the Objective function)

  3. The big picture:structure of the spreadsheet Client requirements (parameters) Assignment variables Individual assignment restrictions Constraints for specialties and rank levels Cost contributions Rates and levels (parameters) Objective function

  4. First Requirement The Las Vegas project requires any Project Manager for 480 hours 3 Project Managers qualify: Simmons, Powell, Taylor.

  5. First Requirement The cost contribution for each Las Vegas Project Manager will be (480 hours)*(Hourly Rate) *(Binary variable indicating whether the manager is assigned) Project requirement Rates for Project Managers Assignment variables Cost contribution for each Project Manager

  6. Third Requirement The Dallas office requires any level 3 consultant for 600 hours 3 consultants qualify: Simmons, Powell, Hernandez.

  7. Third Requirement The cost contribution for each Dallas Office level-3 consultant will be (600 hours)*(Hourly Rate) *(Binary variable indicating whether the consultant is assigned) Office requirement Assignment variables Rates for level-3 consultants Cost contribution for each level-3 consultant

  8. Cost Contributions Some assumptions are needed in order to complete the cost contribution calculations 500 hours are required from a programmer in Dallas 600 hours for anybody else (other than a programmer) in Dallas If somebody goes to Kansas City, they go for 600 hours If the rest of the people get assigned to Las Vegas, they will go there for 480 hours

  9. Assignments that are not required No Auditor is required in Las Vegas. But what if we assign one? Our spreadsheet should be capable of factoring this situation into the cost function, even if it’s not going to be part of the final (optimal) solution Project requirements Rates for programmers and auditors Assignment variables Cost contributions: assume programmers in Kansas City will work for 600 hrs, auditors in Las Vegas will work for 480 hrs, etc.

  10. Cost Contributions Continue this way until you model all the hourly requirements Objective function

  11. Labor Constraints Each consultant can be assigned to at most one position Total assignment for Simmons <= 1, etc.

  12. Labor Constraints At least one proj. manager is needed in LV, at least one level-3 proj. manager is needed in KC, etc. Total Project Managers in Kansas City >= 0, because in KC we will secure level-3 project managers Total Project Managers in Las Vegas >= 1 Total level-3 Project Managers in Kansas City >= 1

  13. Assignment Variables: Binary? • You may be tempted to specify the assignment variables (decision variables) as binary or integer, but then you will not get a sensitivity report • Alternatively, you can go to options and add nonnegativity constraints, without specifying your variables as integer or binary. This way you will get a sensitivity report!

  14. Solver Options • Assume a linear model • Assume Non-Negative decision variables

More Related