Scribd
Upload
165 views11 pages

Midterm BMA

UDT is a consulting firm with six consultants of varying hourly rates and technical abilities. It has eight ongoing client projects of different sizes and requirements. The firm wants to assign consultants to projects in a way that best utilizes their skills while meeting budget and time constraints. A linear programming model is formulated to maximize revenue subject to constraints on project hours, budgets, and consultant availability. The model is solved using QM and Excel Solver to determine optimal consultant assignments. Changes to variables like consultant wages or minimum project ratings would alter the solution.

Uploaded by

ANH NGUYEN TRUONG NGOC
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
165 views11 pages

Midterm BMA

UDT is a consulting firm with six consultants of varying hourly rates and technical abilities. It has eight ongoing client projects of different sizes and requirements. The firm wants to assign consultants to projects in a way that best utilizes their skills while meeting budget and time constraints. A linear programming model is formulated to maximize revenue subject to constraints on project hours, budgets, and consultant availability. The model is solved using QM and Excel Solver to determine optimal consultant assignments. Changes to variables like consultant wages or minimum project ratings would alter the solution.

Uploaded by

ANH NGUYEN TRUONG NGOC
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 11

MIDTERM (INDIVIDUAL WORK – SUBMIT ON LMS – INCLUDE FILE DOC/PDF,

EXCEL SOLVER AND QM FOR WINDOWS)

UDT is a consulting firm that develops e-commerce project, systems and websites for its clients.
It has six available consultants and eight clients project is under contract. The consultants have
different technical abilities and experience, and as a result, the company charges different hourly
rates for its services. Also, the consultant’s skill are more suited for some projects than others,
and clients sometimes prefer some consultants over others. The suitability of a consultant for a
project is rated according to a 5-point scale, in which 1 is the worst and 5 is the best. The
following table shows the rating for each consultant for each project, as well as the hours
available for each consultant and the contracted hours and maximum budget for each project :

Project

Consultant Hourly 1 2 3 4 5 6 7 8 Available


wage hours

A $150 3 3 5 5 3 3 3 3 450

B $140 3 3 2 5 5 5 3 3 550

C $160 2 1 3 3 2 1 5 3 500

D $300 1 3 1 1 2 2 5 1 300

E $270 3 1 1 2 2 3 3 3 710

F $150 4 5 3 2 3 4 3 3 860

Project 500 240 400 450 350 460 290 200


Hours

Contract 100 80 120 90 65 85 50 55


budget
(x1000
USD)

The company wants to know how many hours to assign each consultant to each project in order
to best utilize their skill while meeting clients needs

a. Formulate a linear programming model and write down the mathematical model for this
problem.

b. Solve this problem using QM and SOLVER

c. If the company want to maximize revenue while ignoring client preferences and
consultant compatibility, will this change the solution in b. ?

d. Create a sensitivity report. What is the shadow price in this case ?

e. If consultant A and E change their hourly wage from $155 to $200 (A) and from $270 to
$200, will the solution change ?

f. By exprerience, consultant B and E is getting better at their ability, which mean their
capacity for every project now minimum start from 3 instead of 1 or 2, will the shadow price
change ?

a) Mathematical model for this problem


Constraint
Project hours:
A1+ B1+C1+D1+E1+F1 = 500
A2+B2+C2+D2+E2+F2 = 240
A3 +B3+C3+D3+E3+F3=400
A4+B4+C4+D4+E4+F4=450
A5+B5+C5+D5+E5+F5=350
A6+B6+C6+D6+E6+F6=460
A7+B7+C7+D7+E7+F7=290
A8+B8+C8+D8+E8+F8=200

Contract budget:
150*A1+140*B1+160*C1+300*D1+270*E1+150*F1<=100000
150*A2+140*B2+160*C2+300*D2+270*E2+150*F2<=80000
150*A3+140*B3+160*C3+300*D3+270*E3+150*F3<= 120000
150*A4+140*B4+160*C4+300*D4+270*E4+150*F4<= 90000
150*A5+140*B5+160*C5+300*D5+270*E5+150*F5<= 65000
150*A6+140*B6+160*C6+300*D6+270*E6+150*F6<= 85000
150*A7+140*B7+160*C7+300*D7+270*E7+150*F7<=50000
150*A8+140*B8+160*C8+300*D8+270*E8+150*F8<=55000

Total hours
A1+A2+A3+A4+A5+A6+A7+A8 <= 450
B1+B2+B3+B4+B5+B6+B7+B8<=550
C1+C2+C3+C4+C5+C6+C7+C8<=500
D1+D2+D3+D4+D5+D6+D7+D8<=300
E1+E2+E3+E4+E5+E6+E7+E8<=710
D1+D2+D3+D4+D5+D6+D7+D8<=860

b) Solve this problem using QM and SOLVER

SOLVER

QM for windows
c) If the company want to maximize revenue while ignoring client preferences and consultant
compatibility, will this change the solution in b. ?
The solution in b will change
d) Create a sensitivity report. What is the shadow price in this case ?
The shadow price

e) If consultant A and E change their hourly wage from $155 to $200 (A) and from $270 to $200,
will the solution change ?

If consultant A and E change their hourly wage, there will be changes in the number of hours each
consultant spends on each Project project as followed
f) By exprerience, consultant B and E is getting better at their ability, which mean their capacity
for every project now minimum start from 3 instead of 1 or 2, will the shadow price change ?

You might also like