excel assignment

Excel Assignment: Truck Scheduling

Mal-Wart truck fleet hires temporary drivers for four- and eight-hour shift during holiday sales season. Four-hour shifts start at 6am, noon, and 2pm; eight-hour shifts start at 6am, 8am, noon, and 2pm.

The number of drivers needed varies during the day. The table below shows the minimum number of drivers that are needed each two-hour time block throughout the day. (For example, Mal-Wart needs 37 drivers from 8am to 10am.)

Time	6am	8am	10am	Noon	2pm	4pm	6pm	8pm
Drivers needed	63	37	21	33	48	55	27	17

A driver’s pay depends on the hours worked. A driver gets paid $10 per day, plus $7 per hour worked, plus a $1 hour premium for each hour worked before 8am or after 6pm. Mal-Wart would like to pay as little as possible while having enough drivers at all times.

1. Formulate a linear programming model for this problem and type it in worksheet 1 of your Excel file.

2. In worksheet 2 of your Excel file, formulate an Excel model that corresponds to your mathematical model; and use Solver to find the optimal solution to the problem.