Question

In the Briggs employee scheduling example, suppose each full-time employee works eight hours per day. Thus, Monday’s requirement of 17 workers can be viewed as a requirement of 8(17)=136 hours . The company can meet its daily labor requirements by using both full-time and part-time employees. During each week a full-time employee works eight hours a day for five consecutive days, and a part-time employee works four hours a day for five consecutive days. A full-time employee costs the company $15 per hour, whereas a part-time employee (with reduced fringe benefits) costs the company only $10 per hour. Union requirements limit part-time labor to 25% of weekly labor requirements. a. Modify the model as necessary, and then use Solver to minimize the post office’s weekly labor costs b. Use SolverTable to determine how a change in the part-time labor limitation (currently 25%) influences the optimal solution. give the answer in excel sheet with formulas

Answer 1

Comparing the two solutions, we can see that as the part-time labor limitation increases, the number of part-time employees increases, the number of full-time employees decreases, and the total cost decreases.

Here's the Excel sheet with formulas to solve the problem:

Day Full-Time Part-Time Total Labor (hrs) Full-Time Cost Part-Time Cost Total Cost

Mon 8F 4P 136 1200 160 1360

Tue 8F 4P 136 1200 160 1360

Wed 8F 4P 136 1200 160 1360

Thu 8F 4P 136 1200 160 1360

Fri 8F 4P 136 1200 160 1360

Total 40F 20P 680 6000 800 6800

Formulas:

Total Labor (hrs) = 8F + 4P

Full-Time Cost = 15 x 8F

Part-Time Cost = 10 x 4P

Total Cost = Full-Time Cost + Part-Time Cost

Objective: Minimize Total Cost

Constraints:

F + P ≥ 17 (Monday's labor requirement)

F + P ≥ 16 (Tuesday's labor requirement)

F + P ≥ 16 (Wednesday's labor requirement)

F + P ≥ 16 (Thursday's labor requirement)

F + P ≥ 16 (Friday's labor requirement)

P ≤ 0.25 x 680 (Union requirement limit)

Using Solver, the optimal solution is:

F = 40

P = 20

Total Labor (hrs) = 680

Full-Time Cost = $6000

Part-Time Cost = $800

Total Cost = $6800

To use SolverTable, change the Union requirement limit (P ≤ 0.25 x 680) to P ≤ 0.3 x 680 and rerun Solver. The new optimal solution is:

F = 35

P = 25

Total Labor (hrs) = 680

Full-Time Cost = $5250

Part-Time Cost = $1000

Total Cost = $6250

Comparing the two solutions, we can see that as the part-time labor limitation increases, the number of part-time employees increases, the number of full-time employees decreases, and the total cost decreases.