Use excel solver to find the optimal solution. Define decision variables, objective function, constraints.

Hint. Think about all the possible ways of producing 8-hour schedules by choosing two of the aforementioned 4-hour shifts. There are 15 possible ways of doing so. Which ones are good schedules? Which ones are bad?

You and your friends are a group of 40 drivers who have started a taxi cooperative in the town of Mondragn. Based on the historic data for demand, you know that you need the following number of drivers to be active during different times of each day. 12am-4am 4am-8am 8am-12pm 12pm-4pm 4pm-8pm 8pm-12pm 6 13 11 14 19 15 You want to assign to each driver an 8-hour schedule consisting of two of the 4-hour shifts in the above table. For instance, one possible schedule is 8pm-4am (consisting of 8pm-12am and 12am-4am). Another possible schedule is 8am-12pm and then 4pm-8pm. But 2pm- 10pm is not a valid schedule, since it is not using the 4 hour shifts above. A schedule is called good if it forms a consecutive 8-hour. (For instance, 8pm to 4am is called a good schedule. On the other hand a schedule consisting of 8pm-12am and 4am-8pm is called a bad schedule.) You want to assign the drivers to 8-hour schedules so as to satisfy the demand and to minimize the total number of bad schedules. Make a readable, reusable spreadsheet model that solves this model linearly. You and your friends are a group of 40 drivers who have started a taxi cooperative in the town of Mondragn. Based on the historic data for demand, you know that you need the following number of drivers to be active during different times of each day. 12am-4am 4am-8am 8am-12pm 12pm-4pm 4pm-8pm 8pm-12pm 6 13 11 14 19 15 You want to assign to each driver an 8-hour schedule consisting of two of the 4-hour shifts in the above table. For instance, one possible schedule is 8pm-4am (consisting of 8pm-12am and 12am-4am). Another possible schedule is 8am-12pm and then 4pm-8pm. But 2pm- 10pm is not a valid schedule, since it is not using the 4 hour shifts above. A schedule is called good if it forms a consecutive 8-hour. (For instance, 8pm to 4am is called a good schedule. On the other hand a schedule consisting of 8pm-12am and 4am-8pm is called a bad schedule.) You want to assign the drivers to 8-hour schedules so as to satisfy the demand and to minimize the total number of bad schedules. Make a readable, reusable spreadsheet model that solves this model linearly