Linear Programming

To the extent permitted by local law, each XX store, is open from 7 am - 11 pm every day. Planners have provided the following table, which identifies the minimum number of customer service employees needed on the floor each hour of the day:

Time Period Minimum number of employees
7 am - 8 am 15
8 am - 9 am 15
9 am - 10 am 15
10 am - 11 am 20
11 am - 12 pm 20
12 pm - 1 pm 20
1 pm - 2 pm 20
2 pm - 3 pm 20
3 pm - 4 pm 25
4 pm - 5 pm 25
5 pm - 6 pm 25
6 pm - 7 pm 20
7 pm - 8 pm 20
8 pm - 9 pm 15
9 pm - 10 pm 15
10 pm - 11 pm 15

Full-time employees at XX 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 10 am, 11 am or 12 noon. Workers on the 2-11 shift are assigned an hour-long dinner break at either 5 pm, 6 pm or 7 pm.

Part-time employees work four consecutive hours per day and their shifts can start any time between 7 am and 7 pm.

By corporate policy, which is consistent with local labor law, the company limits part-time hours to 50% of the day's total hours (part-time hours plus full-time hours) worked.

Part-time employees earn $300 per day, and full-time employees earn $750 per day in salary and benefits ($ = Moneda Nacional, ie, the Mexican peso).

Using Excel, Lindo or comparable software, each student prepares an employee assignment schedule for XX store that addresses the following tasks:

1. An optimal schedule for full-time and part-time customer service employees that minimizes personnel costs.

Note: attached sample excel to use the Solver function--similar templates to solve this problem.