Air-Express is an express shipping service that guarantees overnight delivery of packages anywhere in the continental United States. The company has various operations centers, called hubs, at airports in major cities across the country. Packages are received at hubs from other locations and then shipped to intermediate hubs or to their final destinations.
The manager of the Air-Express hub in Baltimore, Maryland, is concerned about labor costs at the hub and is interested in determining the most effective way to schedule workers. The hub operates seven days a week, and the number of packages it handles each day varies from one day to the next. Using historical data on the average number of packages received each day, the manager estimates the number of workers needed to handle packages as:
Day of Week Workers Required
Sunday 18
Monday 27
Tuesday 22
Wednesday 26
Thursday 25
Friday 21
Saturday 19
The package handlers working for Air-Express are unionized and are guaranteed a five-day work week with two consecutive days off. The base wage for handlers is $655 per week. Because most workers prefer to have Saturday or Sunday off, the union has negotiated bonuses of $25 per day for its members who work on these days. The possible shifts and salaries for package handlers are:
Shift Days Off Wage
1 Sunday and Monday $680
2 Monday and Tuesday $705
3 Tuesday and Wednesday $705
4 Wednesday and Thursday $705
5 Thursday and Friday $705
6 Friday and Saturday $680
7 Saturday and Sunday $655
Required:
a) The manager wants to keep the total wage expense for the hub as low as possible. With this in mind, how many package handlers should be assigned to each shift if the manager wants to have a sufficient number of workers available each day?
b) Would you please provide an algebraic formulation for each problem and explain how to use Excel Solver to optimize?