DSCI 3870
This presentation is the property of its rightful owner.
Sponsored Links
1 / 14

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


  • 87 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


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

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)


The big picture structure of the spreadsheet1

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


First requirement

First Requirement

The Las Vegas project requires any Project Manager for 480 hours

3 Project Managers qualify: Simmons, Powell, Taylor.


First requirement1

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


Third requirement

Third Requirement

The Dallas office requires any level 3 consultant for 600 hours

3 consultants qualify: Simmons, Powell, Hernandez.


Third requirement1

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


Cost contributions

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


Assignments that are not required

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.


Cost contributions1

Cost Contributions

Continue this way until you model all the hourly requirements

Objective function


Labor constraints

Labor Constraints

Each consultant can be assigned to at most one position

Total assignment for Simmons <= 1, etc.


Labor constraints1

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


Assignment variables binary

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!


Solver options

Solver Options

  • Assume a linear model

  • Assume Non-Negative decision variables


  • Login