Explore BrainMass

Excel Solver Problem: BW's Food and Drinks

BW's offers a variety of delicious foods and drinks. Management is trying to decide how many employees to schedule over the time period 12 PM - 10 PM on Saturdays. There are two options for employees: Option 1: Part-time employees, who work 4-hour shifts and are paid $8 per hour. These employees could start a shift at 12 PM, 1 PM, 2 PM, 3 PM, 4 PM, 5 PM, or 6 PM. Option 2: Full-time employees, who work 4-hours, take a 1-hour unpaid break, and then work 4 more hours. Full-time employees are paid $9.50 per hour and could start their shifts at either 12 PM or 1 PM. The minimum total number of employees needed during each time period is given in the table below. In addition, BW's wants to ensure that there is at least 1 full-time employee on duty at all times.

Questions: What is the schedule of workers that minimizes total labor cost on Saturdays? What is the total labor cost?

Define the decision variables, the objective function, and the constraints within your answer to this question in your Word report. Then, solve the model using Excel Solver and list the value of the objective function and the values for the decision variables in your Word report. You should specify that all variables are integer.

Time Slots & Minimum Number of Workers Needed

12 PM - 1 PM: 10 5 PM - 6 PM: 14
1 PM - 2 PM: 10 6 PM - 7 PM: 16
2 PM - 3 PM: 6 7 PM - 8 PM: 18
3 PM - 4 PM: 6 8 PM - 9 PM: 12
4 PM - 5 PM: 12 9 PM - 10 PM: 6


Solution Preview

The decision variables are the number of part-time and full-time employees starting their shifts on each hour (cells in yellow). The objective function is the total labor cost (in orange).

There are several constraints. First, the total number of employees on duty needs to be at least the numbers given in the table above. Second, at ...

Solution Summary

BW's food and drinks are examined for Excel Solver.