A fire dept. says that they must have full time firemen work on different days of the week. they must have 10 on mon, 11 on tues, 12 on wed, 13 on thurs, 14 on fri, 15 on sat and 16 on sun . each fireman must work 5 (any 5) consecutive days and then have two days off. but sometimes they can work one day a week OT. each fireman is paid $100/day regular pay and if they work OT they get $125/day.
I need to minimize the cost of meeting the requirements above (my first constraint). the next constraint is that they can each only work one day OT. so if they work mon, tues, wed, thurs, fri, they could if needed work either sat OR sun. how do i set this up.
Your solution is based on the assumption that:
1. They must have 10 on mon, 11 on tues, 12 on wed, 13 on thurs, 14 on fri, 15 on sat and 16 on sun
2. Each person works 6 days a week with 5 days normal and 1 day being paid extra at $125/day. (THIS IS NOT A CORRECT ASSUMPTION!)
There are actually 3 distinct types of workers who begin work on Monday: (1) Those who begin work on Monday and work until Friday; (2) Those who begin work on Monday and work until Saturday; (3) and those who begin work on Monday, work until Friday, have Saturday off, then work an overtime day on Sunday. Since these are clearly distinct schedules, I need to consider introducing decision variables that will account for these different schedules. Of course, this same kind of reasoning applies to workers who start work on Tues or Wed, or. . . . how can i add this into the spreadsheet; the original one that you did was binary; now this isnt really binary.... i have confused myself and tried this a few different ways - using days off instead of on to make it less cumbersome..... but i cannot seem to figure it out. i am including your spreadsheet and then the others that i was working on too.
We can still do this. If it is like you said, we now have 3 categories of employees:
Category A: Those who begin work on Monday and work until Friday (i.e. 5 days per week), etc.
Category B: Those who begin work on Monday and work until Saturday (i.e. 6 days continuously), etc.
Category C: ...
The solution provides detailed explanations, including full Excel-solver codes.