Goal or LP Programming in Excel using the Solver Function

Can someone help me with this problem?  I am looking for the Goal Programming/LP model in Excel.

I have also attached a few similar practice problems with the answer keys.  The question below is Number 4 in the attached pdf.  

The solution has to be in goal programming, including the goal constraints like in the other practice problem answers.

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

Arizona National Marketing is (was) an in-bound only telemarketing company located in
Omaha, Nebraska (the telemarketing capital of the world). ANM’s operators take information
from callers wanting to order certain products seen advertised on TV throughout the day. In
scheduling employees to answer the multitude of their in-bound 1-800 calls, they have relied
upon ‘seat-of-the-pants’ methodologies that leave the operations manager uneasy. She wants to
take a look at a ‘clean slate’ schedule and then adjust her workforce so as to move toward this
schedule in the future.

Historical records are pretty good at ANM. Because of this, the operations manager can estimate
fairly accurately the number of required operators for each hour throughout the work day (8 a.m.
through 10 p.m.). This is shown in the table below.

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

Hour Req. Oper. Hour Req. Oper. Hour Req. Oper.
8-9am 6 1-2pm 13 6-7pm 16
9-10am 8 2-3pm 12 7-8pm 11
10-11am 10 3-4pm 10 8-9pm 9
11am-noon 11 4-5pm 13 9-10pm 5
noon-1pm 14 5-6pm 15

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

Full-time operators are paid $10/hour and work 4 hours, take 1 hour off for lunch, then work
another 4 hours (they’re paid $80/day). Part timers work 4 consecutive hours and are paid
$6/hour for each hour before 5 p.m. and $7/hour for each hour on or after 5 p.m. For simplicity
sake, the schedule should only consider four possible shifts for full-time operators: 8am - 5pm,
9am-6pm, 10am-7pm and 1pm-10pm. All possible part time shifts should be included in the
analysis. Assume shifts only start ‘on the hour’.

Obviously, any schedule generated MUST have at least the required operators on hand (shown
above) for each hour time frame. Treat full and part-time operators equivalently in terms of
productivity. Additionally, it is an ABSOLUTE policy requirement that no more than 5 operators
can be assigned to each individual shift (i.e., no more than 5 operators starting any one of the 4
full time shifts or no more than 5 part time operators starting at x o’clock, etc.). SHIFT is
DIFFERENT THAN HOUR!!!

USING INTEGER VALUE REQUIREMENTS appropriately, create and solve a goal
programming model for the scheduling situation that depicts the following order of goals in
decreasing order of importance:

Goal 1: Keep cost below or at $1600 per day.
Goal 2: Make sure there are at least 4 full-time employees working during each hour.
Goal 3: The combined number of full and part-time people each hour should not exceed the total
required operators stated above for that hour by more than 5 people.
Goal 4: Try to schedule at least 1 person in each shift (all 15 shifts).

Goal 5: There should be no more than 10 part-time employees scheduled during each hour time
frame.
For simplicity, sub-goals can be treated equally.

Attachments:

• 1492954176-gppracticeprobems.xlsx