Instructions: Each of the following problems is to be solved using Solver in Excel. In order to receive proper and maximum credit, your spreadsheet(s) should show the formulation of your LP or ILP model, your assumptions, and the implementation of your model. Label appropriately. Use cell notes if needed.
1. A company needs to hire workers to cover a 7 day work week. Employees work 5 consecutive days with 2 days off. The demand for workers by day of the week and the weekly wages for the employees are:
|
Days of Week
|
Workers Required
|
|
Shift
|
Days off
|
Wage
|
|
Sunday
|
54
|
|
1
|
Sun & Mon
|
900
|
|
Monday
|
50
|
|
2
|
Mon & Tue
|
1000
|
|
Tuesday
|
36
|
|
3
|
Tue & Wed
|
1000
|
|
Wednesday
|
38
|
|
4
|
Wed & Thu
|
1000
|
|
Thursday
|
42
|
|
5
|
Thu & Fri
|
1000
|
|
Friday
|
40
|
|
6
|
Fri & Sat
|
900
|
|
Saturday
|
48
|
|
7
|
Sat & Sun
|
850
|
The company wishes to determine the number of employees to have on each shift such that the operating costs are minimized
a) Formulate the model for this problem.
b) What is the optimal solution?