Background:
To the extent allowed by local law, each Acme Home Improvements store, including Acme Mexico City (AMC), is open from 7 am - 11 pm every day.
Acme Mexico City planners have given the following table that identifies the minimum number of customer service employees estimated to be required on the floor of the store each hour of the day:
Full-time customer service employees at AMC work a 9 hour shift (8 hours of work plus a 1 hour meal break) either from 7 am to 4 pm or from 2 pm to 11 pm. Workers on the 7-4 shift are assigned an hour-long lunch break at either 11 am or 12 noon. Workers on the 2-11 shift are assigned an hour-long dinner break at either 5 pm or 6 pm.
Part-time customer service employees work four consecutive hours per day and their shifts can start any hour between 7 am and 7 pm.
By corporate policy, this is consistent with Mexican labor law, the company limits total part-time customer service employee daily hours to 50% of the day's total customer service employee hours (total hrs = part-time hrs plus fulltime hrs) worked.
In the interest of appropriate supervision of employees and employee safety rules, management has a policy which the number of customer service employees on the floor at any given time must never exceed 30.
Part-time customer service employees earn $500 per day, and full-time customer service employees earn $1100 per day in salary and benefits (here, $ = Moneda Nacional, that is, the Mexican peso).
Contract Statement of Work:
Develop a minimum cost day by day assignment schedule for the Acme Mexico City customer service employees.
Deliverables:
1) A report which provides your recommended schedule/cost and illustrates your solution method. (Note: Try to provide a comprehensive description, but keep it simple enough). As well, be sure to comprise any suppositions or limitations for your model.
2) A copy of the documented spreadsheet model you develop, so that we can rerun it if the situation changes. For this work you will be paid a sum of $9,000 (Mexican pesos).
Additional funds (up to $1,000 pesos) are available under the contract if you can help with the given:
a) Finding out the cost of enforcing the part-time hours constraint (that is, How much money could we save if part-time hours are not limited to 50% of total hours?)
b) Finding out whether adding an additional lunch shift at 10 am and an additional dinner shift at 7 pm can lower our daily cost.