I am facing the below problem

I know for each week how many workers that I need. I need to ensure that for a given week I have workers more than or equal to what I need.

week    workers_required
ww01    5
ww02    2
ww03    1
ww04    6
ww05    2
ww06    10
ww07    2
ww08    1
ww09    0
ww10    9

I also know what are different contracts that my supplier offers. Longer the contract, lesser is the cost per week. For example, if I sign a 1 week contract it costs me 50/week but if I sign 5 week contract it costs me 35/week.

1 contract is for 1 worker.

contract    cost
1 week          50
2 week          80
5 week          175
10 week         300

I can sign any number of contracts. Moreover, I can sign a contract and then extend it at the same weekly rate. For example I can sign a 5 week contract and extend it for any number of weeks at the cost of 35/week.

My objective is to spend minimum

I would like to find how many and what type contracts should I sign and when should I start them

How could I set up my problem? I am planning to use R lpsolve package or in Excel using opensolver or whats best.


I am not exactly sure about the mathematical reason. But I feel that I can solve this problem one worker at a time and still get the optimum solution. In the below case how could I frame the problem? I know that the solution here is going to be buy a 10 week contract and let a worker sit idle in ww09. But how could I formulate the problem and solve it mathematically?

  1. I can start any number of contracts. But i pay for them immediately
  2. Number of active contracts per week should be more than or equal to workers required in that week
  3. My goal is to minimize the cost

    contract cost

    1 week          50
    2 week          80
    5 week          175
    10 week         300

    1 worker is required in ww1 to ww10 except week 9. Week 9 requires 0 workers


1 Answer 1


You have ten types of contract (1week to 10weeks), that may start at ten different times,
so you have 100 variables.
Many contracts cost more than others, for no extra benefit, so you can remove the expensive options before you start.
You have ten inequalities: enough workers in week1, enough in week2, and so on. (and of course each variable is non-negative)
Week 1 would be A1+B1+C1+D1+E1+F1+G1+H1+I1+J>=5,
week 2 would be A2+B1+B2+C1+C2+D1+D2+E1+E2+F1+F2+G1+G2+H1+H2+I1+I2+J>=2,

  • $\begingroup$ Please show a demo if possible. I have 4 contract types (1,2,5,10 weeks) and 10 week horizon. So i think by your way, i will have 40 decision variables. But i am not sure about how to proceed $\endgroup$ Oct 12, 2015 at 19:01
  • $\begingroup$ You also have three-week contracts for 120, 4-week for 160, and so on. By the way, should 10-week contracts cost 300, not 200? I'm not sure why a worker would stay around for 5 a week. $\endgroup$
    – Empy2
    Oct 12, 2015 at 19:30
  • $\begingroup$ i changed the cost of 10 weeks contract. thanks for the input $\endgroup$ Oct 12, 2015 at 20:17
  • $\begingroup$ my main struggle: if i start a 5 week contract in week2 then in constraints for week3, week4, week5, week6, i need to add a worker. But if i dont start the contract then i dont have add that worker. I am not sure how to deal with this IF scenario $\endgroup$ Oct 12, 2015 at 20:19
  • $\begingroup$ I dont have 3 week or 4 weeks contracts. I have only 4 contracts as shown above. $\endgroup$ Oct 12, 2015 at 20:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.