Question

Using Excel Linear Solver Please answer the following, include all of the constraints.

You are a project manager for a consulting firm. You are going to hire four new employees. The candidates that you are choosing from are named Amy, Bob, Charlie, Debbie, and Elizabeth. You are managing two projects that you will use your new employees to complete. Project 1 will require at least 700 labor hours and Project 2 will require at least 870 labor hours. Elizabeth is the owner’s daughter so you will have to hire her. Amy and Charlie used to work together, but they can’t stand each other. If you hire one, you can’t hire the other. You have contracts that guarantee a set fee for each of the projects. Your profit will be determined by your ability to minimize your labor costs. Each of the employees you hire will require a signing bonus. The respective signing bonuses required for each employee if hired follow: Amy $24,000; Bob $11,000; Charlie $16,000; Debbie $17,000; Elizabeth $15,000. The hourly rate you pay each employee is determined by the type of project to which they are assigned. For Project 1, the required hourly rates for each employee if hired follow: Amy $100; Bob $95; Charlie $85; Debbie $50; Elizabeth $45. For Project 2, the required hourly rates for each employee if hired follow: Amy $60; Bob $40; Charlie $75; Debbie $120; Elizabeth $130. If you hire an employee, they can be used for both projects. Labor should be allocated in one hour increments. During the lifespan of these two projects, the candidates are available to work the following number of hours: Amy 440; Bob 730; Charlie 520; Debbie 680; Elizabeth 590. Which candidates will you hire?

Answer 1

Create Excel model as follows:

Enter Solver Parameters as follows:

Click Solve to generate the solution:

Click OK

After click Solve button, values appear automatically in the yellow cells (B12:D16)

------------------------------------------------------------------------

Interpretation of Solution:

If the value in signup column (D12:D16) is equal to 1, that indicates particular should be hired, otherwise not

Number of hours of each candidate allocated to the two projects are shown in cells (B12:C16)

Total Cost = $ 117,600